Subject: [postgresql-interfaces/psqlodbc] issue #43: syntax error at or near "ROWS"

From: "KristianIvarsson (@KristianIvarsson)" <[email-masked]>
Date: Thu, 19 Sep 2024 16:07:00 +0000

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

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 20 Sep 2024 11:42:32 +0000

Thanks for the report.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 20 Sep 2024 12:57:49 +0000

It would be useful to provide test code and the table definition?

Dave

---

From: "KristianIvarsson (@KristianIvarsson)" <[email-masked]>
Date: Fri, 20 Sep 2024 13:52:36 +0000

Table definition

```
CREATE TABLE "ServerSession" (
    "Id" uuid NOT NULL,
    "Begin" timestamp without time zone,
    "End" timestamp without time zone,
    "Logfile" character varying(255)
);

ALTER TABLE ONLY "ServerSession" ADD CONSTRAINT "ServerSession_pkey" PRIMARY KEY ("Id");

CREATE INDEX "Indx_ServerSession_Begin" ON "ServerSession" USING btree ("Begin") WITH (fillfactor='90', deduplicate_items='true');

CREATE INDEX "Indx_ServerSession_End" ON "ServerSession" USING btree ("End") WITH (fillfactor='90', deduplicate_items='true');
```

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 20 Sep 2024 15:17:12 +0000

So I wrote some code and test this. Slightly different table works fine.

```
create table serversession("Id" serial , "Begin" int4, "End" int4, logfile text);
```

```
rc = SQLExecDirect(hstmt, (SQLCHAR *) "SET intervalstyle=postgres_verbose", SQL_NTS);
	/* Prepare a statement */
	rc = SQLPrepare(hstmt, (SQLCHAR *) "SELECT \"Id\",\"Begin\",\"End\",\"Logfile\" FROM ServerSession ORDER BY \"Begin\" ASC OFFSET ? ROWS FETCH FIRST ? ROW ONLY", SQL_NTS);
	CHECK_STMT_RESULT(rc, "SQLPrepare failed", hstmt);

	/* bind param  */
	longparam1 = 0;
	cbParam1 = sizeof(longparam1);
	rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
						  SQL_C_SLONG,	/* value type */
						  SQL_INTEGER,	/* param type */
						  0,			/* column size (ignored for SQL_INTEGER) */
						  0,			/* dec digits */
						  &longparam1,	/* param value ptr */
						  sizeof(longparam1), /* buffer len (ignored for SQL_INTEGER) */
						  &cbParam1		/* StrLen_or_IndPtr (ignored for SQL_INTEGER) */);
	CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);

	/* bind param  */
	longparam2 = 3;
	cbParam2 = sizeof(longparam2);
	rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,
						  SQL_C_SLONG,	/* value type */
						  SQL_INTEGER,	/* param type */
						  0,			/* column size (ignored for SQL_INTEGER) */
						  0,			/* dec digits */
						  &longparam2,	/* param value ptr */
						  sizeof(longparam2), /* buffer len (ignored for SQL_INTEGER) */
						  &cbParam2		/* StrLen_or_IndPtr (ignored for SQL_INTEGER) */);
	CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);

	/* Test SQLNumResultCols, called before SQLExecute() */
	rc = SQLNumResultCols(hstmt, &colcount);
	CHECK_STMT_RESULT(rc, "SQLNumResultCols failed", hstmt);
	printf("# of result cols: %d\n", colcount);

	/* Execute */
	rc = SQLExecute(hstmt);
	CHECK_STMT_RESULT(rc, "SQLExecute failed", hstmt);

	/* Fetch result */
	print_result(hstmt);

	rc = SQLFreeStmt(hstmt, SQL_CLOSE);
	CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);

	/* Clean up */
	test_disconnect();
```

---

From: "KristianIvarsson (@KristianIvarsson)" <[email-masked]>
Date: Fri, 20 Sep 2024 16:10:32 +0000

Cool, the best option is if there’s a defect on our side 

I’ll try to narrow down our code and reproduce the error whenever I’ll have a computer (using phone now)

---

From: "KristianIvarsson (@KristianIvarsson)" <[email-masked]>
Date: Sat, 21 Sep 2024 08:57:03 +0000

Okey, I now read your comment more thoroughly @davecramer and the "Slightly different table works fine"

Do you mean that you can reproduce the error with our table design but not with your table design ? If so, that is not a proper hotfix for us and this error occurs occationally for other tables with other layout as well. It ends up with this error even if you're doing a SELECT with a non existing table.

