Hi, > > > Monty, Venu, I hope you read this... :) > > > I really, really want to use MySQL as the database backend for my > datawarehouse. Mind you I have played around with merge tables quite a > bit and know that MySQL is more than up to the task. There are numerous > (not necessarily cost related) reasons as to why MySQL is better for my > application. If it were just me, it would be a slam-dunk as I only use > perl, etc. to extract data from the database. However most of my users > use MS Access as a front end and extraction tool. > > When pulling datasets from a database, Access tries to be smart and if > there is what it thinks is a primary key on a table, it will extract the > values of the primary key for the matching records and then re-query the > table with a parameterized query to get the rest of the values. This is > true in both the case where a user tries to view a table or runs a > simple query. > > Taking a simple case of the user opening the table in data sheet view > (if this is solved, the other cases will be solved too), the following > happens -- okay, this is a bit simplified, see my message "Large > Datasets w/Access" for better background: > http://lists.mysql.com/cgi-ez/ezmlm-cgi?5:mss:4918:200202:bjcebaok > cknfmaldpokp > > -- Access opens a statement handle (#1) and queries the table for the > primary key values. E.g. It would pass "SELECT idx FROM TABLE". Note > that it only cares about getting a partial list here. I.e. if the > screen only shows 10 records, Access only cares about 10 primary key > values. > > -- Access opens a second statement handle (#2) without closing the first > handle and then gets the values in a parameterized query. E.g.: "SELECT > a, b, idx FROM table WHERE idx=? OR idx=?...". It then pulls the > records it cares about with this statement and closes the statement. > > -- If, say, the user presses "page down", [I think] access then gets the > next set of primary key values from statement handle #1, sets up another > prepared query and gets the values as above. > > > 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. > > 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. > > 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. Regards, Venu -- For technical support contracts, go to https://order.mysql.com __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Venu <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer /_/ /_/\_, /___/\___\_\___/ California, USA <___/ www.mysql.com --------------------------------------------------------------------- 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
