Re: ODBC and Large Objects, FAQ not working

> > 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;
}
}