MySQL Bugs: #30520: MyODBC > 3.51.12 Returns null value for multiple references

Bug #30520	MyODBC > 3.51.12 Returns null value for multiple references
Submitted:	20 Aug 2007 20:14	Modified:	2 Nov 2007 21:50
Reporter:	James Cahill	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.20	OS:	Windows (2003 Server (IIS6/ASP Classic))
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	MyODBC, null return, text

Description:
When selected a TEXT field and referencing it multiple times for the same row, you get a null value for any reference past the first time. I've seen this error in all connectors since 3.51.12. The problem doesn't happen with char or int (that's all I've tested). 

How to repeat:
Using any newer connector other than 3.51.12 with the latest server version.

Create a test table: 

mysql> create table test_table (id int not null primary key auto_increment,
    -> test_text text,
    -> test_int int,
    -> test_char char(50));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test_table (test_text, test_int, test_char) values (
    -> 'this is a test',
    -> '1234',
    -> 'this is another test');
Query OK, 1 row affected (0.03 sec)

Create an ASP test page: 

<%
strConn = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=<server>;PORT=3306;DATABASE=test_db;USER=<user>;PASSWORD=<password>;OPTION=65536;"
set oConn = server.CreateObject("ADODB.Connection")
oConn.Open strConn

set oRS = oConn.Execute("select * from test_table")

response.Write("<ul>")
while not oRS.eof
    response.Write("<li>Text Ref 1: |s|" & oRS("test_text") & "|e|</li>")
    response.Write("<li>Text Ref 2: |s|" & oRS("test_text") & "|e|</li>")
    response.Write("<li>Text Ref 3: |s|" & oRS("test_text") & "|e|</li>")
    response.Write("<li>Int Ref 1: |s|" & oRS("test_int") & "|e|</li>")
    response.Write("<li>Int Ref 2: |s|" & oRS("test_int") & "|e|</li>")
    response.Write("<li>Int Ref 3: |s|" & oRS("test_int") & "|e|</li>")
    response.Write("<li>Char Ref 1: |s|" & oRS("test_char") & "|e|</li>")
    response.Write("<li>Char Ref 2: |s|" & oRS("test_char") & "|e|</li>")
    response.Write("<li>Char Ref 3: |s|" & oRS("test_char") & "|e|</li>")
    oRS.movenext
wend
response.Write("</ul>")

oConn.Close
set oConn = nothing
%>

You'll notice that the rows for the INT and CHAR fields will have all three values filled in between start and end flags (|s| & |e| respectively), but the TEXT field won't. 

Suggested fix:
I'm not quite sure, I haven't checked into the code.

I wrote an ADO regression test case that show this
problem. The test case and manager trace is attached.

Might not be the problem, but ADODB mixes use of
SQLBindCol() and SQLGetData(), something the IBM
ODBC documentation says is not allowed, and Microsoft
documentation mentions as allowed but with restrictions.

  https://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.apd...
  http://msdn2.microsoft.com/en-us/library/ms715441.aspx

Reading the log, the flow is simplified

  SQLBindCol()   - Binds column 1, the integer data
  SQLFetch()     - Get the recordset
  SQLGetData()   - With length 0, to find real length => SQL_SUCCESS_WITH_INFO
                   The manager reports
                     DIAG [01004] [MySQL][ODBC 3.51 Driver]
                     [mysqld-5.0.46-enterprise-gpl-nt-log]
                     String data, right truncated (501)
  SQLGetData()   - Same as the previous
  SQLGetData()   - With length returned above + 1, returns all data
  SQLGetData()   - With length 0, to find real length => SQL_SUCCESS_WITH_INFO.
                   Now gets the answer 0, gets same DIAG.
  SQLGetData()   - Same as the previous
  SQLGetData()   - With length 0 + 1 ?! => SQL_NO_DATA_FOUND
  SQLFreeStmt()  - SQL_UNBIND
  SQLFreeStmt()  - SQL_CLOSE
  SQLFreeStmt()  - SQL_DROP
  SQLDisconnect()
  SQLFreeConnect()

Test case for ADO regression suite

Attachment: bug30520.vbs (application/octet-stream, text), 3.12 KiB.

Manager trace from running the ADO regression suite test case

Attachment: SQL.LOG (application/octet-stream, text), 105.66 KiB.

