TendulkarIsGod has asked for the wisdom of the Perl Monks concerning the following question:

monks, please advise

$stm = "insert into glossary (term_id, definition, state) 
       values (seq_document_id.NEXTVAL,?,?) returning term_id into ?";
$sth = $dbh->prepare($stm);
$sth->execute($rows->[$num][1], state($rows->[$num][2]), $value);
die unless ($value);

[download]

the statement inserts into the db, but returns a null value to $value. From querying the db, i know that term_id is being successfully updated with seq_doc_id.NEXTVAL, but for some reason the 'returning into' part does not want to behave, and keeps giving me null values. what am i doing wrong?

UPDATE: Thanks for the advice, folks. Found out the resolution. The best way to do it was to use

$sth->bind_param(1,$variable)
$sth->bind_param(2,$variable)
$sth->bind_param_inout(3,\$value,max_length)
$sth->execute();

[download]

thanks to ruzam for clarifying that execute() is a one-way communication channel, that was the key for me to find the solution. much appreciated

Replies are listed 'Best First'.
Re: DBI help please
by ruzam (Curate) on Dec 07, 2010 at 21:28 UTC

    execute() only accepts values to pass "into" the query. You can't use it as a two way communication channel. Each parameter must match exactly one place holder, and each place holder will be replaced by the matching parameter. The value of "$value" (and any other parameters) will be exactly the same after the query as it was before.

    If this were MySQL you would probably read the $dbh->{'mysql_insertid'} value to get the inserted auto-increment key, after the execute(). I don't know how Oracle accomplishes the same trick.

[reply]
Re: DBI help please
by erix (Prior) on Dec 07, 2010 at 19:12 UTC

    What is the backend?

[reply]

      i should have specified shouldnt i.. its oracle 11g

[reply]
Re: DBI help please
by moritz (Cardinal) on Dec 07, 2010 at 19:44 UTC

    I've never seen placeholders used for data output, and I don't know if it's supported. I'd try: 

    my $sth = $dbh->prepare('insert ... returning term_id');
$sth->execute($rows->[$num][1], state($rows->[$num][2]));

my ($value) = $sth->fetchrow;
$sth->finish;

    [download]
    Perl 6 - second systems done right
[reply]
[d/l]

      didn't work.. , oracle barfed an error message all over my nice clean screen

      my changed code:

      $stm = "insert into glossary (term_id, definition, state)
    values (seq_document_id.NEXTVAL,?,?) returning term_id";
$sth = $dbh->prepare($stm);
$sth->execute($rows->[$num][1], state($rows->[$num][2]));
my ($value) = $sth->fetchrow($sth);

      [download]

      it croaked with

      DBD::Oracle::st execute failed: ORA-00925: missing INTO keyword (DBD ERROR: error possibly near <*> indicator at char 108 in 'insert into glossary (term_id, definition, state) values (seq_document_id.NEXTVAL,:p1,:p2) returning term_id<*>')
[reply]
[d/l]
[select]

        Since I never used Oracle, I don't really know how to do it. If nobody else has a better idea, maybe take a look at how DBIx::Class does it - it sure needs to obtain the primary key for an insert.

        Perl 6 - second systems done right
[reply]

Back to Seekers of Perl Wisdom