RE: Query with return value

[timothy . helck]

Eric,

Do you realize that you can put all sorts of pl/sql stuff into a DBI cursor?
You could do both of your inserts within one cursor, greatly simplifying
things.

Try something like this:

        my $insertCursor = q {
        DECLARE custId number(8);
        BEGIN
                SELECT cust_id_seq.nextval INTO custId from DUAL;
                INSERT INTO some_table(cust_id) VALUES(custId);
                INSERT INTO some_other_table(cust_id) VALUES(custId);
        END;
        };
If you're not already familiar with pl/sql you should be. Used in this way,
it complements Perl beautifully.

Tim

BTW My definition of a stupid question is "a question that if you're
embarassed to ask it, you stay stupid."


-----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