Bug #102072 calling MySQL stored procedure from VB ado with parameters
Submitted: 25 Dec 2020 8:45 Modified: 12 Jan 2021 12:17
Reporter: Julie Ling Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version: OS:Windows
Assigned to: CPU Architecture:Any
Tags: MySQL 5.7, VBA

[25 Dec 2020 8:45] Julie Ling 
Description:
Hi there, Merry Christmas. 

I got an "MySQL [ODBC5.3(w) Driver][mysqld-5.7.31-log]Invalid parameter type"error. 
The stored procedure is fairly simple:

PROCEDURE `sp_load_visit`(
IN _nid char(18)
)
BEGIN
SET @query = concat("
    select visiting_date, method, visiting_details from tbl_visit where visiting_customer=? order by visiting_date asc;
	");
PREPARE stmt FROM @query;
SET @_nid = _nid;
EXECUTE stmt USING @_nid;
	DEALLOCATE PREPARE stmt;
END

The VBA code looks like this: 
        sConnString = "Driver={MySQL ODBC 5.3 Unicode Driver};Server=192.168.1.246;Database=jkydb1;Uid=agent;pwd=Jky4008389909!;Connect Timeout=500;OPTION=16427"

        myConn.ConnectionString = sConnString
        myConn.Open   
          
        command.ActiveConnection = myConn
        command.CommandText = "sp_load_visit"
        command.CommandType = adCmdStoredProc
        command.CommandTimeout = 10
        command.NamedParameters = True
        command.Prepared = True
        Set prm = command.CreateParameter("_nid", adChar, adParamInput, 18, cell_customer_nid.value)

        command.Parameters.Append prm

        Set rs = command.Execute

the error pops every single time. 

How to repeat:
calling the stored procedure from above code generates the error each time on command.execute.
[12 Jan 2021 12:17] MySQL Verification Team 
Hello Julie Ling,

Thank you for the bug report.
Discussed internally with Connector/ODBC developer and confirmed that this issue is not present in latest version.
Also, we don't fix bugs in old versions, don't back port bug fixes, so you need to check with latest MySQL ODBC version. So, please upgrade to MySQL ODBC 8.0 and inform us if problem still exists.

Regards,
Ashwini Patil