RE: Query with return value

[Sterin, Ilya]

If I were you I would not define custid as a function default, but rather
call it separatelly, therefore you can run a select on dual first, retrieve
the value and then proceed with transaction.  You can then roll or commit
the rest of the queries, other than nextval, since that can't be rolled
back.

Ilya

-----Original Message-----
From: Eric, Audet
To: [EMAIL PROTECTED]
Sent: 7/30/01 10:35 AM
Subject: Query with return value

Now ... this could be a stupid question but, eh .. I will try anyway

I am using the DBI module:

I have a table (custid, email, name ...)
Custid is a sequence (customer_custid_seq)
the custid is DEFAULT nextval('customer_custid_seq')

Well ... when I insert values into this table, I need to get the custid
back
so I can insert into another table that use custid as a foreign key.

I tried to do a SELECT, but it did not work ... my guess is that I need
to
commit the changes first. I don't want to do that ... because I may have
to
rollback.

So how can I get that custid

Here's what I was tryint to do ... but not working
     my($query) = "INSERT INTO customer
(password,fname,lname,addr1,addr2,addr3,city,prov,pcode,country,tel,fax,
emai
l,language,emai$
     $sth4 = $dbh->prepare($query);
     $sth4->execute() or &ReturnError(9) and exit;
     $sth4->finish() or &ReturnError(10) and exit;
     my($query) = "SELECT custid FROM customer WHERE email =
'$GETFORM{'email'}'";
     my($sth) = $dbh->prepare($query);
     $sth->execute() or &ReturnError(11) and exit;
     $sth->finish() or &ReturnError(12) and exit;
     ($custid)=$sth->fetchrow_array;

Eric