postgresql-interfaces/psqlodbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[postgresql-interfaces/psqlodbc] issue #43: syntax error at or near "ROWS"
14+ messages / 2 participants
[nested] [flat]
* [postgresql-interfaces/psqlodbc] issue #43: syntax error at or near "ROWS"
@ 2024-09-19 16:07 "KristianIvarsson (@KristianIvarsson)" <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: KristianIvarsson (@KristianIvarsson) @ 2024-09-19 16:07 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #43: syntax error at or near "ROWS"
@ 2024-09-20 11:42 ` "davecramer (@davecramer)" <[email protected]>
12 siblings, 0 replies; 14+ messages in thread
From: davecramer (@davecramer) @ 2024-09-20 11:42 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
Thanks for the report.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #43: syntax error at or near "ROWS"
@ 2024-09-20 12:57 ` "davecramer (@davecramer)" <[email protected]>
12 siblings, 0 replies; 14+ messages in thread
From: davecramer (@davecramer) @ 2024-09-20 12:57 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
It would be useful to provide test code and the table definition?
Dave
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #43: syntax error at or near "ROWS"
@ 2024-09-20 13:52 ` "KristianIvarsson (@KristianIvarsson)" <[email protected]>
12 siblings, 0 replies; 14+ messages in thread
From: KristianIvarsson (@KristianIvarsson) @ 2024-09-20 13:52 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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');
```
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #43: syntax error at or near "ROWS"
@ 2024-09-20 15:17 ` "davecramer (@davecramer)" <[email protected]>
12 siblings, 0 replies; 14+ messages in thread
From: davecramer (@davecramer) @ 2024-09-20 15:17 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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();
```
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #43: syntax error at or near "ROWS"
@ 2024-09-20 16:10 ` "KristianIvarsson (@KristianIvarsson)" <[email protected]>
12 siblings, 0 replies; 14+ messages in thread
From: KristianIvarsson (@KristianIvarsson) @ 2024-09-20 16:10 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #43: syntax error at or near "ROWS"
@ 2024-09-21 08:57 ` "KristianIvarsson (@KristianIvarsson)" <[email protected]>
12 siblings, 0 replies; 14+ messages in thread
From: KristianIvarsson (@KristianIvarsson) @ 2024-09-21 08:57 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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;
}
```
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #43: syntax error at or near "ROWS"
@ 2024-09-23 13:53 ` "davecramer (@davecramer)" <[email protected]>
12 siblings, 0 replies; 14+ messages in thread
From: davecramer (@davecramer) @ 2024-09-23 13:53 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #43: syntax error at or near "ROWS"
@ 2024-09-23 15:20 ` "davecramer (@davecramer)" <[email protected]>
12 siblings, 0 replies; 14+ messages in thread
From: davecramer (@davecramer) @ 2024-09-23 15:20 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
I changed my table definition to match yours and have no issues with the code I put up above
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #43: syntax error at or near "ROWS"
@ 2024-09-23 17:41 ` "KristianIvarsson (@KristianIvarsson)" <[email protected]>
12 siblings, 0 replies; 14+ messages in thread
From: KristianIvarsson (@KristianIvarsson) @ 2024-09-23 17:41 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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 .
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #43: syntax error at or near "ROWS"
@ 2024-09-24 10:02 ` "davecramer (@davecramer)" <[email protected]>
12 siblings, 0 replies; 14+ messages in thread
From: davecramer (@davecramer) @ 2024-09-24 10:02 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
I'll try your code today
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #43: syntax error at or near "ROWS"
@ 2024-09-24 13:31 ` "davecramer (@davecramer)" <[email protected]>
12 siblings, 0 replies; 14+ messages in thread
From: davecramer (@davecramer) @ 2024-09-24 13:31 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #43: syntax error at or near "ROWS"
@ 2024-09-25 07:52 ` "KristianIvarsson (@KristianIvarsson)" <[email protected]>
12 siblings, 0 replies; 14+ messages in thread
From: KristianIvarsson (@KristianIvarsson) @ 2024-09-25 07:52 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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.eurprd0...
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #43: syntax error at or near "ROWS"
@ 2024-09-25 11:43 ` "davecramer (@davecramer)" <[email protected]>
12 siblings, 0 replies; 14+ messages in thread
From: davecramer (@davecramer) @ 2024-09-25 11:43 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
Well that seems like a bug. I'll see if I can replicate it.
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2024-09-25 11:43 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-19 16:07 [postgresql-interfaces/psqlodbc] issue #43: syntax error at or near "ROWS" "KristianIvarsson (@KristianIvarsson)" <[email protected]>
2024-09-20 11:42 ` "davecramer (@davecramer)" <[email protected]>
2024-09-20 12:57 ` "davecramer (@davecramer)" <[email protected]>
2024-09-20 13:52 ` "KristianIvarsson (@KristianIvarsson)" <[email protected]>
2024-09-20 15:17 ` "davecramer (@davecramer)" <[email protected]>
2024-09-20 16:10 ` "KristianIvarsson (@KristianIvarsson)" <[email protected]>
2024-09-21 08:57 ` "KristianIvarsson (@KristianIvarsson)" <[email protected]>
2024-09-23 13:53 ` "davecramer (@davecramer)" <[email protected]>
2024-09-23 15:20 ` "davecramer (@davecramer)" <[email protected]>
2024-09-23 17:41 ` "KristianIvarsson (@KristianIvarsson)" <[email protected]>
2024-09-24 10:02 ` "davecramer (@davecramer)" <[email protected]>
2024-09-24 13:31 ` "davecramer (@davecramer)" <[email protected]>
2024-09-25 07:52 ` "KristianIvarsson (@KristianIvarsson)" <[email protected]>
2024-09-25 11:43 ` "davecramer (@davecramer)" <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox