How to pass a JSON-formatted string from Excel VBA as argument to a MySQL Store Procedure

Question

The following MySQL stored procedure reads an array of JSON objects and inserts them into a JSON column of a temporary table:

DELIMITER $$
DROP PROCEDURE IF EXISTS proc_json $$
CREATE OR REPLACE PROCEDURE myProcedure(IN myjson TEXT)
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE qryStmt TEXT;
DROP TEMPORARY TABLE IF EXISTS tempTable;
CREATE TEMPORARY TABLE tempTable(update_key int NOT NULL AUTO_INCREMENT, update_data JSON, PRIMARY KEY(update_key));
WHILE i < JSON_LENGTH(myjson) DO
    SET qryStmt = CONCAT("INSERT INTO tempTable VALUES(DEFAULT, (JSON_EXTRACT('", myjson,"','$[",i,"]')))");
    PREPARE stmt FROM qryStmt;
    EXECUTE stmt;
    SET i = i+1;
END WHILE;
END $$
DELIMITER ;

The Stored Procedure works fine when I call the procedure in MySQL >CALL myProcedure('[{"firstname": "Tom", "lastname": "Cruise", "occupation": "Actor"}, {"firstname": "Al", "lastname": "Pacino", "occupation": "Actor"}]');, it reads the string as separate JSON objects and inserts them as records in the table.

In Excel VBA, I also have the following Sub, which when called will deliver a JSON-formatted array of objects to the stored procedure:

Sub proc_jason()

On Error GoTo ErrorHandler

Dim strConnection, jsonString As String

Set objConnection = New ADODB.Connection
Set objRecordset = New ADODB.Recordset

jsonString = "[{""firstname"": ""Tom"", ""lastname"": ""Cruise"", ""occupation"": ""Actor""}, " _
             & "{""firstname"": ""Alfredo"", ""lastname"": ""Pacino"", ""occupation"": ""Actor""}]"

strConnection = "Driver={MySQL ODBC 8.0 ANSI Driver}; Server=[IP]; Database=[Db]; user=[usr]; PWD=[pwd]"
objConnection.Open strConnection
         
With objRecordset
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .Open "CALL myProcedure('" & jsonString & "');", objConnection, adOpenForwardOnly, adLockReadOnly, adCmdStoredProc
End With

objRecordset.Close
Set objRecordset = Nothing
objConnection.Close
Set objConnection = Nothing

Exit Sub

ErrorHandler:
    MsgBox Err.Description

End Sub

The Sub returns the following error:

I've seen posts calling a MySQL Store Procedure using the Command object:

With objCommand
    .ActiveConnection = objConnection
    .CommandType = adCmdStoredProc
    .CommandText = "CALL myProcedure('" & jsonString & "');"
    .Execute
End With

But I still get the same error message.

Kithuzzz · Answer 1 · Apr 2, 2023

Set the parameter length to the json string length.

Option Explicit

Sub demo()

    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    con.Open "mysqlu" ' dsn
    
    Const json = "a json string"
    
    With New ADODB.Command
        .ActiveConnection = con
        .CommandType = adCmdStoredProc
        .CommandText = "myProcedure"
        .Parameters.Append .CreateParameter("P1", adLongVarChar, adParamInput, Len(json), json)
        MsgBox .Execute.GetString
    End With
    con.Close
  
End Sub

CREATE PROCEDURE `myProcedure`(IN myjson TEXT)
BEGIN
    select myjson as result;
END