Perl and Oracle: Using bind_param_inout

[mirage4d]

Hi. I'm attempting to assign a variable via a field that is using an autoincrement sequence. I'm trying to use the Oracle bind_param_inout() procedure to pass the value by reference. The code goes a bit like this:

*************

my $sql=q(INSERT INTO MESSAGES (message_id, message_text, message_category, start_date, stop_date, admin_comments, time_submitted) VALUES (?,?,?,?,?,?,?) RETURNING message_id INTO ?);
my $sth=$dbh->prepare($sql);
die "Could not prepare query. Check SQL syntax."
unless defined $sql;
my $newMessageID;
$sth->bind_param_inout(8,\$newMessageID, 38);
$sth->execute('messages_id_pkseq.nextval', $messageText, $messageCategory, $startDate, $stopDate, $adminComments, $timeStamp);

****************

By example, bind_param_inout seems to by far the best way to retrieve the value, but the examples I have seen so far all assume no arguments to sth->execute(). In this case I have arguments in my execute statement, and that seems to be where my issues lie. Apache returns:

***
DBD::Oracle::st execute failed: called with 7 bind variables when 8 are needed [for Statement "INSERT INTO MESSAGES (message_id, message_text, message_category, start_date, stop_date, admin_comments, time_submitted) VALUES (?,?,?,?,?,?,?) RETURNING message_id INTO ?" with ParamValues: 5=undef, 6=undef, 3=undef, 7=undef, 1=undef, 8=undef, 4=undef, 2=undef]
***

Wondering if anyone could shed some light as to why the DB is expecting the eight value; it should be assigned via the bind_param_inout() function by my understanding. Could very well be something I am missng here though. Thanks in advance for assistance.

 

[PaulTEG]

RETURNING message_id INTO ?

this is the eighth.

you need the value? if you don't specifically need it, then not passing it, and if it's an auto increment, it'll look after itself

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[mirage4d]

Thanks for the response Paul.

I'm aware that the ? after the RETURNING is the eighth variable; I'm just not sure how to pass it. I was under the assumption that the $sth->bind_param_inout statement would bind it before the execute statement, and thus the DB would recognize that the eighth paramater had already been passed prior to the execute statement, but that doesn't seem to be the case. There must be some way of passing it in the execute statement (passing the bound parameter, $newMessageID, in with the seven others in the execute statement generates an error). I do need the value; it will be written as a foreign key to another table.