Office: Access und MySQL (Parameterabfragen)

Hallo zusammen,

ich arbeite mich laaaaangsam und ziemlich ermüdend in das Zusammenspiel von Access und MySQL ein. Es wundert mich, dass es im Netz recht wenig darüber zu finden gibt (oder ich suche an den falschen Stellen *eek.gif* ) Fast alle Fundstellen beziehen sich auf Webanwendungen mit PHP o.ä. oder hören nach der Verknüpfung von MySQL-Tabellen in ein Access Frontend auf*frown.gif* .

Daher folgende Wünsche:
1) Links oder Literaturhinweise mit Beschreibungen und (Praxis-)Beispielen Access-Frontend/MySQL-Backend.

2) Foren, die sich mit dem Thema auseinander setzen (Damit ich nicht hier nerven muss *wink.gif*... oder soll ich?)

3) Konkret beschäftige ich mich momentan damit, wie ich eine Parameterabfrage auf dem MySQL-Backend implementieren muss (Stored Procedur/Function ? (Views lassen wohl keine Parameter zu)) und dann vom Access-Frontend die SP/SF inkl. Parameterübergabe aufrufen kann und das Recordset zurückbekommt.
...

Ich hätte jetzt noch 734 weitere Fragen ... *rolleyes.gif* (to be continued)

:)

 

Hallo Marsu,
grundsätzlich führt der Weg dabei über das ADODB.Command-Objekt. Damit kannst du die entsprechenden Befehle zum Ausführen der Stored Procedure auf der MySQL-DB absetzen.
Im Prinzip übergibst Du Command.Text den Ausführungsbefehl und führst ihn mit Command.Execute aus. Command.Execute gibt ein RS zurück, dass Du dann auswerten kannst.

Ansonsten habe ich gerade mal mit "access stored procedure adodb" gegoogelt und dabei einige Hinweise gefunden.
Unter anderem diesen: Mit VBA Stored Procedure Rückgabewert auslesen? - Entwickler-Forum oder diesen Problem mit SP und ADO mit Rückgabe-/Output-Parametern - Entwickler-Forum
Gruß
Vincenz

 

Es geht auch über JET, wenn du eine PassThrough-Abfrage nimmst, deren SQL-String du per VBA zusammengesetzt hast:

"Call fuServerKunde(kundeid)"

mit z.B. dieser SP:
Code:

Mit ODBC-Direktbereich (nur Access bis 2003) als Alternative zu ADODB kann man damit auch direkt ein DAO-Recordset bestücken.

BTW: Zu Access&MySQL gibt's leider keine Literatur. Man ist auf Foren angewiesen.
Meine Reihe auf AiU (Teil I bis IV) ergab aber viel Resonanz, so dass ich mir mit André schon überlegte, tatsächlich ein Buch dazu zu schreiben. Im Moment haben wir aber keine Zeit. *wink.gif*

Ciao, Sascha

 

Danke euch beiden!
@ Vincenz
Mit 'adodb' bei der Suche findet man schon ein bisken mehr. Bisher klickte ich mich mehr durch die englischen ADO-Hilfeseiten von A03. Eine ganz gute kompakte Übersicht habe ich inzwischen HIER gefunden. Die Beispiele sind zwar auf Access und den MS-Sql-Server ausgelegt, aber das liest sich schon etwas angenehmer *wink.gif*
Danke auch für die beiden Links. Vor allem der erste ist sehr gut kommentiert.

@Sascha
Ich wollte eigentlich alles, was der Server erledigen kann, auch auf diesem belassen, um die Performance zu erhöhen und den Netztraffic zu verringern, daher der Lösungsansatz per SP. Wie sieht der Performancevergleich zwischen SP und PT-Abfrage aus?

Kannst du geeignete Foren nennen?

Die AIU-Artikel hatte ich (sofern frei zu lesen) mit als erstes angesehen. Sie beleuchten aber ja leider nur einen minimalen Bereich.

Schade, dass ihr das Buch noch nicht umgesetzt habt. Für den Fall, dass ihr doch noch dazu kommt. Ich fände für Beispiele eine zweispaltigen Aufbau hilfreich: auf der einen Seite was auf dem MySQL-Server umzusetzen ist, auf der anderen was auf VBA-Seite zu geschehen hat. Falls ihr ein Kapitel aus Anfängersicht braucht oder eine Begründung für die Existenz des Buches ... ich liefer gerne Beides. *wink.gif*


