Ow Mun Heng wrote:
Current method.. pseudo code.
while (1)
{
$from_datetime = time;
$to_datetime = time + $time_interval;
$query = "select count(*) from table where time >= ? and time <?";
$sth = $dbh->prepare($query)
$sth->execute($from_datetime, $to_datetime)
$from_datetime = $to_datetime;
}
Results:
1st time ran.. will have results.
2nd and subsequent.. no results.
based on DBI->trace(1)
<- prepare('select count(*)
from table(nolock) where time >= ? and time < ?
')= DBI::st=HASH(0x8756294) at mssql_2_postgres_cvs.pl line 236
<- execute('2007-08-22 18:00:00' '2007-08-22 18:01:00')= -1 at
mssql_2_postgres_cvs.pl line 238
<- fetchrow_array= ( 103 ) [1 items] row1 at mssql_2_postgres_cvs.pl
line 247
"103"
2nd subsequent queries
<- DESTROY(DBI::st=HASH(8720c4c))= undef at mssql_2_postgres_cvs.pl line
238
<- DESTROY(DBI::db=HASH(8755fd0))= undef at mssql_2_postgres_cvs.pl line
238
!! ERROR: -1 'called with 2 bind variables when 4 are needed' (err#0)
<- execute('2007-08-22 18:01:00' '2007-08-22 18:02:00')= undef at
mssql_2_postgres_cvs.pl line 238
If I don't bind the columns, and use
$query = "select count(*) from table where time >= $from_datetime and
time < $to_datetime";
It works for all loops..
so... how come it says 4 bind variables are needed?
I'm not sure why it is not working as you expect but you should move the
prepare outside of the loop:
$query = "select count(*) from table where time >= ? and time <?";
$sth = $dbh->prepare($query)
while (1)
{
$sth->execute($p1, $p2);
}
Martin