Further investigation shows that an application can
ask if SQLGetData() is supported on bound columns.
This is done with a call to SQLGetInfo() with the
option SQL_GETDATA_EXTENSIONS, and the MySQL driver
will answer it can support

  SQL_GD_ANY_COLUMN
  SQL_GD_ANY_ORDER
  SQL_GD_BOUND

Just as a note, the IBM DB2 database currently
doesn't support SQL_GD_BOUND.

  http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.c...

The ADODB driver does ask about what the driver
support, could be the reason for the mix of calls.

Another track to investigate is if SQLGetData() really
should return success when there is no more data, even
if the caller give length 0.

The problem could also be that ADODB gets confused because
our driver doesn't follow the Microsoft documentation.

  http://msdn2.microsoft.com/en-us/library/ms715441.aspx

The SQLGetData() call returning the last part of data is
to return SQL_SUCCESS. Then Microsoft writes 

  If SQLGetData is called after this, it returns SQL_NO_DATA.

This is not done in the MySQL driver according to the trace,
instead a call by the ADODB driver to SQLGetData() with 
length 0, will return SQL_SUCCESS_WITH_INFO and the length 0.

With the following change, the test case passes

===================================================================
--- driver/utility.c    (revision 697)
+++ driver/utility.c    (working copy)
@@ -256,9 +256,26 @@
             fill_length= src_length;
     }
     if ( *offset == (ulong) ~0L )
-        *offset= 0;         /* First call */
-    else if ( arg_length && *offset >= (ulong) fill_length )
+    {
+        /*
+          This is first call, even if data has zero size, we don't
+          return SQL_NO_DATA_FOUND.
+
+          FIXME this is one interpretation of the ODBC standard, could
+          be wrong.
+        */
+        *offset= 0;
+    }
+    else if ( *offset >= (ulong) fill_length )
+    {
+        /*
+          If not the first call, and we have no data left,
+          must be a call after we gave the last data.
+          We always return SQL_NO_DATA_FOUND in this case,
+          even if size is 0.
+        */
         return SQL_NO_DATA_FOUND;
+    }
 
     src+= *offset;
     src_length-= (long) *offset;

Would also require a change of line 601 in "my_result.c",
the test case, as the test case assumes SQL_SUCCESS_WITH_INFO
when all data is read. But the test case didn't fail (as it
should if not updated) on Windows. Might be caused by how the
test case is compiled (it the macro "assert"), will be
investigated.

SQLGetData() will now always return SQL_NO_DATA_FOUND on
second call when no data left, even if requested size is
0 (bug#30520).

Will be in 3.51.20

This bug was not fixed in 3.51.20. I installed it today and it is still returning empty values for a field referenced more than once.

A note has been added to the 3.51.20 changelog: 

SQLGetData() will now always return SQL_NO_DATA_FOUND on second call when no data left, even if requested size is 0

This bug never occurs if use adClientSideCursor like in the test case below.
This fix contradicts MSDN... I don't think it is needed because there is a workaround.

NOTE: See how bug#30958 is affected by this fix

Test case (see the comment above)

Attachment: bug30520.asp (application/octet-stream, text), 1.33 KiB.

I have made the research related to behavior of Microsoft SQL 2005 and ADO application (VB6). Here are my findings:

 - Microsoft ODBC driver for SQL 2005 and MyODBC work IDENTICALLY with the test
   script I uploaded earlier. Some specific moments about SQLGetData was not
   clarified in MSDN, so we should make MyODBC to work similar to Microsoft 
   ODBC driver

 - ADODB.CursorLocation = adUseClient affects both MS ODBC and MyODBC in
   the same way: they return the data every time ADODB.Recordset("test_text") is
   referenced

 - In case CursorLocation is not specified, both the drivers return values only
   for the first reference

CONCLUSION:

   MyODBC and MS SQL ODBC drivers return SQL_SUCCESS_WITH_INFO every time
   SQLGetData is called with 0 buffer length. SQL_NO_DATA_FOUND is returned only
   when the previous call finished writing data in the buffer (returned
   SQL_SUCCESS) or there is an empty string result for the buffer with size  
   bigger than 0.

   Patch for this bug must be rolled back in order to provide maximum compatibility with ADO and other Microsoft software.

I am re-closing this bug. The originally-reported issue is fixed. That the fix caused a regression is call for opening a new bug, not re-opening this one and forcing people to make sense of the confusing history.