Spoiler: Venu's Suggestion about "Dynamic Cursor" is the answer....


On Thu, 2002-02-14 at 20:34, Venu wrote:
> > MyODBC, as compiled today, uses mysql_store_result to get records.  This
> > is fine for reasonably sized tables.  However, if the table has millions
> > of records, writing the results to a temporary table has many
> > detrimental effects, e.g.: Access seems to hang from the user's
> > perspectiv, Access crashes because there are too many records for it to
> > handle at once (data requirements to great); MySQL creates HUGE
> > temporary tables or bombs if SQL_BIG_RESULT was not set.  
> 
> Probably we can add extra DSN options, to make use of either 
> mysql_store_result() or mysql_use_result(). In the second 
> case, lot of code change is needed in all result set dependency 
> APIs too.  

That would be nice but perhaps unneeded (see below about your
suggestion).


> > So in the case of a very long table, it is important to use
> > mysql_use_result instead.  This makes it so that results are returned
> > right away and eases the load on all programs involved.  The astute
> > reader will realize that if one uses mysql_use_result and does not fetch
> > all of the records, the next query will return the remaining records
> > from the previous query first.  It follows that Access bombs because in
> > statement #2 it is getting results from statement #1. (This is seen from
> > the myodbc.log line: " | error: message: Commands out of sync;  You
> > can't run this command now" in the myodbc3.dll changed to use the said
> > function.)
> 
> Can you be more specific on this ? And a MS ODBC DM trace will be better 
> to analyze.

Sorry, I should have been clearer about this.  Yesterday (Thursday) I
downloaded the bk source.  Aside from many other hacks, I changed
execute.c:do_query to use mysql_use_result() instead of
mysql_store_result().  In THIS version, I got the "Commands out of sync"
error.  To better show what is happening, I just got the souce again,
made the said modification and a couple of more verbose debugging output
modifications.  In the setup, I had checked off "Return Matching
Records" and "Trace...". Here is the sequence of what is happening:


[bill@badams myodbc-3.51]$ grep -E 'SQLFree|SQLPre|sync' myodbc.log 
>SQLFreeHandle
| info: SQLFreeHandle: 157150
| >SQLFreeConnect
| <SQLFreeConnect
<SQLFreeHandle
>SQLFreeHandle
| info: SQLFreeHandle: 154988
<SQLFreeHandle
| >SQLPrepare
| | info: SQLPrepare: 15bd68  SELECT Config, nValue FROM MSysConf
| <SQLPrepare
| >SQLFreeStmt
| | enter: SQLFreeStmt: 15bd68  option: 1000
| <SQLFreeStmt
>SQLFreeHandle
| info: SQLFreeHandle: 15bd68
| >SQLFreeStmt
| | enter: SQLFreeStmt: 15bd68  option: 1
| <SQLFreeStmt
<SQLFreeHandle
| >SQLPrepare
| | info: SQLPrepare: 15bd68  SELECT
`pcm_test_header_200202`.`serial_hi`,`pcm_test_header_200202`.`ymd_ts`
FROM `pcm_test_header_200202` 
| <SQLPrepare
| >SQLFreeStmt
| | enter: SQLFreeStmt: 15bd68  option: 1000
| <SQLFreeStmt
>SQLPrepare
| info: SQLPrepare: 15c780  SELECT [column names removed --bill] FROM
`pcm_test_header_200202`  WHERE `serial_hi` = ? AND `ymd_ts` = ? OR
`serial_hi` = ? AND `ymd_ts` = ? OR `serial_hi` = ? AND `ymd_ts` = ? OR
`serial_hi` = ? AND `ymd_ts` = ? OR `serial_hi` = ? AND `ymd_ts` = ? OR
`serial_hi` = ? AND `ymd_ts` = ? OR `serial_hi` = ? AND `ymd_ts` = ? OR
`serial_hi` = ? AND `ymd_ts` = ? OR `serial_hi` = ? AND `ymd_ts` = ? OR
`serial_hi` = ? AND `ymd_ts` = ?
<SQLPrepare
| >SQLFreeStmt
| | enter: SQLFreeStmt: 15c780  option: 1000
| <SQLFreeStmt
| | error: message: Commands out of sync;  You can't run this command
now
>SQLFreeStmt
| enter: SQLFreeStmt: 15c780  option: 0
<SQLFreeStmt
>SQLFreeHandle
| info: SQLFreeHandle: 15c780
| >SQLFreeStmt
| | enter: SQLFreeStmt: 15c780  option: 1
| <SQLFreeStmt
<SQLFreeHandle
>SQLFreeHandle
| info: SQLFreeHandle: 15bd68
| >SQLFreeStmt
| | enter: SQLFreeStmt: 15bd68  option: 1
| <SQLFreeStmt
<SQLFreeHandle



> > The bottom line is that in order for MySQL + Access + MyODBC to be
> > usable as a datawarehouse MySQL/MyODBC (a) must be able to return
> > uncached results; and (b) be able to have multiple statements open,
> > active, and with pending data to be fetched at the same time.
> 
> Try to use Dynamic Cursor Type (OPTION=32) in MyODBC 3.51.

YES!  The stock 3.51.01.01 myodbc3.dll with Dynamic Cursor Type, Allow
BIG Results, and Return Matching rows is the ticket. AFAIK, this
satisfies my needs.  I will get back later next week after I do some
more testing.


b.





---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Reply via email to