You asked why I am concatenating the 2 scalars, comma and the space below?
my $limit = "$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 “10, 20”.
my $limit = “$offset, $number_rows”; # $limit = “$offset,$number_rows” did not
work as well.
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);
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 = $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);
======================================
100818 12:12:24 16 Connect dbuser20...@localhost ON employees
16 Query set autocommit=1
16 Query SELECT a_session FROM sessions WHERE
id='e8c13b42f381eb683214d55af6c93ce4'
16 Quit
17 Connect dbuser20...@localhost ON employees
17 Query set autocommit=1
17 Query SELECT fname, lname, dob, substr(desc, 1, 200)
FROM user LEFT JOIN personal_data ON user.id = personal_data.id WHERE gender =
'male' and position = 'HR Admin' order by lname LIMIT 0
\n\r192.168.0.88\0\0\0_SESSION_REMOTE_ADDR\n15 Quit
100818 12:12:24 16 Connect dbuser20...@localhost ON employees
16 Query set autocommit=1
16 Query SELECT a_session FROM sessions WHERE
id='e8c13b42f381eb683214d55af6c93ce4'
16 Quit
17 Connect dbuser20...@localhost ON employees
17 Query set autocommit=1
17 Query SELECT fname, lname, dob, substr(desc, 1, 200)
FROM user LEFT JOIN personal_data ON user.id = personal_data.id WHERE gender =
'male' and position = 'HR Admin' order by lname LIMIT 10
K�kL\0\0\0\0\0\0\0_SESSION_CTIME\nfemale\0\0\0query_seek_genderz�kL\0\0\0\0\0\0\0_SESSION_ATIME\n15
Quit
100818 12:12:24 16 Connect dbuser20...@localhost ON employees
16 Query set autocommit=1
16 Query SELECT a_session FROM sessions WHERE
id='e8c13b42f381eb683214d55af6c93ce4'
16 Quit
17 Connect dbuser20...@localhost ON employees
17 Query set autocommit=1
17 Query SELECT fname, lname, dob, substr(desc, 1, 200)
FROM user LEFT JOIN personal_data ON user.id = personal_data.id WHERE gender =
'male' and position = 'HR Admin' order by lname LIMIT 20
Quit
=> -----Original Message-----
=> From: Uri Guttman [mailto:[email protected]]
=> Sent: 18 August 2010 04:15
=> To: Mimi Cafe
=> Cc: [email protected]
=> Subject: Re: Strange behaviour while using DBI with binding
=>
=> >>>>> "MC" == Mimi Cafe <[email protected]> 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 ------ [email protected] --------
=> http://www.sysarch.com --
=> ----- Perl Code Review , Architecture, Development, Training, Support
=> ------
=> --------- Gourmet Hot Cocoa Mix ---- http://bestfriendscocoa.com --
=> -------
--
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
http://learn.perl.org/