However, here's some code that reproduces the error. Compiled with Visual Studio 17.12.0 Preview 2.0 and /std:c++latest (in case the `std::println()` function doesn't work)

```cpp
#ifdef UNICODE
#undef UNICODE
#endif

#include <windows.h>

#include <sqltypes.h>
#include <sqlext.h>
#include <sql.h>

#include <cassert>

#include <print>

void evaluate(const SQLUSMALLINT type, const SQLHANDLE handle, const SQLRETURN result)
{
   switch(result)
   {
   case SQL_SUCCESS:
   case SQL_SUCCESS_WITH_INFO:
      break;
   default:
      SQLCHAR state[5 + 1] = {0};
      SQLCHAR message[SQL_MAX_MESSAGE_LENGTH + 1] = {0};
      SQLSMALLINT length = sizeof(message);

      SQLGetDiagRec(type, handle, 1, state, nullptr, message, length, &length);

      std::println("{} [state={}] [result={}]", (const char*)message, (const char*)state, result);

      assert(false);
   }
}

int main()
{
   SQLHANDLE environment;
   evaluate(SQL_HANDLE_ENV, environment, SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &environment));
   evaluate(SQL_HANDLE_ENV, environment, SQLSetEnvAttr(environment, SQL_ATTR_ODBC_VERSION, reinterpret_cast<void*>(SQL_OV_ODBC3), 0));
   SQLHANDLE connection;
   evaluate(SQL_HANDLE_DBC, connection, SQLAllocHandle(SQL_HANDLE_DBC, environment, &connection));
   evaluate(SQL_HANDLE_DBC, connection, SQLDriverConnect(connection, NULL, (SQLCHAR*)"DSN=TestPG", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT));
   SQLHANDLE statement;
   evaluate(SQL_HANDLE_STMT, statement, SQLAllocHandle(SQL_HANDLE_STMT, connection, &statement));

   SQLSMALLINT number = 0;
   long page = 25;
   evaluate(SQL_HANDLE_STMT, statement, SQLBindParameter(statement, ++number, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &page, sizeof(page), NULL));
   long size = 25;
   evaluate(SQL_HANDLE_STMT, statement, SQLBindParameter(statement, ++number, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &size, sizeof(size), NULL));

   SQLCHAR sql[] = R"(SELECT "Id" FROM "ServerSession" ORDER BY "Begin" ASC OFFSET ? ROWS FETCH FIRST ? ROWS ONLY)";
   evaluate(SQL_HANDLE_STMT, statement, SQLExecDirect(statement, sql, sizeof(sql)));

   return 0;
}
```

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 23 Sep 2024 13:53:34 +0000

No, what I meant to say was that my code runs fine, the only difference is that start and end are integers whereas your code has timestamps.

I'll look at your code shortly

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Mon, 23 Sep 2024 15:20:29 +0000

I changed my table definition to match yours and have no issues with the code I put up above

---

From: "KristianIvarsson (@KristianIvarsson)" <[email-masked]>
Date: Mon, 23 Sep 2024 17:41:45 +0000

Yeah, but have you tried my code @davecramer ? You’re using prepared statement, but we’re using `SQLExecDirect()` (and maybe some other differences) and we’re getting the error despite addressing a bogus table even, so I think the table design is irrelevant in order to reproduce the error .

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 24 Sep 2024 10:02:39 +0000

I'll try your code today

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 24 Sep 2024 13:31:46 +0000

OK, I was able to replicate your problem.

Seems the issue is that only prepared statements will allow you to bind parameters. If I run your code the backend log looks like `SELECT "Id" FROM ServerSession ORDER BY "Begin" ASC OFFSET $1::int4 ROWS FETCH FIRST $2::int4 ROWS ONLY`

You can see the parameters were not bound.

Dave

---

From: "KristianIvarsson (@KristianIvarsson)" <[email-masked]>
Date: Wed, 25 Sep 2024 07:52:27 +0000

Possibly and I didn't know that it meant they weren't bound

It seems like the parameters are bound if using `OFFSET ? LIMIT ?`-statement, and `SQLExecuteDirect()` so it' doesn't seem to be just about that

As I wrote in the mailing list, `OFFSET ? ROWS FETCH FIRST ? ROWS ONLY` statement seems to work if you add a 3rd string binding parameter in some circumstances (it'd better to read the mailing list thread)

https://www.postgresql.org/message-id/DB9PR01MB9512A7C9601A507899D1D465F1632%40DB9PR01MB9512.eurprd01.prod.exchangelabs.com

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 25 Sep 2024 11:43:12 +0000

Well that seems like a bug. I'll see if I can replicate it.