Eine Klasse um SQL-Scripts in seine einzelnen Befehle zu zerteilen und diese auszuführen. Diverse DML, DDL, und DCL werden unterstützt.
Das Modul hat versteckte Attribute. Damit diese aktiv übernommen werden reicht es nicht aus, den Code in ein neues Modul zu kopieren. Man muss das Modul aus der Datei nach VBA importieren.
Bild zum Import
Diese Klasse parst ein SQL-Scripte mit verschiedenen SQL-Befehlen. Einerseits ist sie ein Kontainer mit verschiedenen Befehlen drin, anderseits kann sie ein einzelner Befehl sein. Die einzelnen Befehle können auf die Datenbank angewendet werden.
Die Klasse SQLScript ist einerseits ein Container mit verschiedenen Befehlen, anderseits auch ein Befehl selber. Je nach der Eigenschaft des Property action.
Am besten sieht man das im Beispiel Öffnen einer Script-Datei und die SQL-Statements einzeln ausführen
Der SQL-Type.
DQL (Data Query Language) wird als DCL gehandhabt
Dieser Enumerator beschreibt, um was ür ein SQL-Script es sich handelt
Objekttypen die von Aktionen betroffen sein können werden mit dem Public Property affectedType ausgegeben
Action. Wie soll sich das Script verhalten. Dieser Enum ist kombinierbar spDirect+spOverwrite
Parameter um die Text-zu-TableCreate zu steuern
Container
Set container = SQLScript.instanceByFileDialog([filePath])
Erstellt ein Container aus einer Datei, die mittels dem FileDialog ausgewählt wird
'/** ' * Erstellt ein DDLScriptcontainer aus einem File, das über den Filedialog ausgewählt wurde ' * @example Öffnen des FileDialoges mit direktem ausführen des Codes ' * Call SQLScript.instanceByFileDialog().execute ' * @param String Der Pfad/Dateiname, wo der Dialog öffnet ' * @retrun SQLScript oder bei Abrruch Nothing ' */ Public Static Function instanceByFileDialog(Optional ByVal iFilePath As String = Empty) As SQLScript
Container
Set container = SQLScript.instanceByFilePath(filePath)
Erstellt ein Container aus einer Datei. Der Datepafad muss bekannt sein.
'/** ' * Erstellt ein DDLScriptcontainer aus einem File, das über einen direkten Pfad geöffnet wird ' * @example Öffnen und ausführen des ersten SQLs aus einer Date ohne Nachfrage ' * SQLScript.instanceByFilePath("C:\temp\vba_sql_test.sql")(0).execute(spDirect) ' * @param String Der Pfad/Datreiname ' * @retrun SQLScript ' */ Public Static Function instanceByFilePath(ByVal iFilePath As String) As SQLScript
Container
script = SQLScript.instanceByTableText(text, tabellenName [,Delemiter]
Diese Methode unterscheidet sich von den Anderen. Die Eingabe ist hier kein SQL, sondern eine Tabelle als Text in einer lesbarer form. Zum Beispiel als CSV
'/** ' * Erstellt ein DDLScriptcontainer aus einem Text, der eine Tabelle darstellt ' * @param String Tabelle in Textform. zB. CSV ' * @param String Tabellenname ' * @param String Mögliche Spaltendelemiter als RegExp-Pattern ' * @param text2ddlParams Steuerparameter ' * @param String Eine seperate Kopfzeile. Wenn die Kopfzeile bereits im iText ist, ' * muss sie nicht seperat mitgegeben werden. NoHeader wird ignoriert ' * @retrun SQLScript ' */ Public Function instanceByTableText( _ ByVal iText As String, _ ByVal iTableName As String, _ Optional ByVal iDelemiterRxPattern As String = "[,|;\t]", _ Optional ByVal iParams As text2ddlParams = tdSuppressDashLines, _ Optional ByVal iHeader As String = Empty _ ) As SQLScript
Statement
script = SQLScript.instanceSubScript(command [,action])
Erstellt eine Instance für ein einzelnes Script aus einem SQL-Statement heraus.
Siehe Beispiel Ein einsames Script erstellen und ausführen
'/** ' * Erstellt eine Instanze eines neuen Subscriptes ' * @param String SQL-String ' * @param sqlActions Art des Scriptes. Bei -1 wird die Action ermittelt ' * @retrun SQLScript ' */ Public Static Function instanceSubScript(ByVal iCmd As String, Optional ByVal iAction As sqlActions = saAutomatic) As SQLScript
Container und Statement
result = container.execute([paramters] [,action])
Ist das Objekt ein Container, werden alle Scripte in dem Container ausgeführt.
Der Rückgabewert ist je nach action unterschiedlich.
'/** ' * Führt anhand der Action das Script aus ' * @param sqlParams ' * @param ddlAction (OUT) Angabe, um was für eine Action es sich wirklich handelt ' * @return Variant Je nach Script unterschiedlich. Recordset, Value, QueryDef, Boolean. ' * Wenn als Paramter spOutText ist es ein lesbaerer Rückgabestring ' */ Public Function execute(Optional ByVal iParams As sqlParams = spNone, Optional ByRef oAction As sqlActions) As Variant
Liste der Rückgabetypen nach Action
Action | Rückgabewert | Beschreibung |
---|---|---|
saSelect | QueryDef | |
saSelectWithParams | QueryDef | |
saShow | ADODB.Recordset | |
saShowIn | ADODB.Recordset | |
saShowObjects | ADODB.Recordset | |
saShowVariables | ADODB.Recordset | |
saPrompt | String | Auszugebender Text |
saSet | Variant | Wert des Set-Befehls |
saClearCache | String | Name der Variable oder Empty falls keine Variable mitgegebn wurde |
saCreateView | Boolean | |
saCreate | Boolean | |
saDrop | Boolean | |
saAlter | Boolean | |
saInsertOnDuplicateUpdate | Long | Anzahl betroffener Zeilen |
saSelectInto | Long | Anzahl betroffener Zeilen |
saInsert | Long | Anzahl betroffener Zeilen |
saUpdate | Long | Anzahl betroffener Zeilen |
saDelete | Long | Anzahl betroffener Zeilen |
Else | Long | Boolean |
Container
container.readFile filePath
Liest eine Scriptdatei in ein bestehenden Container
'/** ' * Eine Datei einlesen und auswerten ' * @param String Dateipfad ' */ Public Sub readFile(ByVal iFilePath As String)
Container
container.readText sql-Script-String
Parst ein SQL-Script-String. Zerlegt ihn in einzelne Scripte und erstellt im Conatianer jeweils ein Script-Objekt
'/** ' * Ein Script verarbeiten ' * @param String ScriptText (mehrere Scripts) ' */ Public Sub readText(ByVal iScriptText As String)
Gibt die Art des Scriptes zurück. Also, was für eine Aktion ausgeführt wird. Der Rückgabewert ist vom Type sqlActions
Public Property GET action() AS sqlActions
Vom Script betroffenes Objekt. Tabellen-, Abfrage-, Indexnamen etc.
Public Property Get affectedItem() As String
Der Objekttyp, der vom Script betroffen ist. Der Rückgabewert ist vom Type objectType
Public Property Get affectedType() As objectType
Die Information um was für eine Art Script es sich handelt. Der Rückgabewert ist vom Type sqlType
Public Property Get sqlType() As sqlType
Wenn die Instanz ein Container ist, gibt count die Anzahl Scripte zurück. Ansonsten -1
Public Property Get count() As Long
Der Rückgabewert von Execute kann über dieses Property nachträglich nochmals ausgelesen werden
Public Property Get returnValue() As Variant
Gibt einen Text zurück, der die ausgeführten Aktionen dokumentiert
Public Property Get logText() As String
Der pFad des Scriptfiles. Macht ntürlich auch nur bei einem Containerobjekt Sinn
Public Property Get filePath() As String
Gibt das SQL-SQL-Script zurück. Mit ; am Ende. Im Falle eines Containers wird das ganze Script ausgegeben
Public Property Get script() As String
Die folgenden Funktionen müssen seperat geladen werden, damit die Klasse SQLScript funktioniert. Sie sind zu gross um Sinnvoll in die Klasse zu kopieren.
Für die Ausgabe der QueryDefs und der Recordsets verwende ich die Funktion [VBA][Access] printRs()
Eine Script-Datei öffnen und alle Script ausführen
Dim scriptContainer As SQLScript Set scriptContainer = SQLScript.instanceByFilePath("C:\temp\sql\vba_sql_test2.sql") scriptContainer.execute
Jeder Befehl einzeln ausführen und die action und sqlType ausgeben
Dim scriptContainer As SQLScript Dim script As SQLScript Set scriptContainer = SQLScript.instanceByFilePath("C:\temp\sql\vba_sql_test2.sql") For Each script In scriptContainer script.execute Debug.Print script.sqlType, script.action Next script
Dank der Instance-Funktionen kann man einfache Scripte ohne grossen Codeaufwand ausführen. Beide folgenden Script haben dasselbe Resultat. (Im Beispiel wird [VBA][Access] printRs() verwendet)
printRs SQLScript.instanceSubScript("SHOW TABLES").execute() debug.print SQLScript.instanceSubScript("SHOW TABLES").execute(spLogText)
| table_name | row_count | last_update | indexes | |-----------------------------|-----------|---------------------|--------------------------------------------| | ADDON_SQL_CONSOLE_SESSION | 19 | 13.04.2015 12:57:28 | ID, PrimaryKey (PK) | | t_sql_test | 1 | 17.04.2015 10:08:24 | idx_sql_val, Index_F44530CA_8088_4FED (PK) | | tbl_t1 | 2 | 13.04.2015 12:09:01 | idx_val, Index_B4C4285A_B98F_4415 (PK) | | TODO: ADDON_SQL_CONSOLE_LOG | 0 | 13.04.2015 12:26:40 | PrimaryKey (PK), SESSION_ID, STEP_ID
Und hier noch ein Beispiel mit FileDialog und MsgBox um eine Scriptdatei auszuführen. Dazu verwende ich einfach die Instanzierung über instanceByFileDialog] und die Ausgabe von execute mit dem sqlParams spLogText.
MsgBox SQLScript.instanceByFileDialog().execute(spLogText)
Ein Container kann auch Manuell gefüllt werden. Hier ein Beispiel in dem zuerst ein SET ausgeführt wird und anschliessend ein SELECT.
Dim scriptContainer As New SQLScript 'Neu initialisierte Objekte werden automatisch als Container definiert 'Dies könnte über den action-Parameter übersteuert werden With scriptContainer .addNewScript "SET a = 123" .addNewScript "SELECT a" .addNewScript "SHOW VARIABLES" Debug.Print .item(0).execute printRs .item(1).execute printRs .item(2).execute End With
123 | a | |-----| | 123 | | variable_name | type | value | |---------------|------|-------| | A | BYTE | 123 |
Mit der Methode instanceByTableText() kann ein Tabelle in Textform in ein Create Table - & Insertscript konvertiert werden.
Häufig habe ich für Test Tabellen in der Textform oder einen ähnlichen.
ID, ITEM_NAME, ITEM_VALUE, CREATE_DATE ====================================== 1, ABC , 123 , 1.1.1967 2, DEF , 346.3 , 11.12.2010 3, GHI , 10098 , 17.11.2016 4, JKL , ,
Und heir die Umsetzung für ein kleinen Test:
Sub testSqlScript() Dim script As SQLScript Dim txt(5) As String txt(0) = "ID, ITEM_NAME, ITEM_VALUE, CREATE_DATE" txt(1) = "======================================" txt(2) = " 1, ABC , 123 , 1.1.1967 " txt(3) = " 2, DEF , 346.3 , 11.12.2010 " txt(4) = " 3, GHI , 10098 , 17.11.2016 " txt(5) = " 4, JKL , , " Set script = SQLScript.instanceByTableText(Join(txt, vbCrLf), "TBL_TEST", , tdDropTable + tdSuppressDashLines) Debug.Print script.script script.execute spIgnore Debug.Print script.logText End Sub
DROP TABLE [TBL_TEST]; CREATE TABLE [TBL_TEST] ([ID] BYTE,[ITEM_NAME] TEXT(3),[ITEM_VALUE] DOUBLE,[CREATE_DATE] DATE); INSERT INTO [TBL_TEST] ([ID],[ITEM_NAME],[ITEM_VALUE],[CREATE_DATE]) VALUES (1,'ABC',123,#01-01-1967 00:00:00#); INSERT INTO [TBL_TEST] ([ID],[ITEM_NAME],[ITEM_VALUE],[CREATE_DATE]) VALUES (2,'DEF',346.3,#12-11-2010 00:00:00#); INSERT INTO [TBL_TEST] ([ID],[ITEM_NAME],[ITEM_VALUE],[CREATE_DATE]) VALUES (3,'GHI',10098,#11-17-2016 00:00:00#); INSERT INTO [TBL_TEST] ([ID],[ITEM_NAME],[ITEM_VALUE],[CREATE_DATE]) VALUES (4,'JKL',NULL,NULL); TABLE droped TABLE created INSERT ROWS INSERT ROWS INSERT ROWS INSERT ROWS
Das Modul hat versteckte Attribute. Damit diese aktiv übernommen werden reicht es nicht aus, den Code in ein neues Modul zu kopieren. Man muss das Modul aus der Datei nach VBA importieren.
Bild zum Import