Iloczyn kartezjański w praktycznym przykładzie

Prosty przykład wykorzystania iloczynu kartezjańskiego do uzyskania wszystkich możliwych kombinacji ze zbioru danych.

Szybkie sprawdzenie czy czy istnieje tabela o podanej nazwie

ADODB daje nam szereg możliwości. Jedną z nich jest możliwość pobrania informacji o strukturze bazy do której się podłączyliśmy. Przypadkiem szczególnym takich baz są bazy plikowe czyli popularne pliki mdb i accdb. Przypadkiem jeszcze bardziej szczególnym zaś są pliki Excel-a które można traktować jak pliki bazodanowe.

Po podłączeniu do do tkiego pliku wystarczy uruchomić jedną metodę aby uzyskać pełen komplet informacji na temat tego zo znajduje się w środku a co najważniejsze nie musimy takiego pliku otwierać za pomocą Excel-a co mogło by być naprawdę czasochłonne.

Metoda o której mówię to OpenSchema, zaś parametr odpowiadający za pobranie informacji o tabelach to: adSchemaTables.

Przykładowy skrypt wykorzystujący ta metodę:

Function GetTablesFromDatabase(Plik As String, Tabela As String) As Boolean
    
    Dim aRs As ADODB.Recordset
    Dim aConn As ADODB.Connection
    Dim sConn As String
    Dim e As Long
    Dim ext As String
    
    e = InStrRev(Plik, ".")
    ext = Right(Plik, Len(Plik) - e)
    
    Select Case ext
        Case "xls"
            sConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Plik & "; Extended Properties =""Excel 8.0;HDR=Yes;IMEX=1"";"
        Case "xlsx"
            sConn = "Provider =Microsoft.ACE.OLEDB.12.0; Data Source =" & Plik & "; Extended Properties =""Excel 12.0 Xml;HDR=YES"";"
        Case "mdb"
            sConn = "Provider =Microsoft.Jet.OLEDB.4.0; Data Source =" & Plik & " ; User Id =admin; Password =;"
        Case "accdb"
            sConn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" & Plik & ";"
    End Select

On Error GoTo ERR_Handler:

    Set aConn = New ADODB.Connection
    With aConn
        .Mode = adModeShareDenyNone
        .CursorLocation = adUseServer
        .ConnectionString = sConn
        .Open
    
        Set aRs = aConn.OpenSchema(adSchemaTables)
    
        aRs.MoveFirst
        aRs.Filter = "TABLE_NAME='" & Tabela & "'"
    
        Do While Not aRs.EOF
            If aRs.Fields("TABLE_NAME").Value = Tabela Then
                GetTablesFromDatabase = True
                Exit Do
            End If
            aRs.MoveNext
        Loop
    
        .Close
    End With
    
    Exit Function
    
ERR_Handler:

    MsgBox Err.Description
    If aConn.State > 0 Then
        aConn.Close
    End If
    
End Function

Przykładowe wykorzystanie

Sub test()
    Debug.Print GetTablesFromDatabase("E:\Dane\user\Moje Dokumenty\zeszyt1.xls", "Arkusz1$")
End Sub

Uzyskujemy w ten sposób informację o tym czy dany arkusz istnieje w bazie danych czy też nie.

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

Automatyczne łączenie aplikacji i bibliotek w jeden plik podczas kompilacji

Zastanawiałem się w jaki sposób mógł bym sprawić żeby mój projekt odwołujący się do kilku bibliotek zewnętrznych nie był zbieraniną plików a jednym wygodnym do użycia plikiem. Głównym motywatorem takiego działania jest fakt że to aplikacji przeznaczona do pracy z linii poleceń.

Żeby wykonać scalić wszystkie pliki w jedną całość skorzystałem z narzędzia ILMerge.exe dostępnego na stronach Microsoft. Po ściągnięciu i zainstalowaniu możemy korzystać z tego narzędzia standardowo za pomocą linii poleceń i np. wykonać komendę:

ilmerge /target:exe /out:Aplikacja.exe 
        Program.exe ClassLibrary1.dll ClassLibrary2.dll

Dzięki takiej operacji z trzech plików robi nam się jeden, ale tu uwaga - parametr /target:exe wskazuje na to że plik wynikowy będzie uruchamiany z linii poleceń, można też użyć opcji /target:winexe - dzięki czemu stworzymy aplikację okienkowa lub /target:library - dzięki czemu stworzymy bibliotekę.

Oczywiście takie łączenie da nam jak najbardziej prawidłowy plik, lecz na dłuższą metę nie jest to wygodne. Rozwiązaniem było by takie zmodyfikowanie naszego projektu aby łączenie nastąpiło automatycznie po skomplikowaniu projektu. Taką możliwość możemy uzyskać dzięki ręcznej modyfikacji pliku projektu *.vbproj polegającej na dodaniu następującej sekcji:

http://pastebin.com/L1eGGKYP

