syntax error at or near "ROWS" #43
Comments
|
Thanks for the report. |
|
It would be useful to provide test code and the table definition? Dave |
|
Table definition |
|
So I wrote some code and test this. Slightly different table works fine. |
|
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) |
|
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 #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;
} |
|
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 |
|
I changed my table definition to match yours and have no issues with the code I put up above |
|
Yeah, but have you tried my code @davecramer ? You’re using prepared statement, but we’re using |
|
I'll try your code today |
|
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 You can see the parameters were not bound. Dave |
|
Possibly and I didn't know that it meant they weren't bound It seems like the parameters are bound if using As I wrote in the mailing list, |
|
Well that seems like a bug. I'll see if I can replicate it. |
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 ONLYwith binding parameters, an errorsyntax error at or near "ROWS"is issuedChanging 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 wantedExtending 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 ONLYand 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)
With "abcde" (success)
With LIMIT-statement
With "abc" (success)
With "abcde" (success)
In all the "abc" test-cases
cbColDefandcbValueMaxare 3 toSQLBindParameter()In all the "abcde" test-cases
cbColDefandcbValueMaxare 5SQLBindParameter()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
The text was updated successfully, but these errors were encountered: