MySQL Bugs: #4092: Retrieving blob data > 65k results in a Data Truncated ODBC error message

Bug #4092	Retrieving blob data > 65k results in a Data Truncated ODBC error message
Submitted:	10 Jun 2004 18:43	Modified:	21 Jul 2004 22:38
Reporter:	Greg Lose	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	4.0.18	OS:	Windows (Windows/Linux)
Assigned to:		CPU Architecture:	Any

Description:
I have a table witha  longblob binary field in it. I'm able to save >65k items to it but when attempting to retrieve the items I recieve a "Data Truncated" error. I enable ODBC trace on and it appears that for the longbinary column the 'Valumax' param is always 65k. 

How to repeat:
Using the ODBC connector

Create table with longblob field
Add a row tha contains a longblob value > 65k
attempt to retrieve above row with a select statement

Program that is accessing the data is written in C++ using MFC and MFC Recordsets.

The problem occurs with 3.52 connector - had a site use the 2.5 connector which worked for a few data retrivals and then crashes.

Hi!  I tried to repeat this; I'm attaching a test program in C to demonstrate how I inserted and retrieved a BLOB.

Please, can you modify this test case, to demonstrate your problem?

Thanks,

Timothy

simple test case to fetch a long blob from the database via ODBC

Attachment: bigblob.c (application/octet-stream, text), 7.61 KiB.

The issue I'm having is using the ODBC driver with MFC Recordsets (part of visual studio) and I'm using C++. It's been so long since I've C and have never used the raw ODBC interface that I doubt I would be of any use. If it would help I can generate an ODBC trace log of the issue.

Thanks no logs.

Only C source and tables would help as we need repeatable test cases.

Hi,

I did some tests with the MFC's CRecordset and found that under some
circumstances it is generated a error message when it is opened with
CRecordset::dynaset and CRecordset::snapshot and I only was able for
take off with CRecordset::forwardOnly. 

Notice that the VC++ project wizard uses by default CRecordset::snapshot

On another hand that error message should be generated due in the way
that you are handling the CRecordset, the Microsoft documentation
recommends when handling blob columns to declare a field data member
of type CLongBinary in your recordset class. Please read in your
MSDN stuff about CLongBinary.

So I did a test with a MFC's console application which uses the
ExecuteSQL for to create the table with a longblob column, to
insert a row into it with 5 millions characters and to fetch 
that row with a CRecordset (see below the body of the main routine).
For you know about the class of CRecordset please take a look in
the sample from Microsoft called: 
DBFETCH: Demonstrates Bulk Row Fetching in the MFC ODBC Database Classe   

C:\temp>bug4092.exe
Connected with MySQL Server
Length of blob row: 4999998

C:\temp>dir c:\mysql\data\test\*.*
 O volume na unidade C não tem nome.
 O número de série do volume é 3C2A-26E8

 Pasta de c:\mysql\data\test

27/06/2004  14:04    <DIR>          .
27/06/2004  14:04    <DIR>          ..
27/06/2004  14:04             8.560 myblob.frm
27/06/2004  14:32         5.000.012 myblob.MYD
27/06/2004  14:32             1.024 myblob.MYI
               3 arquivo(s)      5.009.596 bytes
               2 pasta(s) 16.143.704.064 bytes disponíveis

CWinApp theApp;

using namespace std;

int _tmain(int argc, TCHAR* argv[], TCHAR* envp[])
{
  int nRetCode = 0;
  CDatabase my_db;
  string drop_it("DROP TABLE IF EXISTS myblob"),
         create_it("CREATE TABLE myblob (theblob longblob)"),
         insert_row("INSERT INTO myblob VALUES (repeat(\"K\",5000000))");

  int is_open = my_db.Open( _T("mydsn"),FALSE, FALSE,\
                            _T("ODBC;UID=root"));
 
  if ( !is_open)
  {
    cout << "Connection Failed" << endl;
    exit(1);
  }
  else
  {
    cout << "Connected with MySQL Server" << endl;
    
    TRY
    {
      my_db.ExecuteSQL(_T(drop_it.c_str()));
      my_db.ExecuteSQL(_T(create_it.c_str()));
      my_db.ExecuteSQL(_T(insert_row.c_str()));
    }
    CATCH(CDBException, e)
    {
      cout << "Get error with ExecuteSQL" << endl ;
      my_db.Close();
      exit(1);
    }
    END_CATCH
  }
  
  CDynamicBulkSet m_prs(&my_db);
  m_prs.Open(CDynamicBulkSet::forwardOnly ,
              _T("SELECT * FROM myblob"));

  long* rgLength;
  LPSTR rgData;
  CString strData;

  int nFields = m_prs.GetODBCFieldCount();
  int nRowsFetched = m_prs.GetRowsFetched();
 
  for (int nField = 0; nField < nFields; nField++)
  {
    rgData = (LPSTR)m_prs.m_ppvData[nField];
    rgLength = (long*)m_prs.m_ppvLengths[nField];
    
    for (int nRow = 0; nRow < nRowsFetched; nRow++)
    {
      int nStatus = m_prs.GetRowStatus(nRow + 1);

      if (nStatus == SQL_ROW_DELETED)
	strData = _T("<DELETED>");
      else if (nStatus == SQL_ROW_NOROW)
	strData = _T("<NO_ROW>");
      else if (rgLength[nRow] == SQL_NULL_DATA)
	strData = _T("<NULL>");
      else
        strData = &rgData[nRow * MAX_TEXT_LEN];

      cout << "Length of blob row: " << strlen(strData) << endl;
    }
  }
  m_prs.Close();
  my_db.Close();
  return nRetCode;
}

Sorry haven't had time to look into this in the last week. Had a site try the latest ODBC driver and it seemed to clear up the problem. Beleive they were running 3.51.07 the .08 fix the issue.

I'm marking this as closed, based on the latest info from the user.  Thanks!