Microsoft Office Access 2007-Programmierung - Das Handbuch

BEISPIEL

Verwenden von SQLDMO

Sie können zwar SQL-DMO zum Erstellen der Sicht verwenden, die Verwendung ist allerdings umständ-

licher als bei den TSQL-Anweisungen, da Sie trotz allem TSQL verwenden müssen.

Erzeugen einer View:

Dim serv As New SQLDMO.SQLServer

Dim view As New SQLDMO.view

serv.Connect ".\SQLExpress", "sa", ”tom”

view.Name = "NeueSicht"

view.Text = "CREATE VIEW NeueSicht WITH ENCRYPTION AS SELECT nachname, geburtstag FROM personen"

serv.Databases("BeispielDB").Views.Add view

serv.Disconnect

Grundlage ist ein View-Objekt, dessen Name- und Text-Eigenschaft Sie setzen müssen. Unsere Empfehlung:

Verwenden Sie SQLDMO nur, wenn Sie gleichzeitig Nutzerrechte etc. an dieser View vergeben möchten.

Verwenden von Gespeicherten Prozeduren

Mit den Gespeicherten Prozeduren (Stored Procedures) wenden wir uns einem der interessantesten SQL

Server-Objekte zu. Bisher wurden diese in T-SQL programmiert und auf dem Server gespeichert und ausge-

führt (über eine API-Schnittstelle konnten auch Prozeduren mit Compiler-Sprachen, wie C oder Delphi,

programmiert werden). Das hat sich mit dem SQL Server 2005 grundlegend geändert: Auch VB.NET- und

C#-Programmierer sind jetzt in die Lage versetzt, Stored Procedures als managed Code für den SQL Server

zu programmieren

1

.

Das wichtigste Aufgabengebiet der Stored Procedures: Auslagern von Aufgaben auf den Server, das Netz-

werk als Flaschenhals entfällt.

Daneben bieten sich Gespeicherte Prozeduren auch als zusätzliche Programmebene an, zum Beispiel kann

ein Satz von Prozeduren Geschäftsprozesse zentral auf dem Server realisieren. Die Programmlogik wird aus

der einzelnen Client-Anwendung auf den Server verlagert. Damit lassen sich Anpassungen wesentlich ein-

facher und schneller realisieren als wenn Sie jede einzelne Client-Anwendung neu erstellen. Der Vorteil bei

der Entwicklung im Team: Nicht jeder muss alle Tabellen auf dem Server kennen, es genügen die Schnitt-

stellen, die mit Hilfe der Gespeicherten Prozeduren geschaffen wurden:

Anwendung 1

Anwendung 3

Anwendung 2

Prozedur 1

Prozedur 5

Prozedur 4

Prozedur 3

Prozedur 2

Datenbank

Abbildung 10.26 Grundprinzip der

Stored Procedures

1

Siehe dazu unsere Bücher Datenbankprogrammierung mit Visual C# 2005 (ISBN 978-3-86063-588-9) bzw.

Datenbankprogrammierung mit Visual Basic 2005 (ISBN 978-3-86063-589-6)

639

Praktisches Arbeiten mit dem SQL Server

HINWEIS

HINWEIS

BEISPIEL

Kapitel 10: SQL Server

Neben den bereits vordefinierten Systemprozeduren (diese beginnen mit sp_), die den direkten Zugriff auf

die Systemtabellen verhindern, können Sie eigene Prozeduren definieren, die Daten auf vier verschiedenen

Wegen mit Ihrem Visual Basic-Programm austauschen können:

■ Verwenden von Ausgabeparametern, die entweder Daten (z.B. eine Ganzzahl oder einen Zeichenwert)

oder eine Cursor-Variable zurückgeben können.

■ Rückgabecodes, die immer einen ganzzahligen Wert beinhalten (wie bei Funktionen).

■ Eine DataTable für jede SELECT-Anweisung, die von der Gespeicherten Prozedur aufgerufen wird.

■ Globaler Cursor, der auch außerhalb der Gespeicherten Prozedur referenziert werden kann.

Auch für Gespeicherte Prozeduren gilt: Lassen Sie die Daten da wo sie sind: auf dem SQL Server. Was immer

Sie auch mit den Daten anfangen wollen, überlegen Sie dreimal, bevor Sie diese zum Client herunterladen und bearbeiten, denn

dafür ist ja T-SQL auf dem Server da.

Wie Views können Sie auch Stored Procedures verschlüsseln, um deren Definition vor neugierigen Blicken zu

schützen.

Verwenden von Parametern

Wie jede Prozedur in VBA, lassen sich auch Gespeicherte Prozeduren mit Parametern aufrufen. Diese kön-

nen sowohl zur Übergabe als auch zur Rückgabe von Werten dienen. Innerhalb der Prozedur können Sie

den Parameter wie eine Variable verwenden.

Einen Parameter deklarieren Sie mit Name, Datentyp (siehe Tabelle 10.11) und gegebenenfalls mit einem

Defaultwert.

Prozedur mit zwei Parametern (Integer und String)

CREATE PROCEDURE Test @Parameter1 int, @Parameter2 VARCHAR(50)

AS

SELECT

@Parameter1 AS 'Parameter1',

@Parameter2 AS 'Parameter2'

return

Rufen Sie die obige Prozedur aus Access auf, werden automatisch Dialogfelder zur Eingabe der Werte ein-

geblendet:

Abbildung 10.27 Abfrage der Parameterwerte

Etwas anders sieht die Sache beim Aufruf über die ADO aus. Hier müssen/sollten Sie vorher festlegen,

welchen Wert die jeweiligen Parameter erhalten.

640

BEISPIEL

BEISPIEL

Aufruf der Prozedur mit den ADO

Dim cmd As New ADODB.Command

Dim param1 As ADODB.Parameter

cmd.CommandType = adCmdStoredProc

cmd.CommandText = "Test"

cmd.ActiveConnection = CurrentProject.Connection

cmd.Parameters.Append cmd.CreateParameter("Parameter1", adInteger, adParamInput)

cmd.Parameters.Append cmd.CreateParameter("Parameter2", adBSTR, adParamInput)

cmd.Parameters("Parameter1").Value = 123

cmd.Parameters("Parameter2").Value = "abc"

cmd.Execute

Nach dem Definieren des eigentlichen Command-Objektes müssen Sie sich noch um die Parameter küm-

mern. Diese werden zunächst mit Name und Datentyp initialisiert. Setzen Sie danach noch die gewünsch-

ten Werte und rufen Sie die Execute-Methode auf.

Recordsets als Rückgabewerte

Vielleicht haben Sie sich schon gefragt, wie Sie die Rückgabewerte der Prozedur (in unserem Fall ein

Recordset) auswerten können. Hier die Lösung:

Dim rs As New ADODB.Recordset

Dim cmd As New ADODB.Command

Dim param1 As ADODB.Parameter

cmd.CommandType = adCmdStoredProc

cmd.CommandText = "Test"

cmd.ActiveConnection = CurrentProject.Connection

cmd.Parameters.Append cmd.CreateParameter("Parameter1", adInteger, adParamInput)

cmd.Parameters.Append cmd.CreateParameter("Parameter2", adBSTR, adParamInput)

cmd.Parameters("Parameter1").Value = 123

cmd.Parameters("Parameter2").Value = "abc"

Set rs = cmd.Execute

While Not rs.EOF

Debug.Print rs.Fields("Parameter1")

Debug.Print rs.Fields("Parameter2")

rs.MoveNext

Wend

Gespeicherte Prozedur, die eine Tabelle abfragt:

CREATE PROCEDURE Test @Nachname VARCHAR(50)

As

SELECT * FROM personen

WHERE nachname LIKE @nachname

return

641

Praktisches Arbeiten mit dem SQL Server