"out of memory" exception in CRecordset when selecting a LONGTEXT column from MySQL

Question

I am using CODBCRecordset (a class found on CodeProject) to find a single record in a table with 39 columns. If no record is found then the call to CRecordset::Open is fine. If a record matches the conditions then I get an Out of Memory exception when CRecordset::Open is called. I am selecting all the columns in the query (if I change the query to select only one of the columns with the same where clause then no exception).

I assume this is because of some limitation in CRecordset, but I can't find anything telling me of any limitations. The table only has 39 columns.

Has anyone run into this problem? And if so, do you have a work around / solution?

This is a MFC project using Visual Studio 6.0 if it makes any difference.

Here's the query (formatted here so wold show up without a scrollbar):

    SELECT `id`, `member_id`, `member_id_last_four`, `card_number`, `first_name`,
           `mi`, `last_name`, `participant_title_id`, `category_id`, `gender`, 
           `date_of_birth`, `address_line_1`, `address_line_2`, `city`, `state`, 
           `zip`, `phone`, `work_phone`, `mobile_phone`, `fax`, `email`, 
           `emergency_name`, `emergency_phone`, `job_title`, `mail_code`, 
           `comments`, `contract_unit`, `contract_length`, `start_date`, 
           `end_date`, `head_of_household`, `parent_id`, `added_by`, `im_active`, 
           `ct_active`, `organization`, `allow_members`, `organization_category_id`,  
           `modified_date` 
   FROM `participants` 
   WHERE `member_id` = '27F7D0982978B470C5CF94B1B833CC93F997EE23'

Copying and pasting into my query browser gives me only one result.

More info:

Commented out each column in the select statement except for id. Ran the query and no exception.

Then I systematically went through and uncommented each column, one at a time, and re-ran query in between each uncomment.

When I uncomment the comment column then I get the error.

This is defined as the following (Using MySQL): LONGTEXT

Answer 1

Can we assume you mean you're calling C**ODBC**Recordset::Open(), yes? Or more precisely, something like:

CDatabase db;
db.Open (NULL,FALSE,FALSE,"ODBC;",TRUE);
CODBCRecordSet rs (&db);
rs.Open ("select blah, blah, blah from ...");

EDIT after response:

There are some known bugs with various ODBC drivers that appear to be caused by retrieving invalid field lengths. See these links:

• http://forums.microsoft.com/msdn/showpost.aspx?postid=2700779&siteid=1
• https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=296391

This particular one seems to have been because CRecordset will allocate a buffer big enough to hold the field. As the column returns a length of zero, it's interpreted as the max 32-bit size (~2G) instead of the max 8-bit size (255 bytes). Needless to say, it can't allocate enough memory for the field.

Microsoft has acknowledged this as a problem, have a look at these for solutions:

• http://support.microsoft.com/kb/q272951/
• http://support.microsoft.com/kb/940895/

EDIT after question addenda:

So, given that your MySQL field is a LONGTEXT, it appears CRecordSet is trying to allocate the max possible size for it (2G). Do you really need 2 gig for a comments field? Typing at 80 wpm, 6cpw would take a typist a little over 7 years to fill that field, working 24 h/day with no rest :-).

It may be a useful exercise to have a look at all the columns in your database to see if they have appropriate data types. I'm not saying that you can't have a 2G column, just that you should be certain that it's necessary, especially in light of the fact that the current ODBC classes won't work with a field that big.

Answer 2

Read Pax's response. It gives a you a great understanding about why the problem happens.

Work Around:

This error will only happen if the field defined as (TEXT, LONGTEXT, etc) is NULL (and maybe empty). If there is data in the field then it will only allocate for the size the data in the field and not the max size (thereby causing the error).

So, if there is a case where you absolutely have to have these large fields. Here is a potential solution:

1. Give the field a default value in the database. (ie. '<blank>')
2. Then when displaying the value; you pass NULL/empty if you find default value.
3. Then when updating the value; you pass the default value if you find NULL/empty.

Answer 3

I second Pax's suggestion that this error is due to trying to allocate a buffer big enough to hold the biggest LONGTEXT possible. The client doesn't know how large the data is until it has fetched it.

LONGTEXT is indeed way larger than you would ever need in most applications. Consider using MEDIUMTEXT (max size 16MB) or just TEXT (max size 64KB) instead.

There are similar problems in PHP database interfaces. A PHP normally has a memory size limit and any fetch of a LONGBLOB or LONGTEXT is likely to exceed that limit.