Re: syntax error at or near "ROWS"

Ah,
I see the problem is you are trying to use parameters
Can you post this as a bug on postgresql-interfaces/psqlodbc (github.com)
<https://github.com/postgresql-interfaces/psqlodbc>

Dave Cramer
www.postgres.rocks

On Thu, 19 Sept 2024 at 09:28, Kristian Ivarsson <
sten(dot)kristian(dot)ivarsson(at)gmail(dot)com> wrote:

> Hi Dave
>
> Well, as you can see, it works occasionally and it always works with fixed
> values
>
> You can read more about it here
> https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-fetch/
>
> / Kristian
>
>
> > Hi Kristian,
>
> > As far as I know PostgreSQL does not support OFFSET ? ROWS FETCH FIRST ?
> ROWS ONLY
>
> > Dave Cramer
> > www.postgres.rocks
>
>
> > On Thu, 19 Sept 2024 at 09:06, Kristian Ivarsson <
> sten(dot)kristian(dot)ivarsson(at)gmail(dot)com<mailto:sten(dot)kristian(dot)ivarsson(at)gmail(dot)com>>
> wrote:
> > Hi Dave
>
> > No, it still doesn't work with the wished statement OFFSET ? ROWS FETCH
> FIRST ? ROWS ONLY (because OFFSET ? LIMIT ? is non standard SQL)
>
> > The only reason I added the LIMIT-statement example was to show the
> differences in the behaviour
>
> > When $1 is "abc" it seems like the rest comes out as $2::int4 and
> $3::int4
>
> > When $1 is "abcde" it seems like rest comes out as $2 and $3
>
>
>
> > Only "abcde" works with FETCH FIRST-statement
>
> > Both "abc" and "abcde" works with the LIMIT-statement (it seems like the
> following parameters has different types though (as stated))
>
>
> > / Kristian
>
>
> > > HI Kristian,
>
> > > So have you solved your issue ?
>
> > > Dave Cramer
> > > www.postgres.rocks
>
>
> > > > I have dug into this a bit more and turned on the postmaster log
>
> > > > This statement fails if the first parameter is "abc" but works with
> "abcde"
>
> > > > SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE
> "Logfile" = ? ORDER BY "Begin" ASC OFFSET ? ROWS FETCH FIRST ? ROW ONLY
>
> > > > The postmaster log with "abc" (cbColDef and cbValueMax both 3)
>
> > > > 2024-09-19 12:20:26.092 CEST [17828] ERROR: syntax error at or
> near "ROWS" at character 116
> > > > 2024-09-19 12:20:26.092 CEST [17828] STATEMENT: SELECT
> "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = $1
> ORDER BY "Begin" ASC OFFSET $2::int4 ROWS FETCH FIRST $3::int4 ROW ONLY
>
> > > > The postmaster log with "abcde" (cbColDef and cbValueMax both 5)
>
> > > > 2024-09-19 12:20:26.092 CEST [17828] LOG: execute <unnamed>:
> SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" =
> $1 ORDER BY "Begin" ASC OFFSET $2 ROWS FETCH FIRST $3 ROW ONLY
> > > > 2024-09-19 12:20:26.092 CEST [17828] DETAIL: parameters: $1 =
> 'abcde', $2 = '0', $3 = '1'
>
>
>
>
> > > > By changing the statement a bit (using OFFSET ? and LIMIT ?) it
> works with both with "abc" and "abcde"
>
> > > > 2024-09-19 12:23:40.386 CEST [32684] LOG: execute <unnamed>:
> SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" =
> $1 ORDER BY "Begin" ASC OFFSET $2::int4 LIMIT $3::int4
> > > > 2024-09-19 12:23:40.386 CEST [32684] DETAIL: parameters: $1 =
> 'abc', $2 = '0', $3 = '1'
> > > > 2024-09-19 12:23:40.386 CEST [32684] LOG: execute <unnamed>:
> SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" =
> $1 ORDER BY "Begin" ASC OFFSET $2 LIMIT $3
> > > > 2024-09-19 12:23:40.386 CEST [32684] DETAIL: parameters: $1 =
> 'abcde', $2 = '0', $3 = '1'
>
>
>
> > > > There's a lot of fishy things going on (where subsequent parameters
> sometimes seems to be serialized as strings depending of the size of
> earlier string but not always), but I won't speculate of the reasons
>
> > > > We want to use the OFFSET ? ROWS FETCH FIRST ? ROW ONLY statement
>
>
> > > > / Kristian
>
>
>
>
>