Record Insert and serial ID question

Hi all, I'm using Informix Online Workgroup Server verstion 7.12.TC5 on WinNT 4.0 I've looked through the informix FAQ and scanned through the current questions on the newsgroup and haven't found a clear answer to my question so here goes.... I am developing an multi-user order entry system in Java and have normalized the orders data into two separate tables, orders and order_details. Each table has a id column that is defined as 'serial' and I have also created the necessary primary key in orders.id and a foreign key in order_details.order_id to link records to their respective order id. When it comes time to insert the order record I need to know the serial id that informix picks to assign it to all of the order_detail records and show the user. Sure, I can find the next id from a query like 'select max(id) from orders' and add one to the result, but what happens when some other user inserts a record between the time I find the max record and I make the insert call? how long will it take to find the max record when I've got half a million records in the table? Here are a few solutions but I'm not sure if they will work the way I expect them to. 1) Put all order SQL statements in a transaction? I was planning on doing this anyway to insure that order records wont get inserted without at least one valid order_detail record. If I include the 'select max(id) from orders' call in the transaction will this insure that they are all executed one after the other or does this just insure a 'all or nothing' behaivour when it comes to performing the SQL statements. Even then will the max() call take some serious time to return a value? (it should be indexed since it is the primary key) 2) someone on the newgroup mentioned "select id from orders where id = dbinfo('sqlca.sqlerrd1');" this seems based on a internal system table inside informix, does this work with JDBC? How does it know to return the id of the record *YOU* inserted? is it based on your individual connection or the last insert anyone performed on the table? I'm sure this is a popular question but I couldn't find a definite answer in the FAQ Thanks, ~Scott -- ________________________________________ Scott Ahten Code Wrangler Stage 22 Imaging, Inc. scott@stage22.com http://www.stage22.com