------=_NextPart_000_0005_01CB3E7E.B4ACB550
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: 7bit

I experienced a strange behaviour while using DBI binding for MySQL query
with LIMIT clause. My CGI program behaved so strange and the result was
always unpredictable and it took me several hours before I finally detected
the problem.



In my program I use binding as in the example below:



# limit should be like "10, 20".

my $limit = "$offset" . ', ' . "$number_rows";



my Sth = $dbh->prepare(qq{Select fname, lname, dob, substr(desc, 1, 200)
from user left join personal_data on user.id = personal_data.id where gender
= ? and position = ? order by lname limit ?});



$sth->execute($gender, $role, $limit);



When above query is executed, I expected DBI to pass a value containing
offset and number to rows to MySQL (2 values separated by a comma and space,
I,e. 10, 20). This mean LIMIT should be like LIMIT 10, 20. Instead, BDI pass
only one (the value for offset) and this made my program to not behaviour.
Because I did not expect this behaviour from the DBI, I did not enable MYSQL
query logging to help debug the problem. Rather, I rewrote my whole code
from scratch and it did not help. After several hours I decided to enable
MYSQL query logging and voila, I found the error.



Does anyone know why DBI behaves this way?



Mimi


------=_NextPart_000_0005_01CB3E7E.B4ACB550--

>>>>> "MC" == Mimi Cafe <mimicafe [at] googlemail.com> writes:

MC> I experienced a strange behaviour while using DBI binding for MySQL query
MC> with LIMIT clause. My CGI program behaved so strange and the result was
MC> always unpredictable and it took me several hours before I finally detected
MC> the problem.

MC> # limit should be like "10, 20".

MC> my $limit = "$offset" . ', ' . "$number_rows";


why are you quoting scalar variables? that should be one simpler string:

my $limit = "$offset, $number_rows";

MC> my Sth = $dbh->prepare(qq{Select fname, lname, dob, substr(desc, 1, 200)

what is Sth? that isn't legal perl. please copy/paste real
code. obviously it should be $sth.

MC> from user left join personal_data on user.id = personal_data.id where gender
MC> = ? and position = ? order by lname limit ?});

also you can format sql strings to be readable. do that. i like here
docs for long multiline strings:

my $sth = $dbh->prepare( <<SQL ) ;
Select fname, lname, dob, substr(desc, 1, 200) from user
left join personal_data
on user.id = personal_data.id
where gender = ? and position = ? order by lname limit ?
SQL
);

MC> $sth->execute($gender, $role, $limit);


MC> When above query is executed, I expected DBI to pass a value
MC> containing offset and number to rows to MySQL (2 values separated
MC> by a comma and space, I,e. 10, 20). This mean LIMIT should be like
MC> LIMIT 10, 20. Instead, BDI pass only one (the value for offset)
MC> and this made my program to not behaviour. Because I did not
MC> expect this behaviour from the DBI, I did not enable MYSQL query
MC> logging to help debug the problem. Rather, I rewrote my whole code
MC> from scratch and it did not help. After several hours I decided to
MC> enable MYSQL query logging and voila, I found the error.

try removing the space after the comma in your limit. the mysql docs show
SELECT * FROM tbl LIMIT 5,10

it makes sense as white space would end the argument list to
limit. otherwise how could the sql parser know the 10 was for limit when
it is optional.

uri

--
Uri Guttman ------ uri [at] stemsystems.com -------- http://www.sysarch.com --
----- Perl Code Review , Architecture, Development, Training, Support ------
--------- Gourmet Hot Cocoa Mix ---- http://bestfriendscocoa.com ---------

--
To unsubscribe, e-mail: beginners-unsubscribe [at] perl.org
For additional commands, e-mail: beginners-help [at] perl.org
http://learn.perl.org/

You asked why I am concatenating the 2 scalars, comma and the space =
below?
my $limit =3D "$offset" . ', ' . "$number_rows";

I agree your suggestion below should work as well, but I was desperate =
to find the error, so I tried several option to see whether DBI will =
parse the string correctly.


Below I tried quoting the string as you mentioned below, but it still =
didn't work as expected. MySQL has no problem if the value for "limit" =
has space between the offset and number of rows as long as there is a =
comma after the offset.

LIMIT 5, 10 and LIMIT 5,10 are the same in MySQL.

# limit should be like =E2=80=9C10, 20=E2=80=9D.
my $limit =3D =E2=80=9C$offset, $number_rows=E2=80=9D; # $limit =3D =
=E2=80=9C$offset,$number_rows=E2=80=9D did not work as well.

my $sth =3D $dbh->prepare(qq{
Select fname, lname, dob, substr(desc, 1, 200) from user left join =
personal_data on
user.id =3D personal_data.id where gender =3D ? and position =3D ? =
order by lname limit ?
});

$sth->execute($gender, $role, $limit);


As can be seen in the MySQL query log below, the DBI parser ignores the =
comma, space and second value and only passes the offset to the =
database. In the first query the $limit variable contained 0,10, but DBI =
passes only the 0 (offset value) to the db server. The only way to get =
around this is for me to pass $limit directly in the query like:



my $sth =3D $dbh->prepare(qq{
Select fname, lname, dob, substr(desc, 1, 200) from user left join =
personal_data on
user.id =3D personal_data.id where gender =3D ? and position =3D ? =
order by lname limit $limit
});

$sth->execute($gender, $role);

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

100818 12:12:24 16 Connect dbuser207yW [at] localhost ON employees
16 Query set autocommit=3D1
16 Query SELECT a_session FROM sessions WHERE =
id=3D'e8c13b42f381eb683214d55af6c93ce4'
16 Quit=09
17 Connect dbuser207yW [at] localhost ON employees
17 Query set autocommit=3D1
17 Query SELECT fname, lname, dob, substr(desc, 1, 200) FROM user =
LEFT JOIN personal_data ON user.id =3D personal_data.id WHERE gender =3D =
'male' and position =3D 'HR Admin' order by lname LIMIT 0
\n\r192.168.0.88\0\0\0_SESSION_REMOTE_ADDR\n15 Quit=09
100818 12:12:24 16 Connect dbuser207yW [at] localhost ON employees
16 Query set autocommit=3D1
16 Query SELECT a_session FROM sessions WHERE =
id=3D'e8c13b42f381eb683214d55af6c93ce4'
16 Quit=09
17 Connect dbuser207yW [at] localhost ON employees
17 Query set autocommit=3D1
17 Query SELECT fname, lname, dob, substr(desc, 1, 200) FROM user =
LEFT JOIN personal_data ON user.id =3D personal_data.id WHERE gender =3D =
'male' and position =3D 'HR Admin' order by lname LIMIT 10

=
=06K=EF=BF=BDkL\0\0\0\0\0\0\0_SESSION_CTIME\n=06female\0\0\0 query_seek_ge=
nder=06z=EF=BF=BDkL\0\0\0\0\0\0\0_SESSION_ATIME\n15 Quit=09
100818 12:12:24 16 Connect dbuser207yW [at] localhost ON employees
16 Query set autocommit=3D1
16 Query SELECT a_session FROM sessions WHERE =
id=3D'e8c13b42f381eb683214d55af6c93ce4'
16 Quit=09
17 Connect dbuser207yW [at] localhost ON employees
17 Query set autocommit=3D1
17 Query SELECT fname, lname, dob, substr(desc, 1, 200) FROM user =
LEFT JOIN personal_data ON user.id =3D personal_data.id WHERE gender =3D =
'male' and position =3D 'HR Admin' order by lname LIMIT 20
Quit=09




=3D> -----Original Message-----
=3D> From: Uri Guttman [mailto:uri [at] StemSystems.com]
=3D> Sent: 18 August 2010 04:15
=3D> To: Mimi Cafe
=3D> Cc: beginners [at] perl.org
=3D> Subject: Re: Strange behaviour while using DBI with binding
=3D>
=3D> >>>>> "MC" =3D=3D Mimi Cafe <mimicafe [at] googlemail.com> writes:
=3D>
=3D> MC> I experienced a strange behaviour while using DBI binding for
=3D> MySQL query
=3D> MC> with LIMIT clause. My CGI program behaved so strange and the
=3D> result was
=3D> MC> always unpredictable and it took me several hours before I
=3D> finally detected
=3D> MC> the problem.
=3D>
=3D> MC> # limit should be like "10, 20".
=3D>
=3D> MC> my $limit =3D "$offset" . ', ' . "$number_rows";
=3D>
=3D>
=3D> why are you quoting scalar variables? that should be one simpler
=3D> string:
=3D>
=3D> my $limit =3D "$offset, $number_rows";
=3D>
=3D> MC> my Sth =3D $dbh->prepare(qq{Select fname, lname, dob, =
substr(desc,
=3D> 1, 200)
=3D>
=3D> what is Sth? that isn't legal perl. please copy/paste real
=3D> code. obviously it should be $sth.
=3D>
=3D> MC> from user left join personal_data on user.id =3D =
personal_data.id
=3D> where gender
=3D> MC> =3D ? and position =3D ? order by lname limit ?});
=3D>
=3D> also you can format sql strings to be readable. do that. i like =
here
=3D> docs for long multiline strings:
=3D>
=3D> my $sth =3D $dbh->prepare( <<SQL ) ;
=3D> Select fname, lname, dob, substr(desc, 1, 200) from user
=3D> left join personal_data
=3D> on user.id =3D personal_data.id
=3D> where gender =3D ? and position =3D ? order by =
lname limit
=3D> ?
=3D> SQL
=3D> );
=3D>
=3D> MC> $sth->execute($gender, $role, $limit);
=3D>
=3D>
=3D> MC> When above query is executed, I expected DBI to pass a value
=3D> MC> containing offset and number to rows to MySQL (2 values
=3D> separated
=3D> MC> by a comma and space, I,e. 10, 20). This mean LIMIT should be
=3D> like
=3D> MC> LIMIT 10, 20. Instead, BDI pass only one (the value for =
offset)
=3D> MC> and this made my program to not behaviour. Because I did not
=3D> MC> expect this behaviour from the DBI, I did not enable MYSQL =
query
=3D> MC> logging to help debug the problem. Rather, I rewrote my whole
=3D> code
=3D> MC> from scratch and it did not help. After several hours I =
decided
=3D> to
=3D> MC> enable MYSQL query logging and voila, I found the error.
=3D>
=3D> try removing the space after the comma in your limit. the mysql =
docs
=3D> show
=3D> SELECT * FROM tbl LIMIT 5,10
=3D>
=3D> it makes sense as white space would end the argument list to
=3D> limit. otherwise how could the sql parser know the 10 was for limit
=3D> when
=3D> it is optional.
=3D>
=3D> uri
=3D>
=3D> --
=3D> Uri Guttman ------ uri [at] stemsystems.com --------
=3D> http://www.sysarch.com --
=3D> ----- Perl Code Review , Architecture, Development, Training, =
Support
=3D> ------
=3D> --------- Gourmet Hot Cocoa Mix ---- http://bestfriendscocoa.com =
--
=3D> -------


--
To unsubscribe, e-mail: beginners-unsubscribe [at] perl.org
For additional commands, e-mail: beginners-help [at] perl.org
http://learn.perl.org/

>>>>> "MC" =3D=3D Mimi Cafe <mimicafe [at] googlemail.com> writes:

MC> You asked why I am concatenating the 2 scalars, comma and the space b=
elow?
MC> my $limit =3D "$offset" . ', ' . "$number_rows";

MC> I agree your suggestion below should work as well, but I was desperat=
e to find the error, so I tried several option to see whether DBI will pars=
e the string correctly.

i don't see any code where there is no space after the comma.


MC> Below I tried quoting the string as you mentioned below, but it
MC> still didn't work as expected. MySQL has no problem if the value
MC> for "limit" has space between the offset and number of rows as
MC> long as there is a comma after the offset.

i don't see that at all.

MC> LIMIT 5, 10 and LIMIT 5,10 are the same in MySQL.

MC> # limit should be like =E2=80=9C10, 20=E2=80=9D.
MC> my $limit =3D =E2=80=9C$offset, $number_rows=E2=80=9D; # $limit =3D =
=E2=80=9C$offset,$number_rows=E2=80=9D did not work as well.

there is a space there. you claim it makes no difference but that isn't
what i see here. show the dbi log of the code without the space.

MC> my $sth =3D $dbh->prepare(qq{
MC> Select fname, lname, dob, substr(desc, 1, 200) from user left join=
personal_data on
MC> user.id =3D personal_data.id where gender =3D ? and position =3D ? =
order by lname limit ?
MC> });

why not try to hard code the limit in that string? don't use a bind
value. there is no need for a bind here as your numbers are hard coded.

MC> $sth->execute($gender, $role, $limit);


MC> As can be seen in the MySQL query log below, the DBI parser
MC> ignores the comma, space and second value and only passes the
MC> offset to the database. In the first query the $limit variable
MC> contained 0,10, but DBI passes only the 0 (offset value) to the db
MC> server. The only way to get around this is for me to pass $limit
MC> directly in the query like:

again, this is with a space. not what i asked to see.


MC> my $sth =3D $dbh->prepare(qq{
MC> Select fname, lname, dob, substr(desc, 1, 200) from user left join=
personal_data on
MC> user.id =3D personal_data.id where gender =3D ? and position =3D ? =
order by lname limit $limit
MC> });

