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:Microsoft Windows (Windows/Linux)
Assigned to: CPU Architecture:Any

[10 Jun 2004 18:43] Greg Lose
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
[10 Jun 2004 19:16] Greg Lose
Program that is accessing the data is written in C++ using MFC and MFC Recordsets.
[20 Jun 2004 4:47] Greg Lose
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.
[26 Jun 2004 3:21] Timothy Smith
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
[26 Jun 2004 3:22] Timothy Smith
simple test case to fetch a long blob from the database via ODBC

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

[26 Jun 2004 4:26] Greg Lose
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.
[26 Jun 2004 16:10] Sinisa Milivojevic
Thanks no logs.

Only C source and tables would help as we need repeatable test cases.
[27 Jun 2004 20:06] Miguel Solorzano
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;
}
[7 Jul 2004 3:36] Greg Lose
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.
[21 Jul 2004 22:38] Timothy Smith
I'm marking this as closed, based on the latest info from the user.  Thanks!