Some suggestion to make current ODBC driver better:
1. Postgres backend now has an autocommit feature, I think that this will remain the default for some time. Now the
factis that each INSERT, UPDATE and DELETE statement (and the similar ones: ALTER, CREATE, DROP, GRANT, REVOKE) going
throughthe ODBC driver through the SC_execute() and each one is put into a transaction by the driver. It's no sense to
sendBEGIN and END commands (as the ODBC driver does) for transaction handling if the backend also do this without
sendingthese command.
Unfortunately, my Borland Database Engine has the following method for doing these things above:
SQLSetConnectOption(...,SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF) for BEGIN SQLExecute(...) for DELETE, UPDATE
SQLSetConnectOption(...,SQL_AUTOCOMMIT,SQL_AUTOCOMMIT_ON) for END
No chance, BDE on every INSERT, UPDATE, DELETE (and so on) creates a transaction, not trusting in Postgres. OK, this
isa standard behaviour of the database engines, I know. And in addition BDE handles those transactions well, which
areopen already (opened with StartTransaction() in Borland C++ Builder); closing these transactions the BDE calls
SQLTransact(...,SQL_COMMIT) or SQLTransact(...,SQL_ROLLBACK);
I usually use a different method for the INSERT statement. This method doesn't call SQLSetConnectOption. But
currentlythe ODBC driver also puts my INSERT into a transaction. I would like at least my INSERT not to put in a
transactionwithout any sense.
My suggestion for the code modification is
if ( ! self->internal && ! CC_is_in_trans(conn) && globals.use_declarefetch && self->statement_type ==
STMT_TYPE_SELECT)
in statement.c, instead of
if ( ! self->internal && ! CC_is_in_trans(conn) && (globals.use_declarefetch || STMT_UPDATE(self)))
and these 7 lines to cut:
// If we are in autocommit, we must send the commit. if ( ! self->internal && CC_is_in_autocommit(conn) &&
STMT_UPDATE(self)){...}
I tried the new code with BDE 4.0 and PostgreSQL 7.0beta1, it works fine.
2. Currently the ODBC driver doesn't start a new transaction, if someone calls
SQLSetConnectOption(...,SQL_AUTOCOMMIT,SQL_AUTOCOMMIT_OFF), it only changes a bit with CC_set_autocommit_off. This
meansthat only the first INSERT, UPDATE or DELETE (or their friends) after this SQLSetConnectOption can BEGIN the
transaction.This is a problem, because sometimes a SELECT statement can also do some changes is the Postgres database
(e.g.in PL/PgSQL functions!) which should be able to undo.
Suggestion: modifying options.c, SQLSetConnectOptions. Old code:
case SQL_AUTOCOMMIT_OFF: CC_set_autocommit_off(conn); break;
New code:
case SQL_AUTOCOMMIT_OFF: CC_set_autocommit_off(conn); CC_send_query(conn,"BEGIN",NULL);
CC_set_in_trans(conn); break;
In fact the new code is much longer, because it doesn't handle errors, the same lines also seem to be neccessary
whichappear in SC_execute().
3. In connection.c, for the 7.0 referential integrity error reporting:
Old code:
case 'E': SOCK_get_string(sock, cmdbuffer, ERROR_MSG_LENGTH); qlog("ERROR from backend during clear: '%s'\n",
cmdbuffer); break;
New code:
case 'E': SOCK_get_string(sock, cmdbuffer, ERROR_MSG_LENGTH); qlog("ERROR from backend during clear: '%s'\n",
cmdbuffer); self->errormsg = cmdbuffer; if ( ! strncmp(self->errormsg, "FATAL", 5)) { self->errornumber =
CONNECTION_SERVER_REPORTED_ERROR; CC_set_no_trans(self); } else self->errornumber =
CONNECTION_SERVER_REPORTED_WARNING; return NULL; break;
In addition, in statement.c in SC_execute, the following line should be modified (this is for the error message to
reachthe database engine):
Old code:
self->errormsg = "Error while executing the query";
New code:
if (self->errormsg == "") self->errormsg = "Error while executing the query";
-----------------------------------------------------------------------------
Sorry for not sending patches. I still use 7.0beta1 and ODBC driver
06.04.0009 with Thomas' solution for the 'list index' problem.
You may download my last compiled version (with the changes in 1-3.
and lots of additional log messages) on
ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz/Linux/PostgreSQL/WinODBC/psqlodbc.dll
Please tell your comments and suggestions. Thanks!
Regards,
Zoltan
