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.
InvalidRecordset.jpg

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

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