Re: syntax error at or near "ROWS" - Mailing list pgsql-odbc

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.kristian.ivarsson@gmail.com<mailto:sten.kristian.ivarsson@gmail.com>>wrote: 
> Hi Dave

> No, it still doesn't work with the wished statement OFFSET ? ROWS FETCH FIRST ? ROWS ONLY (because OFFSET ? LIMIT ?
isnon 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
FETCHFIRST ? 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
dependingof 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