MySQL Bugs: #24132: reading 2 times TEXT field with ADO, second time returns empty result

Bug #24132	reading 2 times TEXT field with ADO, second time returns empty result
Submitted:	9 Nov 2006 9:40	Modified:	10 Apr 2007 19:21
Reporter:	yann le gloinec	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.12.00, 3.51 SVN	OS:	Windows (windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	Contribution, MyODBC, ODBC5-RC, text, VB

Description:
(sorry for my poor english)
When reading 2 times a TEXT field with an ado record, the second time it returns an empty string

Visual Basic 6
ADO 2.8
Mysql 5.0.21 (windows XP)
MYODBC 3.51.12.00

How to repeat:
Dim MysqlCon        As New ADODB.Connection
Dim Rs              As ADODB.Recordset
Dim szCnnString     As String
Dim lRecords        As Long

szCnnString = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=test;UID=root;PASSWORD=;OPTION=3"
MysqlCon.Open szCnnString

Set Rs = MysqlCon.Execute("SELECT texte from test2", lRecords)
MsgBox Rs![texte]
MsgBox Rs![texte]

Rs.Close
Set Rs = Nothing
MysqlCon.Close
Set MysqlCon = Nothing

Hi Yann and thanks for your bug report.
Verified as described with last 3.51 build against MySQL server 5.0.27BK on Win XP Pro SP2 localhost. Seems to affect only TEXT field *and* when it's only field in SELECT.
Test cases(s) attached.

Test cases

Attachment: Bug24132.zip (application/x-zip-compressed, text), 2.50 KiB.

vb project used to generate bug

Attachment: bugs mysql.zip (application/x-zip-compressed, text), 1.88 KiB.

Suggested fix:

The problem only occurs if the ODBC API 1.0 is used. It should be checked whether all data was read from a blob field during last read and in that case the offset should be reset. The fix should be done on the SQLGetData -function (part of the ODBC 1.0 API) of results.c.

Changes to SQLGetaData -function
================================

+ A new variable is needed for the data length, called resultLength.
+ The data length is counted.
+ The offset is reset if all data was read in last call

The modified version of the function looks like this:
SQLRETURN SQL_API SQLGetData( SQLHSTMT      hstmt,
                              SQLUSMALLINT  icol,
                              SQLSMALLINT   fCType,
                              SQLPOINTER    rgbValue,
                              SQLLEN        cbValueMax, 
                              SQLLEN *      pcbValue )
{
    STMT FAR *stmt= (STMT FAR*) hstmt;
    SQLRETURN result;
	/* BUG 24132, new variable for field data length */
	uint resultLength;

    DBUG_ENTER("SQLGetData");

    DBUG_PRINT("enter",("icol:%d, ctype:%d, rgb:0x%x, len:%d, pcb:0x%x)",
                        icol,fCType,rgbValue,cbValueMax,pcbValue));

    if ( !stmt->result || !stmt->current_values )
    {
        set_stmt_error(stmt,"24000","SQLGetData without a preceding SELECT",0);
        DBUG_RETURN_STATUS(SQL_ERROR);
    }
    if ( fCType == SQL_C_NUMERIC ) /* We don't support this */
    {
        set_error(stmt,MYERR_07006,
                  "Restricted data type attribute violation(SQL_C_NUMERIC)",0);
        DBUG_RETURN_STATUS(SQL_ERROR);
    }
    icol--;     /* Easier code if start from 0 */
    if ( icol != stmt->last_getdata_col )
    {   /* New column. Reset old offset */
        stmt->last_getdata_col= icol;
        stmt->getdata_offset= (ulong) ~0L;
    }

	/* BUG 24132, get length of data in field */
	if (stmt->result_lengths)
		resultLength = stmt->result_lengths[icol];
	else if (stmt->current_values[icol])
		resultLength = strlen(stmt->current_values[icol]);
	else
		resultLength = 0;
	
	/* BUG 24132, blob check - reset offset if all field data was fetched in last call */
	if (icol == stmt->last_getdata_col && stmt->getdata_offset == resultLength)
		stmt->getdata_offset = (ulong) ~0L;

#ifdef LOG_ALL
    DBUG_PRINT("QQ",("icol: %d  fCType: %d  default: %d  value: %.10s",
                     icol+1,fCType,
                     stmt->odbc_types[icol],
                     (stmt->current_values[icol] ? stmt->current_values[icol] :
                      "NULL")));
#endif
    if ( !(stmt->dbc->flag & FLAG_NO_LOCALE) )
        setlocale(LC_NUMERIC,"English");

    result= sql_get_data( stmt,
                          (SQLSMALLINT) (fCType == SQL_C_DEFAULT ? stmt->odbc_types[icol] : fCType),
                          stmt->result->fields+icol,
                          rgbValue,
                          cbValueMax,
                          pcbValue,
                          stmt->current_values[icol],
                          (stmt->result_lengths ? stmt->result_lengths[icol] : (stmt->current_values[icol] ? strlen( stmt->current_values[icol] ) : 0 ) ) );

    if ( !(stmt->dbc->flag & FLAG_NO_LOCALE) )
        setlocale(LC_NUMERIC,default_locale);

    DBUG_PRINT("exit",("return:%d, rgb:0x%x, pcb:0x%x)",
                       result,rgbValue,pcbValue));
    DBUG_RETURN_STATUS(result);
}

Added/modified code is marked with BUG 24132
The fix has been implemented in Visual Studio .NET 2003
The fix has been compiled with Visual C++ 7
The fix has been tested on Windows 2003

Duplicate of bug#16866

Resetting the result length to allow multiple reads is against the ODBC spec and can lead to issues in ADO including infinite loops.

I've had the same problem described above in both 3.51.25 and 5.01.04, though I am using ASP not VB6. However the problem does not occur for me in 3.51.12.  I have MDAC 2.8 installed.

Richard, the fact that something used to work in 3.51.xx does not change the fact that this behavior is outside ODBC specs. We've worked hard to implement standard as strictly as possible and I'm sorry that those changes broke your expectations.