User Tools

Site Tools


vba:access:classes:sqlscript

[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 1.5.2 (18.01.2017)
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

Download sqlscript.cls (V-1.5.2)

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

Im Code verwendeteFunktionen/Klassen

Weiterführnede Seiten

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

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
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

instanceByFilePath()

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

instanceByTableText()

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

instanceSubScript()

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

Wichtigste Methoden

execute

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

readFile()

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)

readText()

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)

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

Public Property GET action() AS sqlActions

affectedItem

Vom Script betroffenes Objekt. Tabellen-, Abfrage-, Indexnamen etc.

Public Property Get affectedItem() As String

affectedType

Der Objekttyp, der vom Script betroffen ist. Der Rückgabewert ist vom Type objectType

Public Property Get affectedType() As objectType

sqlType

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

count

Wenn die Instanz ein Container ist, gibt count die Anzahl Scripte zurück. Ansonsten -1

Public Property Get count() As Long

returnValue

Der Rückgabewert von Execute kann über dieses Property nachträglich nochmals ausgelesen werden

Public Property Get returnValue() As Variant

logText

Gibt einen Text zurück, der die ausgeführten Aktionen dokumentiert

Public Property Get logText() As String

filePath

Der pFad des Scriptfiles. Macht ntürlich auch nur bei einem Containerobjekt Sinn

Public Property Get filePath() As String

script

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

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.

Beispiele

Für die Ausgabe der QueryDefs und der Recordsets verwende ich die Funktion [VBA][Access] printRs()

Programm Beispiele

Einfaches Ausführen einer Script-Datei

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

Öffnen einer Script-Datei und die SQL-Statements einzeln ausführen

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

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

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.

MsgBox SQLScript.instanceByFileDialog().execute(spLogText)

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.

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   |

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.

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

Script Beispiele

Code

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

Download sqlscript.cls (V-1.5.2)

Discussion

Enter your comment. Wiki syntax is allowed:
If you can't read the letters on the image, download this .wav file to get them read to you.
 
vba/access/classes/sqlscript.txt · Last modified: 18.01.2017 10:24:56 by yaslaw