RE: Query with return value

[LBaxter]

This is really an oracle specific question I think:

In a before insert trigger on your table add the following:

select customer_custid_seq.nextval into :new.custid from dual;

You can also do the equivalent in DBI... but I prefer a trigger for
this kind of thing.

Lincoln

-----Original Message-----
From: Eric, Audet [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 30, 2001 12:36 PM
To: [EMAIL PROTECTED]
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