Exception "out of memory" in CRecordset when selecting LONGTEXT column from MySQL
I am using CODBCRecordset (a class found in CodeProject) to find one record in a 39-column table. If no entry is found, calling CRecordset :: Open is fine. If the entry matches the conditions, I get an Out of Memory exception when calling CRecordset :: Open. I select all columns in the query (if I change the query to select only one of the columns with the same where clause as the exception).
I'm guessing this is due to some limitations in CRecordset, but I can't find anything that tells me about any limitations. There are only 39 columns in the table.
Has anyone faced this problem? And if so, do you have a work around / solution?
This is an MFC project using Visual Studio 6.0, if that matters.
Here's the query (formatted here like this, rendered without 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 to the query browser only gives me one result.
Additional Information:
Comment out every column in the select statement except id. Run request and do not exclude.
I then systematically scanned and uncommented each column one at a time and rerun the query between each uncommented.
When I uncomment the comment column, I get an error.
It is defined as the following (using MySQL): LONGTEXT
source to share
Read Pax's answer. This gives you a great idea of why the problem occurs.
Work around:
This error occurs if the field defined as (TEXT, LONGTEXT, etc.) is NULL (and may be empty). If there is data in the field then it will only highlight the size of the data in the field and not the maximum size (thus causing an error).
So, if there is a case where you should have these large fields. Here's a potential solution:
- Give the field a default value in the database. (i.e.
'<blank>'
) - Then when displaying the value; you pass NULL / empty if you find the default.
- Then when the value is updated; you pass the default if you find NULL / empty.
source to share
Can I assume you mean that you are calling C ODBC Recordset :: Open (), huh? 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 answer:
There are some known bugs with various ODBC drivers that appear to be caused by getting invalid field lengths. See the following links:
- http://forums.microsoft.com/msdn/showpost.aspx?postid=2700779&siteid=1
- https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=296391
This one appears to have been because CRecordset allocates a buffer large enough to hold the field. When a column returns a length of zero, it is interpreted as a maximum 32-bit size (~ 2G), not a maximum size of 8 bits (255 bytes). Needless to say, it cannot allocate enough memory for the field.
Microsoft has recognized this as a problem, take a look at these solutions:
EDIT after adding questions:
So, given that your MySQL field is LONGTEXT, it looks like CRecordSet is trying to allocate the largest possible size (2G) for it. Do you really need 2 gigs to comment? Having hit 80 wpm, 6cpw will take the driver a little over 7 years to fill this field, working 24 hours a day without rest :-).
It can be a useful exercise to look at all the columns in the database to see if they are of the correct data types. I am not saying that you cannot have a 2G column, just make sure it is necessary, especially since the current ODBC classes will not work with a field that big.
source to share
I the second Pax assumes that this error is due to an attempt to allocate a buffer large enough to make the largest possible LONGTEXT. The client doesn't know how big the data is until they've retrieved it.
LONGTEXT is indeed much more than you will need for most applications. Consider using MEDIUMTEXT (maximum size 16MB) or just TEXT (maximum size 64KB).
Similar problems occur in PHP database interfaces. PHP usually has a memory size limit, and any choice of LONGBLOB or LONGTEXT will likely exceed that limit.
source to share