ActiveX ADODB LAST_INSERT_ID() returning zero

[dthor]

Hey everyone,

I connect to a MySQL database using the Microsoft ADODB ActiveX classes (ADOBD.recordset). This works fine for basic SELECT, INSERT, and UPDATE MySQL commands, and I'm sure others that I haven't tried yet. The table has an auto-increment, unique identifier column named 'id', and I'd like to return the last value that was inserted to the table. However, running SELECT LAST_INSERT_ID() returns a 0.

I've looked into it, and some people mentioned that the ADO can't handle BIGINT, so I've added "Option=16384" to my connection string (FLAG_NO_BIGINT) and still no luck. Others mentioned that the AUTO_IS_NULL database option is causing issues, so I tried that too and it still didn't work.

I also tried the command "SELECT LAST_INSERT_ID() FROM table" and that returned an array of 0s, with length equal to the table length. This is closer, but because items can be removed from the table, I can't use it for my purposes.

Does anyone have any other information on this?

On a related but much less important topic, can ADODB.recordset execute multiple queries or commands with one call? For example, sending multiple INSERT commands.

[mikeporter · Accepted solution]

Have you tried something like:

select max(my_id_field)
From my_table;

Alternately does mySQL support the new SQL standard "returning" clause in inserts?

On your related question, you don't need a recordset to send a command. Use the connection execute method when you aren't going to be returning data. In terms of queries, the answer is yes but with qualifications.

Mike...

[dthor]

Excellent, thank you mikeporter. It's so simple, that must be why I couldn't figure it out. Since I won't be inserting to any index other than the last, this works wonderfully.

Regarding RETURNING, it looks like MySQL 5.1 doesn't support it, but PostgreSQL does (the other database system I use), so that's good to know.

Thanks!