MySQL Database - obtaining an auto increment value
MySQL Database - obtaining an auto increment value
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
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!
Re: MySQL Database - obtaining an auto increment value
Hi,
http://dev.mysql.com/doc/refman/5.1/en/ ... tions.html
LAST_INSERT_ID() -> Value of the AUTOINCREMENT column for the last INSERT
http://dev.mysql.com/doc/refman/5.1/en/ ... tions.html
LAST_INSERT_ID() -> Value of the AUTOINCREMENT column for the last INSERT
Re: MySQL Database - obtaining an auto increment value
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.
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
Re: MySQL Database - obtaining an auto increment value
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!
- Rook Zimbabwe
- Addict
- Posts: 4326
- Joined: Tue Jan 02, 2007 8:16 pm
- Location: Cypress TX
- Contact:
Re: MySQL Database - obtaining an auto increment value
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!
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!
Re: MySQL Database - obtaining an auto increment value
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 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
Re: MySQL Database - obtaining an auto increment value
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]
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!
- captain_skank
- Enthusiast
- Posts: 636
- Joined: Fri Oct 06, 2006 3:57 pm
- Location: England
Re: MySQL Database - obtaining an auto increment value
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 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
Re: MySQL Database - obtaining an auto increment value
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.
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
The truth is never confined to a single number - especially scientific truth!
Re: MySQL Database - obtaining an auto increment value
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
Kukulkan
- Rook Zimbabwe
- Addict
- Posts: 4326
- Joined: Tue Jan 02, 2007 8:16 pm
- Location: Cypress TX
- Contact:
Re: MySQL Database - obtaining an auto increment value
If you use a procedure such as this... is quad.q declared as a global variable? otherwise the info would never leave the procedure.
- captain_skank
- Enthusiast
- Posts: 636
- Joined: Fri Oct 06, 2006 3:57 pm
- Location: England
Re: MySQL Database - obtaining an auto increment value
Thanks guys - I was executing it as a seperate query - which was why it kept returning 0
Ta muchly
Ta muchly
Re: MySQL Database - obtaining an auto increment value
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)
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
Code: Select all
If DatabaseUpdate(1,query$)
DatabaseQuery(1,"SELECT LAST_INSERT_ID();")
NextDatabaseRow(1)
quad.q = GetDatabaseQuad(1,0)
EndIf
FinishDatabaseQuery(1)