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!
> 
> 
> 

Reply via email to