Обсуждение: ODBC and Large Objects, FAQ not working
Hi,
I know you've read this all before, as I went through most postings concerning this issue. But I
haven't found a reply that helped me, so I hope I'll get one this way.
I want to hook up Microsoft Access (2000) via ODBC (6.400007) to my PostgreSQL server
(6.5.3, Linux 2.2.10). Everything works fine, but I am having trouble inserting OLE objects into
my database.
I created the "lo" type suggested in the ODBC FAQ and a basic table (CREATE TABLE ole (id
int4, bild lo);) for testing purposes. But when I insert an OLE object, I get the error message
"Couldnt open large object for writing (ERROR #1)".
I attached the corresponding log.
I'd appreciate your help in this matter, as I tried any advice given in earlier postings concerning
problems like this, but nothing would help.
Chris
----
Error log:
conn=144654652, query='BEGIN'
conn=144654652, query='INSERT INTO "ole" ("id") VALUES (1)'
conn=144654652, query='COMMIT'
conn=144654652, query='SELECT "oid","id","bild" FROM "ole" WHERE "oid" IS NULL' [ fetched 0 rows ]
conn=144654652, query='SELECT "ole"."oid" FROM "ole" WHERE
"id" = 1' [ fetched 1 rows ]
conn=144654652, query='SELECT "oid","id","bild" FROM "ole"
WHERE "oid" = 19718' [ fetched 1 rows ]
conn=144654652, query='SELECT "oid","id","bild" FROM "ole" WHERE "oid" = 19718' [ fetched 1 rows ]
conn=144654652, query='SELECT "oid","id","bild" FROM "ole" WHERE "oid" = 19718' [ fetched 1 rows ]
ERROR from backend during send_function: 'ERROR: lo_lseek: invalid large obj descriptor (0)
'
STATEMENT ERROR: func=SQLPutData, desc='', errnum=1, errmsg='Couldnt open large object
for writing.' ------------------------------------------------------------
hdbc=144654652,stmt=144669180, result=0 manual_result=0, prepare=0, internal=0
bindings=0,bindings_allocated=0 parameters=145688992, parameters_allocated=1
statement_type=1,statement='INSERT INTO "ole" ("bild") VALUES (?)' stmt_with_params='INSERT INTO
"ole" ("id") VALUES (1)' data_at_exec=0, current_exec_param=0, put_data=1 currTuple=-1,
current_col=-1,lobj_fd=-1 maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1
cursor_name='SQL_CUR089F79FC' ----------------QResult Info -------------------------------
CONN ERROR: func=SQLPutData, desc='', errnum=0, errmsg='ERROR: lo_lseek: invalid large
obj descriptor (0)
' ------------------------------------------------------------ henv=145686660, conn=144654652,
status=1,num_stmts=16 sock=145688632, stmts=145688672, lobj_type=19268 ---------------- Socket Info
------------------------------- socket=308, reverse=0, errornumber=0, errormsg='(null)'
buffer_in=144660980,buffer_out=144665080 buffer_filled_in=53, buffer_filled_out=0, buffer_read_in=53
ERROR from backend during send_function: 'ERROR: lo_lseek: invalid large obj descriptor (0)
'
STATEMENT ERROR: func=SQLPutData, desc='', errnum=1, errmsg='Couldnt open large object
for writing.' ------------------------------------------------------------
hdbc=144654652,stmt=144669180, result=0 manual_result=0, prepare=0, internal=0
bindings=0,bindings_allocated=0 parameters=145688992, parameters_allocated=1
statement_type=1,statement='INSERT INTO "ole" ("bild") VALUES (?)' stmt_with_params='INSERT INTO
"ole" ("id") VALUES (1)' data_at_exec=0, current_exec_param=0, put_data=1 currTuple=-1,
current_col=-1,lobj_fd=-1 maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1
cursor_name='SQL_CUR089F79FC' ----------------QResult Info -------------------------------
CONN ERROR: func=SQLPutData, desc='', errnum=0, errmsg='ERROR: lo_lseek: invalid large
obj descriptor (0)
' ------------------------------------------------------------ henv=145686660, conn=144654652,
status=1,num_stmts=16 sock=145688632, stmts=145688672, lobj_type=19268 ---------------- Socket Info
------------------------------- socket=308, reverse=0, errornumber=0, errormsg='(null)'
buffer_in=144660980,buffer_out=144665080 buffer_filled_in=53, buffer_filled_out=0, buffer_read_in=53
conn=144654652, SQLDisconnect
Hi, > 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. Thank, Christian Hang
> > 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;
} }
On 8 Dec 99, at 22:43, Hiroki Kataoka wrote: > > 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. Thanks a lot! I downloaded the source code and edited it according to your patch. Now the problem I have is that I don't know how to compile the code and integrate it into the ODBC-driver! Do I need a C-Compiler for Windows/DOS to create a new dll-file, compile and integrate it on the server side or how does it work? A last help in this matter will be highly appreciated. Greetings, Christian
> > > 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. > > Thanks a lot! I downloaded the source code and edited it according > to your patch. > > Now the problem I have is that I don't know how to compile the > code and integrate it into the ODBC-driver! Do I need a C-Compiler > for Windows/DOS to create a new dll-file, compile and integrate it > on the server side or how does it work? To compile the PsqlODBC source, you need MS Visual C++ 4.0 or higher. Step 1: Install original PsqlODBC driver to the Client PC. If you have already installed it, then you may skip this step. Step 2: Compile the patched source. Step 3: Replace original PSQLODBC.DLL file with compiled new one. There is in C:\WINDOWS\SYSTEM(Windows9x) or C:\WINNT\SYSTEM32(WindowsNT). Step 4: Try to use any large objects with new ODBC driver. There is no work on the server side. ===== Hiroki Kataoka