I've been arguing with MS Access lately. Fortunately, I won. But only
because I had the source to MySQL available. Here's the situation:
(If you're looking for the feature request, skip to the end of the e-mail.
This message is a summary of a series of message posted on the MyODBC list.
I'm posting here in hopes the MySQL developers, or at least one, will read
it.)
I am using attached tables. If you are using attached tables in Access, it
will not allow you to use the statement "SELECT LAST_INSERT_ID()" *EVEN* if
you tell it to make it a passthrough query. It tries to parse it and bombs
saying it is an unknown function. Poppycock! OK, we can always try "SELECT
id FROM table_name WHERE ID IS NULL"
So, I did. If I do a manual insert, i.e. db.execute("insert query"), and
then open a recordset selecting the NULL id, I can get the new ID. GREAT!
Or so I thought. Access has a glitch, though...well, many actually. :)
Then I went about inserting a record via recordset.addnew, adding data, then
recordset.update. Then when I tried to select the NULL id, it gave me an
empty recordset. HUH!? So, I went and turned on logging**, and found out
after an insert via recordset commands, Access issues a query to get the new
record using the IS NULL construct. *AND THEN* MySQL resets the last insert
id when that query is issued. Not exactly sure why, as you can do a "SELECT
LAST_INSERT_ID()" as many times as you want. So, since Access already used
IS NULL, I can't. So there has to be a way to disable that, right? Right.
I grep'ed the source, and found this at line 3062 of sql/sql_select.cc:
thd->insert_id(0); // Clear for next request
Well, I commented that out, recompiled, and now
SELECT id FROM table_name WHERE id IS NULL
mirrors the behavior of
SELECT LAST_INSERT_ID()
So, I can "SELECT id FROM table_name WHERE id IS NULL" as many times as I
need/want to, and it will return the proper value. Very nice.
So, that was my problem, and the solution. Could these be changed in the
official distribution? Or at least a compile time option? I see no reason
why the SELECT NULL behavior cannot mirror the behavior of LAST_INSERT_ID.
Thanks very much for a great product! And thanks for making the source
available! That saved me a VERY large headache!
j----- k-----
**MySQL logs are a LIFESAVER! Especially when all Access will tell you is
"ODBC Call Failed" !! :)
--
Joshua Kugler
Associated Students of the University of Alaska Fairbanks
Information Services Director
[EMAIL PROTECTED]
907-474-7601
---------------------------------------------------------------------
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
