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 20:54	Modified:	14 Mar 2008 19:37
Reporter:	Bogdan Degtyariov
Status:	Closed
Category:	Connector/ODBC	Severity:	S3 (Non-critical)
Version:	3.51.22	OS:	Microsoft Windows
Assigned to:	Bogdan Degtyariov	Target Version:
Tags:	SQLGetFunctions, windows, ADO

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.