BTW: Auf MySQL-Seite sind häufig eigentlich kleine Probleme, für die ich viel Zeit aufwenden muss, um sie zu lösen. Die MySQL-Referenz ist mir zu technisch ausgelegt und beinhaltet zu wenig Beispiele. Die Gegebenen funktionieren teilweise auch nicht.
Bsp: Select Date_ADD ('2010-02-03', Interval 1 DAY) liefert mir als Ergebnis BLOB (mit Date-Funktion drum herum geht´s)
Warum soll man für einen Access Ja/Nein-Wert ein TINYINT und nicht BOOLEAN verwenden?

Wer also noch (Praxis-)Links oder gute Literatur zu MySQL selbst hat ... wird dankend angenommen.

[Edit]
OLEDB soll, nach dem was ich gelesen habe, performanter sein als ODBC. Habe inzwischen auch einen Treiber (MyOLEDB) für MySQL gefunden. Hat da jemand Erfahrung mit, oder ist das Quatsch?
[/Edit]

 

Ja, der MyOLEDB-Provider ist Quatsch.
Der ist veraltet und unterstützt nur einen Bruchteil der Funktionen und ist außerdem buggy.

Wer behauptet, dass ODBC langsamer wäre, als OLEDB, der hat keine Ahnung - glaub das nicht!

Äh, sowohl Vincenz wie ich haben doch Beispiele dafür gegeben?
Der Code von mir oben ist die SP, die auf MySQL eingerichtet würde, das "Call fuServerKunde(kundeid)" ist die PT-Abfrage. Falls du dachtest, die SP sollte über VBA erzeugt werden, war das ein Missverständnis.
Ich hab beides hingeschrieben, damit der Zusammenhang zwischen SP und PT-Abfrage über den Parameter deutlich wird.

Performancemäßig gibt es nichts Schnelleres, als diese Kombination.
Der SQL-String der PT wird direkt an den Server geleitet und JET macht gar nichts, als das von diesem über ODBC zurückgegebene Rowset auch wieder direkt in ein Recordset zu speichern bzw. an ein Datenblatt zu liefern.

Grundsätzlich mal dazu: Ich bin kein Freund davon, ALLES SQL per Views und SPs auf den Server zu portieren. Denn dann muss der fast all das machen, was JET lokal auf dem Rechner macht. Üblicherweise aber arbeitet der Server für Dutzende von Usern und muss entspr. viel rechnen. Auch wenn das effizienter vonstatten geht, als JET es schafft - intelligenters Caching z.B. -, kann dabei die Kapazität des Servers überzogen werden. Ich hatte den Fall schon!
Ich bin deshalb für eine Balance und Aufgabenverteilung zwischen Server und Arbeitsplatzrechnern. Warum sollte man deren (inzwischen sehr hohe) Rechenleistung ungenutzt lassen?

Bez. Foren: Da gibt es kein spezielles für das Thema Access&MySQL.
Man muss halt sowohl Access-Foren, wie dieses, konsultieren, wie auch MySQL-Foren und -Newsgroups.
Das MySQL-Forum von MySQL selbst finde ich z.B. gar nicht schlecht, wenn man halbwegs Englisch kann.
Abgesehen davon gilt für Access&MySQL auch das meiste, wie für andere DBMS-Anbindungen. Und zu Access&Microsoft SQL gibt es ja so einiges an Literatur. Das lässt sich fast alles übertragen. (Z.B. wird auch bei MSSQL ein "tinyint" für ein Access-Bool-Feld verwendet. *wink.gif* )

Ciao, Sascha

 

.. und wenn jemand das Gegenteil behauptet, stimmt das auch nicht. *Smilie Es kommt nämlich ganz einfach darauf an, welcher Treiber schneller ist.
Der MySQL-OLEDB-Treiber ist (war?) nur ein modifizierter ODBC-Treiber. Bei MSSQL ist definitiv der OLEDB-Zugriff mit ADODB schneller, weil der OLEDB-Treiber einen schnelleren Zugriff erlaubt. (Das spielt sich aber alles im Millisekundenbereich ab und ist eigentlich zu vernachlässigen - der größte Block der Ausführungsdauer wird normalerweise für die Ausführung der SQL-Anweisung benötigt und da ist es ziemlich egal ob der Server die SQL-Anweisung per ODBC vs. OLEDB oder DAO vs. ADODB erhält.)

