Re: how to know the next serial id

"June C. Hunt" <june_c_hunt@hotmail.com> wrote in message news:<3zkKc.77225$iJ4.37771@twister.nyroc.rr.com>... > Jonathan Leffler wrote: > > vk02720 wrote: > > > >> How do I know the next value that is going to be in a serial id column > >> before I do an insert ? select max(my_id) may not work because the > >> rows get constantly deleted as well. > >> Is there any way in dbaccess to view the next id as well ? > > > > > > No-one can tell what number will be allocated to your insertion because > > it is a multi-user system and who gets which number depends on who > > inserts which rows when. Note that the 'no-one' in question includes > > the DBMS -- it knows which number will be allocated to the next request, > > but it does not know who is going to make the next request. > > Just out of curiosity, why do you want to know what the next value will > be *prior* to the insertion? Why won't the standard methods of > retrieving the value of the serial *after* the insertion (reviewing the > value in the sqlca record or calling the dbinfo function) work for you? I need that to synchronize 2 tables. I am loading one with external data and need to bring the other one in sync so I need to do "alter table" to set the next serial id. I know for this there maybe other ways of doing it.

vk02720 wrote: > "June C. Hunt" <june_c_hunt@hotmail.com> wrote in message > news:<3zkKc.77225$iJ4.37771@twister.nyroc.rr.com>... >> Jonathan Leffler wrote: >> > vk02720 wrote: >> > >> >> How do I know the next value that is going to be in a serial id column >> >> before I do an insert ? select max(my_id) may not work because the >> >> rows get constantly deleted as well. >> >> Is there any way in dbaccess to view the next id as well ? >> > >> > >> > No-one can tell what number will be allocated to your insertion because >> > it is a multi-user system and who gets which number depends on who >> > inserts which rows when. Note that the 'no-one' in question includes >> > the DBMS -- it knows which number will be allocated to the next >> > request, but it does not know who is going to make the next request. >> >> Just out of curiosity, why do you want to know what the next value will >> be *prior* to the insertion? Why won't the standard methods of >> retrieving the value of the serial *after* the insertion (reviewing the >> value in the sqlca record or calling the dbinfo function) work for you? > > I need that to synchronize 2 tables. I am loading one with external > data and need to bring the other one in sync so I need to do "alter > table" to set the next serial id. I know for this there maybe other > ways of doing it. Sounds like you're determined to make work for yourself, mate! Have you tried Enterprise Replication? -- Strewth! Stick a sock in it, Sheila!

vk02720 wrote: > "June C. Hunt" <june_c_hunt@hotmail.com> wrote: >>Jonathan Leffler wrote: >>>vk02720 wrote: >>>>How do I know the next value that is going to be in a serial id column >>>>before I do an insert ? select max(my_id) may not work because the >>>>rows get constantly deleted as well. >>>>Is there any way in dbaccess to view the next id as well ? >>> >>>No-one can tell what number will be allocated to your insertion [...] >> >>Just out of curiosity, why do you want to know what the next value will >>be *prior* to the insertion? Why won't the standard methods of >>retrieving the value of the serial *after* the insertion (reviewing the >>value in the sqlca record or calling the dbinfo function) work for you? > > I need that to synchronize 2 tables. I am loading one with external > data and need to bring the other one in sync so I need to do "alter > table" to set the next serial id. I know for this there maybe other > ways of doing it. Well, quite apart from using ER which Bruce mentioned (which might or might not be applicable), using a DDL statement like ALTER TABLE is way out of order. You don't discuss the relationship between the two tables; I'm assuming that one (the one loaded with external data) has a SERIAL column and is the controlling table. The second, which cross-references the first, currently has a SERIAL column too, but it should have an INTEGER column - though a SERIAL can be made to work. The key observation is that this inserts the next available serial value: INSERT INTO SomeTable(SerialColumn) VALUES(0); Whereas this inserts the given value, in this case, 654321: INSERT INTO SomeTable(SerialColumn) VALUES(654321); Further, after inserting that value, the next value autogenerated will be 654322 unless either a larger value is supplied or the next autogenerated value is already larger than that. assuming that your serial column has a primary key or unique constraint on it -- it should - note that if you try to insert the same (non-zero) value twice, or if you try to insert a non-zero value that coincides with an already inserted value, you will get an error. After inserting a record into the first table, you collect the serial value that was inserted -- afterwards, when we know what the value is, and not before when we're speculating. You can then use that value in the insert to the secondary value. That's true whether it is INTEGER or SERIAL - which is why it "doesn't matter" which it is. OTOH, it is preferable to have it as an INTEGER column since the secondary table follows the values in the primary, rather than being used to generate its own new values. Well, that's my hypothesis -- your actual situation could be quite different. Assuming you're using IDS 9.40 or later (you don't give any version information, which doesn't help us), then you can use SEQUENCES instead. They're independent of any table; you can generate a new value from the sequence and use it as many times as you want. AFAICS, the main advantage of a sequence over a SERIAL is the non-unit increment and user-defined ranges; otherwise, it is as easy to use serial columns. But you might find it easier to manage than a serial column. Oh, and one final point -- it is not clear that DB-Access is the tool of choice for what you are trying to do. Not remotely clear! I'm not even sure that SQLCMD is what you're after - though its verbose mode would provide you with the serial numbers for each singleton insert. That information, though, is simply visible; there's no way to incorporate the value into the following SQL other than by manual copy'n'paste. In fact, I'd strongly suspect neither DB-Access nor SQLCMD is what you're after at all. You probably need a programming language - ESQL/C, or maybe Perl + DBI + DBD::Informix. Or maybe you should start with SQLCMD and add the requisite features. Or maybe you should look at Marco Greco's stuff (see news posting at http://tinyurl.com/3lphu). -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/