Rubrik: Datenbanken | VB-Versionen: VB5, VB6 | 15.08.05 |
Query Analyzer für den MS SQL-Server in VB Mit diesem Workshop möchten wir Ihnen zeigen, wie Sie komplexe "Transact SQL (T-SQL)" Scripte mit VB und ADO auf einem MS SQL-Server, bzw. der kostenlosen MSDE absetzen können. Was wir dafür benötigen, ist ein Query Analyzer den wir uns in diesem Workshop bauen wollen. | ||
Autor: Roland Wutzke | Bewertung: | Views: 37.634 |
Mit diesem Workshop möchten wir Ihnen zeigen, wie Sie komplexe "Transact SQL (T-SQL)" Scripte mit VB und ADO auf einem MS SQL-Server, bzw. der kostenlosen MSDE absetzen können. Was wir dafür benötigen, ist ein Query Analyzer den wir uns in diesem Workshop bauen wollen.
Grundlagen
Zunächst wollen wir Ihnen einige Grundlagen über T-SQL vermitteln.
Transact SQL, auch T-SQL genannt, sind i.d.R. komplexe Anweisungsscripte für unseren Server. Dabei kann ein solches Script aus mehreren Anweisungsblöcken bestehen. Solche Scripte führen meist direkte serverseitige Aktionen aus um Daten zu aktualisieren, zu löschen oder aber Daten zu importieren und exportieren usw...
Aufbau eines Scripts:
Wir bereits erwähnt, besteht ein T-SQL Script aus mehreren Anweisungsblöcken. Die Blöcke selber werden mit dem Schlüsselwort "GO" abgeschlossen.
USE DatabaseName Go Anweisungsblock 1 ... ... Go Anweisungsblock 2 ... ... Go
Wie hier im Aufbau zu sehen, verwenden wir ein weiteres Schlüsselwort: "USE".
Mit Use <DatabaseName> wird die Datenbank gewechselt, also zur aktuellen Datenbank für das Script gemacht, da unser Server ja mehrere Datenbanken beinhaltet.
Neue Datenbank erzeugen:
Die wohl elementarste Funktion unseres Servers ist die, eine neue Datenbank anlegen zu können. Das Schlüsselwort hierzu ist die "CREATE" Anweisung. Im Script sieht das dann so aus:
USE master Go CREATE DATABASE [Verkauf] Go
Datenbank löschen:
Das Schlüsselwort um ein Serverobjekt zu löschen ist "DROP". Also eine Datenbank löschen geht mit folgendem Script:
/* ACHTUNG !!! Dieses Script löscht die Datenbank Verkauf */ USE master Go DROP1 DATABASE [Verkauf] -- Löscht die DB Verkauf Go
Kommentieren Sie Ihr Script für die Nachwelt:
Schauen wir uns das letzte Beispiel an, so sehen wir zwei Möglichkeiten das Script zu dokumentieren. Sie können ganze Blöcke kommentieren, indem Sie diese mit "/* Kommentarblock */" einfassen. Die zweite Möglichkeit kommentiert mit zwei Minuszeichen "-" den Rest einer Zeile aus.
Verwenden von Variablen:
In T-SQL können Sie lokale Variablen verwenden. Variablen werden mit der Anweisung "DECLARE" unter Angabe des Datentyps definiert. Wahlweise können Sie das Schlüsselwort "AS" verwenden. Vor dem Variablennamen wird immer ein @ gesetzt:
DECLARE @var1 AS Int DECLARE @var2 AS Smallmoney DECLARE @var3 AS Varchar(5)
Die Wertzuweisung einer Variable erfolgt auf zwei Arten:
- SET Anweisung
- SELECT Anweisung
DECLARE @Umsatz AS Int DECLARE @Verkäufer AS Varchar(50) SET @Umsatz = 5000 SET @Verkäufer = (SELECT Nachname FROM Personal WHERE PersNr = 456)
Im folgenden Beispiel wird die Tabelle Abverkauf mittels eines Schwellenwerts aktualisiert:
/* Aktualisieren der Tabelle Abverkauf */ USE [Verkauf] Go DECLARE @Umsatz AS Smallmoney SET @Umsatz = 5000 UPDATE Abverkauf SET Verkaufshit = 10 WHERE Gesamtverkauf > @Umsatz Go
IF Bedingungen:
Genau wie in VB kennt T-SQL ebenfalls die IF-Bedingung. Doch im Gegensatz zu VB gibt es kein "Then" und "End If".
IF Bedingung Anweisung ELSE Anweisung
Eine einfache IF Anweisung im Script könnte dann so aussehen:
/* Aktualisieren der Tabelle Abverkauf */ USE [Verkauf] Go DECLARE @Umsatz AS Smallmoney SET @Umsatz = 5000 IF @Umsatz < 1000 UPDATE Abverkauf SET Verkaufshit = 0 ELSE UPDATE Abverkauf SET Verkaufshit = 10 Go
Anweisungsblöcke in einer IF-Anweisung werden mit BEGIN und END eingefasst:
IF Bedingung BEGIN Anweisung 1 ... Anweisung n END ELSE BEGIN Anweisung 1 ... Anweisung n END
Datenimport mit BULK INSERT:
Mit T-SQL können Sie den Massenimport einer flachen Textdatei, bspw. einer CSV Datei, per BULK INSERT vornehmen. Der BULK INSERT ist extrem schnell und mit nur wenigen Zeilen zu realisieren:
USE [Verkauf] GO BULK INSERT Tabellenname FROM 'C:\Daten\Import.csv' WITH (FIELDTERMINATOR = ';', DATAFILETYPE = 'char', CODEPAGE = 'RAW') Go
Bitte beachten Sie die Pfadangabe im BULK INSERT. Der Pfad muss ein gültiger Pfad für den Server sein, sonst kommt es zu einem Fehler.
Table und View:
Wie Tabellen und Sichten angelegt und gelöscht werden, wird bereits im Workshop Grundlagen: Arbeiten mit der kostenlosen MSDE-Datenbank von Microsoft von Wolfgang Christ beschrieben. Ich möchte an dieser Stelle lediglich auf diesen Workshop verweisen.
Die hier aufgeführten Beispiele stellen nur eine kleine Übersicht über T-SQL dar. Wenn Sie mehr über T-SQL erfahren möchten, sollten Sie folgenden Link besuchen:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_tsqlcon_6lyk.asp.
Die Benutzerverwaltung des SQL-Servers:
Mit dem Tipp User auf dem MS SQL-Server anlegen haben wir Ihnen bereits gezeigt, wie die Userverwaltung auf dem Server aufgebaut ist. Nun möchten wir das Script aufzeigen um einen User anzulegen, damit wir dieses später in unserem Query Analyzer einsetzen können.
/* Anlegen eines neuen Users auf dem Server und Zulassen für die Datenbank Verkauf. Der neue User bekommt die Zugriffsrechte DataReader und DataWriter */ /* Wichtig! Da ein neuer User nur über die Master DB angelegt werden kann */ USE master Go /* User auf dem Server anlegen */ EXEC sp_addlogin 'Paul', 'x1234' Go /* Wechseln zur Datenbank Verkauf */ USE Verkauf Go /* Den neuen User die DB zuweisen */ EXEC sp_grantdbaccess 'Paul' Go /* Jetzt den User die Zugriffsrollen zuweisen */ EXEC sp_addrolemember 'db_datareader', 'Paul' Go EXEC sp_addrolemember 'db_datawriter', 'Paul' Go
Stored Procedures:
Stored Procedures sind Scripte, die auf dem Server liegen und auch dort ausgeführt werden. Es gibt zwei Typen von Stored Procedures: Die globalen serverweit gültigen und die datenbankspezifischen Procedures. Die globalen Procedures liegen i.d.R. in der Master-DB. Eine globale Procedure kann von jeder DB auf dem Server ausgeführt werden, eine datenbankspezifische dagegen nur aus der eigenen Datenbank. Sie ist demnach auch nur für diese Datenbank gültig.
Die Ergebnisse dieser Scripte werden an den Client als Recordset zurückgegeben. Um eine Stored Procedure auszuführen, wird das Schlüsselwort "EXEC" verwendet.
EXEC sp_who2
Diese Procedure bspw. liefert Ihnen alle laufenden Prozesse auf dem Server.
Der MS SQL-Server, bzw. die MSDE werden mit einer Reihe von Stored Procedures ausgeliefert. Nachfolgend möchten wir Ihnen einige vorstellen.
sp_droplogin:
Diese Procedure löscht einen Server Login-User. Sie ist das Gegenstück zu sp_addlogin.
EXEC sp_droplogin 'LoginName'
sp_revokedbaccess:
Mit dieser Procedure wird ein Datenbankbenutzer aus einer Datenbank gelöscht. Das Gegenstück hierzu ist sp_grantdbaccess.
EXEC sp_revokedbaccess 'Datenbankbenutzer'
sp_droprolemember:
Diese Procedure löscht einen Datenbankbenutzer aus der angegebenen Datenbankrolle. Das Gegenstück hierzu ist sp_addrolemember.
EXEC sp_droprolemember 'DBUser'
sp_addsrvrolemember:
Mit dieser Procedure wird ein LoginUser in eine Serverrolle eingetragen. Die Serverrolle für einen System-Administrator ist 'sysadmin'
EXEC sp_addsrvrolemember 'LoginUser', 'ServerRole'
sp_dropsrvrolemember:
Löscht einen angegebenen LoginUser aus einer Serverrolle
EXEC sp_dropsrvrolemember 'LoginName', 'ServerRole'
sp_addrole:
Eine neue Datenbankrolle wird mit dieser Procedure angelegt.
EXEC sp_addrole 'RoleName'
sp_droprole:
Gelöscht wird eine Datenbankrolle mit dieser Procedure.
EXEC sp_droprole 'RoleName'
sp_helpdb:
Diese Procedure listet alle Datenbanken des Servers auf. Es wird ein Recordset zurückgeliefert.
EXEC sp_helpdb [Optional DatabaseName]
sp_helpgroup:
Diese Procedure listet alle Rollen der aktuellen Datenbank auf. Es wird ein Recordset zurückgeliefert.
EXEC sp_helpgroup [Optional RoleName]
sp_helpindex:
Diese Procedure listet alle Indizes einer Tabelle oder Sicht aus der aktuellen Datenbank auf. Es wird ein Recordset zurückgeliefert.
EXEC sp_helpindex 'ObjectName'
sp_helplogins:
Diese Procedure listet alle LoginUser des Servers auf. Es wird ein Recordset zurückgeliefert.
EXEC sp_helplogins [Optional LoginUser]
sp_helprolemember:
Diese Procedure listet alle Mitglieder aller Rollen der aktuellen Datenbank auf. Es wird ein Recordset zurückgeliefert.
EXEC sp_helprolemember [Optional RoleName]
sp_helpuser:
Diese Procedure listet alle Benutzernamen der aktuellen Datenbank auf. Es wird ein Recordset zurückgeliefert.
EXEC sp_helpuser [Optional Benutzername]
sp_helptext:
Diese Procedure listet die Regel (SQL-Statement) einer Sicht, Stored Procedure, Funktion und Trigger auf. Tabellen werden nicht unterstützt. Es wird ein Recordset zurückgeliefert.
EXEC sp_helptext 'ObjectName'
Wenn Sie mehr über Stored Procedues erfahren möchten, dann sollten Sie die MSDN Online Seite besuchen:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_00_519s.asp
Goldgrube:
Eine sehr gute Fundgrube für SQL-Scripte ist die Webseite von SQL-Server Central. Hier finden Sie Scripte, Tipps & Tricks und ein gutes Forum. Sie müssen sich registrieren lassen und bekommen 2-3 mal wöchentlich einen Newsletter. Die Seite ist ansonsten kostenlos. Empfehlung: ***
http://www.sqlservercentral.com/
Query Analyzer:
So, nun aber zu unserem Query Analyzer in VB6. Das nachfolgende Klassenmodul bildet das Kernstück unseres Query Analyzers.
Über die ADO Schnittstelle kann Zugriff auf den MS SQL-Server, bzw. der kostenlosen MSDE genommen werden. Das Klassenmodul ist in der Lage, komplexe T-SQL Scripte auszuführen. Dabei werden die Anweisungsblöcke, die mit "GO<CR>" getrennt sein müssen, geparst und einzeln über ein Command-Objekt ausgeführt. Die Rückgabe der betroffenen Records erfolgt über das Ereignis "RecordsAffected".
' *********************************************************************** ' * ' * clsQueryAnalyzer VB6 Klassenmodul ' * ' * Dieses Klassenmodul stellt die Basisroutinen des Query Analyzers ' * zur Verfügung. Über die ADO Schnittstelle kann Zugriff auf den ' * MS SQL-Server, bzw. der kostenlosen MSDE genommen werden. ' * ' * Das Klassenmodul ist in der Lage, komplexe T-SQL Scripte ' * auszuführen. Dabei werden die Anweisungsblöcke, die mit "GO<CR>" ' * getrennt sein müssen, geparst und einzeln über ein Command-Objekt ' * ausgeführt. Die Rückgabe der betroffenen Records erfolgt über ' * das Ereignis "RecordsAffected". ' * ' * Juli 2005 - VB-Power.net www.vb-power.net ' * ' *********************************************************************** Option Explicit ' Klassenvariablen Private WithEvents cConn As ADODB.Connection Private cTimeOut As Long ' Events Public Event RecordsAffected(ByVal AnzRecords As Long)
' Klasse initialisieren Private Sub Class_Initialize() cTimeOut = 30 End Sub
' Active Connection Public Property Set ActiveConn(vData As ADODB.Connection) Set cConn = vData End Property
' Command TimeOut Public Property Let TimeOut(ByVal vData As Long) cTimeOut = vData End Property Public Property Get TimeOut() As Long TimeOut = cTimeOut End Property
' =========================== ' Hauptroutine Query Analyzer ' =========================== Public Function RunTSQL(TSQL As String) As Boolean Dim oCmd As New ADODB.Command Dim oRSCmd As ADODB.Recordset Dim cmdSet() As String Dim lRet As Long Dim X As Long ' Überprüfungen If cConn Is Nothing Then Exit Function If TSQL = "" Then Exit Function On Error GoTo RunTSQL_Error ' Das Script parsen. Dabei werden die Anweisungsblöcke ' in das Array geschoben. Es wird angenommen, ' dass die einzelnen Anweisungen im Script ' mit "go<CR>" getrennt sind. cmdSet = TSQLParser(TSQL) With oCmd ' Command initialisieren .ActiveConnection = cConn .CommandType = adCmdText .CommandTimeout = cTimeOut ' Schleife über alle Anweisungen ' aus dem T-SQL Script. For X = 0 To UBound(cmdSet) If cmdSet(X) <> "" Then ' RollBack sicherstellen cConn.BeginTrans ' T-SQL ausführen .CommandText = Trim(cmdSet(X)) Set oRSCmd = .Execute(lRet) ' Wenn kein Fehler, dann übernehmen cConn.CommitTrans ' Ereignis auslösen RaiseEvent RecordsAffected(lRet) End If Next X End With ' Command und Recodset terminieren Set oCmd = Nothing Set oRSCmd = Nothing RunTSQL = True Exit Function RunTSQL_Error: cConn.RollbackTrans Set oCmd = Nothing Set oRSCmd = Nothing RunTSQL = False MsgBox Err.Description, vbCritical, "Fehler" End Function
' ===================== ' SQL-Syntax überprüfen ' ===================== Public Function CheckTSQL(TSQL As String) As Boolean If cConn Is Nothing Then Exit Function If TSQL = "" Then Exit Function ' Ausführung unterdrücken cConn.Execute "SET NOEXEC ON" ' Syntax prüfen If RunTSQL(TSQL) Then CheckTSQL = True Else CheckTSQL = False End If ' Ausführung wieder zulassen cConn.Execute "SET NOEXEC OFF" End Function
' Hilfsfunktion Private Function TSQLParser(ByVal oText As String) As Variant Dim tmpStr As String tmpStr = oText tmpStr = Replace(tmpStr, "go" & vbCrLf, "GO" & vbCrLf, , , vbBinaryCompare) tmpStr = Replace(tmpStr, "Go" & vbCrLf, "GO" & vbCrLf, , , vbBinaryCompare) tmpStr = Replace(tmpStr, "gO" & vbCrLf, "GO" & vbCrLf, , , vbBinaryCompare) TSQLParser = Split(tmpStr, "GO" & vbCrLf) End Function
' Ereignis der Connection Private Sub cConn_InfoMessage(ByVal pError As ADODB.Error, _ adStatus As ADODB.EventStatusEnum, _ ByVal pConnection As ADODB.Connection) If adStatus = adStatusErrorsOccurred Then MsgBox Err.Description End If End Sub
Demoprojekt Query Analyzer:
Das VB6 Demoprojekt ist die ideale Ergänzung für alle Benutzer der kostenlosen MSDE - aber auch für die SQL-Server Benutzer. Es zeigt den Einsatz der Klasse "clsQueryAnalyzer". Besonders hilfreich dabei sind die vorgefertigten Scripte, welche die Arbeit mit dem Server erleichtern. Die Scripte können einfach aus dem Kontextmenue abgerufen werden.
Das Demoprojekt gibt es hier zum downloaden.
QueryAnalyzer.zip (15 KB)
Ich wünsche Ihnen gutes Gelingen mit dem Query Analyzer.
Roland Wutzke