Zrzut danych z bazy do pliku tekstowego

Nieraz stajemy przed wyzwaniem jak sobie poradzić w przypadku braku jakiegoś narzędzia na naszym komputerze. Z doświadczenia wiem że w korporacjach komputery są dosyć rygorystycznie ograniczane pod kątem możliwości instalacji aplikacji, co może niestety dosyć utrudnić życie. Dlatego też trzeba często kombinować jak tu sobie poradzić w takiej ekstremalnej sytuacji. Dobrym przykładem moze być zrzut danych z bazy do pliku tekstowego. Do wielu baz danych są dostarczane odpowiednie narzędzia jak np. BCP.EXE albo SQLCMD.EXE do MSSQL-a. Problem w tym że trzeba te narzędzia zainstalować. Rozwiązaniem tego problemu może być prosty skrypt w VBS-e pobierający dane z bazy i zrzucający do pliku. Pozwoliłem sobie coś takiego napisać:

Dim aConn, sConn , aRs, sSQL 
Dim sPath
Dim oFld, sHeader, bHeader, sContent, sDelimiter
Dim sCharset
dim oArgs, oArg, sArg
dim oStdOut

Const adTypeText = 2
Const adSaveCreateOverWrite = 2

set oArgs=wscript.Arguments 
Set oStdOut = WScript.StdOut

sPath = ""
sCharset = "utf-8"
sDelimiter = ";"
bHeader = 0

For Each oArg In oArgs
 sArg = fGetParmName(oArg)
 select case sArg
  case "Sql", "S"
   sSQL = fGetParmValue(oArg)
  case "Path", "P"
   sPath = fGetParmValue(oArg)
  case "Conn", "C"
   sConn = fGetParmValue(oArg)
  case "Charset", "A"
   sCharset = fGetParmValue(oArg)
  case "Header" , "H"
   bHeader = fGetParmValue(oArg)
  case "Delimiter", "D" 
   sDelimiter = fGetParmValue(oArg)
 End Select
Next

On Error Resume Next
Err.Clear

Set aConn = CreateObject("ADODB.Connection")
aConn.Open sConn
If Err.Number <> 0 Then call sError

Set aRs = aConn.Execute(sSQL)
If Err.Number <> 0 Then call sError

aRs.MoveFirst
If Err.Number <> 0 Then call sError

if bHeader = "Yes" Then
 For Each oFld In aRs.Fields
  sHeader = sHeader & oFld.Name & sDelimiter
 Next
 sHeader = Left(sHeader, Len(sHeader) - 1) & Chr(13) & Chr(10)
End If

sContent = sHeader & aRs.GetString(, , sDelimiter)
If Err.Number <> 0 Then call sError

if sPath<> "" Then
 ExportToFile sPath, sContent
Else
 oStdOut.Write sContent
end if

aConn.Close
If Err.Number <> 0 Then call sError

set oStdOut = Nothing
Set aRs = Nothing
Set aConn = Nothing

function fGetParmName (sIn)
 fGetParmName= left(sIn, InStr(sIn,":") -1 )
 if left(fGetParmName,1) ="/" Then fGetParmName = mid(fGetParmName,2)
End Function

function fGetParmValue (sIn)
 fGetParmValue= mid(sIn, InStr(sIn,":") + 1 )
End Function

sub sError
 Wscript.Echo Err.Description
 On Error GoTo 0
 Err.Clear
 Wscript.Quit
End Sub

sub ExportToFile (sPath, sContent)
 Dim aStream 'As ADODB.Stream
 Set aStream = CreateObject("ADODB.Stream")
 With aStream
  .Open
  .Type = adTypeText
  .Charset = sCharset
  If Err.Number <> 0 Then call sError
  .Position = 0
  .WriteText sContent
  If Err.Number <> 0 Then call sError
  .SaveToFile sPath, adSaveCreateOverWrite
  If Err.Number <> 0 Then call sError   
 End With
 Set aStream = Nothing
End Sub

Skrypt ten można uruchomić w następujący sposób:

eksport.vbs /Sql:"SELECT * FROM dbo.v_struktura_akt" /Conn:"DRIVER=SQL Server Native Client 10.0;SERVER=MASZYNA;UID=username;Trusted_Connection=Yes;WSID=MASZYNA;DATABASE=baza_danych;LANGUAGE=polski;" /Path:"E:\Roboczy\wynik.csv"