zu MySQL:
Bei meinen Tests (vor einigen Jahren) mit MySQL stellte ich überraschenderweise fest, dass der Weg über ADODB und ODBC schneller war als über Jet/PT-Abfrage und ODBC. Ich hätte damals erwartet, dass kein Unterschied bemerkbar ist bzw. die ADODB-Variante langsamer ist, da für die Nutzung des MySQL-ODBC-Treibers unter ADODB zusätzlich noch der MSDASQL zum Einsatz kommt. Der Unterschied war zwar wieder nur im Millisekundenbereich bis 1/10-Sekundenbereich - aber klar messbar und kein Zufallsergebnis.
Wird bei PT-Abfragen (gespeichert bzw. temporär) jedes Mal die Verbindung neu aufgebaut bzw. die Verbindung im Cache geprüft? Dann könnte ich mir die Unterschiede so erklären, dass ADODB beim Aufbau der Verbindung bzw. beim Übertragen der SQL-Anweisung schneller ist.

@Sascha:

Seit wann ist bit ein Tinyint? *Smilie
Ich verwende für Ja/Nein-Felder immer bit und nie tinyint. (Und wer behauptet das funktioniert nicht, hat keine Ahnung. *biggrin.gif*)
Bei Access-Abfragen auf verknüpfte Tabellen ist allerdings zu beachten, dass man für True auch True und nicht -1 als Vergleichswert schreibt .. aber das macht man ja sowieso, damit die Abfrage besser lesbar ist, oder? True kann Jet/ODBC nämlich ohne Probleme konvertieren. Bei -1 wird allerdings die Zahl weitergegeben und die stimmt dann serverseitig nicht mit 1 überein (True im Bit-Datentyp ist 1 als Zahl).

mfg
Josef

 

Das habe ich gesondert noch nicht analysiert.
Mir ist aber noch nicht aufgefallen, dass mehr als 2-3 Connections zu einer meiner DBs auf einer Maschine existieren.
Ich nehme eigentlich an, dass JET die Connection aus dem Pooling auch für die PTs verwendet.
BTW: Mit dem neueren "Allow multiple statements"-Flag in den Connect-Options kann man übrigens die Zahl der Verbindungen automatisch herabsetzen.

Mit meiner Aussage zu Performance ODBC vs. OLEDB hatte ich nicht Unterschiede im Millisekundenbereich im Sinn, sondern die zuhauf in irgendwelchen Foren anzutreffenden Pauschalaussagen dazu, wo angeblich ADODB 40% schneller sei, als ODBC, oder so ähnlich, was gar nicht direkt auf MySQL gemünzt ist. Sowas ist kompletter Nonsens und durch nichts zu belegen. Man kann natürlich jedes Test-Set so hinbiegen, dass man die Ergebnisse bekommt, die man will. *wink.gif*
Richtig ist hingegen, dass der Treiber die Performance macht. Und da, denke ich, ist in den letzten Jahren beim ODBC-Connector/MySQL 5.x-Gespann so einiges passiert.

Ciao, Sascha

 

Solche Aussagen sind besonders toll, weil ODBC (Treiber) mit ADDOB (Zugriffsbibliothek) verglichen wird. Ich wüsste zumindest nicht, wie ich ODBC direkt aus Access ansprechen kann, ohne Jet bzw. DAO arbeiten zu lassen. *Smilie

[etwas OT - gilt aber sinngemäß auch für die Vergleiche von PT/ODBC, Jet/DAO/ODBC, ADODB/ODBC, ADODB/OLEDB usw.]

Da kann man die übliche Regel bei Vergleichen anwenden: eine Aussage in Prozent und ohne Beschreibung des Tests kann man vergessen, da es gerade bei Performance-Messungen auf den Testablauf ankommt und vor allem aus den Werten die Sinnhaftigkeit einer Optimierung erkannt werden soll.

Mein Lieblingsbeispiel:
vbNullstring vs. ""
Code:

Die Zuweisung von vbNullstring (B) läuft um ungefähr 80% schneller als die Zuweisung von "" (A).
=> Dann müsste man also sofort alles auf vbNullstring umstellen und man hat damit seine Anwendung sehr beschleunigt ... sind ja immerhin 80%. *Smilie

Der Vergleich in Absolutwerten (gemessen unter identischen Messbedingungen - Absolutwerte nur als Anhaltswerte betrachten, die verändern sich laufen je nach Speicher, Prozessor und Lust und Laune von VBA):
A: 0,194 μs
B: 0,047 μs
=> Differenz: 0,000146 ms
Frage: wie oft muss vbNullstring statt "" (hintereinander) verwendet werden, damit der user den Performance-Vorteil erkennt? *biggrin.gif*

