postgresql-interfaces/psqlodbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feedFrom: KristianIvarsson (@KristianIvarsson) <[email protected]>
To: postgresql-interfaces/psqlodbc <[email protected]>
Subject: [postgresql-interfaces/psqlodbc] issue #43: syntax error at or near "ROWS"
Date: Thu, 19 Sep 2024 16:07:00 +0000
Message-ID: <[email protected]> (raw)
PostgreSQL version: 16.4 (SQLGetInfo with SQL_DBMS_NAME says version 16.0.4 though)
Microsoft Windows 10 Enterprise Version 10.0.19044
PSQLODBC35W.DLL version 16.00.0005
When executing a statement `SELECT "Id","Begin","End","Logfile" FROM "ServerSession" ORDER BY "Begin" ASC OFFSET ? ROWS FETCH FIRST ? ROW ONLY` with binding parameters, an error `syntax error at or near "ROWS"` is issued
Changing to `SELECT "Id","Begin","End","Logfile" FROM "ServerSession" ORDER BY "Begin" ASC OFFSET ? LIMIT ?` executes successfully, but that is not standard SQL and thus not wanted
Extending the test a bit reveals some fishy stuff though
Executing `SELECT "SELECT "Id","Begin","End","Logfile" FROM "ServerSession" WHERE "Logfile" = ? ORDER BY "Begin" ASC OFFSET ? ROWS FETCH FIRST ? ROW ONLY` and having a string "abcde" as first parameter works, but not with the string "abc"
Looking at the postmaster log looks even more confusing
With FETCH FIRST-statement
With "abc" (failure)
```
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
2024-09-19 12:20:26.092 CEST [17828] ERROR: syntax error at or near "ROWS" at character 116
```
With "abcde" (success)
```
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'
```
With LIMIT-statement
With "abc" (success)
```
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'
```
With "abcde" (success)
```
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'
```
In all the "abc" test-cases `cbColDef` and `cbValueMax` are 3 to `SQLBindParameter()`
In all the "abcde" test-cases `cbColDef` and `cbValueMax` are 5 `SQLBindParameter()`
Why the integer parameters are serialized as $2int4 and $3int4 when $1 is "abc" and as $2 and $3 when $1 is "abcde" is a bit weird but maybe irrelevant
Maybe needles to say, but the top SQL-statement works for numerous other ODBC drivers to other DBMS
view thread (14+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: github://postgresql-interfaces/psqlodbc
Cc: [email protected], [email protected]
Subject: Re: [postgresql-interfaces/psqlodbc] issue #43: syntax error at or near "ROWS"
In-Reply-To: <<[email protected]>>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox