Re: Record Insert and serial ID question

Scott Ahten <scott@stage22.com> wrote: :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) The max() will return immediately on an indexed column, but it will not give you the right result even within a transaction. Others can insert new rows after yours anyway. (You should still use transactions, but do get the Informix manuals and read up on how they work.) :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? It would work, but the following is much simpler: select dbinfo('sqlca.sqlerrd1') from sometable where condition returning only one row The best is to have a table that contains a single row only (we always have one anyway with some system data) and simply do: select dbinfo('sqlca.sqlerrd1') from thetablewithonlyonerow This allways returns the last serial value created by the last insert statement using the same connection from your application whatever language or connectivity software you use. The engine keeps track of all this, so you allways get the right number returned. It doesn't matter what table you do the select from. It's the dbinfo('sqlca.sqlerrd1') that's the key. :I'm sure this is a popular question but I couldn't find a definite :answer in the FAQ I didn't see it immediately either, but would suspect it's in there. The new FAQ that David Williams is working on is at http://www.datajunction.com/djeval/djev_idx.htm until it's finished and moved to its regular location. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org