dbi ODBC problem executing prepared statement taking datetime type

Discussion in 'Perl Misc' started by niall.macpherson@ntlworld.com, Apr 27, 2006.

  1. Guest

    Please note that this has been posted to both comp.lang.perl and
    comp.databases.informix as I have had useful responses from both groups
    in the past re the DBI interface

    I have a problem with a call to execute an Informix prepared statment
    which takes 2 datetime values
    failing.

    The following code produces the expected results (i.e the execute
    succeeds)

    ##------------------------------------------------------------------------------------------------------------
    use strict;
    use warnings;
    use Data::Dumper;
    use DBI;

    my $dbh = DBI->connect('dbi:ODBC:eek:l_histinf', '', '',
    , {RaiseError=>1})
    || die $DBI::errstr . "\n";

    my $clipsqlstring = "SELECT sum(clipgetcount(series, '2003-12-06
    00:00:00.00000', '2003-12-07 00:00:00.00000')) FROM spreadsticks;";
    my $selsth = $dbh->prepare($clipsqlstring);
    $selsth->execute() or die 'Failed to execute SQL';
    while(my @row = $selsth->fetchrow)
    {
    print Dumper \@row;
    }
    exit(0);
    ##------------------------------------------------------------------------------------------------------------

    This produces the following results

    C:\develop\NiallPerlScripts>clpm15.pl
    $VAR1 = [
    '32'
    ];
    This is not what I want since the start and end date are hard coded

    However when I try to prepare the statement so that I can use variables
    for the start and end dates as follows

    -----------------------------------------------------------------------------------------------------------------------------------
    my $clipsqlstring = "SELECT sum(clipgetcount(series, ?, ?)) FROM
    spreadsticks;";
    my $selsth = $dbh->prepare($clipsqlstring);
    $selsth->execute('2003-12-06 00:00:00.00000', '2003-12-06
    00:00:00.00000') or die 'Failed to execute SQL';

    ------------------------------------------------------------------------------------------------------------------------------------

    I get the following error

    C:\develop\NiallPerlScripts>clpm15.pl
    DBD::ODBC::st execute failed: [Informix][Informix ODBC Driver]Invalid
    applicatio
    n buffer type. (SQL-HY003)(DBD: _rebind_ph/SQLBindParameter err=-1) at
    C:\develo
    p\NiallPerlScripts\clpm15.pl line 12.
    DBD::ODBC::st execute failed: [Informix][Informix ODBC Driver]Invalid
    applicatio
    n buffer type. (SQL-HY003)(DBD: _rebind_ph/SQLBindParameter err=-1) at
    C:\develo
    p\NiallPerlScripts\clpm15.pl line 12.

    I though this might be a problem with casting datetime year to
    fraction(5) types but the following works as expected.

    my $clipsqlstring = "SELECT srcid FROM spreadsticks_series where
    tstamp = ?;";
    my $selsth = $dbh->prepare($clipsqlstring);
    $selsth->execute('2003-04-16 07:06:45.00000') or die 'Failed to execute
    SQL';
    while(my @row = $selsth->fetchrow)
    {
    print Dumper \@row;
    }

    Can anyone help ?

    TIA
    , Apr 27, 2006
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Panther
    Replies:
    0
    Views:
    2,122
    Panther
    Jul 2, 2003
  2. Ryan
    Replies:
    4
    Views:
    503
    Rhino
    Feb 27, 2005
  3. Ankit
    Replies:
    5
    Views:
    945
    GArlington
    Oct 4, 2007
  4. David Bennett
    Replies:
    0
    Views:
    123
    David Bennett
    Jan 10, 2009
  5. iain
    Replies:
    2
    Views:
    172
Loading...

Share This Page