DBI-Bind parameters

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?

Re: DBI-Bind parameters

On Thu, 2007-08-23 at 10:17 +0800, 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 managed to solve it using bind_param..

eg:
$sth->bind_param(1, $from_datetime);
$sth->bind_param(2, $to_datetime);
$sth->execute();
Why is that the above works and the org method doesnt? Anyone knows?

Re: DBI-Bind parameters

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

Re: DBI-Bind parameters

On Thu, 2007-08-23 at 08:26 +0100, Martin J. Evans wrote:
> 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);
> }


oh.. that's better practice? OK. I'll take a look at it.. but as of now,
bind_param works w/ the prepare in the loop.

Re: DBI-Bind parameters

--5mCyUwZo2JvN/JJP
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2007-08-23 10:17:58 +0800, Ow Mun Heng wrote:
> Current method.. pseudo code.
>=20
> while (1)
> {
> $from_datetime =3D time;
> $to_datetime =3D time + $time_interval;
>=20
> $query =3D "select count(*) from table where time >=3D ? and time > $sth =3D $dbh->prepare($query)=20
> $sth->execute($from_datetime, $to_datetime)
> $from_datetime =3D $to_datetime;
> }

Please don't post pseudo code. It is hard to say what you are doing
wrong if you don't tell us what you are actually doing. Post a
complete, minimal script which demonstrates the problem.


>=20
> Results:
>=20
[...]
> 2nd subsequent queries
> <- DESTROY(DBI::st=3DHASH(8720c4c))=3D undef at mssql_2_postgres_cvs.pl l=
ine
> 238
> <- DESTROY(DBI::db=3DHASH(8755fd0))=3D undef at mssql_2_postgres_cvs.pl l=
ine
> 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')=3D undef at
> mssql_2_postgres_cvs.pl line 238
[...]
> so... how come it says 4 bind variables are needed?

I suspect that you aren't really preparing the same statement in the
second run through the loop. Maybe you conatenated two queries, or
something like that. But since we don't know your actual code, we can
just guess. Insert a 'print "query=3D<$query>\n" before the prepare to
check.=20

hp

--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

--5mCyUwZo2JvN/JJP
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGzUJzMdFfQa64PCwRAgzSAJ9p+W1r3JOtUoE1G2Og6kea+Q371QCc DNR8
tSAKoy9xVLo+z7H4KWHLx2U=
=FU2p
-----END PGP SIGNATURE-----

--5mCyUwZo2JvN/JJP--