Consulting

Results 1 to 4 of 4

Thread: Parameterized MySQL SP as Form data input

  1. #1
    VBAX Regular
    Joined
    Jul 2022
    Posts
    7
    Location

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

  2. #2
    VBAX Mentor
    Joined
    Sep 2019
    Location
    Philippines
    Posts
    406
    Location
    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.

  3. #3
    VBAX Regular
    Joined
    Jul 2022
    Posts
    7
    Location
    Quote Originally Posted by arnelgp View Post
    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 View Post
    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.

  4. #4
    VBAX Regular
    Joined
    Jul 2022
    Posts
    7
    Location
    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
    Last edited by ArneG; 07-26-2022 at 04:27 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •