Yaslaw.Info

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
<const>
    version=1.5.4	
    vdate=04.09.2019
    fname=sqlscript.cls
    ns=%NAMESPACE%    
    fpath=/vba/access/classes
</const>

====== [VBA][Access] Klasse SQLScript ======
//Eine Klasse um SQL-Scripts in seine einzelnen Befehle zu zerteilen und diese auszuführen. Diverse DML, DDL, und DCL werden unterstützt.//

==Version %%version%% (%%vdate%%)==
<WRAP round download 50%>
><fc #808080>//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.//</fc>
>{{popup>:vba:vba_importfile.png|Bild zum Import}}

{{%%ns%%:%%fname%%|Download %%fname%% (V-%%version%%)}}
</WRAP>
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.
===== Informationen zur SQL Console =====

==== Links zu weiteren Dokumentationen ====

=== Im Code verwendeteFunktionen/Klassen ===
  * [[vba:access:functions:printrs]] Eine Funktion um Datensätze als Plaintext auszugeben
  * [[vba:functions:printf:index]] sprintf() für VBA.

=== Weiterführnede Seiten ===
  * [[vba:access:sqlscript:scriptexamples]] Diverse Beispiele zu den SQL-Befehlen
  * [[vba:access:sqlscript:sqlconsole]] Eine SQL-Konsole für MS Access basierend auf der SQLScript Klasse

===== Definitionen =====
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]]

{{ :vba:access:containerprinzip.png?nolink |}}


==== Public Enumeratoren ====
=== sqlType ===
Der SQL-Type.
>DQL (Data Query Language) wird als DCL gehandhabt

  * **stNA** N/A
  * **stDML** Data Manipulation Language (DML, deutsch „Datenverarbeitungssprache“): Sprache oder Sprachteile für das Abfragen, Einfügen, Ändern oder Löschen von Nutzdaten
  * **stDDL** Data Definition Language (DDL, deutsch „Datenbeschreibungssprache“): Sprache oder Sprachteile für das Anlegen, Ändern und Löschen von Datenstrukturen
  * **stDCL** Data Control Language (DCL, deutsch „Datenaufsichtssprache“): Sprache oder Sprachteile für die Zugriffskontrolle

=== sqlActions ===
Dieser Enumerator beschreibt, um was ür ein SQL-Script es sich handelt
  * **saAutomatic** Die Action ist nicht definiert und soll ermittelt werden
  * **saContainer** Das Objekt ist ein Container. Also die Liste mit den versch. SQLs
  * //**DDL** Data Definition Language (DDL, deutsch „Datenbeschreibungssprache“)//
    * **saCreateView** Abfrageerstellungsscript
    * **saCreate** Objekterstellungsscript
    * **saAlter** Ändern eines Objektes (Tabelle, Index etc)
    * **saDrop** Löschen eines Objektes (Tabelle, Index View, etc)
  * //**DML** Data Manipulation Language (DML, deutsch „Datenverarbeitungssprache“)//
    * **saUpdate**
    * **saInsertOnDuplicateUpdate** Spezialfall INSERT INTO ... ON DUPLICATE KEY UPDATE ....
    * **saSelectInto** SELECT INTO. Das erstellen einer Tabelle aus einem SELECT
    * **saInsert**
    * **saDelete**
  * //**DCL** Data Control Language (DCL, deutsch „Datenaufsichtssprache“)//
    * **saSelect** Ein einfaches Select als RS zurückgeben
    * **saSelectWithParams** Ein einfaches Select mit Parametern als RS zurückgeben
    * **saShow** Ausgeben von Auflistungen von Objekten
    * **saShowIn**  Spezialform von saShow
    * **saShowObjects** Spezialform von saShow für Objekte
    * **saShowVariables**  Spezialform von saShow für Variablen
    * **saSet** Setzt Variablen
    * **saClearCache** löscht alle oder einzelne Variabeln
    * **saPrompt** Zurückgeben eines Textes
  * **saDirect** Alle nicht definierten werden direkt ausgeführt