dostępne są następujące parametry:

/SQL:"select * from tabela" - zapytanie które chcemy uruchomić
/Conn:"DRIVER=SQL Server....." - ciąg połączenia do bazy danych, zaletą tego rozwiązania jest to że możemy pobrać dane z praktycznie dowolnej bazy danych
/Path:"d:\katalog\plik.csv" - ścieżka do pliku w którym chcemy przechowywać wynik. W przypadku gdy nie podamy pliku wynik zostanie przekierowany do strumienia STDOUT
/Charset:"utf-8" - domyślny marametr strony kodowej w której zapiszemy plik. Standardowo jest utf-8, ale można zastosować dowolną stronę kodową obsługiwaną przez ADODB.Stream np. windows-1250
/Header:"Yes" - dodaje wiersz z nagłówkami
/Delimiter:";" - ustala znak podziału poszczególnych kolumn

Mała uwaga: jeżeli chcemy wyłączyć Banner w programie CSCRIPT

Microsoft (R) Windows Script Host Version 5.7
Copyright (C) Microsoft Corporation. All rights reserved.

Wykonajmy polecenie

cscript //NoLogo //S

Określenie daty zakończenia miesiąca w SQL-u

Dziś pokażę jak określić koniec miesiąca dla dowolnej daty. Wykorzystam tu pewną sztuczkę związaną z dodawaniem dat za pomocą funkcji DATEADD oraz wyciąganiem części składowych za pomocą funkcji YEAR i MONTH.

declare @dstop as datetime
set @dstop = getdate()
set @dstop =  dateadd(d,-1,dateadd(mm,1,convert(datetime,cast(year(@dstop) as nvarchar(4)) + '-' + RIGHT('0' + cast(MONTH(@dstop) as nvarchar(4)),2) + '-01',120)))

select @dstop

Algorytm postępowania jest następujący:
Wyciągamy Rok i miesiąc i na podstawie tego sklejamy datę określającą pierwszy dzień miesiąca
dodajemy miesiąc do tak otrzymanej daty
dodajemy -1 dzień do otrzymanej wcześniej sumy

Inna metoda to:

declare @dstop datetime
set @dstop = getdate() + 1
set @dstop =  dateadd(d,-day(dateadd(m,1,@dstop)),dateadd(m,1,@dstop))

select @dstop

Sposób ten zaprezentował kolega Bartosz Ślepowroński w tym wątku

Z powodzeniem ten sposób można zastosować w innych dialektach SQL np. JET

Coding Horror: A Visual Explanation of SQL Joins

Coding Horror: A Visual Explanation of SQL Joins

Bardzo proste wyjaśnienie istoty Joinów w SQL-u

Losowanie próbki danych z Tabeli Accessowej

Prezentuję dziś sztuczkę umożliwiająca pobranie określonej próbki danych z dowolnej tabeli lub kwerendy Accessowej.

Sztuczka ta polega na dodaniu sortowania po wartości losowej uzyskanej za pomocą funkcji Rnd.

SELECT TOP 25 PERCENT t.*
FROM Tabela1 as t
ORDER BY Rnd(t.[Identyfikator])*1;

gdzie:
Identyfikator - nazwa jakiegoś unikalnego identyfikatora będącego
cyfrą
Tabela1 - tabela którą w której losujesz
25 PERCENT - jest to informacja że chcemy pobierać 25 procent, jeżeli pominiemy słowo PERCENT, pobierzemy pierwszych 25 wartości

Współpraca MSSQL 2008 Express z pakietem Office

Zapraszam wszystkich do lektury materiału, którego jestem autorem.

Dokument OnLine

View more documents from Przemysław ....

Zaznaczam od razu że materiał ten może ewoluować  zgodnie z aktualnymi potrzebami odbiorców. Dlatego też zachęcam do dyskusji na temat treści w nim zawartych.

Tworzenie funkcji CLR bez drogiego środowiska Visual Studio

Dziś po chwili prób udało mi się stworzyć zewnętrzną funkcję dla bazy danych MSSQL 2005 za pomocą notatnika i kompilatora obsługiwanego z linii poleceń VBC.EXE. O ile samo stworzenie funkcji rozszerzającej możliwości bazy danych nie jest zbyt skomplikowane to zrobienie tego bez Visual Studio jest nieco karkołomne, gdyż w dzisiejszych czasach wszechobecnych kreatorów i szablonów możemy czuć się trochę zagubieni gdy ich nam zabraknie.

