I've struck a problem in binding a date to a DBI prepared statement, to send
to Oracle8i.
I declared a variable:
$dbtimestamp="TO_DATE('$db_ts', 'DD-MM-YYYY HH24:MI:SS')" ;
The string $db_ts is from a function which outputs the system time, in
exactly the same format as that declared in the TO_DATE function.
I can insert with no problem, eg:
####### This works!!!
$sql=qq { insert into aflbookprice
(season,round,pricedt,bookmaker,aflteam,hhprice)
values(
2003,20,$dbtimestamp,'globalsportsbet','Brisbane',1.35 ) };
$sth=$dbh->prepare($sql);
$sth->execute();
$dbh->commit();
$sth->finish();
$dbh->disconnect;
#######
But when I try to use a prepared statement instead, I find that types
SQL_DATE, SQL_VARCHAR all fail, with oracle message:
ORA-01858: a non-numeric character was found where numeric was
expected.
This is an example of the script I'm trying:
####### This Doesn't work!!!
$sql=qq { insert into aflbookprice
(season,round,bookmaker,aflteam,hhprice,pricedt)
values( ?,?,?,?,?,? ) };
$sth=$dbh->prepare($sql);
for( @records ) {
eval {
$sth->bind_param( 1, @$_->[0], SQL_INTEGER);
$sth->bind_param( 2, @$_->[1], SQL_INTEGER );
$sth->bind_param( 3, @$_->[2], SQL_VARCHAR );
$sth->bind_param( 4, @$_->[3], SQL_VARCHAR );
$sth->bind_param( 5, @$_->[4], SQL_DOUBLE );
$sth->bind_param( 6, @$_->[5], SQL_DATE );
$sth->execute();
$dbh->commit();
};
if( $@ ) {
warn "Database error: $DBI::errstr\n";
$dbh->rollback(); #just die if rollback is failing
}
}
#######
If anyone could suggest what I'm doing wrong, I would greatly appreciate it.
I just started on Perl and DBI a couple of weeks ago, and despite searching
Oracle for the ORA error, I could find no way to get around this. It's
driving me nuts!
MTIA, Max Hugen
Hugen Enterprises Pty Ltd
207 Flood St, Leichhardt NSW 2040, Australia
Tel: 02 9560 3061
www.hugen.com.au