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:Microsoft Windows
Assigned to: Bogdan Degtyariov CPU Architecture:Any
Tags: ADO, SQLGetFunctions, windows

[17 Dec 2007 19:54] Bogdan Degtyariov
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.
[1 Feb 2008 19:38] Jim Winstead
Bug #34125 has been marked as a duplicate of this bug.
[25 Feb 2008 21:17] Bogdan Degtyariov
patch and test case (also changed the test case for bug#27591)

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

[27 Feb 2008 18:33] Jim Winstead
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.
[5 Mar 2008 18:26] Bogdan Degtyariov
Updated patch with thest case

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

[11 Mar 2008 10:29] Lawrenty Novitsky
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
[13 Mar 2008 21:43] Jim Winstead
The patch for this bug has been committed, and will be included in 3.51.24.
[14 Mar 2008 18:37] MC Brown
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
[24 Mar 2008 9:10] William Anthony
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?
[24 Mar 2008 14:36] Bogdan Degtyariov
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.