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
Content-Type: text/plain
MIME-Version: 1.0
Message-Id: <00042014060300.01312@tir>
Content-Transfer-Encoding: 8bit

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