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.

W klasycznym podejściu do sprawy zapytanie SQL-owe jest sklejane w stringu tekstowym kilku elementów oraz zmiennych reprezentujących parametry. Sporym problemem może być tutaj korzystanie z parametrów niebędących tekstem. Niejawna konwersja np. daty do tekstu po stronie serwera może doprowadzić do nieoczekiwanych efektów np. błędnego wybierania daty. Pośrednim rozwiązaniem tego problemu może być jawna konwersja, lecz ta skutecznie wydłuża kod SQL i czyni go zdecydowanie mniej przejrzystym.

Sub test_00()
Dim aConn As ADODB.Connection
Dim aRs As ADODB.Recordset
Dim sSql As String
Dim sPar(1) As String

Set aConn = New ADODB.Connection

sSql = "SELECT * from sys.objects where name = "
sPar(0) = Array("sysftinds", "sysprivs")

With aConn
.ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=master;Integrated Security=SSPI;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;"
.Open

Set aRs = .Execute(sSql & Chr(39) & sPar(0) & Chr(39))

Debug.Print aRs.Fields(0).Value

Set aRs = .Execute(sSql & Chr(39) & sPar(1) & Chr(39))

Debug.Print aRs.Fields(0).Value

.Close

End With

Set aConn = Nothing
Set aRs = Nothing

End Sub

Sytuację taka możemy rozwiązać np. za pomocą parametryzowanego SQL-a i obiektu Command. Operacja parametryzacji polega w zasadzie na wstawieniu znaków zapytania w odpowiednie miejsca zapytania a następnie dodaniu parametrów w dokładnie takiej samej kolejności jak wcześniej dodane znaki zapytania. Może wydawać się to dłuższe na pierwszy rzut oka, lecz całość jest zdecydowanie prostsza już przy kolejnym wywołaniu takiego zapytania. Rola programisty ogranicza się wtedy tylko do nadania nowych wartości stworzonym parametrom i ponownym wywołaniu.

Sub test_01()
Dim aConn As ADODB.Connection
Dim aComm As ADODB.Command
Dim aRs As ADODB.Recordset
Set aConn = New ADODB.Connection

With aConn
.ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=master;Integrated Security=SSPI;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;"
.Open

Set aComm = New ADODB.Command
With aComm
Set .ActiveConnection = aConn
.CommandText = "SELECT * from sys.objects where name = ? "
.CommandType = adCmdText
.Prepared = True
.Parameters.Append .CreateParameter("1", adVarChar, adParamInput, 200)

.Parameters("1").Value = "sysftinds"
Set aRs = .Execute

Debug.Print aRs.Fields(0).Value

.Parameters("1").Value = "sysprivs"
Set aRs = .Execute

Debug.Print aRs.Fields(0).Value

End With

.Close

End With

Set aConn = Nothing
Set aComm = Nothing
Set aRs = Nothing

End Sub

W przypadku korzystania z procedur sytuacja jest trochę inna. Istnieje Możliwość wykonania procedury za pomocą samego tylko stringa SQL-owego, wykorzystamy w takim przypadku komendę EXEC, zaś parametry możemy przekazać w postaci kolejnych elementów po nazwie procedury

EXEC usp_test 'sysprivs'

Możemy też przypisać konkretne wartości do wybranych parametrów

EXEC usp_test @name = 'sysprivs'

Rozwiązanie takie ma spore ograniczenia, np. przekazanie parametru będącego datą jest dosyć skomplikowane i może być obarczone błędem konwersji niejawnej oraz nie mamy dostępu do parametrów typu OUTPUT.

Wykonanie tej samej procedury za pomocą obiektu Command jest stosunkowo proste i wymaga w zasadzie tylko ustawienia odpowiedniego typu wysyłanej komendy. Dodatkowym plusem jest fakt że kolekcja parametrów jest tworzona na podstawie informacji pobranych z serwera (o ile mamy do tego uprawnienia, więcej informacji znajduje się w tym poście). Parametry mogą przekazywać dane w obu kierunkach – zależy to tylko od ich zdefiniowania na serwerze.

Sub test_02()
Dim aConn As ADODB.Connection
Dim aComm As ADODB.Command
Dim aRs As ADODB.Recordset
Set aConn = New ADODB.Connection

With aConn
.ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=master;Integrated Security=SSPI;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;"
.Open

Set aComm = New ADODB.Command
With aComm
Set .ActiveConnection = aConn
.CommandType = adCmdStoredProc
.CommandText = "usp_test"

.Parameters("@name").Value = "sysftinds"
Set aRs = .Execute

Debug.Print aRs.Fields(0).Value
Debug.Print .Parameters("@name_count").Value

.Parameters("@name").Value = "sysprivs"
Set aRs = .Execute

Debug.Print aRs.Fields(0).Value
Debug.Print .Parameters("@name_count").Value

End With

.Close

End With

Set aConn = Nothing
Set aComm = Nothing
Set aRs = Nothing

End Sub

Kod procedury SQL

CREATE PROCEDURE usp_test

@name nvarchar(200)
, @name_count int = Null OUTPUT

AS
BEGIN
SET NOCOUNT ON;

set @name_count = (select COUNT(*) from sys.objects where name = @name)

SELECT * from sys.objects where name = @name

END

Uwaga: Istotnym szczegółem jest kolejność ustawiania parametrów tak aby kolekcja
parametrów utworzyła się poprawnie. Tak jak w przykładzie pierwsze podpinamy
obiekt połączenia, następnie ustalamy typ zapytania na adCmdStoredProc i dopiero na
końcu ustawiamy nazwę procedury w jako parametr CommandText

Opisane tutaj metody działają z większością baz danych. osobiście sprawdzałem poprawność pracy z MySQL, MSSQL i Access

Konkluzją tego postu może być to, że sklejanie SQL-a nie jest jedyną drogą do dynamizacji zapytań. Dokładnie ten sam efekt nawet z większymi możliwościami uzyskamy za pomocą parametryzacji zapytań.