"out of memory" exception in CRecordset when selecting a LONGTEXT column from MySQL
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.
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
Can we assume you mean you're calling CODBCRecordset::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:
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:
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.