Re: Question on stability of results returned by C api mysql_fetch_row()

On Thu, 11 Feb 2021 16:56:29 +0000, Tony Mountifield wrote:

> In article <[email protected]>,
> Lew Pitcher  <[email protected]> wrote:
>> I have written a C program that summarizes the contents of a table. It
>> only "SELECT"s from the table, and retrieves rows using mysql_fetch_row
>> (). As the program must interpret the row data, it may need to work
>> with as many as five (5) rows as a time.
>> 
>> I'm chasing down a memory corruption problem with this program, that
>> seems to overwrite the stored row data, and I want to eliminate my use
>> (or possibly, misuse) of the C api as the source.
>> 
>> My question is: Once I've retrieved a row using mysql_fetch_row(), does
>> that returned row (the MYSQL row, and the strings it's elements point
>> to)
>> remain stable and unaltered by subsequent mysql_fetch_row() calls? If
>> so,
>> then my problem lies outside of my use of the API, otherwise, I suspect
>> that I've used the API incorrectly, and will have to refactor that
>> portion of the code.
> 
> Your example looks ok, so you must be doing something subtly different
> in the other program.

This is what I hoped.

> If you do mysql_store_result(), it allocates memory for all the rows in
> the result set, so you can seek around them and fetch multiple rows
> independently, as per your example.

Again, exactly what I had hoped. It looks like I didn't misunderstand the 
API after all. The documentation concentrates on the results of a single 
row mysql_fetch_row(), and says nothing on how multiple calls interact. 
My assumption was that multiple calls /don't/ interact, but the buggy 
code made me question that assumption.


> If the expected result set is very large, mysql_store_result() can use a
> lot of memory, and mysql_use_result() can be used instead. But in this
> case, rows are fetched from the server one at a time, you cannot seek
> around the data set, and I would think you can only have one live row
> at a time.

I use mysql_store_result() rather than mysql_use_result() for a several 
reasons.

First off, (for reasons :-) ) I need to "seek around" the resultset in 
order to properly parse the rows, which rules out mysql_use_result().

Secondly, I have additional queries to make, in conjunction with each 
row. I can't wrap these queries into the main query as JOINs, as they 
have their own limitations that preclude that. So, as mysql_use_result() 
requires that I issue no other queries until I've mysql_free_result(), I 
cannot use mysql_use_result() in this processing.

Finally, I've got the memory space to store the entire resultset, and if 
I exceed that space, I can introduce limits to the size of the resultset.

> If that doesn't explain it, then you may need to share the appropriate
> portion of the real code that doesn't work.

Perhaps, but not yet. The real code is in such a raw state that it isn't 
worth sharing yet.

FWIW, it attempts to produce a printed, interpreted log of telephone 
calls from raw "Call Detail Record" data (as generated by my Asterisk 
18.1.0 PBX) stored in a MySql table. The "interpretation" includes 
grouping related call detail records into a single "telephone call" 
instance for reporting purposes, determining the caller's name (from a 
couple of "telephone book" tables), and which telephone (or internal 
service) answered the call (with data derived from a "channel alias" 
table and/or an "extension alias" table, as appropriate for the call).

And, this is just a hobby  ;-)

> Cheers Tony
[snip my example code]

Thanks
-- 
Lew Pitcher
"In Skills, We Trust"