MS-Office-Forum

Zurück   MS-Office-Forum > Microsoft Access & Datenbanken > Microsoft Access
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads
Themen-Optionen Ansicht
Alt 10.03.2018, 22:05   #1
stefaktiv
MOF User
MOF User
Standard Acc2013 - ADO-Alternative für einfachen DAO-Aufruf einer SP im SQL-Server

Ich habe eine einfache Routine um beliebige Stored Procedures via VBA & DAO von einem SQL-Server aufzurufen. Das schöne daran ist, dass man beliebig viele Parameter hintereinander klemmen kann und sich darum schon mal keine Sorgen machen muss:


Code:

Public Function SPRecordset(strStoredProcedure As String, intAufruftyp As Integer, _
                            ParamArray varParameter() As Variant) As DAO.Recordset

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strParameter As String

Set db = CurrentDb
Set qdf = db.CreateQueryDef("")

'Wenn der Connection-String noch unbelegt ist, jetzt holen
If strSQLODBC = vbNullString Then
   strSQLODBC = Funktion_Connection_String("ODBC", "", "")
   End If


'Aus den übergebenen Parametern einen String erzeugen
strParameter = Parameterliste(varParameter)


With qdf
     .Connect = strSQLODBC
     
     Select Case intAufruftyp
     
            Case Is = 0
                 .sql = "EXEC " & strStoredProcedure
            
            Case Is = 1
                 .sql = "EXEC " & strStoredProcedure & " " & strParameter
                 
            End Select
                 
     Set SPRecordset = .OpenRecordset
     On Error GoTo 0
     End With

Set db = Nothing



Exit Function


sprFehler:

Call Fehler("Stored_Procedures", "SPRecordset", 0, "")

Set qdf = Nothing
Set db = Nothing

End Function

Der Aufruf ist denkbar einfach - z.B. via

Code:

Set rst_Person = SPRecordset("Prod_Aufruf_Person", 1, intPers_ID)

Jetzt hätte ich gerne für einen Aufruf mittels ADO / ADODB gleichermaßen eine so leichte Möglichkeit. Leider hab ich nur Lösungen mit Command-Befehlen gefunden. Das geht zwar technisch ohne Probleme - allerdings muss man immer pro Parameter einen eigenen Parameter im Command-Befehl definieren. Das ist für einen Aufruf mit beliebig vielen Parametern aber hinderlich.

Gibt es ggf. eine einfachere Möglichkeit?

Irgendwo hab ich eine Lösung gesehen, bei welcher der SQL-String mit einem Execute-Befehl über die Connection abgesetzt wurde. Leider scheint das eine One-Way-Lösung zu sein - man bekommt also nicht einmal ein Ergebnis. Und ein ADO-Recordset wird auch nicht zurückgegeben.
stefaktiv ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.03.2018, 07:10   #2
Josef P.
MOF Guru
MOF Guru
Standard

Hallo!

Sowohl ADODB.Recordset.Open als auch ADODB.Connection.Execute können einen SQL-Text mit "exec Schema.Prozedurname @Param1 = ..." verarbeiten und ein Recordset zurückgeben.
Wie sieht der von der getestet VBA-Code aus, der kein Recordset zurückgibt?

Zur Sicherheit nachgefragt: "Set nocount on" ist in der Prozedur im SQL-Server eingestellt?

mfg
Josef
Josef P. ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.03.2018, 07:26   #3
markusxy
MOF Meister
MOF Meister
Standard

Du könntest an ein Command Objekt das Parameter Array direkt übergeben - sofern alles korrekt Formatiert ist. Das Formatieren erspart man sich über die Parameter Collection.
Schlussendlich macht es auch nichts anderes als einen String zu erzeugen und natürlich einiges mehr.

Wenn man z.b. bei Shapes seine SP einsetzen möchte, geht es auch nur indem man den String selbst zusammenstellt.
markusxy ist gerade online  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.03.2018, 14:00   #4
stefaktiv
Threadstarter Threadstarter
MOF User
MOF User
Standard

Zitat: von Josef P. Beitrag anzeigen

Sowohl ADODB.Recordset.Open als auch ADODB.Connection.Execute können einen SQL-Text mit "exec Schema.Prozedurname @Param1 = ..." verarbeiten und ein Recordset zurückgeben.

