MySQL Database - obtaining an auto increment value

Just starting out? Need help? Post your questions and find answers here.
merendo
Enthusiast
Enthusiast
Posts: 449
Joined: Sat Apr 26, 2003 7:24 pm
Location: Germany
Contact:

MySQL Database - obtaining an auto increment value

Post by merendo »

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
The truth is never confined to a single number - especially scientific truth!
User avatar
Kukulkan
Addict
Addict
Posts: 1352
Joined: Mon Jun 06, 2005 2:35 pm
Location: germany
Contact:

Re: MySQL Database - obtaining an auto increment value

Post by Kukulkan »

Hi,

http://dev.mysql.com/doc/refman/5.1/en/ ... tions.html

LAST_INSERT_ID() -> Value of the AUTOINCREMENT column for the last INSERT
Jumbuck
User
User
Posts: 63
Joined: Mon Nov 03, 2008 8:30 am
Location: Australia

Re: MySQL Database - obtaining an auto increment value

Post by Jumbuck »

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'

Code: Select all

Query.s ="SELECT seq FROM sqlite_sequence WHERE name='scales' "
If DatabaseQuery(DatabaseHandle, Query.s)
    While NextDatabaseRow(DatabaseHandle)
      Item.s =  GetDatabaseString(DatabaseHandle, 0)
    Wend
FinishDatabaseQuery(DatabaseHandle)     ; Clear the database buffer
EndIf
This will also be accurate if the last entry in a table has been deleted.
merendo
Enthusiast
Enthusiast
Posts: 449
Joined: Sat Apr 26, 2003 7:24 pm
Location: Germany
Contact:

Re: MySQL Database - obtaining an auto increment value

Post by merendo »

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?
The truth is never confined to a single number - especially scientific truth!
User avatar
Rook Zimbabwe
Addict
Addict
Posts: 4326
Joined: Tue Jan 02, 2007 8:16 pm
Location: Cypress TX
Contact:

Re: MySQL Database - obtaining an auto increment value

Post by Rook Zimbabwe »

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!
Binarily speaking... it takes 10 to Tango!!!

Image
http://www.bluemesapc.com/
User avatar
Kukulkan
Addict
Addict
Posts: 1352
Joined: Mon Jun 06, 2005 2:35 pm
Location: germany
Contact:

Re: MySQL Database - obtaining an auto increment value

Post by Kukulkan »

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
merendo
Enthusiast
Enthusiast
Posts: 449
Joined: Sat Apr 26, 2003 7:24 pm
Location: Germany
Contact:

Re: MySQL Database - obtaining an auto increment value

Post by merendo »

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]
The truth is never confined to a single number - especially scientific truth!
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: MySQL Database - obtaining an auto increment value

Post by captain_skank »

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
merendo
Enthusiast
Enthusiast
Posts: 449
Joined: Sat Apr 26, 2003 7:24 pm
Location: Germany
Contact:

Re: MySQL Database - obtaining an auto increment value

Post by merendo »

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:

Code: Select all

If DatabaseUpdate(1,query$)
  DatabaseQuery(1,"SELECT LAST_INSERT_ID();")
  If FirstDatabaseRow(1)
    quad.q = GetDatabaseQuad(1,0)
    ; Variable quad now contains the ID of the DB row just inserted.
  EndIf
EndIf
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.
The truth is never confined to a single number - especially scientific truth!
User avatar
Kukulkan
Addict
Addict
Posts: 1352
Joined: Mon Jun 06, 2005 2:35 pm
Location: germany
Contact:

Re: MySQL Database - obtaining an auto increment value

Post by Kukulkan »

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
User avatar
Rook Zimbabwe
Addict
Addict
Posts: 4326
Joined: Tue Jan 02, 2007 8:16 pm
Location: Cypress TX
Contact:

Re: MySQL Database - obtaining an auto increment value

Post by Rook Zimbabwe »

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

:mrgreen:
Binarily speaking... it takes 10 to Tango!!!

Image
http://www.bluemesapc.com/
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: MySQL Database - obtaining an auto increment value

Post by captain_skank »

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

Ta muchly :)
User avatar
hujambo
User
User
Posts: 48
Joined: Wed May 15, 2013 8:26 pm
Location: South Pacific

Re: MySQL Database - obtaining an auto increment value

Post by hujambo »

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?

Code: Select all

If DatabaseUpdate(1,query$)
  DatabaseQuery(1,"SELECT LAST_INSERT_ID();")
  If FirstDatabaseRow(1)
    quad.q = GetDatabaseQuad(1,0)
    ; Variable quad now contains the ID of the DB row just inserted.
  EndIf
EndIf
However, this works (Purebasic 5.70 and 10.1.36-MariaDB)

Code: Select all

If DatabaseUpdate(1,query$)
  DatabaseQuery(1,"SELECT LAST_INSERT_ID();")
  NextDatabaseRow(1)
  quad.q = GetDatabaseQuad(1,0)
EndIf
FinishDatabaseQuery(1)
Post Reply