Taka konstrukcja sprawi że po wykonaniu opeacjii Build zostanie wykonana komenda ILMerge z parametrami. Dodatkowo dodałem parametr /targetplatform:v4,C:\Windows\Microsoft.NET\Framework\v4.0.30319 sprawiający że ILMerge bedzie działał z .NET w wersji 4.0

Dodanie takiej sekcji działa również w pliku projektu stworzonego w wersji Visual Studio Express .

Wielokrotny wybór w dialekcie JET SQL (Access)

Często zdarza mi się korzystać z konstrukcji IIF(warunek,wartość dla prawda,wartość dla fałsz) z pozycji kwerend w Access-e. Taka przykładowa kwerenda mogła by wyglądać np. tak:

SELECT t1.pole, IIF(t2.pole_w = 'TAK', 1,0) as FLAGA 
FROM TABELA_1 as t1 join TABELA_2 as t2 on t1.id = t2.id

Problem zaczyna się w momencie gdy musimy sprawdzić kilka niezależnych warunków i na podstawie takiego wyboru określić wynik końcowy. Oczywiście można zagnieżdżać IIF-y, ale to nie jest ani ładne, ani czytelne a tym bardziej wygodne. Idealnym rozwiązaniem było by zastosowanie CASE WHEN .... THEN .... ELSE END, ale JET SQL nie udostępnia nam bezpośrednio tego typu rozwiązania. Daje nam za to coś podobnego i działającego de fakto tak samo. jest to instrukcja SWITCH. Nasz wcześniejszy przykład możemy sobie rozbudować np. tak

SELECT t1.pole
, SWITCH(t2.pole_w = 'TAK', 1, t2.pole_w = 'NIE' ,0, t2.pole_w = 'NIE WIEM', -1, t2.pole_w = 'INNA ODPOWIEDŹ', -2) as FLAGA 
FROM TABELA_1 as t1 join TABELA_2 as t2 on t1.id = t2.id

W tym momencie jak widzimy mamy kilka opcji bez zbędnego komplikowania, ale to nie koniec możliwości ;)

SELECT t1.pole
, SWITCH(t2.pole_w = 'TAK', 1, t2.pole_w = 'NIE' ,0, t2.pole_w = 'NIE WIEM', -1, t2.pole_w = 'INNA ODPOWIEDŹ', -2, t2.pole_w in ('A','B','C'), 'ABC' , 1=1, 'COKOLWIEK INNEGO') as FLAGA 
FROM TABELA_1 as t1 join TABELA_2 as t2 on t1.id = t2.id

t2.pole_w in ('A','B','C') - to klasyczne sprawdzenie czy element jest w podanym zbiorze
1=1 - to ostatni warunek logiczny będący zawsze prawdą i wykonany w momencie gdy którykolwiek z wcześniejszych nie został uwzględniony.

I tu małą uwaga: jeżeli SWITCH natrafi na kryterium które jest spełnione to zwraca wartość i kończy sprawdzanie.

Jako ciekawostkę podam że w instrukcji SWITCH można jako zbiór danych podzapytanie np.

t2.pole_w in (SELECT KLUCZ FROM SLOWNIK)

a jak znam życie to pewnie można też dać podzapytanie skorelowane, ale sprawdzenie tego pozostawiam już czytelnikom

Mapowanie dysku sieciowego z pozycji T-SQL-a

Dziś pokażę w jaki prosty sposób zmapować dysk sieciowy z poziomu MSSQL-a. Jest to szczególnie przydatne w momencie jak chcemy pobrać jakiś plik ze zdalnego serwera zabezpieczonego za pomocą loginu i hasła np. w celu załadowania za pomocą BULK INSERT

xp_cmdshell 'cmd /c IF EXIST W: net use W: /DELETE'
xp_cmdshell 'cmd /c net use W: \\maszyna\udzial haslo /USER:maszyna\login /PERSISTENT:YES'

 BULK INSERT dbo.tabela
   FROM 'W:\plik.txt'
   WITH
   (
   FIELDTERMINATOR =';',
   ROWTERMINATOR ='\n',
   FIRSTROW = 2,
   CODEPAGE = 1250
   )

jeżeli z jakiegoś powodu nie chcemy angażować dysku sieciowego, to wykorzystamy UNC

xp_cmdshell 'cmd /c IF EXIST \\maszyna\udzial net use \\maszyna\udzial /DELETE'
xp_cmdshell 'cmd /c net use W: \\maszyna\udzial haslo /USER:maszyna\login /PERSISTENT:YES'

 BULK INSERT dbo.tabela
   FROM '\\maszyna\udzial\plik.txt'
   WITH 
   (
   FIELDTERMINATOR =';',
   ROWTERMINATOR ='\n',
   FIRSTROW = 2,
   CODEPAGE = 1250
   )

Synchronizacja tabeli z polem w Excelu

Taka mała magiczna sztuczka dla tych, którzy by chcieli bez użycia kodu VBA zsynchronizować tabelę w Excelu z filtrami umieszczonymi w polach.