Muss die Bezeichnung von "@Param1 =" dann mit derjenigen übereinstimmen, die in der betreffenden Prozedur hinterlegt sind? Das wäre schwierig, weil es eben so viele Prozeduren sind.


Zitat: von markusxy

Du könntest an ein Command Objekt das Parameter Array direkt übergeben - sofern alles korrekt Formatiert ist.

Also korrekt formatiert sind die Parameter auch in der DAO-Routine schon. Das erledigt die Funktion

Code:

Parameterliste(varParameter)
Dort wird z.B. ein String-Wert in Anführungszeichen gesetzt oder Datumswerte entsprechend formatiert. Daher könnte ich eigentlich diesen String auch direkt verwenden.

Wie genau übergebe ich das jetzt an das Command Objekt - also wohin genau?
stefaktiv ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.03.2018, 14:25   #5
Josef P.
MOF Guru
MOF Guru
Standard

Zitat:

Muss die Bezeichnung von "@Param1 =" dann mit derjenigen übereinstimmen, die in der betreffenden Prozedur hinterlegt sind? Das wäre schwierig, weil es eben so viele Prozeduren sind.

Natürlich müssen die genau so wie bei der Verwendung in der Pass-Through-Abfrage übereinstimmen.
Wenn du @xyz = 1 übergibst, wird der SQL-Server nicht erkennen können, dass der Wert 1 zum Parameter @abc gehört, weil es @xyz nicht gibt.

Code:

"EXEC " & strStoredProcedure & " " & strParameter
Die Zusammenstellung des SQL-Ausdrucks bleibt im Vergleich zur PT-Abfrage unverändert.

mfg
Josef

Geändert von Josef P. (11.03.2018 um 14:28 Uhr).
Josef P. ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.03.2018, 14:40   #6
markusxy
MOF Meister
MOF Meister
Standard

Zitat: von stefaktiv Beitrag anzeigen

Wie genau übergebe ich das jetzt an das Command Objekt - also wohin genau?

Benützt du die VBA Hilfe eigentlich auch?

Schau dir die command.Execute Methode an und lies es sorgfältig durch.
Da findest du alles dazu

LG Markus

/Edit:
Schau dir auch alle Infos zum CommandObjekt und aller Methoden/Eigenschaften an.
Um Effizient mit ADO arbeiten zu können musst du das wissen. Ansonsten bist du immer nur am Suchen von Beispielcodes und kannst ihn dennoch nicht effizient einsetzen.

Geändert von markusxy (11.03.2018 um 14:43 Uhr).
markusxy ist gerade online  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 12.03.2018, 19:58   #7
stefaktiv
Threadstarter Threadstarter
MOF User
MOF User
Standard

Leider führt weder die Suche in der MS-Hilfe, noch im wirklich ausführlichen Buch "Office Access Programmierung" von MS Press zu dem gewünschten Ergebnis. Es ist mir zwar schon gelungen Stored Procedures mit einem Parameter aufzurufen - spätestens ab dem zweiten klappt es aber nicht mehr.

Hier mein aktueller Code:


Code:

Public Function SPRecordset_ADO(strStoredProcedure As String, intAufruftyp As Integer, _
                                ParamArray varParameter() As Variant) As ADODB.Recordset

On Error GoTo sprFehler

Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim conn As New ADODB.Connection

Dim strParameter As String
Dim i As Integer



'Wenn der Connection-String noch unbelegt ist, jetzt holen
If strSQLOLEDB = vbNullString Then
   strSQLOLEDB = Funktion_Connection_String("SQLOLEDB", "", "")
   End If


'Aus den übergebenen Parametern einen String erzeugen
strParameter = Parameterliste(varParameter)


'Direkte Verbindung zum SQL-Server öffnen
With conn
     .CursorLocation = adUseClient
     .ConnectionString = strSQLOLEDB
     
     .Open
     End With


'Jetzt den Command-Befehl erstellen und definieren
cmd.CommandType = adCmdStoredProc
cmd.CommandText = strStoredProcedure
cmd.ActiveConnection = conn
cmd.CommandTimeout = 120
cmd.Parameters.Refresh


For i = 0 To UBound(varParameter(0))
    cmd.Parameters(i + 1) = varParameter(0)(i)
    Next i



Set rst = cmd.Execute



Set SPRecordset_ADO = rst


On Error GoTo 0
Exit Function

sprFehler:


