MySQL Bugs: #33298: ADO returns wrong parameter count in the query (always 0)

Bug #33298	ADO returns wrong parameter count in the query (always 0)
Submitted:	17 Dec 2007 19:54	Modified:	14 Mar 2008 18:37
Reporter:	Bogdan Degtyariov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.22	OS:	Windows
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	ADO, SQLGetFunctions, windows

Description:
When ADO Application extracts the parameters count from the query using MyODBC it is always 0. SQLNumParams return the correct result, but ADO just ignores it. Analyzing the changes in MyODBC I found that for some unknown reason disabling SQL_API_SQLPROCEDURECOLUMNS (see SQLGetFunctions()) into the list of supported functions affects ADO in such a way.

MyODBC does not have SQLProcedureColumns() function implemented except the following function body:

{
    return set_error(hstmt, MYERR_S1000,
                     "Driver doesn't support this yet", 4000);
}

Consequently, since the version 3.51.15 it was eliminated from the list of supported functions. Unfortunately, this change made ADO not to count parameters correctly.

How to repeat:
Create Table:
--------------------------------------------
CREATE TABLE `test_val` (
  `ID` int(11) default NULL,
  `Val1` varchar(50) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

VB6 Code:
--------------------------------------------
Sub issue21368()
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim cmd As ADODB.Command
    
    Set rs = New ADODB.Recordset
    Set conn = New ADODB.Connection
    conn.CursorLocation = adUseClient
    
    conn.Open ("DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;Database=test7;UID=***;pwd=***")
   
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandText = "SELECT Val1 FROM test_val WHERE ID = ? AND Val1 = ?;"
    cmd.CommandType = adCmdText
    
    x = cmd.Parameters.Count
    
    Debug.Print x
    
End Sub

Suggested fix:
Developers agreed on including SQL_API_SQLPROCEDURECOLUMNS into the list as it solves the problem with ADO and has no known malicious effects.

Bug #34125 has been marked as a duplicate of this bug.

patch and test case (also changed the test case for bug#27591)

Attachment: patch33298.diff (application/octet-stream, text), 6.18 KiB.

Okay with some changes: Remove trailing spaces in added lines. Remove SPC_* variables and just use "" in SQLPROCEDURECOLUMNS_values for those entries. And go ahead and just remove the test case for Bug #27591.

Updated patch with thest case

Attachment: patch33298v2.diff (application/octet-stream, text), 6.26 KiB.

approved.
it doesn't look fantastic, since "..SQLProcedureColumns returns the list of input and output parameters...". I can imagine ADO doesn't care about SQLNumParams after  SQLProcedureColumns is not supported

The patch for this bug has been committed, and will be included in 3.51.24.

A note has been added to the 3.51.24 changelog: 

When using ADO, the count of parameters in a query would always return zero

I desperately trying to populate my stored-procedure's parameters but it doesn't work.

With cm
        .ActiveConnection = acn
        .CommandText = acmd
        .CommandType = adCmdStoredProc
        
        .Parameters.Refresh
End With

Is it related with this bug?

Anthony,

SQLProcedureColumns() ODBC API function has not been implemented due to the server-side restrictions. Therefore calling this function will not return desired results. Sorry for bad news, but this is expected behavior.