Re: MySQL LIMIT statement

2003-07-29 Thread Keith C. Ivey
On 29 Jul 2003 at 22:44, Bart Lateur wrote:

> So MySQL cares now, does it? I'm still on MySQL 3.x, and I know that
> there you may quote any type of field, so "123" for an inetegr is
> alright.

The issue is specific to the values after "LIMIT".  Strings in most 
other places are converted to integers automatically as required in 
MySQL 4 just as they were in 3.  Have you tried quoting LIMIT values 
in 3.23.x?

-- 
Keith C. Ivey <[EMAIL PROTECTED]>
Tobacco Documents Online
http://tobaccodocuments.org



Re: MySQL LIMIT statement

2003-07-29 Thread Bart Lateur
On 29 Jul 2003 19:08:23 +0100, Dan Rowles wrote:

>2) Does anyone know if there's a way of getting the DBI to revert to
>it's old behaviour of not automatically quoting placeholder-values? 

So MySQL cares now, does it? I'm still on MySQL 3.x, and I know that
there you may quote any type of field, so "123" for an inetegr is
alright.

Good to know if ever we upgrade. Ouch.

-- 
Bart.


Re: MySQL LIMIT statement

2003-07-29 Thread Bart Lateur
On 29 Jul 2003 19:08:23 +0100, Dan Rowles wrote:

>To go through and change all of these methods to do a
>"$sth->bind_param(n, $val, SQL_INTEGER)" is a lot of work!

Er, you don't have to. It's the first occurrence of using a value for
some placeholder that defines how it'll be used further on. So bind a
parameter to a type first, and you can go on using the rest of the code
as before. Even

$sth->bind_param(3, undef, SQL_INTEGER);

will do -- which is a NULL, but a field of type integer.

-- 
Bart.


Re: MySQL LIMIT statement

2003-07-29 Thread Rudy Lippan
On 29 Jul 2003, Dan Rowles wrote:

> My problem is that I have a large code-base already installed that uses
> the "$sth->execute(@args)" way of parsing in placeholder values. This
> used to work fine. To go through and change all of these methods to do a
> "$sth->bind_param(n, $val, SQL_INTEGER)" is a lot of work!

That might have worked fine but if you had something like:

$args[3] = 'test';
.
.
.
for (1..9) {
print "HI" if $args[$_] >1;
}


$sth->execute(@args);

It would fail because DBD::mysql would not quote any of your arguemnts 
because they were used in numeric context.

> 
> 
> I suppose there are really a couple of reasons for me posting:-
> 
> 1) The $sth->execute(@args) way of running statements is *very* nice,
> and it's a shame that this has been broken (hint to DBI developers :).

The guessing was broken before, so now it quotes on the safe side 
defaulting to quoting as a varchar() see the ChangeLog (btw, I just 
noticed a typo in there -- bind_param(, SQL_INTEGER) should be
bind_param(, undef, SQL_INTEGER).

> 2) Does anyone know if there's a way of getting the DBI to revert to
> it's old behaviour of not automatically quoting placeholder-values? 

Nope.


Rudy



Re: MySQL LIMIT statement

2003-07-29 Thread Michael A Chase
On 29 Jul 2003 19:08:23 +0100 Dan Rowles <[EMAIL PROTECTED]> wrote:

> My problem is that I have a large code-base already installed that uses
> the "$sth->execute(@args)" way of parsing in placeholder values. This
> used to work fine. To go through and change all of these methods to do a
> "$sth->bind_param(n, $val, SQL_INTEGER)" is a lot of work!

You don't have to do it for all statements or for all parameters even
in the statements where it is necessary.  Add it after the prepare()
call for just the placeholders that need the hint.

-- 
Mac :})
** I usually forward private questions to the appropriate mail list. **
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.



Re: MySQL LIMIT statement

2003-07-29 Thread Dan Rowles
Hi Paul,

Thanks for your input. 


My problem is that I have a large code-base already installed that uses
the "$sth->execute(@args)" way of parsing in placeholder values. This
used to work fine. To go through and change all of these methods to do a
"$sth->bind_param(n, $val, SQL_INTEGER)" is a lot of work!


I suppose there are really a couple of reasons for me posting:-