Set rst = Nothing
Set conn = Nothing
Set cmd = Nothing


End Function


Dazu noch folgende Funktion für die Parameterliste:


Code:

Public Function Parameterliste(ByVal varParameter As Variant) As String

Dim strParameter As String
Dim i As Integer


On Error Resume Next
i = UBound(varParameter(0))



If CBool(Err.Number = 0) Then
   
   On Error GoTo sprFehler
   
   For i = LBound(varParameter(0)) To UBound(varParameter(0))
    
       Select Case VarType(varParameter(0)(i))
           
              Case vbString
                   If varParameter(0)(i) = "NULL" Then
                      strParameter = strParameter & ", " & varParameter(0)(i)
                      Else
                      strParameter = strParameter & ", '" & varParameter(0)(i) & "'"
                      End If
           
              Case 0, 1
                   strParameter = strParameter & ", NULL"

              Case Else
                   strParameter = strParameter & ", " & Replace(CStr(varParameter(0)(i)), ",", ".")
           
              End Select

       Next i
   
   Else
   
   On Error GoTo sprFehler
   
   For i = LBound(varParameter) To UBound(varParameter)
    
       Select Case VarType(varParameter(i))
           
              Case vbString
                   If varParameter(i) = "NULL" Then
                      strParameter = strParameter & ", " & varParameter(i)
                      Else
                      varParameter(i) = Replace(varParameter(i), "'", "''")
                      strParameter = strParameter & ", '" & varParameter(i) & "'"
                      End If
           
              Case 0, 1, 10
                   strParameter = strParameter & ", NULL"

              Case Else
                   strParameter = strParameter & ", " & Replace(CStr(varParameter(i)), ",", ".")
           
              End Select

       Next i

   End If
   
   
   


If Len(strParameter) > 0 Then
   strParameter = Mid$(strParameter, 3)
   End If


Parameterliste = strParameter


On Error GoTo 0
Exit Function


sprFehler:


End Function

Ich hab auch schon versucht mit "EXEC NAME_SP Parameter" als cmdText zu arbeiten - leider hat das auch nicht funktioniert. Gleiches gilt, wenn ich die Parameterliste mit dem Connection-Objekt abschicke und daraus den Recordset entnehmen will.
stefaktiv ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 12.03.2018, 20:05   #8
stefaktiv
Threadstarter Threadstarter
MOF User
MOF User
Standard

Ach ja - eine nette neue, nette Fehlermeldung gibt es auch noch:

Zitat:

Der Datenprovider oder ein anderer Dienst gab den Status E_FAIL zurück.

stefaktiv ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 12.03.2018, 21:20   #9
Josef P.
MOF Guru
MOF Guru
Standard

Hallo!

Ich verwende einmal deine anfangs gezeigte PT-Abfrage-Variante und mach daraus eine PT- und eine ADODB-Variante.
Der folgende Code ist nur Luftcode => enthält möglicherweise Fehler, aber die Vorgehensweise sollte erkennbar sein.

Code:

Public Function SPRecordset(byval strStoredProcedure As String, byval intAufruftyp As Integer, _
                            ParamArray varParameter() As Variant) As DAO.Recordset

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim SqlText as string

SqlText = GetTsqlText(strStoredProcedure, intAufruftyp, varParameter)

Set db = CurrentDb
Set qdf = db.CreateQueryDef("")

'Wenn der Connection-String noch unbelegt ist, jetzt holen
If strSQLODBC = vbNullString Then
   strSQLODBC = Funktion_Connection_String("ODBC", "", "")
   End If


With qdf
     .Connect = strSQLODBC
     .SQL = SqlText           
     Set SPRecordset = .OpenRecordset
     On Error GoTo 0
     End With

Set db = Nothing

Exit Function


sprFehler:

Call Fehler("Stored_Procedures", "SPRecordset", 0, "")

Set qdf = Nothing
Set db = Nothing

End Function

Public Function SPRecordset_ADO(strStoredProcedure As String, intAufruftyp As Integer, _
                            ParamArray varParameter() As Variant) As ADODB.Recordset

dim rs as adodb.recordset
dim cnn as adodb.connection
Dim SqlText as string

SqlText = GetTsqlText(strStoredProcedure, intAufruftyp, varParameter)

'Wenn der Connection-String noch unbelegt ist, jetzt holen
If strSQLOLEDB = vbNullString Then
   strSQLOLEDB = Funktion_Connection_String("SQLOLEDB", "", "")
   End If

