making winodbc driver better? - Mailing list pgsql-interfaces

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