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


Reply via email to