set cnn = new adodb.connection ' ich würde diese Connection nicht immer neu öffnen sondern eine Connection offen halten
cnn.open  strSQLOLEDB 

set rs = new adodb.recordset
rs.CursorLocation = adUseClient
rs.open SqlText, cnn

set SPRecordset_ADO = rs

end function


private Function GetTsqlText(byval strStoredProcedure As String, byval intAufruftyp As Integer, _
                            byref varParameter() As Variant) As string

     Dim strParameter As String

     'Aus den übergebenen Parametern einen String erzeugen
     strParameter = Parameterliste(varParameter)


     Select Case intAufruftyp
     
            Case Is = 0
                 GetTsqlText = "EXEC " & strStoredProcedure
            
            Case Is = 1
                 GetTsqlText = "EXEC " & strStoredProcedure & " " & strParameter
                 
     End Select

end function

Anm.:
Ich selbst verwende die Klasse AdodbHandler für den OLEDB-Zugriff.


mfg
Josef

Geändert von Josef P. (12.03.2018 um 21:31 Uhr).
Josef P. ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 13.03.2018, 20:05   #10
stefaktiv
Threadstarter Threadstarter
MOF User
MOF User
Standard

Leider kommt wieder die bisher unbekannte Fehlermeldung:
Code:

Der Datenprovider oder ein anderer Dienst gab den Status E_FAIL zurück.
Auffällig ist folgendes: wird nur ein Parameter übergeben, dann geht es. Sind es mehrere, dann geht es nicht. Also bei Stored-Procedures, bei denen nur eine ID als Übergabe erwartet wird, da klappt es. Bei anderen klappt es selbst dann nicht, wenn nur die ID übergeben wird und beim zweiten Wert schon ein Standard vorgegeben ist. Bei einer DAO-Übergabe klappt das schon.


Zu dem Hinweis mit der Connection wäre meine Frage, ob es von der Performance besser ist, wenn ich ganz zu Beginn beim Öffnen der Datenbank die Connection aufbaue und als Global bereitstelle. Schließt sich das nicht nach einiger Zeit von selbst wieder?
stefaktiv ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 13.03.2018, 20:18   #11
Josef P.
MOF Guru
MOF Guru
Standard

Hallo!

Zitat:

Auffällig ist folgendes: wird nur ein Parameter übergeben, dann geht es.

Wie sieht eine SQL-Anweisung mit mehreren Parametern aus, bei der dieser Fehler kommt?

Code:

Public Function SPRecordset_ADO(strStoredProcedure As String, intAufruftyp As Integer, _
                            ParamArray varParameter() As Variant) As ADODB.Recordset

dim rs as adodb.recordset
dim cnn as adodb.connection
Dim SqlText as string

SqlText = GetTsqlText(strStoredProcedure, intAufruftyp, varParameter)

'Wenn der Connection-String noch unbelegt ist, jetzt holen
If strSQLOLEDB = vbNullString Then
   strSQLOLEDB = Funktion_Connection_String("SQLOLEDB", "", "")
   End If

set cnn = new adodb.connection ' ich würde diese Connection nicht immer neu öffnen sondern eine Connection offen halten
cnn.open  strSQLOLEDB 

debug.print SqlText

set rs = new adodb.recordset
rs.CursorLocation = adUseClient
rs.open SqlText, cnn

set SPRecordset_ADO = rs

end function
Und da die Frage noch offne ist: Ist in den gepseicherten Prozeduren "SET NOCOUNT ON" gesetzt?

mfg
Josef

Geändert von Josef P. (13.03.2018 um 20:21 Uhr).
Josef P. ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 13.03.2018, 20:52   #12
stefaktiv
Threadstarter Threadstarter
MOF User
MOF User
Standard

Also die Parameter sind nur zwei Zahlen - eine ID und die Aufrufart.

Beispiel: "EXEC spMeineProzedur 1710, 1"


SET NOCOUNT geht leider nicht:

Code:

Anweisung "SET OPTION ON" wird von systemintern kompilierte Module nicht unterstützt.
Die Stored Procedures sind nämlich "native kompiliert". Ich hab den Server auf die In-Memory-Technik umgestellt. Beim DAO-Aufruf geht es wie gesagt.
stefaktiv ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 14.03.2018, 07:24   #13
sonic8
MOF Profi
MOF Profi
Standard

Zitat: von stefaktiv Beitrag anzeigen

SET NOCOUNT geht leider nicht:
[...]
Die Stored Procedures sind nämlich "native kompiliert".

Dieses Details hättest auch etwas früher schon mal erwähnen können.

Dann ist SET NOCOUNT ON nicht erforderlich, weil automatisch aktiv für die SP.

Mal davon abgesehen, dass varParameter in deinem Code bei direktem Aufruf kein multidimensionales Array ist, sollte der Code so funktionieren.

Bzgl. multidimensionales Array: Ich weiß nicht, wie du deinen Code aufrufst, dass es deswegen nicht zu einem Laufzeitfehler kommt, aber vielleicht ist das schon die einfache Ursache dafür, dass dein Code bestenfalls mit einem Parameter funktioniert!

Wenn das nicht der Fehler war, dann zeig mal ein minimales, aber vollständiges (inkl. SP-Code), Beispiel, das den Fehler reproduziert.

__________________

Neues Access 2019 Feature angekündigt: Modern Charts
sonic8 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 17.03.2018, 21:04   #14
stefaktiv
Threadstarter Threadstarter
MOF User
MOF User
Standard

Hier die SP:

Code:

ALTER PROCEDURE [dbo].[Prod_Aufruf_Person] 
           
	@intID				int,
	@intAufrufart		int = 1
		

WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS  
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'german', DELAYED_DURABILITY = ON) 


-- Ruft alle Daten zu einer Person auf
IF	@intAufrufart = 1
	BEGIN
	SELECT	Pers_ID, Pers_Nachname, Pers_Geburtsname, Pers_Vorname, Pers_Vorname_Weitere, Pers_Geburtsdatum, Pers_Geburtsort, Pers_Geburtsland, 
			Pers_Geschlecht, Pers_Religion, Pers_Nickname, Pers_Profil, Pers_Schwimmer, Pers_Freizeitsperre, Pers_Juleica_berechtigt, Pers_Juleica_Inhaber, 
			Pers_Zwilling, Pers_Krankheiten, Pers_Hinweistext, Pers_Anlagedatum, Pers_angelegt_durch, Pers_Letzte_Änderung, Pers_geändert_durch
	FROM	dbo.Personen
	WHERE	Pers_ID = @intID
	RETURN
	END


-- Ruft alle IDs auf, die größer als eine bestimmte ID einer Person sind
IF	@intAufrufart = 2
	BEGIN
	SELECT	Pers_ID
	FROM	dbo.Personen
	WHERE	Pers_ID > @intID
	RETURN
	END



END

Hier der Aufruf, also dass, was anschließend in strSQL steht:

Code:

EXEC Prod_Aufruf_Person 8838, 1

Der vollständige Aufruf sieht jetzt so aus:


Code:

'SQL-String
Select Case intAufruftyp
     
       Case Is = 0
            strSQL = "EXEC " & strStoredProcedure
            
       Case Is = 1
            strSQL = "EXEC " & strStoredProcedure & " " & strParameter
                 
       End Select


'Direkte Verbindung zum SQL-Server öffnen
With conn
     .ConnectionString = strSQLOLEDB
     .Open
     End With


rst.CursorLocation = adUseClient
rst.Open strSQL, conn
stefaktiv ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 17.03.2018, 21:08   #15
stefaktiv
Threadstarter Threadstarter
MOF User
MOF User
Wink

Als Ergebnis wird angezeigt:

Zitat:

Laufzeitfehler -2147467259 (80004005)
Der Datenprovider oder ein anderer Dienst gab den Status E_FAIL zurück

stefaktiv ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 12:55 Uhr.


Partner und Co.
Access-Paradies -Alles rund um die Datenbank Microsoft Access -Code -Programme-Tools -Tipps   Kostenlose Tipps & Tricks, Downloads und Programme   www.kulpa-online.com - Tipps - Tricks - Tutorials - Meinungen - Downloads uvm...   vb@rchiv · Willkommen in der Welt der VB Programmierung   Access-Garhammer - Hier finden Sie jede Menge Beispiel-Datenbanken zu Access und mehr ...   mcseboard.de   Die Top Seite für Excel-VBA-Makros uvm.

Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

Copyright ©2000-2018 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günter Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.