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

0 votes

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.

enter image description hereIn 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:

enter image description here

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. 

Apr 2, 2023 in Others by narikkadan
• 63,420 points
455 views

1 answer to this question.

0 votes

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
answered Apr 2, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

How to automatically get a specified range of data from Excel to XML in VBA

Range method works, always identify the sheet ...READ MORE

answered Mar 18, 2023 in Others by narikkadan
• 63,420 points
511 views
0 votes
1 answer

How to import data from a HTML table on a website to excel?

Hello  To import any HTML file in excel there ...READ MORE

answered Feb 10, 2022 in Others by gaurav
• 23,260 points
6,200 views
0 votes
1 answer

How to connect ms excel-2007 with mysql.

Try this: Function runQuery() Dim cn As Object Dim rs ...READ MORE

answered Dec 23, 2022 in Others by narikkadan
• 63,420 points
305 views
0 votes
1 answer

Online Excel Web-based Platform

You can use any of the following ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,420 points
322 views
0 votes
1 answer
0 votes
1 answer

Online MYSQL editor as an Excel-like spreadsheet

That depends on who are those users, ...READ MORE

answered Oct 8, 2022 in Others by narikkadan
• 63,420 points
728 views
0 votes
1 answer

Excel VBA: how to find a description from an AD-group

First add the 'description' property to your ...READ MORE

answered Feb 16, 2023 in Others by Kithuzzz
• 38,010 points
541 views
0 votes
1 answer

How to make an error flagging array in VBA and translate all array elements as a string message?

In my opinion, using an array in ...READ MORE

answered Mar 17, 2023 in Others by Kithuzzz
• 38,010 points
322 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP