LIMIT clause and placeholders

am 05.03.2004 09:59:24 von beau

Hello -

I noticed the following problem occurs when placeholders
are used in statements with a LIMIT clause.

Using the command line 'mysql' utility:

mysql> select * from some_table limit 10;
OK
mysql> select * from some_table limit '10';
Syntax error [blah, blah, ...] near ''10''...

Using DBI/DBD::mysql (9.2003 and cvs tested):

my $sth = $dbh->prepare("select * from some_table limit ?");
OK
$sth->execute(10);
same syntax error as with 'mysql'

It seems that DBD 'under the covers', quotes all placeholders
whilst contructing the statement to execute, i.e.:

select * from some_table limit '10';

and hence, the syntax error.

The DBI/DBD engine cannot simply not quote placeholders (I know,
I tried it), and not even not quote numeric values (again, I
tested that). It seems something in the mysql engine must indicate
whether or not quotes are allowed, but here is where I stop quessing.

Has anyone else had this problem? Any solutions out there?

Aloha => Beau;

PS: I finally used 'whilst' ;) - not bad for an Hawaiian...

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: LIMIT clause and placeholders

am 05.03.2004 10:49:12 von Jochen Wiedmann

Beau E. Cox wrote:

> my $sth = $dbh->prepare("select * from some_table limit ?");
> OK
> $sth->execute(10);
> same syntax error as with 'mysql'

It would probably work by using $sth->bind_param and specifying
the type. (DBD::mysql contained an incompatbile change that
made all anonymous placeholders strings by default.)

However, using a placeholder for the LIMIT clause cannot be
recommended anyways: It is not portable and perhaps even not
upwards compatible, if at some point the DBD::mysql driver
uses server side placeholders.

Jochen

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: LIMIT clause and placeholders

am 05.03.2004 10:49:12 von Jochen Wiedmann

Re: LIMIT clause and placeholders

am 05.03.2004 11:32:04 von Tim Bunce

All true. I'd only add that dbi-users is the place for such issues.

Tim.

On Fri, Mar 05, 2004 at 10:49:12AM +0100, Jochen Wiedmann wrote:
> Beau E. Cox wrote:
>
> >my $sth = $dbh->prepare("select * from some_table limit ?");
> > OK
> >$sth->execute(10);
> > same syntax error as with 'mysql'
>
> It would probably work by using $sth->bind_param and specifying
> the type. (DBD::mysql contained an incompatbile change that
> made all anonymous placeholders strings by default.)
>
> However, using a placeholder for the LIMIT clause cannot be
> recommended anyways: It is not portable and perhaps even not
> upwards compatible, if at some point the DBD::mysql driver
> uses server side placeholders.
>
>
> Jochen
>

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: LIMIT clause and placeholders

am 05.03.2004 11:32:04 von Tim Bunce

Re: LIMIT clause and placeholders

am 05.03.2004 12:32:03 von beau

On Thursday 04 March 2004 11:49 pm, Jochen Wiedmann wrote:
> Beau E. Cox wrote:
> > my $sth = $dbh->prepare("select * from some_table limit ?");
> > OK
> > $sth->execute(10);
> > same syntax error as with 'mysql'
>
> It would probably work by using $sth->bind_param and specifying
> the type. (DBD::mysql contained an incompatbile change that
> made all anonymous placeholders strings by default.)
>
> However, using a placeholder for the LIMIT clause cannot be
> recommended anyways: It is not portable and perhaps even not
> upwards compatible, if at some point the DBD::mysql driver
> uses server side placeholders.
>
>
> Jochen

Good. May I suggest that the test in t/35limit.t for limit
placeholders be rmoved from the CVS source?

Here is a patch to do just that (tested):

