[PATCH] SQLFreeStmt deletes params, but does not reset the stmt->prepared state.
Oliver Freyd <Oliver.Freyd <at> iontof.com>
2014-10-17 10:07:47 GMT
In ResolveOneParam (convert.c:4586) there is code that converts boolean
'-1' to '1'. This is necessary for MS Access because it uses -1 as true
In my application, an Access 2013 frontend with a postgresql backend,
sometimes this conversion did not work, and the backend would fail like
invalid input syntax for type boolean: "-1" at character 399
The statement is like this:
DELETE FROM "public"."someview" WHERE "id" = 13020
AND ... "someboolean" = '-1' ...
This does not happen always, and I've seen it only in DELETE queries,
when I try to delete a row that has a checkbox that is checked (true).
The first try always works, the second try (deleting another row) it fails.
Now this is how it fails (IMHO):
The ResolveOneParam() function relies on the correct types being
assigned to the parameters, In qb->ipdopts->parameters there is
SQLType and PGType. The SQLType is and enum that does not contain
boolean, only the PGType can be boolean.
In PGAPI_Execute() (execute.c:1025) it calls prepareParameters, where
the PGTypes are found (don't know how exactly), and put into the stmt,
If the PGTypes are not 0, the query runs fine. The second time
the query is executed, the query is already prepared (stmt->prepared=5)
and the prepare step is omitted. But then the PGTypes are missing,
the bool '-1' -> '1' conversion is not done and the query fails.
It turns out Access calls SQLFreeStmt(option=SQL_RESET_PARAMS),
that does SC_free_params, so the params get deleted.
But the params contain the result of the prepare call,
so IMHO the stmt->prepared state is now wrong, the transaction is no
more prepared, so stmt->prepared should be reset to 0.
And that actually fixes the bug.
I hope this is useful, it took quite a while to track this down...
diff --git a/statement.c b/statement.c
index da5abf5..a019d5d 100644
<at> <at> -581,6 +581,7 <at> <at> SC_free_params(StatementClass *self, char option)
+ if (self->prepared!=NOT_YET_PREPARED)\
self->data_at_exec = -1;
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription: