In einem anderen Artikel namens “ADODB: SQL-Befehle schnell ausführen mit Execute” (www.vbentwickler.de\/447) haben wir bereits gezeigt, wie wir unter ADO mit der Execute-Methode der Connection-Klasse schnell SQL-Anweisungen zum Manipulieren von Daten ausführen oder Daten abrufen und mit einem Recordset durchlaufen können. Allerdings gibt es gerade für den Austausch von Daten mit dem SQL Server noch einige weitere Möglichkeiten. Dazu benötigen wir allerdings die Command-Klasse. Sie bietet primär auch eine Execute-Methode, mit der wir die gleichen Dinge erledigen können, wie mit der gleichnamigen Methode der Connection-Klasse. Sie bietet allerdings viele weitere Optionen, mit denen wir zum Beispiel Parameter an eine gespeicherte Prozedur übergeben können. <\/b><\/p>\nWarum die Command-Klasse?<\/h2>\n
Die Command<\/b>-Klasse ermöglicht es Dir, Abfragen und gespeicherte Prozeduren sehr flexibel und sicher auszuführen.<\/p>\n
Während einfache SQL-Strings in der Connection.Execute<\/b>-Methode direkt übergeben werden, kannst Du mit Command auch Parameter definieren. Das ist gerade bei dynamischen Abfragen oder bei wiederverwendbaren SQL-Prozeduren entscheidend.<\/p>\nWichtige Eigenschaften und Methoden der Command-Klasse<\/h2>\n
Hier findest Du die wichtigsten Eigenschaften und Methoden der Command<\/b>-Klasse:<\/p>\n Ein Command<\/b>-Objekt besteht grundsätzlich aus der Zuweisung der Connection, der Angabe des SQL-Befehls (oder Prozedurnamens) und der Einstellung des CommandType<\/b>.<\/p>\n Danach kannst Du über die Parameters<\/b>-Auflistung Parameter <\/b>hinzufügen und konfigurieren. Schließlich rufst Du die Execute<\/b>-Methode auf, um den Befehl auszuführen oder ein Recordset zu erhalten.<\/p>\n Im folgenden Beispiel öffnen wir ein Recordset mit Hilfe eines Command<\/b>-Objekts und geben die Inhalte direkt im Direktbereich aus:<\/p>\n Die Eigenschaft ActiveConnection<\/b> legt fest, welche Verbindung das Command<\/b>-Objekt verwendet. Meist weist Du hier eine bestehende Connection zu, damit das Command<\/b>-Objekt genau weiß, gegen welche Datenbank es ausgeführt werden soll. Beispiel:<\/p>\n Mit der Methode Cancel<\/b> kannst Du einen laufenden Command<\/b>-Vorgang abbrechen. Dies ist hilfreich, wenn ein Befehl sehr lange dauert oder der Benutzer den Vorgang abbricht. Beispiel:<\/p>\n Die Eigenschaft CommandStream<\/b> erlaubt es, anstelle von SQL-Text einen Datenstrom (Stream) an ein Command<\/b>-Objekt zu übergeben. Meist handelt es sich dabei um einen XML-Stream oder einen anderen binären Datenstrom, der als Eingabe für einen Befehl dient.<\/p>\n Ein typisches Szenario ist der Import großer XML-Datenmengen in eine Datenbank oder das Ausführen von gespeicherten XML-Abfragen, die nicht als reiner Text übergeben werden sollen. In solchen Fällen wird ein Stream<\/b>-Objekt erstellt (zum Beispiel mit der ADODB.Stream<\/b>-Klasse), befüllt und dann der Eigenschaft CommandStream<\/b> zugewiesen.<\/p>\n Beispiel (verkürzt dargestellt):<\/p>\n Die Verwendung von CommandStream<\/b> ist eher selten und wird vor allem in komplexeren Szenarien mit XML-Daten, BLOBs (Binary Large Objects) oder bei bestimmten Webservice-Integrationen eingesetzt. In klassischen Access- und SQL Server-Szenarien spielt diese Eigenschaft nur eine untergeordnete Rolle.<\/p>\n Hier gibst Du den eigentlichen SQL-Befehl oder den Namen einer gespeicherten Prozedur an. Beispiel:<\/p>\n Mit CommandTimeout<\/b> legst Du die maximale Zeit in Sekunden fest, die auf die Ausführung des Befehls gewartet wird. Beispiel:<\/p>\n Mit CommandType<\/b> steuerst Du, wie Access den Inhalt von CommandText<\/b> interpretiert (Text, Tabelle, Prozedur et cetera). Beispiel:<\/p>\n Die folgenden Werte können wir für die Eigenschaft CommandType <\/b>verwenden:<\/p>\n Mit CreateParameter<\/b> erstellst Du einen neuen Parameter für Dein Command<\/b>-Objekt. Dies ist vor allem nützlich bei gespeicherten Prozeduren oder dynamischen SQL-Abfragen. Der folgende Parameter übergibt den Wert 5 <\/b>als Parameter für das Feld KundenID<\/b>:<\/p>\n Die Eigenschaft Dialect<\/b> gibt an, welche SQL-Syntax oder welcher Dialekt für CommandText<\/b> verwendet werden soll. Meist wird der Standard-SQL-Dialekt genutzt, sodass diese Eigenschaft oft nicht explizit gesetzt wird.<\/p>\n Die Eigenschaft Dialect<\/b> gibt an, welche Abfragesyntax das Command-Objekt für den Inhalt von CommandText<\/b> verwenden soll. Standardmäßig wird der SQL-Dialekt des verwendeten Providers (meist ANSI SQL) genutzt. Folgende Werte können je nach Provider genutzt werden:<\/p>\n Zusätzlich gibt es bei einigen OLE DB Providern proprietäre Dialekte, die über eine Dialekt-URI (Uniform Resource Identifier) angegeben werden können, zum Beispiel für XML-Datenquellen. In der Praxis wird Dialect<\/b> sehr selten explizit gesetzt, da der Standardwert für die meisten SQL-Statements vollkommen ausreichend ist. <\/p>\n Erst bei speziellen XML- oder proprietären Abfragen ist es nötig, diesen Wert explizit festzulegen.<\/p>\n Mit Execute<\/b> führst Du das Command aus und erhältst optional ein Recordset zurück. Beispiel:<\/p>\n Mit der Methode Execute<\/b> führst Du ein Command<\/b>-Objekt aus. Dabei kannst Du verschiedene Parameter verwenden, um das Verhalten der Ausführung zu steuern. Die grundlegende Syntax lautet:<\/p>\n Wenn Du die Command<\/b>-Klasse mit vorher definierten Parametern nutzt, brauchst Du meist nur RecordsAffected<\/b> als Rückgabewert. Beispiel:<\/p>\n Mit diesen Parametern kannst Du also noch genauer steuern, wie Dein SQL-Befehl ausgeführt wird, und erhältst wichtige Rückmeldungen über das Ergebnis.<\/p>\n Die Eigenschaft Name<\/b> erlaubt es Dir, einem Command<\/b>-Objekt einen eindeutigen Bezeichner zu geben. Das ist vor allem hilfreich, wenn Du mehrere Command<\/b>-Objekte parallel verwalten möchtest. Du kannst den Namen später zum Beispiel beim Debuggen verwenden oder gezielt über diesen Namen in einer Collection auf das Command<\/b>-Objekt zugreifen.<\/p>\n Das kann wie folgt aussehen:<\/p>\n Mit NamedParameters<\/b> steuerst Du, ob Parameter nach Namen oder nach Reihenfolge übergeben werden:<\/p>\n Die Parameters<\/b>-Auflistung enthält alle Parameter, die Du mit CreateParameter <\/b>erstellt und an das Command<\/b>-Objekt angehängt hast. Über diese Auflistung kannst Du die Werte später ändern oder Parameter entfernen. Normalerweise wird man aber immer nur Parameter anhängen.<\/p>\n Die Eigenschaft Prepared<\/b> gibt an, ob das Command<\/b>-Objekt vorbereitet (vorkompiliert) werden soll.<\/p>\n Wenn Du diese Eigenschaft auf True <\/b>setzt, bereitet ADO die SQL-Anweisung oder die gespeicherte Prozedur einmal vor, sodass sie bei mehrfacher Ausführung schneller verarbeitet werden kann. Das ist besonders sinnvoll, wenn Du ein Command<\/b>-Objekt mit Parametern mehrfach aufrufst, etwa in einer Schleife oder bei wiederholten Benutzeraktionen.<\/p>\n Durch das Vorbereiten muss die Anweisung nicht jedes Mal erneut interpretiert und optimiert werden.<\/p>\n Beispiel: Du möchtest eine gespeicherte Prozedur mehrfach mit verschiedenen Parameterwerten aufrufen. In diesem Fall setzt Du Prepared<\/b> vor der ersten Ausführung auf True<\/b>.<\/p>\n Nach dem ersten Aufruf bleibt das Command<\/b>-Objekt vorbereitet und kann mit geänderten Parametern immer wieder schnell ausgeführt werden. Damit sparst Du vor allem bei großen Abfragen oder bei Zugriffen auf SQL Server-Datenbanken spürbar Zeit.<\/p>\n Die Properties<\/b>-Sammlung enthält zusätzliche Einstellungen, die für das Command<\/b>-Objekt gelten. Hierüber kannst Du spezielle Optionen abrufen oder setzen, die je nach Provider variieren können. Diese Auflistung ist kompatibel mit den Properties<\/b>-Auflistungen aller anderen gängigen Klassen.<\/p>\n Die Eigenschaft State<\/b> zeigt an, in welchem Zustand sich das Command<\/b>-Objekt aktuell befindet.<\/p>\n Sie gibt eine Zahl zurück, die einem bestimmten Status entspricht. Typische Werte sind:<\/p>\n Die State<\/b>-Eigenschaft ist vor allem dann nützlich, wenn Du prüfen möchtest, ob ein Command<\/b>-Objekt gerade aktiv ist oder ob es noch verwendet werden kann.<\/p>\n So kannst Du zum Beispiel vor einem erneuten Ausführen sicherstellen, dass kein vorheriger Vorgang mehr läuft.<\/p>\n Dies prüft den Zustand und gibt diesen im Direktbereich aus:<\/p>\n Eine besondere Stärke der Command<\/b>-Klasse ist die Möglichkeit, Parameter zu definieren. Dies eignet sich vor allem, wenn Du mit gespeicherten Prozeduren arbeitest. Hier kannst Du Input<\/b>-Parameter, Output<\/b>-Parameter oder auch Parameter mit einem Rückgabewert definieren und verwalten.<\/p>\n Ausführliche Beispiele dazu findest Du im Artikel ADODB: Beispiele für den Datenzugriff mit Command <\/b>(www.vbentwickler.de\/470<\/b>).<\/p>\n Eine wichtige Besonderheit der Command-Klasse<\/b> ist, dass sie als einzige Möglichkeit in ADODB einen bearbeitbaren Zugriff auf die Ergebnisse einer gespeicherten Prozedur bietet.<\/p>\n Mit einem reinen SQL-Text (SELECT * FROM …<\/b>) über Connection.Execute <\/b>kannst Du zwar Daten abrufen, aber nicht direkt bearbeiten.<\/p>\n Verwendest Du jedoch die Command<\/b>-Klasse mit adCmdStoredProc<\/b>, kannst Du – abhängig von der Datenquelle – ein Recordset erhalten, das bearbeitet und später wieder gespeichert werden kann.<\/p>\n Wichtig: Dies funktioniert nur, wenn die gespeicherte Prozedur ein sogenanntes “aktualisierbares” Recordset zurückgibt. Das bedeutet, die Prozedur darf keine komplexen Joins oder Aggregatfunktionen enthalten und muss so aufgebaut sein, dass ADODB eine eindeutige Zuweisung zu den zugrunde liegenden Tabellen herstellen kann.<\/p>\n Diese Möglichkeit steht Dir vor allem bei SQL Server, Oracle und anderen professionellen relationalen Datenbanksystemen zur Verfügung. Bei Access (JET\/ACE) sind gespeicherte Prozeduren in Form von Abfragen oft nicht direkt über Command <\/b>mit Parametern ausführbar und liefern in der Regel auch kein bearbeitbares Recordset.<\/p>\n Damit eignet sich die Command<\/b>-Klasse ideal für Szenarien, in denen Du Daten nicht nur anzeigen, sondern direkt in der Anwendung weiterbearbeiten möchtest, zum Beispiel in Formularen oder bei automatisierten Aktualisierungsvorgängen.<\/p>\n Der große Vorteil: Du kannst damit die Vorteile der schnellen Datenlieferung von gespeicherten Prozeduren in Zusammenhang mit Parametern als Kriterien nutzen und das Ergebnis gleichzeitig bearbeiten. Wenn Du eine gespeicherte Prozedur auf DAO-Basis mit einer Pass-Through-Abfrage öffnest, ist das in jedem Fall unmöglich, weil diese Recordsets nicht bearbeitbar sind.<\/p>\n Neben den technischen Details zur Command<\/b>-Klasse lohnt es sich, ein paar praktische Tipps und häufige Fehlerquellen zu beleuchten. Gerade beim Arbeiten mit gespeicherten Prozeduren kann es leicht passieren, dass ein Recordset unerwartet schreibgeschützt ist. Der häufigste Grund: Die Prozedur enthält Joins oder berechnete Spalten, die nicht eindeutig einer Tabelle zugeordnet werden können.<\/p>\n Ein weiterer häufiger Stolperstein ist das Vergessen der Längenangabe bei Zeichenkettenparametern (adVarChar <\/b>oder adVarWChar<\/b>). Ohne Länge interpretiert SQL Server den Parameter oft als Länge 0 <\/b>oder liefert Fehlermeldungen. Daher gilt: Für jeden Parameter mit Zeichenketten immer explizit die Länge angeben, zum Beispiel 50 <\/b>für einen typischen Varchar(50)<\/b>-Parameter.<\/p>\n Darüber hinaus solltest Du, wenn möglich, die Verwendung von Platzhaltern wie SELECT * <\/b>vermeiden und stattdessen immer die benötigten Spalten explizit angeben. Das verbessert nicht nur die Performance, sondern reduziert auch das Risiko von Konflikten, falls sich die Tabellendefinition einmal ändert.<\/p>\n Wenn Du die Command<\/b>-Klasse für Datenänderungen verwendest, ist es oft sinnvoll, vor dem Aufruf von Execute die Eigenschaft Prepared <\/b>auf True <\/b>zu setzen. Das lohnt sich insbesondere bei wiederholten Aufrufen mit unterschiedlichen Parametern, weil die SQL-Anweisung dann nur einmal vom SQL Server vorbereitet (kompiliert) werden muss.<\/p>\n Schließlich empfiehlt es sich, den Rückgabewert von Execute (RecordsAffected<\/b>) zu prüfen. So stellst Du sicher, dass tatsächlich Datensätze geändert wurden, und kannst auf unerwartete Situationen (zum Beispiel keine betroffenen Zeilen) reagieren. Das erhöht die Stabilität und Nachvollziehbarkeit Deiner VBA-Anwendungen erheblich.<\/p>\n Mit der Command<\/b>-Klasse steht Dir eine sehr leistungsfähige Alternative zur reinen Execute<\/b>-Methode der Connection<\/b>-Klasse zur Verfügung. Du kannst SQL-Befehle flexibel steuern, Parameter nutzen und auch komplexe Prozeduren ansprechen.<\/p>\n In einem weiteren Artikel namens ADODB: Beispiele für den Datenzugriff mit Command <\/b>(www.vbentwickler.de\/470<\/b>) zeigen wir Dir einige Beispiele für den Zugriff per ADODB\/Command auf Recordsets in Access- und SQL Server-Datenbanken.<\/p>\n Hier schauen wir uns auch an, wo die Grenzen liegen beim Erstellen von Recordsets auf Basis des Command<\/b>-Objekts.<\/p>\n","protected":false},"excerpt":{"rendered":" In einem anderen Artikel namens “ADODB: SQL-Befehle schnell ausführen mit Execute” (www.vbentwickler.de\/447) haben wir bereits gezeigt, wie wir unter ADO mit der Execute-Methode der Connection-Klasse schnell SQL-Anweisungen zum Manipulieren von Daten ausführen oder Daten abrufen und mit einem Recordset durchlaufen können. Allerdings gibt es gerade für den Austausch von Daten mit dem SQL Server noch einige weitere Möglichkeiten. Dazu benötigen wir allerdings die Command-Klasse. Sie bietet primär auch eine Execute-Methode, mit der wir die gleichen Dinge erledigen können wir mit der gleichnamigen Methode der Connection-Klasse. Sie bietet allerdings viele weitere Optionen, mit denen wir zum Beispiel Parameter an eine gespeicherte Prozedur übergeben können. Was wir mit der Command-Klasse alles erledigen können, liest Du in diesem Artikel.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[662025,66032025,44000008],"tags":[],"yst_prominent_words":[],"class_list":["post-55000475","post","type-post","status-publish","format-standard","hentry","category-662025","category-66032025","category-Datenzugriffstechnik"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/vbentwickler.de\/data\/wp\/v2\/posts\/55000475","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/vbentwickler.de\/data\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vbentwickler.de\/data\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vbentwickler.de\/data\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/vbentwickler.de\/data\/wp\/v2\/comments?post=55000475"}],"version-history":[{"count":0,"href":"https:\/\/vbentwickler.de\/data\/wp\/v2\/posts\/55000475\/revisions"}],"wp:attachment":[{"href":"https:\/\/vbentwickler.de\/data\/wp\/v2\/media?parent=55000475"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vbentwickler.de\/data\/wp\/v2\/categories?post=55000475"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vbentwickler.de\/data\/wp\/v2\/tags?post=55000475"},{"taxonomy":"yst_prominent_words","embeddable":true,"href":"https:\/\/vbentwickler.de\/data\/wp\/v2\/yst_prominent_words?post=55000475"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\n
Grundaufbau eines Command-Objekts<\/h2>\n
Public Sub <\/span>KundenPerCommand()\r\n Dim <\/span>cnn As <\/span>ADODB.Connection\r\n Dim <\/span>cmd As <\/span>ADODB.Command\r\n Dim <\/span>rst As <\/span>ADODB.Recordset\r\n Set<\/span> cnn = CurrentProject.Connection\r\n Set<\/span> cmd = New<\/span> ADODB.Command\r\n cmd.ActiveConnection = cnn\r\n cmd.CommandText = \"SELECT * FROM tblKunden\"\r\n cmd.CommandType = adCmdText\r\n Set<\/span> rst = cmd.Execute\r\n Do Until rst.EOF\r\n Debug.Print<\/span> rst!Vorname & \" \" & rst!Nachname\r\n rst.MoveNext<\/span>\r\n Loop<\/span>\r\n rst.Close\r\n Set<\/span> rst = Nothing\r\n Set<\/span> cmd = Nothing\r\n Set<\/span> cnn = Nothing\r\nEnd Sub<\/span><\/pre>\nActiveConnection<\/h2>\n
Set<\/span> cmd.ActiveConnection = cnn\r\ncmd.CommandText = \"SELECT * FROM tblKunden\"<\/pre>\nCancel<\/h2>\n
cmd.Execute\r\ncmd.Cancel<\/pre>\n
CommandStream<\/h2>\n
Dim <\/span>stm As <\/span>ADODB.Stream\r\nSet<\/span> stm = New<\/span> ADODB.Stream\r\nstm.Open\r\nstm.WriteText \"<root><datensatz>...<\/datensatz><\/root>\"\r\nSet<\/span> cmd.CommandStream = stm\r\ncmd.CommandType = adCmdText\r\ncmd.Execute<\/pre>\nCommandText<\/h2>\n
cmd.CommandText = \"SELECT * FROM tblKunden\"\r\ncmd.CommandType = adCmdText<\/pre>\n
CommandTimeout<\/h2>\n
cmd.CommandTimeout = 30<\/pre>\n
CommandType<\/h2>\n
cmd.CommandType = adCmdText<\/pre>\n
\n
CreateParameter<\/h2>\n
Set<\/span> prm = cmd.CreateParameter(\"KundenID\", adInteger, adParamInput, , 5)\r\ncmd.Parameters.Append prm<\/pre>\nDialect<\/h2>\n
Dialect<\/h2>\n
\n
Execute<\/h2>\n
Set<\/span> rst = cmd.Execute<\/pre>\nDie Execute-Methode und ihre Parameter<\/h2>\n
Set<\/span> rst = cmd.Execute(RecordsAffected, Parameters, _\r\n Options)<\/pre>\n\n
Dim <\/span>lngBetroffen As Long<\/span>\r\ncmd.Execute lngBetroffen\r\nDebug.Print<\/span> lngBetroffen & \" Datensätze wurden aktualisiert.\"<\/pre>\nName<\/h2>\n
Dim <\/span>cmd1 As <\/span>ADODB.Command\r\nDim <\/span>cmd2 As <\/span>ADODB.Command\r\nSet<\/span> cmd1 = New<\/span> ADODB.Command\r\nSet<\/span> cmd2 = New<\/span> ADODB.Command\r\ncmd1.Name = \"Kundenabfrage\"\r\ncmd2.Name = \"Bestellabfrage\"\r\nDebug.Print<\/span> cmd1.Name\r\nDebug.Print<\/span> cmd2.Name<\/pre>\nNamedParameters<\/h2>\n
cmd.NamedParameters = True<\/span><\/pre>\nParameters<\/h2>\n
Prepared<\/h2>\n
cmd.Prepared = True<\/span>\r\nSet<\/span> prm = cmd.CreateParameter(\"KundenID\", adInteger, adParamInput, , 1)\r\ncmd.Parameters.Append prm\r\ncmd.Execute\r\ncmd.Parameters(\"KundenID\").Value = 2\r\ncmd.Execute<\/pre>\nProperties<\/h2>\n
State<\/h2>\n
\n
If <\/span>cmd.State = adStateOpen Then<\/span>\r\n Debug.Print<\/span> \"Command ist aktiv oder wird ausgeführt\"\r\nElse<\/span>\r\n Debug.Print<\/span> \"Command ist geschlossen\"\r\nEnd If<\/span><\/pre>\nParameter an gespeicherte Prozeduren übergeben<\/h2>\n
Besonderheit bei gespeicherten Prozeduren<\/h2>\n
Praktische Tipps und typische Fehlerquellen<\/h2>\n
Zusammenfassung<\/h2>\n