diff -Naru DBD-mysql/t/35limit.t DBD-mysql-35limit/t/35limit.t
--- DBD-mysql/t/35limit.t 2004-02-22 15:35:59.000000000 -1000
+++ DBD-mysql-35limit/t/35limit.t 2004-03-05 01:23:34.000000000 -1000
@@ -96,26 +96,6 @@
Test($state or $cursor->execute($id, $testVals->{$id}))
or DbiError($cursor->err, $cursor->errstr);
}
-
- print "PERL testing prepare of select statement with LIMIT placeholders:
\n";
- Test($state or $cursor = $dbh->prepare("SELECT * FROM $table
LIMIT ?, ?"))
- or DbiError($dbh->err, $dbh->errstr);
-
- print "PERL testing exec of bind vars for LIMIT\n";
- Test($state or $cursor->execute(20, 50))
- or DbiError($cursor->err, $cursor->errstr);
-
- my ($row, $errstr, $array_ref);
- Test(
- $state or
- (defined($array_ref = $cursor->fetchall_arrayref) &&
- (!defined($errstr = $cursor->errstr) || $cursor->errstr eq '')))
- or DbiError($cursor->err, $cursor->errstr);
-
- Test ($state or @$array_ref == 50) or print "results not equaling 50\n";
- #for (@$array_ref) {
- # print "id $_->[0] name $_->[1]\n";
- #}

Test($state or $cursor->finish, "\$sth->finish failed")

Aloha => Beau;

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: LIMIT clause and placeholders

am 05.03.2004 12:32:03 von beau

Re: LIMIT clause and placeholders

am 05.03.2004 16:19:59 von Rudy Lippan

On Fri, 5 Mar 2004, Beau E. Cox wrote:

> On Thursday 04 March 2004 11:49 pm, Jochen Wiedmann wrote:
> > Beau E. Cox wrote:
> > > my $sth = $dbh->prepare("select * from some_table limit ?");
> > > OK
> > > $sth->execute(10);
> > > same syntax error as with 'mysql'
> >
> > It would probably work by using $sth->bind_param and specifying
> > the type. (DBD::mysql contained an incompatbile change that
> > made all anonymous placeholders strings by default.)
> >
> > However, using a placeholder for the LIMIT clause cannot be
> > recommended anyways: It is not portable and perhaps even not
> > upwards compatible, if at some point the DBD::mysql driver
> > uses server side placeholders.

I agree; And I am of half a mind not to allow LIMIT clause ?s :- mainly
because of the vitriolic email that I have gotten on this issue.
However, Patrick Galbraith submitted a patch for
serverside placeholder support that also parsed the statement for the
LIMIT clause and set the placeholders to default to numeric, and another
bit to turn off server-side prepare on statements that have a LIMIT
clause, so it looks the driver will end up supporting ?s with LIMITs.

The reason why the LIMIT stuff is not working now is because I pulled out
the current placeholder scan and replaced it with the one that I wrote for
DBD::Pg which allows for :named_placeholders.

For the LIMITS, instead of parsing the statment looking for them I might
make DBD::mysql not quote /^(\d+)$/ which would make it work like it did
(with LIMITS) before the change, and the ints should get casted to VARCHAR
when used as a param to VARCHAR.

> > Jochen
>
> Good. May I suggest that the test in t/35limit.t for limit
> placeholders be rmoved from the CVS source?
>

Depends on how I decide to go wrt LIMITs & placeholders, so for right now
I plan to leave the broken 35limit.t there, but I can be prevailed upon to
change my plans should I hear a good argument as to why I should go one
way or the other with LIMITS -- This would probably be a good discussion
to have on dbi-users at some point.

And FYI, the stuff in CVS can and will break as I make changes. I am
doing quite a bit of refactoring so functions will come and go sections of
code will break and then be fixed.

Rudy

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: LIMIT clause and placeholders

am 05.03.2004 16:19:59 von Rudy Lippan

Re: LIMIT clause and placeholders

am 05.03.2004 18:39:59 von beau

On Friday 05 March 2004 05:19 am, Rudy Lippan wrote:
> On Fri, 5 Mar 2004, Beau E. Cox wrote:
> > On Thursday 04 March 2004 11:49 pm, Jochen Wiedmann wrote:
> > > Beau E. Cox wrote:
> > > > my $sth = $dbh->prepare("select * from some_table limit ?");
> > > > OK
> > > > $sth->execute(10);
> > > > same syntax error as with 'mysql'
> > >
> > > It would probably work by using $sth->bind_param and specifying
> > > the type. (DBD::mysql contained an incompatbile change that
> > > made all anonymous placeholders strings by default.)
> > >
> > > However, using a placeholder for the LIMIT clause cannot be
> > > recommended anyways: It is not portable and perhaps even not
> > > upwards compatible, if at some point the DBD::mysql driver
> > > uses server side placeholders.
>
> I agree; And I am of half a mind not to allow LIMIT clause ?s :- mainly
> because of the vitriolic email that I have gotten on this issue.
> However, Patrick Galbraith submitted a patch for
> serverside placeholder support that also parsed the statement for the
> LIMIT clause and set the placeholders to default to numeric, and another
> bit to turn off server-side prepare on statements that have a LIMIT
> clause, so it looks the driver will end up supporting ?s with LIMITs.
>
> The reason why the LIMIT stuff is not working now is because I pulled out
> the current placeholder scan and replaced it with the one that I wrote for
> DBD::Pg which allows for :named_placeholders.
>
> For the LIMITS, instead of parsing the statment looking for them I might
> make DBD::mysql not quote /^(\d+)$/ which would make it work like it did
> (with LIMITS) before the change, and the ints should get casted to VARCHAR
> when used as a param to VARCHAR.
>
> > > Jochen
> >
> > Good. May I suggest that the test in t/35limit.t for limit
> > placeholders be rmoved from the CVS source?
>
> Depends on how I decide to go wrt LIMITs & placeholders, so for right now
> I plan to leave the broken 35limit.t there, but I can be prevailed upon to
> change my plans should I hear a good argument as to why I should go one
> way or the other with LIMITS -- This would probably be a good discussion
> to have on dbi-users at some point.
>
> And FYI, the stuff in CVS can and will break as I make changes. I am
> doing quite a bit of refactoring so functions will come and go sections of
> code will break and then be fixed.
>
>
> Rudy

I didn't mean to be pushy. Your choice, of course. I understand
comletely the nature of CVS (that is the place to have things
break).

FOr my testing purposes (for PERL_NO_GET_CONTEXT) I will bypass
the limits test. The PNGX patch is in final testing - working
with or without a patched DBI; I will send it soon,

Aloha =< Beau;

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: LIMIT clause and placeholders

am 05.03.2004 18:39:59 von beau

Re: LIMIT clause and placeholders

am 05.03.2004 20:36:59 von Jochen Wiedmann

Rudy Lippan wrote:

> However, Patrick Galbraith submitted a patch for
> serverside placeholder support that also parsed the statement for the
> LIMIT clause and set the placeholders to default to numeric, and another
> bit to turn off server-side prepare on statements that have a LIMIT
> clause, so it looks the driver will end up supporting ?s with LIMITs.

Does that mean, that the client parses the statement without any
serious reason? I would stongly recommend not to accept this part
of the patch. It contradicts the advantages of server side
placeholders, IMO.

> For the LIMITS, instead of parsing the statment looking for them I might
> make DBD::mysql not quote /^(\d+)$/ which would make it work like it did
> (with LIMITS) before the change, and the ints should get casted to VARCHAR
> when used as a param to VARCHAR.

Don't. Go to server side placeholders and leave everything to the
server as soon as possible.

Jochen

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: LIMIT clause and placeholders

am 05.03.2004 20:36:59 von Jochen Wiedmann

Re: LIMIT clause and placeholders

am 05.03.2004 20:47:36 von Garth Webb

--=-04ygi/b9dj0z6GFwHeoh
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Fri, 2004-03-05 at 11:36, Jochen Wiedmann wrote:
> Rudy Lippan wrote:
[snip]
> > For the LIMITS, instead of parsing the statment looking for them I migh=
t
> > make DBD::mysql not quote /^(\d+)$/ which would make it work like it di=
d
> > (with LIMITS) before the change, and the ints should get casted to VARC=
HAR
> > when used as a param to VARCHAR.
>=20
> Don't. Go to server side placeholders and leave everything to the
> server as soon as possible.

I was under the impression that MySQL doesn't support placeholders and
that the placeholder syntax for DBD::mysql was just for
compatibility/continuity with the other DBD::* classes. If you know
differently, do you know what version of MySQL supports this?

--=20

|- Garth Webb -|
|- garth@zappos.com -|

--=-04ygi/b9dj0z6GFwHeoh
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQBASNlXZJKKSGXZQTcRAvzFAKCvR8RFuQcciMigYgSB6m0o1aAQowCg kdGW
wUOi2TEAq2Ogy3F7tSerOxY=
=McFz
-----END PGP SIGNATURE-----

--=-04ygi/b9dj0z6GFwHeoh--

Re: LIMIT clause and placeholders

am 05.03.2004 20:47:36 von Garth Webb

Re: LIMIT clause and placeholders

am 05.03.2004 20:53:21 von Garth Webb

--=-0CnZu7/TihITel45ulO8
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Fri, 2004-03-05 at 11:47, Garth Webb wrote:
> On Fri, 2004-03-05 at 11:36, Jochen Wiedmann wrote:
> > Rudy Lippan wrote:
> [snip]
> > > For the LIMITS, instead of parsing the statment looking for them I mi=
ght
> > > make DBD::mysql not quote /^(\d+)$/ which would make it work like it =
did
> > > (with LIMITS) before the change, and the ints should get casted to VA=
RCHAR
> > > when used as a param to VARCHAR.
> >=20
> > Don't. Go to server side placeholders and leave everything to the
> > server as soon as possible.
>=20
> I was under the impression that MySQL doesn't support placeholders and
> that the placeholder syntax for DBD::mysql was just for
> compatibility/continuity with the other DBD::* classes. If you know
> differently, do you know what version of MySQL supports this?

I'm sorry, what I meant is that I don't think MySQL will cache prepared
statements with placeholders (thus negating the speed half of the speed
and readability argument for placeholders). Again, if you know
differently I'd love to hear about it.

--=20

|- Garth Webb -|
|- garth@zappos.com -|

--=-0CnZu7/TihITel45ulO8
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQBASNqwZJKKSGXZQTcRAi9hAJ0YZV/B/cZ8CpVWIr0fgfJNUK85/QCg laeX
T3cabbnrOjPDutkV064ng5A=
=Le6N
-----END PGP SIGNATURE-----

--=-0CnZu7/TihITel45ulO8--

Re: LIMIT clause and placeholders

am 05.03.2004 20:53:21 von Garth Webb

Re: LIMIT clause and placeholders

am 05.03.2004 21:00:55 von Jochen Wiedmann

Garth Webb wrote:

> I was under the impression that MySQL doesn't support placeholders and
> that the placeholder syntax for DBD::mysql was just for
> compatibility/continuity with the other DBD::* classes. If you know
> differently, do you know what version of MySQL supports this?

See

http://www.mysql.com/documentation/mysql/bychapter/manual_Cl ients.html#C_API_Prepared_statements

Not sure about the first version, probably 4.1.

Jochen

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: LIMIT clause and placeholders

am 05.03.2004 21:00:55 von Jochen Wiedmann

Re: LIMIT clause and placeholders

am 05.03.2004 21:02:19 von Daniel Koch

Garth Webb wrote:

>>I was under the impression that MySQL doesn't support placeholders and
>>that the placeholder syntax for DBD::mysql was just for
>>compatibility/continuity with the other DBD::* classes. If you know
>>differently, do you know what version of MySQL supports this?
>>
>>
>
>I'm sorry, what I meant is that I don't think MySQL will cache prepared
>statements with placeholders (thus negating the speed half of the speed
>and readability argument for placeholders). Again, if you know
>differently I'd love to hear about it.
>
>
>

New with version 4.1 ...
http://www.mysql.com/documentation/mysql/bychapter/manual_Cl ients.html#C_API_Prepared_statements

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: LIMIT clause and placeholders

am 05.03.2004 21:02:19 von Daniel Koch

Re: LIMIT clause and placeholders

am 05.03.2004 21:13:58 von Jochen Wiedmann

Garth Webb wrote:

> I'm sorry, what I meant is that I don't think MySQL will cache prepared
> statements with placeholders (thus negating the speed half of the speed
> and readability argument for placeholders). Again, if you know
> differently I'd love to hear about it.

No idea, whether it does now or not. However, if one uses the API,
you'll benefit at some point in the future, if the database will
cache. And, besides, at the very least it can return statement
metadata, thus allowing for type safety and the like.

(And, not the least important effect: It will stop related
discussions on this list. ;-)

Jochen

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: LIMIT clause and placeholders

am 05.03.2004 21:13:58 von Jochen Wiedmann

Re: LIMIT clause and placeholders

am 06.03.2004 03:03:36 von Rudy Lippan

On Fri, 5 Mar 2004, Beau E. Cox wrote:

> > The reason why the LIMIT stuff is not working now is because I pulled out
> > the current placeholder scan and replaced it with the one that I wrote for
> > DBD::Pg which allows for :named_placeholders.

Wrote the :named_placeholder handling for Pg -- Not all of the prepare,
most of that was already.

> I didn't mean to be pushy. Your choice, of course. I understand

I did not take it as you being pusy. I just wanted to let you know that I
was going to breaking things and that the 3.0 branch was under heavy
development.

>
> FOr my testing purposes (for PERL_NO_GET_CONTEXT) I will bypass
> the limits test. The PNGX patch is in final testing - working

That is fine -- I am ignoring those tests myself right now :)

> with or without a patched DBI; I will send it soon,
>

Sweet. I wonder if Tim would include A patch to DBI that would allow it
to optionally use PERL_NO_GET_CONTEXT (eg -Dwhatever)? Which will
probably be useful with an embedded DBD::mysql where you would have to use
threads.

Rudy

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: LIMIT clause and placeholders

am 06.03.2004 03:03:36 von Rudy Lippan

Re: LIMIT clause and placeholders

am 06.03.2004 03:24:03 von Rudy Lippan

On Fri, 5 Mar 2004, Jochen Wiedmann wrote:

> Rudy Lippan wrote:
>
> > However, Patrick Galbraith submitted a patch for
> > serverside placeholder support that also parsed the statement for the
> > LIMIT clause and set the placeholders to default to numeric, and another
> > bit to turn off server-side prepare on statements that have a LIMIT
> > clause, so it looks the driver will end up supporting ?s with LIMITs.
>
> Does that mean, that the client parses the statement without any
> serious reason? I would stongly recommend not to accept this part
> of the patch. It contradicts the advantages of server side
> placeholders, IMO.
>

Partly. But I have added support in the driver for named placeholders. It
takes a statement like "SELECT * FROM mytable WHERE col_one = :column_one"
and rewrites the statement into "SELECT * FROM mytable WHERE col_one = ?",
but lets you $sth->bind_param(":column_one", $value); And it will also
return the name, ':column_one", when you do an $sth->{PramValues} All
ofwhich (might?) be working in CVS right now.

I will probably make the named placeholder scan optional in the case of
real server side preared statements for when you want that extra speedup
on prepare, but I see the main strengths of server-side prepare being 1)
faster execution at the expense of prepare time, so the extra scan should
not make that much of a difference and 2) the ability to syntax check a
statement at prepare time rather than having to wait until execute.