Anm.: vbNullstring ist trotzdem zu bevorzugen, weil es den Code besser lesbar (eindeutige Beschreibung des Vorhabens - bei "" hätte man vielleicht auch das Leerzeichzen zum Schreiben vergessen können) macht und sogar noch spezielle Auswertungen ermöglicht (strptr(vbnullstring) = 0).
Die Verwendung von vbNullstring aber nur aufgrund des 80%-Geschwindigkeitsvorteils zu begründen, finde ich etwas überzogen. Da argumentiere ich lieber damit, dass eine eindeutige Anweisung entsteht.


Bezüglich Geschwindigkeitszugriffen mit unterschiedlichen Datentechniken (ADO vs. DAO, ODBC vs. OLEDB usw.): so ein Vergleich macht meiner Ansicht nur Sinn, wenn man jede Technik unter Access/VBA für bestimmte Szenarien optimiert und dann den Geschwindigkeitsunterschied ermittelt. => Damit gelten diese Vergleiche allerdings nur für das eine spezielle Szenario.

mfg
Josef

 

Ich schon: http://msdn.microsoft.com/en-us/libr...8VS.85%29.aspx
*grins

So als Beispiel, wenn auch kein Resultset erhalten wird, was aber ebenfalls ginge:
http://www.ms-office-forum.net/forum...15&postcount=2

Ciao, Sascha

 

Das hatte ich vermutet (bzw. erhofft), als ich den Satz schrieb. *Smilie

Setzt du so etwas auch produktiv ein, um SQL-Statements (ohne Recordsetrückgabe) abzusetzen?
Falls ja, würde mich nämlich interessieren, ob es da bezüglich Geschwindigkeit einen Unterschied zu einer PT-Abfrage gibt. Denn das müsste dann annähernd der Jet-Anteil vom Aufruf der PT-Abfrage sein, oder?

mfg
Josef

 

Nein. Produktiv setze ich nur das Zeug mit der Connection-Abfrage ein, vielleicht noch Info-Rückfragen zum Status und Schema, aber nicht, um aktiv per ODBC-API Tabellen anzusprechen.
Damit habe ich nur experimentiert, ist aber recht aufwändig und ich sah bisher keinen Grund, JET durch einen "VBA-Treiber" zu ersetzen. *wink.gif*

Du hast aber Recht, dass so ein Vergleich interessant wäre.
Vielleicht komme ich umgehend dazu, das mal ein in einem Testprojekt abzuchecken.

Ciao, Sascha

 

Merci, erste SP erstellt (noch ohne Parameter) und einem DAO RS zugewiesen. Wenn man es einmal erfolgreich gemacht, hat ist´s ja wirklich simpel *Smilie
Code:

Der nächste Schritt wird die Umstellung auf Parameter sein. Gibt es bzgl. Datumsübergaben etwas besonderes zu beachten (ich frag mal im Vorhinein wg. der schlechten Erfahrungen (s.#4))?

Bzgl. Sicherheit:
Den Connection-String habe ich in der PT-Abfrage gespeichert. Kann dieser inkl. Passwort irgendwie ausgelesen werden, wenn die Anwendung später mal als MDE verteilt wird? Wenn ja, gibt es Alternativen?

 

Hallo Marsu,

Du musst natürlich die Datumswerte in einem entsprechenden Format übergeben. Sonst werden die nicht richtig erkannt.

Umsteigen auf ADODB. Dann kannst Du das Ganze im Code unterbringen.
Luftcode:
Code:

Also vom Prinzip her deiner PT-Abfrage sehr ähnlich, nur eben alles im Code.

Gruß
Vincenz

 

Code:

Falls du das Passwort im Connectionstring angegeben hast, kann man es auch in einer mde auslesen. Das gilt übrigens auch für verknüpfte Tabellen.

Mögliche Variante: Verknüpfung ohne Passwort speichern und beim Anwendungsstart eine temporäre PT-Abfrage öffnen, in der die Logindaten im Connectionstring enthalten sind. Das öffnet dann einen Verbindung zum Server und du kannst auch die Tabellen bzw. PT-Abfragen ohne Loginkennung öffnen - zumindest wenn bei den verknüpften Tabellen bzw. PT-Abfragen der Connectionstring bis auf die Logindaten identisch ist.

Du könntest auch jedes Mal vor Verwendung den Connectionstring einstellen und nach Verwendung wieder entfernen. Das halte ich aber für unpraktisch. Würdest du den Connectionstring beim Anwendungsstart mit dem Passwort aktivieren und beim Anwendungsende wieder entfernen, dann ist das Passwort während dieser Zeitspanne auslesbar.

BTW: Es gibt aber auch die Möglichkeit eine temporäre PT-Abfrage zu erstellen.
Code:

mfg
Josef