Parametryzacja ADO

Pracując z bazami danych uświadamiamy sobie w pewnym momencie, że pewne operacje powtarzają się lub wręcz są identyczne. Dużym ułatwieniem w takim wypadku może być parametryzacja zapytań przesyłanych do bazy zarówno tych stricte SQL-owych jak i procedur składowanych po stronie serwera.

Formater SQL

Dziś znalazłem świetne narzędzie wspomagające pracę:
http://www.dpriver.com/pp/sqlformat.htm
To fomater SQL-a (różne dialekty) z możliwością konstruowania kodu dla
rożnych języków programowania.
Przykład działania poniżej:

SELECT DISTINCT o.offerID as offerID_OR_categoryID,0 as searchType,'offer'
as attributeID,'porady' as word FROM Offers o LEFT JOIN OfferTagAssign ota
ON ota.offerID = o.offerID LEFT JOIN Tags t ON t.tagID = ota.tagID LEFT
JOIN OfferPromoTypeAssign opta ON opta.offerID = o.offerID WHERE
o.beginDatetimeNOW() AND ((o.label LIKE 'porady' OR o.label LIKE 'porady %'
OR o.label LIKE '% porady %' OR o.label LIKE '% porady') OR (t.tag LIKE
'porady' OR t.tag LIKE 'porady %' OR t.tag LIKE '% porady %' OR t.tag LIKE
'% porady') OR (o.offeror LIKE 'porady' OR o.offeror LIKE 'porady %' OR
o.offeror LIKE '% porady %' OR o.offeror LIKE '% porady')) UNION SELECT
o.offerID,1,a.attributeID,'
porady' FROM Offers o LEFT JOIN OfferAttr oa ON
oa.offerID = o.offerID LEFT JOIN Attributes a ON a.attributeID =
oa.attributeID LEFT JOIN AttributeTagAssign ota ON ota.attributeID =
a.attributeID LEFT JOIN Tags t ON t.tagID = ota.tagID LEFT JOIN
OfferPromoTypeAssign opta ON opta.offerID = o.offerID WHERE
o.beginDatetimeNOW() AND ((oa.valueText LIKE 'porady') OR (oa.valueFloat
LIKE 'porady') OR (oa.valueDate LIKE 'porady') OR (oa.valueTime LIKE
'porady') OR (oa.valueInt LIKE 'porady')) 

Po przepuszczeniu zaś wygląda tak:

SELECT DISTINCT o.offerid AS offerid_or_categoryid,
0         AS searchtype,
'offer'   AS attributeid,
'porady'  AS word
FROM   offers o
LEFT JOIN offertagassign ota
ON ota.offerid = o.offerid
LEFT JOIN tags t
ON t.tagid = ota.tagid
LEFT JOIN offerpromotypeassign opta
ON opta.offerid = o.offerid
WHERE  o.Begindatetimenow()
AND ((o.label LIKE 'porady'
OR o.label LIKE 'porady %'
OR o.label LIKE '% porady %'
OR o.label LIKE '% porady')
OR (t.tag LIKE 'porady'
OR t.tag LIKE 'porady %'
OR t.tag LIKE '% porady %'
OR t.tag LIKE '% porady')
OR (o.offeror LIKE 'porady'
OR o.offeror LIKE 'porady %'
OR o.offeror LIKE '% porady %'
OR o.offeror LIKE '% porady'))
UNION 
SELECT o.offerid,
1,
a.attributeid,
'
porady'
FROM   offers o
LEFT JOIN offerattr oa
ON oa.offerid = o.offerid
LEFT JOIN attributes a
ON a.attributeid = oa.attributeid
LEFT JOIN attributetagassign ota
ON ota.attributeid = a.attributeid
LEFT JOIN tags t
ON t.tagid = ota.tagid
LEFT JOIN offerpromotypeassign opta
ON opta.offerid = o.offerid
WHERE  o.Begindatetimenow()
AND ((oa.valuetext LIKE 'porady')
OR (oa.valuefloat LIKE 'porady')
OR (oa.valuedate LIKE 'porady')
OR (oa.valuetime LIKE 'porady')
OR (oa.valueint LIKE 'porady'))