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 ?

TIA

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.


--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

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?

--
June Hunt

"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/

But, in a multi-user system you wouldn't know if you or someone else would
get the 'next' serial id.

And, even if a row is deleted the serial won't be reused.

"vk02720" <vk02720@my-deja.com> wrote in message
news:4d814faa.0407171501.21e44082@posting.google.c om...
> 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 ?
>
> TIA