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
