Question 20232 in Mysql Question posted in
The official MySQL documentation can be found here.

How to pass a JSON-formatted string from Excel VBA as argument to a MySQL stored procedure

I have the following stored procedure in MySQL that reads a JSON array of objects and insert the objects in a JSON column of a temp 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.

enter image description here

I also have the following Sub in Excel VBA that intends to call the stored procedure, passing 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:

enter image description here

I’ve seen posts calling a MySQL stored 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. Any help is appreciated.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks to Sam and CPD1802 inputs the final Sub that worked is the following:

    Sub demo()

On Error GoTo errHandler

    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection
    conn.Open "Driver={MySQL ODBC 8.0 ANSI Driver}; Server=ip; Database=db; user=usr; password=psw"
    
    Const json = "[{""firstname"": ""Tom"", ""lastname"": ""Cruise"", ""occupation"": ""Actor""}, " _
             & "{""firstname"": ""Alfredo"", ""lastname"": ""Pacino"", ""occupation"": ""Actor""}, " _
             & "{""firstname"": ""Winston"", ""lastname"": ""Churchill"", ""occupation"": ""Politician""}]"
    
    With New ADODB.Command
        .ActiveConnection = conn
        .CommandType = adCmdStoredProc
        .CommandText = "myProcedure"
        .Parameters.Append .CreateParameter("myjson", adLongVarChar, adParamInput, Len(json), json)
        MsgBox .Execute.GetString
    End With
    conn.Close
    
    Exit Sub

errHandler:
    MsgBox Err.Description
    
End Sub

    No error message but still the Stored Procedure did not insert the records in the table. I dropped TEMPORARY in the lines of the Stored Procedure:

    DROP TABLE IF EXISTS tempTable;
CREATE TABLE tempTable(update_key int NOT NULL AUTO_INCREMENT, update_data JSON, PRIMARY KEY(update_key));

    And now it works, for some reason using TEMPORARY TABLE when calling a Stored Procedure from Excel VBA doesn't work.


  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search