making winodbc driver better?

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 fact is that each INSERT, UPDATE
and DELETE statement (and the similar ones: ALTER, CREATE, DROP, GRANT,
REVOKE) going through the ODBC driver through the SC_execute() and each one
is put into a transaction by the driver. It's no sense to send BEGIN and END
commands (as the ODBC driver does) for transaction handling if the backend
also do this without sending these 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 is a standard
behaviour of the database engines, I know. And in addition BDE handles those
transactions well, which are open 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 currently the ODBC driver also puts
my INSERT into a transaction. I would like at least my INSERT not to
put in a transaction without 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 means that 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 which appear 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 reach the 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