1) The $sth->execute(@args) way of running statements is *very* nice,
and it's a shame that this has been broken (hint to DBI developers :).
2) Does anyone know if there's a way of getting the DBI to revert to
it's old behaviour of not automatically quoting placeholder-values? 
3) Is this a feature that's likely to remain with us permanently - or do
the DBI people think that this is a bug to be fixed???

Thank you for your reply. I'm sorry I was not clearer in my original
post.

Dan






On Tue, 2003-07-29 at 17:56, Paul DuBois wrote:
> At 16:20 +0100 7/29/03, Dan Rowles wrote:
> >Hi there,
> >
> >I'm using MySQL 4.0.13-standard, DBD-mysql 2.9002, DBI 1.37, perl 5.6.1
> >AND perl 5.8.0.
> >
> >The attached program causes a crash, with the following error statement
> >in both version sof perl. It used to work fine using MySQL 3.23.x, and
> >older versions of the DBI.
> >
> >
> >bash-2.05a$ ./database.pl 1 3
> >Offset num: 1 +OK
> >Limit num:  1 +OK
> >DBD::mysql::st execute failed: You have an error in your SQL syntax.
> >Check the manual that corresponds to your MySQL server version for the
> >right syntax to use near ''1', '3'' at line 1 [for statement ``SELECT
> >email FROM users ORDER BY email LIMIT ?, ?'']) at ./database.pl line 25.
> >Can't list users: You have an error in your SQL syntax.  Check the
> >manual that corresponds to your MySQL server version for the right
> >syntax to use near ''1', '3'' at line 1 at ./database.pl line 28.
> 
> LIMIT values must be integer constants.  The placeholder binding
> is probably adding quotes, so you'll need to supply a DBI SQL_INTEGER
> type when you bind.  See "Data Types for Placeholders" in the DBI docs,
> or p124 of the Cheetah book.
> 
> >
> >
> >
> >
> >
> >The error message suggests to me that the numeric values are being
> >passed in as strings to the database. A quick test of MySQL confirms
> >that passing in the limit parameters as strings does indeed cause the
> >same error message (e-mail addresses replaced):-
> >
> >
> >mysql> SELECT email FROM users ORDER BY email LIMIT 0,10;
> >+--+
> >| email|
> >+--+
> >| [EMAIL PROTECTED]  |
> >| [EMAIL PROTECTED] |
> >| [EMAIL PROTECTED] |
> >+--+
> >3 rows in set (0.16 sec)
> >
> >mysql> SELECT email FROM users ORDER BY email LIMIT "0","10";
> >ERROR 1064: You have an error in your SQL syntax.  Check the manual that
> >corresponds to your MySQL server version for the right syntax to use
> >near '"0","10"' at line 1
> >
> >
> >
> >
> >I've been trawling through the mailling lists, and I've found this post,
> >which relates to a problem with the Postgres DBI driver. It sounds to me
> >like this has caused the problem:-
> >
> >http://archive.develooper.com/[EMAIL PROTECTED]/msg02313.html
> >
> >Can anyone comment on this? Has anyone else had a similar problem?
> >
> >Thanks,
> >
> >Dan
> >
> >Attachment converted: ice3:database.pl (TEXT/R*ch) (0001DF53)
> 


Re: MySQL LIMIT statement

2003-07-29 Thread Paul DuBois
At 16:20 +0100 7/29/03, Dan Rowles wrote:
Hi there,

I'm using MySQL 4.0.13-standard, DBD-mysql 2.9002, DBI 1.37, perl 5.6.1
AND perl 5.8.0.
The attached program causes a crash, with the following error statement
in both version sof perl. It used to work fine using MySQL 3.23.x, and
older versions of the DBI.
bash-2.05a$ ./database.pl 1 3
Offset num: 1 +OK
Limit num:  1 +OK
DBD::mysql::st execute failed: You have an error in your SQL syntax.
Check the manual that corresponds to your MySQL server version for the
right syntax to use near ''1', '3'' at line 1 [for statement ``SELECT
email FROM users ORDER BY email LIMIT ?, ?'']) at ./database.pl line 25.
Can't list users: You have an error in your SQL syntax.  Check the
manual that corresponds to your MySQL server version for the right
syntax to use near ''1', '3'' at line 1 at ./database.pl line 28.
LIMIT values must be integer constants.  The placeholder binding
is probably adding quotes, so you'll need to supply a DBI SQL_INTEGER
type when you bind.  See "Data Types for Placeholders" in the DBI docs,
or p124 of the Cheetah book.




The error message suggests to me that the numeric values are being
passed in as strings to the database. A quick test of MySQL confirms
that passing in the limit parameters as strings does indeed cause the
same error message (e-mail addresses replaced):-
mysql> SELECT email FROM users ORDER BY email LIMIT 0,10;
+--+
| email|
+--+
| [EMAIL PROTECTED]  |
| [EMAIL PROTECTED] |
| [EMAIL PROTECTED] |
+--+
3 rows in set (0.16 sec)
mysql> SELECT email FROM users ORDER BY email LIMIT "0","10";
ERROR 1064: You have an error in your SQL syntax.  Check the manual that
corresponds to your MySQL server version for the right syntax to use
near '"0","10"' at line 1


I've been trawling through the mailling lists, and I've found this post,
which relates to a problem with the Postgres DBI driver. It sounds to me
like this has caused the problem:-
http://archive.develooper.com/[EMAIL PROTECTED]/msg02313.html

Can anyone comment on this? Has anyone else had a similar problem?

Thanks,

Dan

Attachment converted: ice3:database.pl (TEXT/R*ch) (0001DF53)



MySQL LIMIT statement

2003-07-29 Thread Dan Rowles
Hi there,

I'm using MySQL 4.0.13-standard, DBD-mysql 2.9002, DBI 1.37, perl 5.6.1
AND perl 5.8.0.

The attached program causes a crash, with the following error statement
in both version sof perl. It used to work fine using MySQL 3.23.x, and
older versions of the DBI.


bash-2.05a$ ./database.pl 1 3
Offset num: 1 +OK
Limit num:  1 +OK
DBD::mysql::st execute failed: You have an error in your SQL syntax. 
Check the manual that corresponds to your MySQL server version for the
right syntax to use near ''1', '3'' at line 1 [for statement ``SELECT
email FROM users ORDER BY email LIMIT ?, ?'']) at ./database.pl line 25.
Can't list users: You have an error in your SQL syntax.  Check the
manual that corresponds to your MySQL server version for the right
syntax to use near ''1', '3'' at line 1 at ./database.pl line 28.





The error message suggests to me that the numeric values are being
passed in as strings to the database. A quick test of MySQL confirms
that passing in the limit parameters as strings does indeed cause the
same error message (e-mail addresses replaced):-


mysql> SELECT email FROM users ORDER BY email LIMIT 0,10;
+--+
| email|
+--+
| [EMAIL PROTECTED]  |
| [EMAIL PROTECTED] |
| [EMAIL PROTECTED] |
+--+
3 rows in set (0.16 sec)

mysql> SELECT email FROM users ORDER BY email LIMIT "0","10";
ERROR 1064: You have an error in your SQL syntax.  Check the manual that
corresponds to your MySQL server version for the right syntax to use
near '"0","10"' at line 1




I've been trawling through the mailling lists, and I've found this post,
which relates to a problem with the Postgres DBI driver. It sounds to me
like this has caused the problem:-

http://archive.develooper.com/[EMAIL PROTECTED]/msg02313.html

Can anyone comment on this? Has anyone else had a similar problem?

Thanks,

Dan
#! /usr/bin/perl -w

use strict;

use DBI;

unless(@ARGV == 2) {
die "Usage: offset limit\n";
}

my $offset = int($ARGV[0]) + 0;
my $limit  = int($ARGV[1]) + 0;

print "Offset num: ", DBI::looks_like_number($offset), " +OK\n";
print "Limit num:  ", DBI::looks_like_number($limit), " +OK\n";

my $dbh = DBI->connect("DBI:mysql:test", "test", "test", 
		   { PrintError => 1, ShowErrorStatement => 1});

my $sth = $dbh->prepare("SELECT email FROM users " . 
			"ORDER BY email LIMIT ?, ?");
$sth->execute($offset, $limit);

if($sth->errstr) {
die "Can't list users: ", $sth->errstr;
}

while(my $tmp = $sth->fetchrow_arrayref) {
print "Email:", $tmp->[0], " +OK\n";
}

print "DONE\n";

exit;