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: :p5=undef, :p6=undef, :p3=undef, :p7=undef, :p1=undef, :p8=undef, :p4=undef, :p2=undef]
***
Wondering if anyone could shed some light as to why the DB is expecting the eight value (the ? after the RETURNING clause); it should be assigned via the bind_param_inout() function by my understanding. Could very well be something I am missng here though. There may be a way to pass it in the execute() statement as well. but just passing $newMessageID as the eight parameter returns an error. Thanks in advance for assistance.