It is currently Wed Aug 21, 2019 2:41 pm

All times are UTC + 1 hour




Post new topic Reply to topic  [ 13 posts ] 
Author Message
 Post subject: MySQL Database - obtaining an auto increment value
PostPosted: Thu May 06, 2010 5:58 pm 
Offline
Enthusiast
Enthusiast

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


Top
 Profile  
Reply with quote  
 Post subject: Re: MySQL Database - obtaining an auto increment value
PostPosted: Fri May 07, 2010 6:43 am 
Offline
Addict
Addict
User avatar

Joined: Mon Jun 06, 2005 2:35 pm
Posts: 1212
Location: germany
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


Top
 Profile  
Reply with quote  
 Post subject: Re: MySQL Database - obtaining an auto increment value
PostPosted: Fri May 07, 2010 10:04 am 
Offline
User
User

Joined: Mon Nov 03, 2008 8:30 am
Posts: 63
Location: Australia
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:
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.


Top
 Profile  
Reply with quote  
 Post subject: Re: MySQL Database - obtaining an auto increment value
PostPosted: Fri May 14, 2010 8:58 am 
Offline
Enthusiast
Enthusiast

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


Top
 Profile  
Reply with quote  
 Post subject: Re: MySQL Database - obtaining an auto increment value
PostPosted: Fri May 14, 2010 6:34 pm 
Offline
Addict
Addict
User avatar

Joined: Tue Jan 02, 2007 8:16 pm
Posts: 4327
Location: Cypress TX
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/


Top
 Profile  
Reply with quote  
 Post subject: Re: MySQL Database - obtaining an auto increment value
PostPosted: Mon May 17, 2010 8:49 am 
Offline
Addict
Addict
User avatar

Joined: Mon Jun 06, 2005 2:35 pm
Posts: 1212
Location: germany
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


Top
 Profile  
Reply with quote  
 Post subject: Re: MySQL Database - obtaining an auto increment value
PostPosted: Mon May 17, 2010 5:59 pm 
Offline
Enthusiast
Enthusiast

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


Top
 Profile  
Reply with quote  
 Post subject: Re: MySQL Database - obtaining an auto increment value
PostPosted: Wed May 19, 2010 10:48 am 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Oct 06, 2006 3:57 pm
Posts: 526
Location: England
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


Top
 Profile  
Reply with quote  
 Post subject: Re: MySQL Database - obtaining an auto increment value
PostPosted: Wed May 19, 2010 12:48 pm 
Offline
Enthusiast
Enthusiast

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


Top
 Profile  
Reply with quote  
 Post subject: Re: MySQL Database - obtaining an auto increment value
PostPosted: Wed May 19, 2010 1:29 pm 
Offline
Addict
Addict
User avatar

Joined: Mon Jun 06, 2005 2:35 pm
Posts: 1212
Location: germany
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


Top
 Profile  
Reply with quote  
 Post subject: Re: MySQL Database - obtaining an auto increment value
PostPosted: Wed May 19, 2010 3:15 pm 
Offline
Addict
Addict
User avatar

Joined: Tue Jan 02, 2007 8:16 pm
Posts: 4327
Location: Cypress TX
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/


Top
 Profile  
Reply with quote  
 Post subject: Re: MySQL Database - obtaining an auto increment value
PostPosted: Wed May 19, 2010 3:16 pm 
Offline
Enthusiast
Enthusiast
User avatar

Joined: Fri Oct 06, 2006 3:57 pm
Posts: 526
Location: England
Thanks guys - I was executing it as a seperate query - which was why it kept returning 0

Ta muchly :)


Top
 Profile  
Reply with quote  
 Post subject: Re: MySQL Database - obtaining an auto increment value
PostPosted: Thu Jul 04, 2019 11:01 pm 
Offline
User
User
User avatar

Joined: Wed May 15, 2013 8:26 pm
Posts: 36
Location: South Pacific
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:
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:
If DatabaseUpdate(1,query$)
  DatabaseQuery(1,"SELECT LAST_INSERT_ID();")
  NextDatabaseRow(1)
  quad.q = GetDatabaseQuad(1,0)
EndIf
FinishDatabaseQuery(1)


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 13 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: Little John and 9 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye