Message-ID: <gh-postgresql-interfaces-psqlodbc-43@github.com>
From: "KristianIvarsson (@KristianIvarsson)" <noreply+KristianIvarsson@github.com>
To: "postgresql-interfaces/psqlodbc" <noreply+postgresql-interfaces-psqlodbc@github.com>
Date: Thu, 19 Sep 2024 16:07:00 +0000
Subject: [postgresql-interfaces/psqlodbc] issue #43: syntax error at or near "ROWS"
List-Id: <gh-postgresql-interfaces-psqlodbc.github.com>
X-GitHub-Author-Id: 82163703
X-GitHub-Author-Login: KristianIvarsson
X-GitHub-Issue: 43
X-GitHub-Repo: postgresql-interfaces/psqlodbc
X-GitHub-State: open
X-GitHub-Type: issue
X-GitHub-Url: https://github.com/postgresql-interfaces/psqlodbc/issues/43
Content-Type: text/plain; charset=utf-8

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