Thread: ecpg and getting just assigned serial number
-----BEGIN PGP SIGNED MESSAGE----- Hi to all, I've written a small application first based on Informix and trying to implement now support for PostgreSQL. I was mostly successful, but there is still one problem left. I'm using 'ecpg' to develop my application, because it's mostly compatible to Informix's 'esqlc'. However. I have a table with a field of type 'serial'. Now I've no problem to insert any sentences into this table, but immediately after inserting a new sentence I need the newly assigned number to the serial field in the table. How can I access this number? With Informix I got this number in a field of structure 'sqlca'. With PostgreSQL the same field in this structure exists but contains always 0, as documented. - -- Theofilu Andreas http://members.eunet.at/theofilu ------------------------------------------------- Enjoy the science of Linux! Genieße die Wissenschaft von Linux! ------------------------------------------------- -----BEGIN PGP SIGNATURE----- Version: 2.6.3i Charset: latin1 iQCVAwUBN9u302j/BcrYxhjVAQGr3gQAhlFZ6Uka/tfcEdSo85NG9Bzq6n4m70HW f4lsECCTOm8EMCeKHvtJN7t5IZ6PqHTquwkY3WpZpM8PPALRSHPlBtxJF+fI4RrB dsQ0g02bwm71XgMNh4VLFYf9gttTOAQjxmA30WONIWoYngQIWr6u+7E6ihjOCdb0 BFPplhR5Vts= =n75F -----END PGP SIGNATURE-----
theofilu@eunet.at (Theofilu Andreas) writes:
> to Informix's 'esqlc'. However. I have a table with a field of type
> 'serial'. Now I've no problem to insert any sentences into this table, but
> immediately after inserting a new sentence I need the newly assigned number
> to the serial field in the table. How can I access this number?
Postgres doesn't return that number automatically; you'll have to run a
separate query to find out what was inserted. You could do something
likeselect serialcolumn from table where oid = NNNN;
since a single-row insert does return the OID of the inserted row.
(Note this will be pretty slow if the table is big and does not have
an index on OID.)
Another possibility is to execute nextval() for yourself and then insert
that result explicitly when you create the row. Still takes two queries,
though.
regards, tom lane
On Sun, Sep 12, 1999 at 04:25:17PM +0200, Theofilu Andreas wrote: > I'm using 'ecpg' to develop my application, because it's mostly compatible > to Informix's 'esqlc'. However. I have a table with a field of type > 'serial'. Now I've no problem to insert any sentences into this table, but > immediately after inserting a new sentence I need the newly assigned number > to the serial field in the table. How can I access this number? > With Informix I got this number in a field of structure 'sqlca'. With > PostgreSQL the same field in this structure exists but contains always 0, > as documented. Where exactly does Informix return it? sqlca[?]. Also does anyone know whether the backend returns that number somewhere? Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL!
Am Mo, 13 Sep 1999 schrieben Sie:
> On Sun, Sep 12, 1999 at 04:25:17PM +0200, Theofilu Andreas wrote:
> > I'm using 'ecpg' to develop my application, because it's mostly compatible
> > to Informix's 'esqlc'. However. I have a table with a field of type
> > 'serial'. Now I've no problem to insert any sentences into this table, but
> > immediately after inserting a new sentence I need the newly assigned number
> > to the serial field in the table. How can I access this number?
> > With Informix I got this number in a field of structure 'sqlca'. With
> > PostgreSQL the same field in this structure exists but contains always 0,
> > as documented.
>
> Where exactly does Informix return it? sqlca[?].
Informix returns this number in 'sqlca.sqlerrd[1]'. Currently this field
is not used by ecpg.
> Also does anyone know whether the backend returns that number somewhere?
Now I know that the backend does not return this number anywhere. Instead
you need two SQL commands to insert a sentence and get the number. You can
do it with following commands:
EXEC SQL nextval ('<sequence name>') into :variable; EXEC SQL insert into <table> values (:variable, ...);
Maybe ecpg can implement the first call behind the scenes and put the new
serial number into 'sqlca.sqlerrd[1]'.
In Informix I have to write:
EXEC SQL insert into <table> values (0, ...);
The '0' increments the serial counter, who is a simple int4 field in a
system table, by one, inserts the sentence with new serial number into the
table and returns the new number in 'sqlca.sqlerrd[1]'.
It would be nice to have this feature exactly the same way with 'ecpg'.
This would make it more compatible, at least against Informix. Don't know
about Oracle.
BTW: When I insert a sentence in Informix and the serial field is not 0,
but contains a unique number and this number is higher than the current
value of the serial counter, the serial counter is set to the manualy set
number. This is important if one inserts sentences from a file. For
example when I move data between two machines/databases. With PostgreSQL
I've to set the counter into the sequence by hand, or it will not be
incremented.
--
Theofilu Andreas
http://members.eunet.at/theofilu
------------------------------------------------- Enjoy the science of Linux!
Genie�e die Wissenschaft von Linux! -------------------------------------------------
> theofilu@eunet.at (Theofilu Andreas) writes:
> > to Informix's 'esqlc'. However. I have a table with a field of type
> > 'serial'. Now I've no problem to insert any sentences into this table,
but
> > immediately after inserting a new sentence I need the newly assigned
number
> > to the serial field in the table. How can I access this number?
>
> Postgres doesn't return that number automatically; you'll have to run a
> separate query to find out what was inserted. You could do something
> like
> select serialcolumn from table where oid = NNNN;
> since a single-row insert does return the OID of the inserted row.
> (Note this will be pretty slow if the table is big and does not have
> an index on OID.)
>
> Another possibility is to execute nextval() for yourself and then insert
> that result explicitly when you create the row. Still takes two queries,
> though.
Another possibility is to "select currval('seqname')" where seqname is the
name of the sequence associated with the serial field. currval's are
maintained per connection so there are no multiuser problems. In many cases
you don't have retrieve the value but just use the expression is subsequent
statements.
On Mon, Sep 13, 1999 at 02:11:24PM +0200, Andreas Theofilu wrote: > Informix returns this number in 'sqlca.sqlerrd[1]'. Currently this field > is not used by ecpg. Added to TODO. But I doubt this can easily be implemented. > Maybe ecpg can implement the first call behind the scenes and put the new > serial number into 'sqlca.sqlerrd[1]'. No. ECPG does not interpret any command. They are just fed into the backend. After an insert the backend returns the number of records inserted, but does it also return the OID or whatever? I'm not sure but since an insert could add more than one tuple I doubt that. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL!
Michael Meskes <meskes@postgreSQL.org> writes:
> After an insert the backend returns the number of records inserted, but does
> it also return the OID or whatever? I'm not sure but since an insert could
> add more than one tuple I doubt that.
If just one record is inserted, then it returns the OID of that record.
regards, tom lane