Sequence value
So, I want to insert new record and get immediately its sequence
value (serial column) (or OID). I Use iHTML web interface.
Now I have found out that I can give two queries in iHTML like this:
SQL ="SELECT nextval('koe_pkey_id') as val;
INSERT INTO koe (id, name) values (val, 'uusi');"
Now there are some problems. The previous example causes an error:
ERROR: Attribute 'val' not found
Is there a method how to transmit a value from one query to another?
If I try the examples of FAQ:
SQL ="$newSerialID = nextval('koe_pkey_id');
INSERT INTO koe (id, name) values ($newSerialID, 'uusi');"
Or
SQL ="INSERT INTO koe (name) values ('uusi');"
$newSerialID = curr('koe_pkey_id');
I get: ERROR: parser: parse error at or near "$"
And lately (this may be an iHTML problem) if I try:
SQL ="INSERT INTO koe (name) values ('uusi');
SELECT currval('koe_pkey_id') as val;"
It causes no error, but I cannot retrieve the value because the
queries seem not return any recordsets (nothig can be fetched).
Any hints are valuable!
Thanks
Esa Pikkarainen
You could try
INSERT INTO koe (id, name) SELECT nextval('koe_pkey_id'), 'uusi';
or something like that.
/Roger
Esa Pikkarainen wrote:
Show quoted text
So, I want to insert new record and get immediately its sequence
value (serial column) (or OID). I Use iHTML web interface.
Now I have found out that I can give two queries in iHTML like this:SQL ="SELECT nextval('koe_pkey_id') as val;
INSERT INTO koe (id, name) values (val, 'uusi');"Now there are some problems. The previous example causes an error:
ERROR: Attribute 'val' not found
Is there a method how to transmit a value from one query to another?If I try the examples of FAQ:
SQL ="$newSerialID = nextval('koe_pkey_id');
INSERT INTO koe (id, name) values ($newSerialID, 'uusi');"
Or
SQL ="INSERT INTO koe (name) values ('uusi');"
$newSerialID = curr('koe_pkey_id');I get: ERROR: parser: parse error at or near "$"
And lately (this may be an iHTML problem) if I try:
SQL ="INSERT INTO koe (name) values ('uusi');
SELECT currval('koe_pkey_id') as val;"
It causes no error, but I cannot retrieve the value because the
queries seem not return any recordsets (nothig can be fetched).Any hints are valuable!
Thanks
Esa Pikkarainen
Esa Pikkarainen wrote:
SQL ="SELECT nextval('koe_pkey_id') as val;
INSERT INTO koe (id, name) values (val, 'uusi');"
INSERT INTO koe (id, name) (SELECT nextval('koe_pkey_id'), 'uusi');
--
Alessio F. Bragadini alessio@albourne.com
APL Financial Services http://village.albourne.com
Nicosia, Cyprus phone: +357-2-755750
"It is more complicated than you think"
-- The Eighth Networking Truth from RFC 1925
Thank you,
Yet another way to do it ;-)
but unfortunately it did not solve my problem. Now I have no access
to the value of nextval('koe_pkey_id'). This query does not return
any recordset.
Is it possible to embed Insert query as a subquery into a Select
query? I have not managed to dot it. I mean something like:
SELECT nextval('koe_pkey_id') as val, (INSERT INTO koe (id, name)
values (val, 'uusi')) as dummy FROM koe;"
That would both insert new record and return serial value - if it
just worked...
Thanks
Esa
Alessio Bragadini wrote (15 Dec 00,):
Show quoted text
Esa Pikkarainen wrote:
SQL ="SELECT nextval('koe_pkey_id') as val;
INSERT INTO koe (id, name) values (val, 'uusi');"
INSERT INTO koe (id, name) (SELECT nextval('koe_pkey_id'), 'uusi');
Esa Pikkarainen wrote:
but unfortunately it did not solve my problem. Now I have no access
to the value of nextval('koe_pkey_id'). This query does not return
any recordset.
Sorry, I didn't check this requirement.
Is it possible to embed Insert query as a subquery into a Select
query? I have not managed to dot it. I mean something like:SELECT nextval('koe_pkey_id') as val, (INSERT INTO koe (id, name)
values (val, 'uusi')) as dummy FROM koe;"
I don't think so.
That would both insert new record and return serial value - if it
just worked...
In one of your examples you used a double statement, so you could
probably try:
INSERT INTO koe (id, name) (SELECT nextval('koe_pkey_id'), 'uusi');
SELECT currval('koe_pkey_id');
This should INSERT and then retrieve the same sequence value. As has
been explained to me before :-) the sequence value is safe (i.e. doesn't
get updated by another connection) inside the connection. One of your
examples was similar, but without the nextval part, so probably the
backend doesn't have a value for currval yet.
--
Alessio F. Bragadini alessio@albourne.com
APL Financial Services http://village.albourne.com
Nicosia, Cyprus phone: +357-2-755750
"It is more complicated than you think"
-- The Eighth Networking Truth from RFC 1925
AAAHHH, I DID IT!!!!
That:
SQL="SELECT nextval('koe_pkey_id');
INSERT INTO koe (id, name) VALUES
(SELECT currval('koe_pkey_id'), 'uusi');"
Now it returns the correct Serial value of the record it inserts.
Yee, I'm glad now
:-D
Thank you all and have a good weekend!
Esa Pikkarainen