=== objectType ===
Objekttypen die von Aktionen betroffen sein können werden mit dem Public Property affectedType ausgegeben
  * **soTable** Tabelle
  * **soQueryDef** Abfrage
  * **soIndex** Index einer Tabelle
  * **soParams** SQL-Variable

=== sqlParams ===
Action. Wie soll sich das Script verhalten. Dieser Enum ist kombinierbar spDirect+spOverwrite
  * **spNone**
  * **spDirect** Ohne nachfragen ausführen
  * **spOverwrite** Bestehendes Objekt ohne Nachfragen überschreiben
  * **spIgnore** Fehler ignorieren und weiterfahren
  * **spLogText** Gibt anstelle des [[#returnValue]] gleich den genierten [[#logText]] zurück

=== text2ddlParams ===
Parameter um die Text-zu-TableCreate zu steuern
  * **tdAutomatic**
  * **tdNoHeader ** Die Erste Zeile ist keine Kopfzeile
  * **tdSuppressDashLines** Linien die nur aus -_=| und Leerzeichen bestehen werden unterdrückt. Das werden Trennlinien sein
  * **tdDropTable ** Erstelle zum  CREATE TABEL auch ein DROP TABLE

==== Creatoren ====

=== instanceByFileDialog() ===
>Container
<code>Set container = SQLScript.instanceByFileDialog([filePath])</code>
Erstellt ein Container aus einer Datei, die mittels dem FileDialog ausgewählt wird
<code vb>'/**
' * 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</code>

=== instanceByFilePath() ===
>Container
<code>Set container = SQLScript.instanceByFilePath(filePath)</code>
Erstellt ein Container aus einer Datei. Der Datepafad muss bekannt sein.
<code vb>'/**
' * 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</code>

=== instanceByTableText() ===
>Container
<code>script = SQLScript.instanceByTableText(text, tabellenName [,Delemiter]</code>
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
<code vb>'/**
' * 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</code>

=== instanceSubScript()===
>Statement
<code>script = SQLScript.instanceSubScript(command [,action])</code>
Erstellt eine Instance für ein einzelnes Script aus einem SQL-Statement heraus.
\\ Siehe Beispiel [[#Ein einsames Script erstellen und ausführen]]
<code vb>'/**
' * 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</code>

==== Wichtigste Methoden ====
=== execute ===
>Container und Statement
<code>result = container.execute([paramters] [,action])</code>
Ist das Objekt ein Container, werden alle Scripte in dem Container ausgeführt.
\\ Der Rückgabewert ist je nach [[#action]] unterschiedlich.
<code vb>'/**
' * 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</code>

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 |

=== readFile() ===
>Container
<code>container.readFile filePath</code>
Liest eine Scriptdatei in ein bestehenden Container
<code vb>'/**
' * Eine Datei einlesen und auswerten
' * @param  String  Dateipfad
' */
Public Sub readFile(ByVal iFilePath As String)</code>

=== readText() ===
>Container
<code>container.readText sql-Script-String</code>
Parst ein SQL-Script-String. Zerlegt ihn in einzelne Scripte und erstellt im Conatianer jeweils ein Script-Objekt 
<code vb>'/**
' * Ein Script verarbeiten
' * @param  String      ScriptText (mehrere Scripts)
' */
Public Sub readText(ByVal iScriptText As String)</code>

==== Wichtigste Properties ====
=== action ===
Gibt die Art des Scriptes zurück. Also, was für eine Aktion ausgeführt wird. Der Rückgabewert ist vom Type [[#sqlActions]]
<code sql>Public Property Get action() As sqlActions</code>

=== affectedItem ===
Vom Script betroffenes Objekt. Tabellen-, Abfrage-, Indexnamen etc.
<code vb>Public Property Get affectedItem() As String</code>

=== affectedType ===
Der Objekttyp, der vom Script betroffen ist. Der Rückgabewert ist vom Type [[#objectType]]
<code vb>Public Property Get affectedType() As objectType</code>

=== sqlType ===
Die Information um was für eine Art Script es sich handelt. Der Rückgabewert ist vom Type [[#sqlType]]
<code vb>Public Property Get sqlType() As sqlType</code>

=== count ===
Wenn die Instanz ein Container ist, gibt count die Anzahl Scripte zurück. Ansonsten -1
<code vb>Public Property Get count() As Long</code>

=== returnValue ===
Der Rückgabewert von Execute kann über dieses Property nachträglich nochmals ausgelesen werden
<code vb>Public Property Get returnValue() As Variant</code>

=== logText ===
Gibt einen Text zurück, der die ausgeführten Aktionen dokumentiert
<code vb>Public Property Get logText() As String</code>

=== filePath ===
Der pFad des Scriptfiles. Macht ntürlich auch nur bei einem Containerobjekt Sinn
<code vb>Public Property Get filePath() As String</code>

=== script ===
Gibt das SQL-SQL-Script zurück. Mit ; am Ende. Im Falle eines Containers wird das ganze Script ausgegeben
<code vb>Public Property Get script() As String</code>

==== verwendete Libraries ====
Die folgenden Funktionen müssen seperat geladen werden, damit die Klasse SQLScript funktioniert.
Sie sind zu gross um Sinnvoll in die Klasse zu kopieren.
  * [[vba:functions:printf:index]]: Funktion sprintf() ist nützlich um Texte zu parsen


===== Beispiele =====
>Für die Ausgabe der %%QueryDefs%% und der Recordsets verwende ich die Funktion [[vba:access:functions:printrs]]

==== Programm Beispiele ====
=== Einfaches Ausführen einer Script-Datei ===
Eine Script-Datei öffnen und alle Script ausführen
<code vb>Dim scriptContainer As SQLScript
Set scriptContainer = SQLScript.instanceByFilePath("C:\temp\sql\vba_sql_test2.sql")
scriptContainer.execute</code>

=== Öffnen einer Script-Datei und die SQL-Statements einzeln ausführen===
Jeder Befehl einzeln ausführen und die [[#action]] und [[#sqlType]] ausgeben
<code vb>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
</code>

=== Ein einsames Script erstellen und ausführen ===
Dank der Instance-Funktionen kann man einfache Scripte ohne grossen Codeaufwand ausführen. Beide folgenden Script haben dasselbe Resultat. (Im Beispiel wird [[vba:access:functions:printrs]] verwendet)
<code vb>printRs SQLScript.instanceSubScript("SHOW TABLES").execute()
debug.print SQLScript.instanceSubScript("SHOW TABLES").execute(spLogText)</code>
<code>| 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</code>

=== Manuelles Ausführen einer Scriptdatei ohne GUI ===
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.
<code vb>MsgBox SQLScript.instanceByFileDialog().execute(spLogText)</code>

=== Manuell erstellter Container ===
Ein Container kann auch Manuell gefüllt werden. Hier ein Beispiel in dem zuerst ein SET ausgeführt wird und anschliessend ein SELECT.
<code vb>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</code>
<code> 123 
| a   |
|-----|
| 123 |
| variable_name | type | value |
|---------------|------|-------|
| A             | BYTE | 123   |</code>

=== Text in Tabellenscript wanden ===
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.
<code>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      ,           ,            </code>

Und heir die Umsetzung für ein kleinen Test:
<code vb>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</code>
<code sql>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</code>

==== Script Beispiele ====
Siehe [[vba:access:sqlscript:scriptexamples]]

===== Code =====
<WRAP round download>
><fc #808080>//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.//</fc>
>{{popup>:vba:vba_importfile.png|Bild zum Import}}

{{%%ns%%:%%fname%%|Download %%fname%% (V-%%version%%)}}
</WRAP>
Yaslaw.Info

User Tools

Site Tools

Page Tools
