where to find the serial value?
Hi everybody, i just want to know, how to retrieve the current value of an serial in a table. For example, there is a table with a serial. In the past i inserted data sets. And now i want to know the current value of the serial. I just thouhgt of the table syssequences. But there are no values in this table. I would appreciate, if there is someone with a solution. thx in advance. Michael
I really can't think of a good reason why you'd need this... If you plan to use the next serial for anything, please don't. In fact you may be better using sequences. In any case, the technical answer to your question is: SELECT serialv FROM sysmaster:sysptnhdr p, sysmaster:systabnames t WHERE t.dbsname = DBINFO('dbname') AND t.tabname = 'some_table' AND t.partnum = p.partnum; DON'T USE IT! Regards. On Thu, Jun 7, 2012 at 4:05 PM, Michael Weber <michael.weber@fh-stralsund.de > wrote: > Hi everybody, > > i just want to know, how to retrieve the current value of an serial in a > table. For example, there is a table with a serial. In the past i > inserted data sets. And now i want to know the current value of the serial. > > I just thouhgt of the table syssequences. But there are no values in > this table. > > I would appreciate, if there is someone with a solution. > > thx in advance. > > Michael > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...
In the table sysmaster:sysactptnhdr (or if you have an older release
sysptnhdr) are columns holding the NEXT (not the last inserted) values for
serial, serial8, and bigserial columns for the table (zeros if the table
does not have all three types of columns). You can join that to
systabnames by partnum to be able to find the correct row by database and
table name:
select cur_serial4, cur_serial8, cur_bigserial
from systabnames st, sysactptnhdr sa
where st.partnum = sa.partnum
and st.dbsname = 'mydatabase'
and st.tabname = 'mytable';
If the table is fragmented, this will return multiple rows, but the master
(first) fragment's record will have the actual values (the others may be
zeros - don't remember). Note if your server is older and does not have
sysactptnhdr, then only serial types are available and the column is
sysptnhdr.serialv (current servers have all three values in sysptnhdr - the
columns are serialv, cur_serial8, cur_bigserial).
Note that these values should NEVER be used to insert rows into the table
nor to insert child table rows. Inserting a zero and checking the sqlca
structure or using the dbinfo('sqlca.sqlerrd1') method for retrieving the
inserted value must be used.
FYI, if you use my dbschema replacement utility, myschema, it prints out
the current values for serial, serial8, and bigserial columns unless you
tell it not to (-I). So that's an easier way to get this:
myschema -d mydatabase -t mytable
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Thu, Jun 7, 2012 at 11:05 AM, Michael Weber <
michael.weber@fh-stralsund.de> wrote:
> Hi everybody,
>
> i just want to know, how to retrieve the current value of an serial in a
> table. For example, there is a table with a serial. In the past i
> inserted data sets. And now i want to know the current value of the serial.
>
> I just thouhgt of the table syssequences. But there are no values in
> this table.
>
> I would appreciate, if there is someone with a solution.
>
> thx in advance.
>
> Michael
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
A very good morning, @Jonathan: Thx for the reply. @Art: The solution is brilliant. Now it would be possible to get the next serial values. And do not worry. I need the value as information only. Thanks a lot. Michael