same thing here. i don't see the limit without the space.

uri

--
Uri Guttman ------ uri [at] stemsystems.com -------- http://www.sysarch.com =
--
----- Perl Code Review , Architecture, Development, Training, Support ----=
--
--------- Gourmet Hot Cocoa Mix ---- http://bestfriendscocoa.com -------=
--

--
To unsubscribe, e-mail: beginners-unsubscribe [at] perl.org
For additional commands, e-mail: beginners-help [at] perl.org
http://learn.perl.org/

------=_NextPart_000_0005_01CB3F39.411545B0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

From your last comments, I am not sure where this is leading to, but here is
all I have to say.

Below are 2 pieces of code. Both have been tested with space in the string
and again without space.

$limit = "$offset,$number_rows" or $limit = "$offset, $number_rows";

1) This does not work (with or without space in the string).

my $sth = $dbh->prepare(qq{
Select fname, lname, dob, substr(desc, 1, 200) from user
left join personal_data on
user.id = personal_data.id where gender = ? and position = ?
order by lname limit ?
});
$sth->execute($gender, $role, $limit);


2) This works fine (with or without space in the string).

my $sth = $dbh->prepare(qq{
Select fname, lname, dob, substr(desc, 1, 200) from user
left join personal_data on
user.id = personal_data.id where gender = ? and position = ?
order by lname limit $limit
});
$sth->execute($gender, $role);


In the first example, DBI always passed the value for offset and ignored the
second value after the comma.

I'm now using the second code as it works without problem even if string
contains space. I just need to understand why DBI behaves that way.

I cannot hard code limit in the string because the value varies. This is
used in a pager and the values of limit varies depending on which page is to
be displayed.



------=_NextPart_000_0005_01CB3F39.411545B0--

--0022158bfd91f7a3b5048e2bf73f
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Aug 18, 2010 at 6:55 PM, Babale Fongo <bfongo [at] googlemail.com> wrote:

> Below are 2 pieces of code. Both have been tested with space in the string
> and again without space.
>
> $limit = "$offset,$number_rows" or $limit = "$offset, $number_rows";
>
> [snip...]
>
> 2) This works fine (with or without space in the string).
>
> my $sth = $dbh->prepare(qq{
> Select fname, lname, dob, substr(desc, 1, 200) from user
> left join personal_data on
> user.id = personal_data.id where gender = ? and position =
> ?
> order by lname limit $limit
> });
> $sth->execute($gender, $role);
>


If DBI treats it as two values, maybe passing two value will work. Something
like this code...

my $sth = $dbh->prepare(qq{
Select fname, lname, dob, substr(desc, 1, 200) from user
left join personal_data on
user.id = personal_data.id where gender = ? and position = ?
order by lname limit ?,?
});
$sth->execute($gender, $role, $offset, $number_rows);

--
Robert Wohlfarth

--0022158bfd91f7a3b5048e2bf73f--

On Wed, Aug 18, 2010 at 19:55, Babale Fongo <bfongo [at] googlemail.com> wrote:
snip
> In the first example, DBI always passed the value for offset and ignored the
> second value after the comma.
>
> I'm now using the second code as it works without problem even if string
> contains space. I just need to understand why DBI behaves that way.
>
> I cannot hard code limit in the string because the value varies. This is
> used in a pager and the values of limit varies depending on which page is to
> be displayed.
snip

Placeholders represent single values. The DBI will properly quote any
value it binds to the placeholder. Therefore, you must use more than
one placeholder if you want to provide more than one value:

#!/usr/bin/perl

use strict;
use warnings;

use DBI;

unlink "foo.db" or die $! if -e "foo.db";
my $dbh = DBI->connect(
"dbi:SQLite:dbname=foo.db",
"",
"",
{
ChopBlanks => 1,
PrintError => 0,
RaiseError => 1,
ShowErrorStatement => 1,
FetchHashKeyName => "NAME_lc",
}
) or die DBI->errstr;

$dbh->do("CREATE TABLE foo (n INTEGER)");

my $insert = $dbh->prepare("INSERT INTO foo (n) VALUES (?)");

for my $n (1 .. 100) {
$insert->execute($n);
}

my $select = $dbh->prepare("SELECT n FROM foo ORDER BY n LIMIT ?, ?");

my $count = 5;
my $offset = 0;

do {
$select->execute($offset, $count);
my [at] a;
while (my $rec = $select->fetchrow_hashref) {
push [at] a, $rec->{n};
}
if ($select->rows) {
my $format = join(" ", ("%3d") x [at] a) . "\n";
printf $format, [at] a;
$offset += $count;
} else {
$offset = 0;
}
} while ($offset);


