From: Zoltan Kovacs <tip@pc10.radnoti-szeged.sulinet.hu>
To: pgsql-interfaces@postgresql.org
Subject: making winodbc driver better?
Date: Thu, 20 Apr 2000 14:02:52 +0200
Message-ID: <00042014060300.01312@tir> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: tip@pc10.radnoti-szeged.sulinet.hu
Subject: Re: making winodbc driver better?
In-Reply-To: <00042014060300.01312@tir>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox