From: "Brian Johnson" <[EMAIL PROTECTED]>
> I'm trying to add a new record (new values) into one table and I have a
> second table that refers to the record number of the first table.
>
> I can get the SQL statement to grab the current value of the incrementing
> record number to work, but I need to translate this to operate in perl (I'm
> not used to dealing with multiple SQL statements in perl - usually just a
> select or an insert)
>
> INSERT INTO notes (fields)
> VALUES (new stuff);
> $variable = SELECT CURRVAL('name-of-sequence-for-notes_id')
> INSERT INTO palm_memos (notes_id, other fields)
> VALUES ($variable, other fields);
So just use the SQL (actually PL/SQL I believe. This looks like
Oracle.) Either
$sql = <<"*END*";
INSERT INTO notes (fields)
VALUES (new stuff);
Declare variable as Int;
variable = SELECT CURRVAL('name-of-sequence-for-notes_id');
INSERT INTO palm_memos (notes_id, other fields)
VALUES (variable, other fields);
*END*
Or
$sql = <<"*END*";
INSERT INTO notes (fields)
VALUES (new stuff);
INSERT INTO palm_memos (notes_id, other fields)
VALUES (CURRVAL('name-of-sequence-for-notes_id'), other fields);
*END*
I don't have any Oracle server here to try this on, but you should be
able to execute several commands with one $sth->execute() just
fine.
> Is there a better way to accomplish the objective? I'm a little nervous
> that in a multi-user system the database may slip in another INSERT
> statement from another user prior to this routine getting the current value
> of the incrementing record number and thereby throwing off the returned
> value
The CURRVAL() returns a new number each time so you should be
safe even if you connect to database, fetch CURRVAL(),
disconnect, wait 5 minutes, connect and insert.
You may not assume though that the IDs will be in the order of
inserts or that there will be no gaps.
Jenda
=========== [EMAIL PROTECTED] == http://Jenda.Krynicky.cz ==========
There is a reason for living. There must be. I've seen it somewhere.
It's just that in the mess on my table ... and in my brain.
I can't find it.
--- me
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