--
Chas. Owens
wonkden.net
The most important skill a programmer can have is the ability to read.

--
To unsubscribe, e-mail: beginners-unsubscribe [at] perl.org
For additional commands, e-mail: beginners-help [at] perl.org
http://learn.perl.org/

Babale Fongo wrote:

> 2) This works fine (with or without space in the string).
>
> my $sth = $dbh->prepare(qq{
> Select fname, lname, dob, substr(desc, 1, 200) from user
> left join personal_data on
> user.id = personal_data.id where gender = ? and position = ?
> order by lname limit $limit
> });
> $sth->execute($gender, $role);
>
>
> In the first example, DBI always passed the value for offset and ignored the
> second value after the comma.


If you have 2 values, you need to "prepare" with two placeholders.

(I quoted "prepare" because a limit is not really preparable)

--
Ruud

--
To unsubscribe, e-mail: beginners-unsubscribe [at] perl.org
For additional commands, e-mail: beginners-help [at] perl.org
http://learn.perl.org/

Normally I would expect a quoted string to represent one value, this is =
probably not true for DBI binding as you mentioned.

Cheers

=3D> -----Original Message-----
=3D> From: Chas. Owens [mailto:chas.owens [at] gmail.com]
=3D> Sent: 19 August 2010 13:22
=3D> To: Babale Fongo
=3D> Cc: Uri Guttman; beginners [at] perl.org
=3D> Subject: Re: Strange behaviour while using DBI with binding
=3D>
=3D> On Wed, Aug 18, 2010 at 19:55, Babale Fongo <bfongo [at] googlemail.com>
=3D> wrote:
=3D> snip
=3D> > In the first example, DBI always passed the value for offset and
=3D> ignored the
=3D> > second value after the comma.
=3D> >
=3D> > I'm now using the second code as it works without problem even if
=3D> string
=3D> > contains space. I just need to understand why DBI behaves that =
way.
=3D> >
=3D> > I cannot hard code limit in the string because the value varies.
=3D> This is
=3D> > used in a pager and the values of limit varies depending on which
=3D> page is to
=3D> > be displayed.
=3D> snip
=3D>
=3D> Placeholders represent single values. The DBI will properly quote =
any
=3D> value it binds to the placeholder. Therefore, you must use more =
than
=3D> one placeholder if you want to provide more than one value:
=3D>
=3D> #!/usr/bin/perl
=3D>
=3D> use strict;
=3D> use warnings;
=3D>
=3D> use DBI;
=3D>
=3D> unlink "foo.db" or die $! if -e "foo.db";
=3D> my $dbh =3D DBI->connect(
=3D> "dbi:SQLite:dbname=3Dfoo.db",
=3D> "",
=3D> "",
=3D> {
=3D> ChopBlanks =3D> 1,
=3D> PrintError =3D> 0,
=3D> RaiseError =3D> 1,
=3D> ShowErrorStatement =3D> 1,
=3D> FetchHashKeyName =3D> "NAME_lc",
=3D> }
=3D> ) or die DBI->errstr;
=3D>
=3D> $dbh->do("CREATE TABLE foo (n INTEGER)");
=3D>
=3D> my $insert =3D $dbh->prepare("INSERT INTO foo (n) VALUES (?)");
=3D>
=3D> for my $n (1 .. 100) {
=3D> $insert->execute($n);
=3D> }
=3D>
=3D> my $select =3D $dbh->prepare("SELECT n FROM foo ORDER BY n LIMIT ?, =
?");
=3D>
=3D> my $count =3D 5;
=3D> my $offset =3D 0;
=3D>
=3D> do {
=3D> $select->execute($offset, $count);
=3D> my [at] a;
=3D> while (my $rec =3D $select->fetchrow_hashref) {
=3D> push [at] a, $rec->{n};
=3D> }
=3D> if ($select->rows) {
=3D> my $format =3D join(" ", ("%3d") x [at] a) . "\n";
=3D> printf $format, [at] a;
=3D> $offset +=3D $count;
=3D> } else {
=3D> $offset =3D 0;
=3D> }
=3D> } while ($offset);
=3D>
=3D>
=3D> --
=3D> Chas. Owens
=3D> wonkden.net
=3D> The most important skill a programmer can have is the ability to =
read.


--
To unsubscribe, e-mail: beginners-unsubscribe [at] perl.org
For additional commands, e-mail: beginners-help [at] perl.org
http://learn.perl.org/