如何从Excel VBA传递JSON格式字符串作为MySQL存储过程参数

我有一个MySQL存储过程，可读取JSON对象数组并将对象插入临时表的JSON列，在MySQL中直接调用运行正常，但通过Excel VBA调用该存储过程、传递JSON格式数组对象时出现报错，尝试了Recordset和Command对象两种调用方式均未解决。

现有代码 #

存储过程代码 #

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 ;

Excel VBA调用代码（Recordset方式） #

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

尝试过的Command对象调用方式 #

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

解决方案 #

1. 修正VBA参数传递方式 #

原调用方式存在语法冲突（JSON中的引号与SQL拼接的引号冲突），且adCmdStoredProc模式下无需手动拼接CALL语句。改用参数化传递：

Sub proc_json_fix()
On Error GoTo ErrorHandler

Dim strConnection As String, jsonString As String
Dim objConnection As New ADODB.Connection
Dim objCommand As New ADODB.Command
Dim objParam As ADODB.Parameter

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 objCommand
    .ActiveConnection = objConnection
    .CommandType = adCmdStoredProc
    .CommandText = "myProcedure" '直接指定存储过程名
    
    '创建并添加参数，避免字符串拼接的引号冲突
    Set objParam = .CreateParameter("myjson", adLongVarChar, adParamInput, Len(jsonString), jsonString)
    .Parameters.Append objParam
    
    .Execute '执行存储过程，无返回集时直接调用Execute
End With

objConnection.Close
Set objCommand = Nothing
Set objConnection = Nothing

Exit Sub
ErrorHandler:
    MsgBox Err.Description & " 错误代码：" & Err.Number
End Sub

2. 优化存储过程（消除动态SQL风险与提升效率） #

原存储过程用循环拼接动态SQL存在注入风险，且效率低下。改用JSON_TABLE一次性批量插入：

DELIMITER $$
DROP PROCEDURE IF EXISTS proc_json $$
CREATE OR REPLACE PROCEDURE myProcedure(IN myjson TEXT)
BEGIN
DROP TEMPORARY TABLE IF EXISTS tempTable;
CREATE TEMPORARY TABLE tempTable(
    update_key int NOT NULL AUTO_INCREMENT, 
    update_data JSON, 
    PRIMARY KEY(update_key)
);

-- 用JSON_TABLE解析JSON数组，批量插入
INSERT INTO tempTable(update_data)
SELECT jt.data
FROM JSON_TABLE(
    myjson,
    '$[*]' COLUMNS(
        data JSON PATH '$'
    )
) AS jt;
END $$
DELIMITER ;

3. 调整ODBC连接配置 #

• 确保MySQL ODBC驱动版本与服务器版本兼容
• 连接字符串添加OPTION=3参数，支持多结果集返回（部分存储过程调用场景需要）：

  strConnection = "Driver={MySQL ODBC 8.0 ANSI Driver}; Server=[IP]; Database=[Db]; user=[usr]; PWD=[pwd]; OPTION=3"
  

内容的提问来源于stack exchange，提问作者M Garcia