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:Microsoft Windows (windows XP)
Assigned to: CPU Architecture:Any
Tags: Contribution, MyODBC, ODBC5-RC, text, VB

[9 Nov 2006 9:40] yann le gloinec
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
[9 Nov 2006 23:01] Tonci Grgin
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.
[9 Nov 2006 23:02] Tonci Grgin
Test cases

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

[10 Nov 2006 15:09] yann le gloinec
vb project used to generate bug

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

[17 Dec 2006 23:14] Rasmus Johansson
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
[10 Apr 2007 19:21] Jess Balint
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.
[9 Jun 2008 10:27] Richard Harris
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.
[18 Jun 2008 14:14] Tonci Grgin
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.