Re: syntax error at or near "ROWS" - Mailing list pgsql-odbc
From | Kristian Ivarsson |
---|---|
Subject | Re: syntax error at or near "ROWS" |
Date | |
Msg-id | DB9PR01MB9512AD039FEF5C2F462DA173F1632@DB9PR01MB9512.eurprd01.prod.exchangelabs.com Whole thread Raw |
In response to | Re: syntax error at or near "ROWS" (Dave Cramer <davecramer@postgres.rocks>) |
Responses |
Re: syntax error at or near "ROWS"
|
List | pgsql-odbc |
Hi Dave No, it still doesn't work with the wished statement OFFSET ? ROWS FETCH FIRST ? ROWS ONLY (because OFFSET ? LIMIT ? is nonstandard 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
pgsql-odbc by date: