Parameterized MySQL SP as Form data input

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:

Code:

---

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

Code:

---

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

Code:

---

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.
Attachment 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.

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.

Quote:

---

Originally Posted by 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.

---

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 ;-)

Quote:

---

Originally Posted by arnelgp

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.

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!

Code:

---

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

---