Parameterized MySQL SP as Form data input

[ArneG]

Hi guys,
I am totally lost, on getting around with parameterized stored procedures on a MySQL Server and binding the result of them to a Form recordset.

On the net, I found dozens of "approaches" but they did not yield an working solution.

So, here is my setup:

I am using a MySQL Server which is connected via the MySQL ODBC Driver (Version 8). This server hosts a schema which consists of the following stored procedure:


PROCEDURE `mapCompetenceToPerson`(IN pID Integer)
BEGIN
select * from `ma-profile`.skill as s left join (select PersonID, SkillID from `ma-profile`.person_skills where PersonId = pID) as ps
on s.ID = ps.SKillID;
END

On the Access Site I have build up some routines like

Public Function RDAOGet(ByVal procCall As String) As ADODB.Recordset
Dim rs As New ADODB.Recordset

openConn ' prepares the global ADODB.Connection 'conn'
Set rs = conn.Execute("Call " & procCall & ";") ' not a one liner for debug purposes
Set RDAOGet = rs
closeConn
End Function
Yepp, I am currently using ADO to retrieve the data. I played around with DAOs, but I could not bring parameters to work.

The calling routine looks as follows

Private Sub Form_Load()
Dim rs As ADODB.Recordset
Set rs = RDAOGet("mapCompetenceToPerson(1)") ' not a one liner for debug purposes
Set Me.Recordset = rs
End Sub

While this actually results in a recordset holding data from the SP Execution. The 'rs' could not be assigned to the form's recordset, since its not a "valid" recordset.
29980

Now, the big question is: Am I on the right track, or did I made some wrong turns to get here? Because right now, I have no clue what the correct way may look like.

[arnelgp]

first question on your stored proc, you are joining two tables, and only getting fields from one table? so what is the purpose of the join if you only want to show
fields from one side of the join.

next, you can just create a Linked ODBC table to ms access, then create the required query (joined table) into ms access.

[ArneG]

first question on your stored proc, you are joining two tables, and only getting fields from one table? so what is the purpose of the join if you only want to show
fields from one side of the join.
I do join the tables, but also having the rows PersonID and SkillID from the righthand site. Reason: I want a full list of all skills and their possible association to a person: Left Join ;-)


next, you can just create a Linked ODBC table to ms access, then create the required query (joined table) into ms access.
Yes, i did that just to begin with, but sorted it out - for the moment at least. I want to hide away sql code and table structure as much as possible. Also, doing it that way is the more Client-Server approach, which brings me the benefit of not having to load all the data to the local machine for, lets say, joining them. This is fast and for a VPN-connection based system desirable.

[ArneG]

I was able to solve it on my own. Yippy!

Part of my problem dispirited above, was a wrong CursorLocation/CursorType. It was set to adUseServer :banghead:

For anybody interested some code of my current solution follows (Inspired by some internet sources). Error handling is still missing nearly completely!

Public Function load(ByVal procedurName As String, ParamArray params()) As ADODB.Recordset
Dim cmd As ADODB.Command
Dim rst As New ADODB.Recordset

rst.CursorLocation = adUseClient

Set cmd = createCmd(procedurName, params)
rst.Open cmd
Set load = rst
End Function



Private Function createParam(ByVal pName As String, pValue)
Dim param As New ADODB.Parameter

With param
.name = pName
.Direction = adParamInput
If TypeName(pValue) = "String" Then
.Type = adVarChar
.Size = Len(CStr(pValue))
.value = CStr(pValue)
ElseIf TypeName(pValue) = "Integer" Then
.Type = adInteger
.value = CInt(pValue)
ElseIf TypeName(pValue) = "Double" Then
.Type = adDouble
.value = CDbl(pValue)
Else
errMsg = "Kein Parametermapping für " & TypeName(pValue) & " hinterlegt."
GoTo FEHLER
End If
End With
Set createParam = param
Exit Function

FEHLER:
Debug.Print "RDAO.createParam > Es ist eine Fehler aufgetreten: " & errMsg
End Function



Private Function createCmd(ByVal spName As String, ParamArray params())
Dim errMsg As String
Dim cmd As New ADODB.Command

If (UBound(params(0)) - (LBound(params(0)) + 1)) Mod 2 <> 0 Then
errMsg = "Anzahl der Parameter darf nicht ungerade sein."
GoTo FEHLER
End If

openConn

With cmd
.ActiveConnection = conn
.CommandText = spName ' SP
.CommandType = adCmdStoredProc
.CommandTimeout = 15
End With

For i = LBound(params(0)) To UBound(params(0))
cmd.Parameters.Append createParam(params(0)(i), params(0)(i + 1))
i = i + 1
Next i

Set createCmd = cmd
Exit Function

FEHLER:
Debug.Print "RDAO.createCmd > Es ist eine Fehler aufgetreten: " & errMsg
End Function