>
> > For the LIMITS, instead of parsing the statment looking for them I might
> > make DBD::mysql not quote /^(\d+)$/ which would make it work like it did
> > (with LIMITS) before the change, and the ints should get casted to VARCHAR
> > when used as a param to VARCHAR.
>
> Don't. Go to server side placeholders and leave everything to the
> server as soon as possible.

You are probably right. I am going to play this by ear right now and come
back and think on this when I get to this section of code -- when I have
an releasable 3.0 pre-alpa done (probably in about 1-2 weeks or so).

Rudy

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: LIMIT clause and placeholders

am 06.03.2004 03:24:03 von Rudy Lippan

Re: LIMIT clause and placeholders

am 06.03.2004 03:35:28 von beau

On Friday 05 March 2004 04:03 pm, Rudy Lippan wrote:
> On Fri, 5 Mar 2004, Beau E. Cox wrote:
> > > The reason why the LIMIT stuff is not working now is because I pulled
> > > out the current placeholder scan and replaced it with the one that I
> > > wrote for DBD::Pg which allows for :named_placeholders.
>
> Wrote the :named_placeholder handling for Pg -- Not all of the prepare,
> most of that was already.
>
> > I didn't mean to be pushy. Your choice, of course. I understand
>
> I did not take it as you being pusy. I just wanted to let you know that I
> was going to breaking things and that the 3.0 branch was under heavy
> development.
>
> > FOr my testing purposes (for PERL_NO_GET_CONTEXT) I will bypass
> > the limits test. The PNGX patch is in final testing - working
>
> That is fine -- I am ignoring those tests myself right now :)
>
> > with or without a patched DBI; I will send it soon,
>
> Sweet. I wonder if Tim would include A patch to DBI that would allow it
> to optionally use PERL_NO_GET_CONTEXT (eg -Dwhatever)? Which will
> probably be useful with an embedded DBD::mysql where you would have to use
> threads.
>

That's a good idea. I have a few more days' testing, but when everthing
is ready (currently I'm working on DBI, DBD::mysql, and DBD::Pg) I'll
broach the subject of a PNGX option for DBI.

My code in the DBDs needs no option: macros are setup depending on whether
or not DBI sets PERL_NO_GET_CONTEXT in DBIXS.h. If DBI is NOT using PNGX,
all subroutines called by DBI are coded in IMPLICIT context. Internal
DBD subroutines are alwaays in PNGX - acheiving a speed improvement
even if DBI is not PNGX.

Will post in a few days.

Aloha => Beau;

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: LIMIT clause and placeholders

am 06.03.2004 03:35:28 von beau