where to find the serial value?

[Michael Weber]

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

[Fernando Nunes]

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.

_______________________________________________
Informix-list mailing list
Inform...@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...

[Art Kagel]

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.

[Michael Weber]

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