MySQL Bugs: #19052: Auto-increment field not updated in recordset after inserting

Bug #19052	Auto-increment field not updated in recordset after inserting
Submitted:	12 Apr 2006 15:01	Modified:	24 Jan 2008 9:55
Reporter:	Adrian Sandor
Status:	Closed
Category:	Connector/ODBC	Severity:	S3 (Non-critical)
Version:	3.51.12	OS:	Microsoft Windows (Windows XP)
Assigned to:	Bugs System	Target Version:
Tags:	ADO, ODBC5-RC
Triage:	D3 (Medium)

Description:
I'm using MySQL 4.1.11 and MyODBC 3.51.12 and coding in ASP (javascript) with ADO. I want
to insert a record through a recordset, and then get the generated ID (auto-increment) of
the new record, back from the recordset.
This works with other database servers; however, with MySQL and MyODBC the result is
undefined (the "undefined" value in javascript).

How to repeat:
Create a table:

CREATE TABLE `tst` (
  `ID` int(11) NOT NULL auto_increment,
  `Name` varchar(50) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And run this code:

<%@Language=JavaScript%>
<!--METADATA TYPE="typelib" uuid="00000205-0000-0010-8000-00AA006D2EA4" -->
<%
var cnn=Server.CreateObject("ADODB.Connection");
cnn.Open("DSN=sysmysql");
cnn.Execute("USE test");
var rst=Server.CreateObject("ADODB.Recordset");
rst.Open("SELECT * FROM tst", cnn, adOpenForwardOnly, adLockPessimistic);
rst.AddNew();
rst("Name")="blah";
rst.Update();
Response.Write(rst("ID").Value+"<br>");
%>

(assuming that sysmysql is a system dsn that connects to mysql, and the database is
called `test`)
The code is in javascript ASP, but it should be trivial to translate it to vbscript or vb
if needed.

The actual result is "undefined"; the expected result is the ID of the new record (e.g.
2)

Suggested fix:
When Update() is called on the recordset (and succeeds), the auto-increment field should
be updated with the generated value (i.e. LAST_INSERT_ID() )

Hi. Thanks for your problem report. Can you please do the same like I asked for #19065?

I tried all cursor types (adOpenDynamic, adOpenForwardOnly, adOpenKeyset,
adOpenStatic and adOpenUnspecified) and the result is exactly the same.

I'm not sure what else I can do (see my reply for the other bug).

Adrian, we are back to #19065... VB is working. Can you please try:
.AddNew("Id", 0) and inform me of result.

I tried it, although it doesn't make much sense to me.
Here's the vbs code:

Option Explicit
Const DSN = "DSN=sysmysql"
Dim cnxDatabase
Dim strSQL

' connecting database
Set cnxDatabase = CreateObject("ADODB.Connection")
cnxDatabase.Open(DSN)
cnxDatabase.Execute("USE test")

' querying data
strSQL = "SELECT * FROM tst"

Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
    .ActiveConnection = cnxDatabase
    .LockType = 3
    .CursorType = 1
    .Open(strSQL)
    .AddNew "ID", 0
    .Update
End With

WScript.Echo rs("ID").Value

The displayed value is 0; in the database, a record is created with an auto-generated ID
and a NULL Name.

Hi Adrian.
I was unable to reproduce this problem in VB:
<cut>
        rs = New ADODB.Recordset
        With rs
            .ActiveConnection = cnxDatabase
            .LockType = LockTypeEnum.adLockOptimistic
            .CursorType = CursorTypeEnum.adOpenKeyset
            .CursorLocation = CursorLocationEnum.adUseClient

            .Open(strSQL)

            .AddNew("Name", "blabla")
            .Update()
        End With
        MsgBox(rs("ID").Value)
and I get 2, 3 and so on...
MySQL server version is 4.1.18-nt-max-log.
Other considerations on VB and VBS behavior I posted in #19065. Please follow the link to
#15277 also, it may prove helpfull since I believe that this comes from MS update on your
system.

> I was unable to reproduce this problem in VB

But have you reproduced it from scripting?

> Other considerations on VB and VBS behavior I posted in #19065

I replied there

Adrian, we are pushing on this scripting issue but with no definite results so far...

Verified as described by reporter with scripts provided.

This bug is easily reproduced in MSAccess 2003. Insert a record manually before running
this code. I'd recommend upgrading to Severe!

Dim rs As dao.Recordset, i As Integer

Set rs = CurrentDb.OpenRecordset("tst")
For i = 1 To 2
   With rs
      .AddNew
      !name = "Blah"
      .Update
      MsgBox (!id)
   End With
Next
rs.Close

I need to know the record id that i have just created, but i have a concurrent
environement, how could i get this?

The same thing happens to me using ADO on windows 2K. (I am using C++ & don't have code I
can post)

When is this going to be addressed? It is really very important to be able to add a new
record via ODBC, and to be able to retrieve the new autogenerated ID. S3 severity seems
too low.

Completly agree with you, for me it is a priority because y can't write foreign keys.
I would have to be higer priority than Severity: S3 (Non-critical)
who has ODBC 3.51.11?

Hello all, I hope everybody is using 3.51.17GA. Please check myodbc3.h file and notice:
#define FLAG_AUTO_IS_NULL     (FLAG_SAFE << 6) /* 8388608 Enables SQL_AUTO_IS_NULL */

What happens if you add 8388608 to your OPTIONs (OPTION=1+2+8388608 ...)?

This is working fine in 5.1 beta with both static and dynamic cursors. If you are affected
by this issue, please try out the new driver.

Script test case

Attachment: 19052.vbs (application/octet-stream, text), 1.79 KiB.

Thank you for your bug report. This issue has been committed to our source repository of
that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available
version, including the bug fix. More information about accessing the source trees is
available at

    http://dev.mysql.com/doc/en/installing-source.html

Explanation: Attached test case shows everything works in both versions of MyODBC. Jess,
if you have objections to test case and/or my ruling, please reopen the report.