i have found it helpful to use the quote() method for strings like this:
$dbtimestamp="TO_DATE('$db_ts', 'DD-MM-YYYY HH24:MI:SS')" ;> -----Original Message----- > From: Max Hugen [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, August 13, 2002 2:58 AM > 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! > > >
