FAQ
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

Search Discussions

  • Max Hugen at Aug 13, 2002 at 8:27 am
    Hi Ken

    Thanks very much, as usual the obvious was staring me in the face!

    Most appreciated!

    Cheers, Max


    -----Original Message-----
    From: Gaul, Ken
    Sent: Tuesday, 13 August 2002 6:14 PM
    To: 'maxhugen@hugen.com.au'
    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
    Sent: 13 August 2002 07:58
    To: dbi-users@perl.org
    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
  • Kipp, James at Aug 13, 2002 at 12:29 pm
    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
    Sent: Tuesday, August 13, 2002 2:58 AM
    To: dbi-users@perl.org
    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!

  • Michael A Chase at Aug 14, 2002 at 12:52 am

    On Tue, 13 Aug 2002 08:28:29 -0400 "Kipp, James" wrote:

    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')" ;
    Placeholders are much more effecient in DBD::Oracle. You just have to
    remember to pass values, not lexical elements.
    --
    Mac :})
    ** I normally forward private questions to the appropriate mail list. **
    Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
    Give a hobbit a fish and he eats fish for a day.
    Give a hobbit a ring and he eats fish for an age.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedAug 13, '02 at 7:01a
activeAug 14, '02 at 12:52a
posts4
users3
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase