dbi ODBC problem executing prepared statement taking datetime type

N

niall.macpherson

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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,757
Messages
2,569,543
Members
45,026
Latest member
camilin05

Latest Threads

Top