Skip Menu |

This queue is for tickets about the DBD-Pg CPAN distribution.

Report information
The Basics
Id: 69423
Status: resolved
Priority: 0/
Queue: DBD-Pg

People
Owner: Nobody in particular
Requestors: rep.dot.nop [...] gmail.com
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: 3.0.0



Subject: Cannot bind count parameter in 'FETCH NEXT :count ROWS ONLY'
Date: Tue, 12 Jul 2011 11:00:49 +0200
To: bug-DBD-Pg [...] rt.cpan.org
From: Bernhard Reutner-Fischer <rep.dot.nop [...] gmail.com>
Hi, DBD::Pg-2.18.1 with perl-5.12.2 The former LIMIT is now in SQL-2008 ¹) of the form: OFFSET start { ROW | ROWS } FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY DBD-2.18.1 rejects binding the 'count' parameter though; Should but does not work: my $sth = $dbh->prepare("SELECT * FROM x OFFSET 0 ROWS FETCH NEXT :count ROWS ONLY"); $sth->bind_param(":count", $count, SQL_INTEGER); ¹) http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-LIMIT cheers, Bernhard
From: greg [...] turnstep.com
What error message are you seeing? This will not work as it stands, because placeholders are not allowed in the FETCH section via Postgres' PREARE. You can make it work by setting pg_server_prepare to 0. Note that you may also need a recent commit if you are also using bind_param, else it will use PQexecParam and you will see the same error. Commit is: 702d8d097c1ed8463f12f7a32b8d4f32d6d07e5b
Subject: Re: [rt.cpan.org #69423] Cannot bind count parameter in 'FETCH NEXT :count ROWS ONLY'
Date: Fri, 9 Mar 2012 18:26:03 +0100
To: bug-DBD-Pg [...] rt.cpan.org
From: Bernhard Reutner-Fischer <rep.dot.nop [...] gmail.com>
On 9 March 2012 18:06, Greg Sabino Mullane via RT <bug-DBD-Pg@rt.cpan.org> wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=69423 > > > What error message are you seeing? This will not work as it stands, > because placeholders are not allowed in the FETCH section via Postgres' > PREARE. You can make it work by setting pg_server_prepare to 0. Note that > you may also need a recent commit if you are also using bind_param, else > it will use PQexecParam and you will see the same error. > > Commit is: 702d8d097c1ed8463f12f7a32b8d4f32d6d07e5b >
The proper syntax for postgres is to quote the count, i.e. this works fine and is what i'm using now: my $sth = $dbh->prepare(" SELECT * FROM x OFFSET 0 ROWS FETCH NEXT (:count) ROWS ONLY "); $sth->bind_param(":count", $count, SQL_INTEGER); I consider this fixed (resp. unfixable from DBD POV) since this is a glitch in the postgres grammar. This glitch will not be fixed on the postgres side since the grammar in this area does not really allow for non-quoted parameters (at least easily). HTH,
Subject: Re: [rt.cpan.org #69423] Cannot bind count parameter in 'FETCH NEXT :count ROWS ONLY'
Date: Fri, 9 Mar 2012 18:48:28 +0100
To: bug-DBD-Pg [...] rt.cpan.org
From: Bernhard Reutner-Fischer <rep.dot.nop [...] gmail.com>
On 9 March 2012 18:26, Bernhard Reutner-Fischer <rep.dot.nop@gmail.com> wrote: Show quoted text
> On 9 March 2012 18:06, Greg Sabino Mullane via RT > <bug-DBD-Pg@rt.cpan.org> wrote:
>> <URL: https://rt.cpan.org/Ticket/Display.html?id=69423 > >> >> What error message are you seeing? This will not work as it stands, >> because placeholders are not allowed in the FETCH section via Postgres' >> PREARE. You can make it work by setting pg_server_prepare to 0. Note that >> you may also need a recent commit if you are also using bind_param, else >> it will use PQexecParam and you will see the same error. >> >> Commit is: 702d8d097c1ed8463f12f7a32b8d4f32d6d07e5b >>
> The proper syntax for postgres is to quote the count, i.e. this works > fine and is what i'm using now: > my $sth = $dbh->prepare(" > SELECT * FROM x > OFFSET 0 ROWS > FETCH NEXT (:count) ROWS ONLY > "); > $sth->bind_param(":count", $count, SQL_INTEGER); > > I consider this fixed (resp. unfixable from DBD POV) since this is a > glitch in the postgres grammar. > This glitch will not be fixed on the postgres side since the grammar > in this area does not really allow for non-quoted parameters (at least > easily). > HTH,
Forgot to mention that this was added to the postgresql documentation. See http://www.postgresql.org/docs/9.1/static/sql-select.html#SQL-LIMIT [quote] SQL:2008 introduced a different syntax to achieve the same result, which PostgreSQL also supports. It is: OFFSET start { ROW | ROWS } FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY In this syntax, to write anything except a simple integer constant for start or count, you must write parentheses around it. [end of quote] thanks,