Hi Ken Thanks very much, as usual the obvious was staring me in the face!
Most appreciated! Cheers, Max -----Original Message----- From: Gaul, Ken [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 13 August 2002 6:14 PM To: '[EMAIL PROTECTED]' Subject: RE: Novice: Date Binding Problem Move the to_date function to your prepare statment and bind the character string representation of the timestamp $sql=qq { insert into aflbookprice (season,round,bookmaker,aflteam,hhprice,pricedt) values( ?,?,?,?,?,to_date(?,'DDMMYYYY HH24:blah blah) ) }; Ken. -----Original Message----- From: Max Hugen [mailto:[EMAIL PROTECTED]] Sent: 13 August 2002 07:58 To: [EMAIL PROTECTED] Subject: Novice: Date Binding Problem 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
