Joshua J. Kugler writes:
> 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
>
Hi!
We shall definitely consider your idea. There might be other
implication of the above change, but the idea is very interesting.
Regards,
Sinisa
____ __ _____ _____ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____
^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^
/*/ \*\ Developers Team
>
---------------------------------------------------------------------
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
