AppletTalk.com Java discussions newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Postgres and RETURN_GENERATED_KEYS          AppletTalk.com Forum Index -> Java and Databases View previous topic :: View next topic   Author Message APB Guest Posted: Thu Apr 15, 2004 10:48 am    Post subject: Postgres and RETURN_GENERATED_KEYS Hi all! I would like to use this feature when inserting new records in a PostgreSQL database via JDBC but the possibility of retrieving the autonumber key is not supported... How can I perform a workaround? I currently re-query the database with the same data I just inserted, but this does not necessarily yield the exact record (by definition). Could I use a stored procedure? How? I have non idea on how to implement this. Thanks Alex. -- ___________________________________________________________ a p bertolini (www.webwarrior.org) Back to top Paul Thomas Guest Posted: Fri Apr 16, 2004 2:08 pm    Post subject: Re: Postgres and RETURN_GENERATED_KEYS On Thu, 15 Apr 2004 11:48:09 +0100, APB wrote: Quote: Hi all! I would like to use this feature when inserting new records in a PostgreSQL database via JDBC but the possibility of retrieving the autonumber key is not supported... Hardly suprising as PG doesn't have auto-number fields. I expect you're using a SERIAL type which, if you bothered to read PGs excellent docs, is just a short-hand way of creatinga SEQUENCE and and setting a default contraint. Quote: How can I perform a workaround? I currently re-query the database with the same data I just inserted, but this does not necessarily yield the exact record (by definition). Peform your insert in a transaction and then SELECT currval('myschema.mytable_mycolumn_seq') before commiting the transaction. HTH -- Paul Thomas +------------------------------+---------------------------------------------+ Quote: Thomas Micro Systems Limited | Software Solutions for Business | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+ Back to top APB Guest Posted: Fri Apr 16, 2004 3:44 pm    Post subject: Re: Postgres and RETURN_GENERATED_KEYS "Paul Thomas" <paul (AT) tms-l (DOT) demon.co.uk> ha scritto nel messaggio news:pan.2004.04.16.14.08.17.23823.4010 (AT) tms-l (DOT) demon.co.uk... Quote: Hardly suprising as PG doesn't have auto-number fields. I expect you're using a SERIAL type which, if you bothered to read PGs excellent docs, is just a short-hand way of creatinga SEQUENCE and and setting a default contraint. No wonder searching the PG documentation I couldn't find any reference! Quote: Peform your insert in a transaction and then SELECT currval('myschema.mytable_mycolumn_seq') before commiting the transaction. The DB is not mine, I was told it was an automatic ID so I gathered it was an autonumber. Thanks for the info! Alex. -- ___________________________________________________________ a p bertolini (www.webwarrior.org) Back to top Chris Guest Posted: Mon Apr 19, 2004 12:39 pm    Post subject: Re: Postgres and RETURN_GENERATED_KEYS another way (my preference) is to do a select nextval('sequence_name') then use the value returned to put that in my insert statement. either way will work.. i just prefer getting the nextval before inserting. chris Quote: Peform your insert in a transaction and then SELECT currval('myschema.mytable_mycolumn_seq') before commiting the transaction. The DB is not mine, I was told it was an automatic ID so I gathered it was an autonumber. Thanks for the info! Alex. Back to top APB Guest Posted: Mon Apr 19, 2004 7:59 pm    Post subject: Re: Postgres and RETURN_GENERATED_KEYS "Chris" <cbroussard (AT) liquiddatainc (DOT) com> ha scritto nel messaggio news:89b6b185.0404190439.7dca1815 (AT) posting (DOT) google.com... Quote: another way (my preference) is to do a select nextval('sequence_name') then use the value returned to put that in my insert statement. either way will work.. i just prefer getting the nextval before inserting. chris Thanks, we where thinking of this type of approach today, since we may need to know the ID of the record *before* actually populating it... What a mess. I hate recursion but we're dealing with RDF trees. Alex. -- ___________________________________________________________ a p bertolini (www.webwarrior.org) Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         AppletTalk.com Forum Index -> Java and Databases All times are GMT Page 1 of 1   Jump to: Select a forum Java Newsgroups----------------Java DiscussionsWeb-based java games comp.lang.java 3D Graphics API's for Java Java: Support and criticism Java System Announcements comp.lang.java.api JavaBeans Java and CORBA Java and Databases comp.lang.java.developer Java GUI Toolkits Java Help comp.lang.java.javascript JVM, native methods and hardware comp.lang.java.misc Java Language Programming Security and Java comp.lang.java.setup Java Software Tools comp.lang.java.tech ibm.software.java.linux  Non-English Java Newsgroups----------------Java Language (German)Language Java (Français)Java (Italian)  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 You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.