Re: ODBC and Large Objects, FAQ not working - Mailing list pgsql-interfaces

> > Reason of this problem is that PsqlODBC handles the large object without
> > transaction.  Since PostgreSQL 6.5, the large object must be handled in
> > transaction.
> > 
> > To solve, we should insert source code to begin transaction 
> > into PsqlODBC
> > source before calling lo_create and lo_open functions.  Also 
> > should commit
> > transaction after calling lo_close too.
> > 
> > Sorry, I have not tested this solution yet.
> > 
> 
> Is there a driver update in the near future planed, or can you give 
> me a hint, how I can manage this on my own? I have some 
> experiences with C, but how to update the current driver in 
> Windows with the new code is off my limits.

I will attach a patch for PsqlODBC 6.40.0007 to solve this problem.  This
patch also includes another large object patch reported by Sam in this
mailing list.

I have done short test with this patch.

=====
Hiroki Kataoka

===== cur here =====
diff -rc src.v06-40-0007/bind.c src.v06-40-0007.test/bind.c
*** src.v06-40-0007/bind.c    Fri Jan  8 11:32:46 1999
--- src.v06-40-0007.test/bind.c    Wed Dec  8 21:31:35 1999
***************
*** 124,130 ****     }      if (stmt->parameters[ipar].EXEC_buffer) {
!         free(stmt->parameters[ipar].EXEC_buffer);         stmt->parameters[ipar].EXEC_buffer = NULL;     } 
--- 124,131 ----     }      if (stmt->parameters[ipar].EXEC_buffer) {
!         if (stmt->parameters[ipar].SQLType != SQL_LONGVARBINARY)
!             free(stmt->parameters[ipar].EXEC_buffer);         stmt->parameters[ipar].EXEC_buffer = NULL;     } 
diff -rc src.v06-40-0007/convert.c src.v06-40-0007.test/convert.c
*** src.v06-40-0007/convert.c    Fri Apr  9 18:47:40 1999
--- src.v06-40-0007.test/convert.c    Wed Dec  8 21:36:30 1999
***************
*** 40,45 ****
--- 40,46 ---- #include <math.h> #include "convert.h" #include "statement.h"
+ #include "qresult.h" #include "bind.h" #include "pgtypes.h" #include "lobj.h"
***************
*** 895,901 ****
--- 896,926 ----              }             else {
+   
+                 /* begin transaction if needed */
+                 if(!CC_is_in_trans(stmt->hdbc)) {
+                     QResultClass *res;
+                     char ok; 
+                     res = CC_send_query(stmt->hdbc, "BEGIN", NULL);
+                     if (!res) {
+                         stmt->errormsg = "Could not begin (in-line) a transaction";
+                         stmt->errornumber = STMT_EXEC_ERROR;
+                         SC_log_error(func, "", stmt);
+                         return SQL_ERROR;
+                     }
+                     ok = QR_command_successful(res);
+                     QR_Destructor(res);
+                     if (!ok) {
+                         stmt->errormsg = "Could not begin (in-line) a transaction";
+                         stmt->errornumber = STMT_EXEC_ERROR;
+                         SC_log_error(func, "", stmt);
+                         return SQL_ERROR;
+                     }
+ 
+                     CC_set_in_trans(stmt->hdbc);
+                 }
+                  /*    store the oid */                 lobj_oid = lo_creat(stmt->hdbc, INV_READ | INV_WRITE);
        if (lobj_oid == 0) {
 
***************
*** 917,922 ****
--- 942,971 ----                 retval = lo_write(stmt->hdbc, lobj_fd, buffer, used);
lo_close(stmt->hdbc,lobj_fd);
 
+ 
+                 /* commit transaction if needed */
+                 if (!globals.use_declarefetch && CC_is_in_autocommit(stmt->hdbc)) {
+                     QResultClass *res;
+                     char ok;
+ 
+                     res = CC_send_query(stmt->hdbc, "COMMIT", NULL);
+                     if (!res) {
+                         stmt->errormsg = "Could not commit (in-line) a transaction";
+                         stmt->errornumber = STMT_EXEC_ERROR;
+                         SC_log_error(func, "", stmt);
+                         return SQL_ERROR;
+                     }
+                     ok = QR_command_successful(res);
+                     QR_Destructor(res);
+                     if (!ok) {
+                         stmt->errormsg = "Could not commit (in-line) a transaction";
+                         stmt->errornumber = STMT_EXEC_ERROR;
+                         SC_log_error(func, "", stmt);
+                         return SQL_ERROR;
+                     }
+ 
+                     CC_set_no_trans(stmt->hdbc);
+                 }             }              /*    the oid of the large object -- just put that in for the
***************
*** 1340,1345 ****
--- 1389,1417 ----     */      if ( ! bindInfo || bindInfo->data_left == -1) {
+ 
+         /* begin transaction if needed */
+         if(!CC_is_in_trans(stmt->hdbc)) {
+             QResultClass *res;
+             char ok;
+ 
+             res = CC_send_query(stmt->hdbc, "BEGIN", NULL);
+             if (!res) {
+                 stmt->errormsg = "Could not begin (in-line) a transaction";
+                 stmt->errornumber = STMT_EXEC_ERROR;
+                 return COPY_GENERAL_ERROR;
+             }
+             ok = QR_command_successful(res);
+             QR_Destructor(res);
+             if (!ok) {
+                 stmt->errormsg = "Could not begin (in-line) a transaction";
+                 stmt->errornumber = STMT_EXEC_ERROR;
+                 return COPY_GENERAL_ERROR;
+             }
+ 
+             CC_set_in_trans(stmt->hdbc);
+         }
+          oid = atoi(value);         stmt->lobj_fd = lo_open(stmt->hdbc, oid, INV_READ);         if (stmt->lobj_fd <
0){
 
***************
*** 1374,1379 ****
--- 1446,1474 ----     retval = lo_read(stmt->hdbc, stmt->lobj_fd, (char *) rgbValue, cbValueMax);     if (retval < 0)
{        lo_close(stmt->hdbc, stmt->lobj_fd);
 
+ 
+         /* commit transaction if needed */
+         if (!globals.use_declarefetch && CC_is_in_autocommit(stmt->hdbc)) {
+             QResultClass *res;
+             char ok;
+ 
+             res = CC_send_query(stmt->hdbc, "COMMIT", NULL);
+             if (!res) {
+                 stmt->errormsg = "Could not commit (in-line) a transaction";
+                 stmt->errornumber = STMT_EXEC_ERROR;
+                 return COPY_GENERAL_ERROR;
+             }
+             ok = QR_command_successful(res);
+             QR_Destructor(res);
+             if (!ok) {
+                 stmt->errormsg = "Could not commit (in-line) a transaction";
+                 stmt->errornumber = STMT_EXEC_ERROR;
+                 return COPY_GENERAL_ERROR;
+             }
+ 
+             CC_set_no_trans(stmt->hdbc);
+         }
+          stmt->lobj_fd = -1;          stmt->errornumber = STMT_EXEC_ERROR;
***************
*** 1396,1401 ****
--- 1491,1519 ----      if (! bindInfo || bindInfo->data_left == 0) {         lo_close(stmt->hdbc, stmt->lobj_fd);
+ 
+         /* commit transaction if needed */
+         if (!globals.use_declarefetch && CC_is_in_autocommit(stmt->hdbc)) {
+             QResultClass *res;
+             char ok;
+ 
+             res = CC_send_query(stmt->hdbc, "COMMIT", NULL);
+             if (!res) {
+                 stmt->errormsg = "Could not commit (in-line) a transaction";
+                 stmt->errornumber = STMT_EXEC_ERROR;
+                 return COPY_GENERAL_ERROR;
+             }
+             ok = QR_command_successful(res);
+             QR_Destructor(res);
+             if (!ok) {
+                 stmt->errormsg = "Could not commit (in-line) a transaction";
+                 stmt->errornumber = STMT_EXEC_ERROR;
+                 return COPY_GENERAL_ERROR;
+             }
+ 
+             CC_set_no_trans(stmt->hdbc);
+         }
+          stmt->lobj_fd = -1;    /* prevent further reading */     } 
diff -rc src.v06-40-0007/execute.c src.v06-40-0007.test/execute.c
*** src.v06-40-0007/execute.c    Fri Jan  8 11:33:02 1999
--- src.v06-40-0007.test/execute.c    Wed Dec  8 21:38:45 1999
***************
*** 517,522 ****
--- 517,547 ----     /* close the large object */     if ( stmt->lobj_fd >= 0) {         lo_close(stmt->hdbc,
stmt->lobj_fd);
+ 
+         /* commit transaction if needed */
+         if (!globals.use_declarefetch && CC_is_in_autocommit(stmt->hdbc)) {
+             QResultClass *res;
+             char ok;
+ 
+             res = CC_send_query(stmt->hdbc, "COMMIT", NULL);
+             if (!res) {
+                 stmt->errormsg = "Could not commit (in-line) a transaction";
+                 stmt->errornumber = STMT_EXEC_ERROR;
+                 SC_log_error(func, "", stmt);
+                 return SQL_ERROR;
+             }
+             ok = QR_command_successful(res);
+             QR_Destructor(res);
+             if (!ok) {
+                 stmt->errormsg = "Could not commit (in-line) a transaction";
+                 stmt->errornumber = STMT_EXEC_ERROR;
+                 SC_log_error(func, "", stmt);
+                 return SQL_ERROR;
+             }
+ 
+             CC_set_no_trans(stmt->hdbc);
+         }
+          stmt->lobj_fd = -1;     } 
***************
*** 607,612 ****
--- 632,661 ----         /*    Handle Long Var Binary with Large Objects */         if ( current_param->SQLType ==
SQL_LONGVARBINARY){ 
 
+             /* begin transaction if needed */
+             if(!CC_is_in_trans(stmt->hdbc)) {
+                 QResultClass *res;
+                 char ok;
+ 
+                 res = CC_send_query(stmt->hdbc, "BEGIN", NULL);
+                 if (!res) {
+                     stmt->errormsg = "Could not begin (in-line) a transaction";
+                     stmt->errornumber = STMT_EXEC_ERROR;
+                     SC_log_error(func, "", stmt);
+                     return SQL_ERROR;
+                 }
+                 ok = QR_command_successful(res);
+                 QR_Destructor(res);
+                 if (!ok) {
+                     stmt->errormsg = "Could not begin (in-line) a transaction";
+                     stmt->errornumber = STMT_EXEC_ERROR;
+                     SC_log_error(func, "", stmt);
+                     return SQL_ERROR;
+                 }
+ 
+                 CC_set_in_trans(stmt->hdbc);
+             }
+              /*    store the oid */             current_param->lobj_oid = lo_creat(stmt->hdbc, INV_READ | INV_WRITE);
           if (current_param->lobj_oid == 0) {
 
diff -rc src.v06-40-0007/statement.c src.v06-40-0007.test/statement.c
*** src.v06-40-0007/statement.c    Thu Sep  2 22:08:04 1999
--- src.v06-40-0007.test/statement.c    Wed Dec  8 21:40:15 1999
***************
*** 327,333 ****             }              if (self->parameters[i].EXEC_buffer) {
!                 free(self->parameters[i].EXEC_buffer);                 self->parameters[i].EXEC_buffer = NULL;
    }         }
 
--- 327,334 ----             }              if (self->parameters[i].EXEC_buffer) {
!                 if (self->parameters[i].SQLType != SQL_LONGVARBINARY)
!                     free(self->parameters[i].EXEC_buffer);                 self->parameters[i].EXEC_buffer = NULL;
        }         }