PureBasic Forum

Hello everyone!

I am interacting with a MySQL database through the PB Database command set, which is working out just fine so far.

Now I have a small problem - When I perform an INSERT with a table with a primary key, configured as an auto increment value (ID field), is there a way to obtain that value which the database previously used? This would make my work a lot easier, for I wouldn't have to look up the database row by the other field contents and then extract the ID that way.

Thanks for any help!!

merendo

Hi,

http://dev.mysql.com/doc/refman/5.1/en/information-functions.html

LAST_INSERT_ID() -> Value of the AUTOINCREMENT column for the last INSERT

Not MySQL solution but possible interest for SQLite databases.
SQLite users can also get this value using the following for any table i.e WHERE name = 'table_name'

This will also be accurate if the last entry in a table has been deleted.

Alright, thanks so far, but I take it there is no way to do it directly with an ODBC command implemented with PureBasic. Perhaps that's a neat feature for future versions of PB?

Most of the methods I have seen thus far in the past 7 years are exactly like what has been posted above... I have heard you can use:

strSQL = "SELECT @@Identity

On SOME ODBC DB but I have never tried it as I work with an Access2000 DB and I think this has been implemented in access2007.

It should be called RIGHT AFTER the insert statement.

However I would simply keep track of it OR if you are writing a multi-user server get the server to keep track and then you could send a simple query to the server to see the latest number... KEPP IN MIND that if you have a multiuser server this number could be off if someone completes a query whil another user is still setting one up!

Hi,

Why don't you use LAST_INSERT_ID(), as I mentioned in my first post? Works great. No matter if you are using ODBC or another connection method.

Step 1: Make your insert
INSERT INTO tbltest(NAME, AGE) VALUES('Kukulkan', 36)

Step 2: Make a query that gathers the value of LAST_INSERT_ID()
SELECT LAST_INSERT_ID() AS COUNTER FROM tbltest

Step 3: finished

There is no generic way, as some databases don't know automatic incrementing fields (for example oracle or intersystems caché).

Kukulkan

Why, thanks for the query Sorry, I must have overread your posting before.

However, SELECT LAST_INSERT_ID(); always returns 0 for me... is this some trouble with my DB?? It's MySQL.

[edit] Alright, works now. It was a trouble with the procedure which places the query. It returned a .b value, whereas the query result was a quad. My fault... Thanks a lot for your help, folks!![/edit]

Hi all,

merendo could u post some working code ??

I've been trying to figure this out, on and off, for a day now and i still keep getting 0 as the last record used.

I'm using mysql through ODBC.

Cheers

Captain_S

Actually it's very simple. MySQL just seems to be a little picky about the timing of the query - when I perform the "SELECT LAST_INSERT_ID();" query in phpmyadmin, after an insert, I also get 0, but when I do it in PB, right after the "INSERT..." query, it works. It would seem, phpmyadmin performs some other queries of it's own without my having any control over it...

Here a brief snipet:


Of course, the DB must already be connected. I used the #Database number 1 in this example. Also see the link to the MySQL documentation posted byKukulkan above.

As merendo quoted, you are not allowed to modify the connection-state between the insert and gathering the last autoid value, otherwise the value is lost. The example of merendo works fine, because no other database-action will happen between insert and gathering autoid.

Kukulkan

If you use a procedure such as this... is quad.q declared as a global variable? otherwise the info would never leave the procedure.

Thanks guys - I was executing it as a seperate query - which was why it kept returning 0

Ta muchly

Old post, but if anyone is having problems with this then try using NextDatabaseRow instead of FirstDatabaseRow

Perhaps someone in the know can explain, but I'm getting 0 with merendo's example - might be down to Maria vs MySQL?



However, this works (Purebasic 5.70 and 10.1.36-MariaDB)