Received: from nt.gameszone.net ([194.176.220.91]) by hub.org (8.9.3/8.9.3) with ESMTP id IAA58315 for ; Thu, 17 Feb 2000 08:36:13 -0500 (EST) (envelope-from rdear@egomaniacs.net) Received: from laptop ([212.161.68.201]) by nt.gameszone.net (Post.Office MTA v3.5.3 release 223 ID# 0-62387U100L2S100V35) with SMTP id net for ; Thu, 17 Feb 2000 13:18:46 +0000 Message-ID: <001201bf794b$9481b5e0$2837370a@egomaniacs.net> From: rdear@egomaniacs.net (Rick Dearman) To: Subject: OID insert fails between MS-Access 2000 and Postgresql 6.5.2 Date: Thu, 17 Feb 2000 13:33:27 -0000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000D_01BF794B.923F6700" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6600 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 This is a multi-part message in MIME format. ------=_NextPart_000_000D_01BF794B.923F6700 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have downloaded the latest ODBC driver for postgresql ( = http://www.insightdist.com/psqlodbc/ ) and the latest Jet Engine patch = from Microsoft. I have created the sample table shown in the ODBC FAQ. +++++++++++++++++++++++++++++++++++++ create type lo (=20 internallength=3D4, externallength=3D10,=20 input=3Dint4in, output=3Dint4out,=20 default=3D'', passedbyvalue=20 ); create table employee (=20 id integer,=20 name varchar(30),=20 picture lo=20 ); +++++++++++++++++++++++++++++++++++++ However when I attempt to insert a small icon into the OLE object field = and save the record Access gives an error "Couldn't open object for = writing. Error #1" I here included is the excerpt from the psqlcomm.log CONN ERROR: func=3DSQLGetConnectOption, desc=3D'fOption=3D30002', = errnum=3D205, errmsg=3D'Unknown connect option (Get)' ------------------------------------------------------------ henv=3D139198596, conn=3D138152060, status=3D0, num_stmts=3D16 sock=3D139198612, stmts=3D139198652, lobj_type=3D-999 ---------------- Socket Info ------------------------------- socket=3D-1, reverse=3D0, errornumber=3D0, errormsg=3D'(null)' buffer_in=3D138158388, buffer_out=3D138162488 buffer_filled_in=3D0, buffer_filled_out=3D0, buffer_read_in=3D0 CONN ERROR: func=3DSQLSetConnectOption, desc=3D'fOption=3D30002, = vParam=3D122356332', errnum=3D205, errmsg=3D'Unknown connect option = (Set)' ------------------------------------------------------------ henv=3D139198596, conn=3D138152060, status=3D0, num_stmts=3D16 sock=3D139198612, stmts=3D139198652, lobj_type=3D-999 ---------------- Socket Info ------------------------------- socket=3D-1, reverse=3D0, errornumber=3D0, errormsg=3D'(null)' buffer_in=3D138158388, buffer_out=3D138162488 buffer_filled_in=3D0, buffer_filled_out=3D0, buffer_read_in=3D0 conn=3D138152060, SQLDriverConnect( = in)=3D'DSN=3DPostgreSQL;UID=3Dtest_user;PWD=3D;DATABASE=3Dtest;SERVER=3D1= 0.55.55.1;PORT=3D5432;READONLY=3D0;PROTOCOL=3D6.4;FAKEOIDINDEX=3D0;SHOWOI= DCOLUMN=3D0;ROWVERSIONING=3D0;SHOWSYSTEMTABLES=3D0;CONNSETTINGS=3D;', = fDriverCompletion=3D0 DSN info: = DSN=3D'PostgreSQL',server=3D'10.55.55.1',port=3D'5432',dbase=3D'test',use= r=3D'test_user',passwd=3D'test_user001' readonly=3D'0',protocol=3D'6.4',showoid=3D'0',fakeoidindex=3D'0',showsyst= able=3D'0' conn_settings=3D'' translation_dll=3D'',translation_option=3D'' Global Options: Version=3D'06.40.0006', fetch=3D100, socket=3D4096, = unknown_sizes=3D0, max_varchar_size=3D254, max_longvarchar_size=3D8190 disable_optimizer=3D1, ksqo=3D1, unique_index=3D0, use_declarefetch=3D0 text_as_longvarchar=3D1, unknowns_as_longvarchar=3D1, bools_as_char=3D1 extra_systable_prefixes=3D'dd_;', conn_settings=3D'' conn=3D138152060, query=3D' ' conn=3D138152060, query=3D'set DateStyle to 'ISO'' conn=3D138152060, query=3D'set geqo to 'OFF'' conn=3D138152060, query=3D'set ksqo to 'ON'' conn=3D138152060, query=3D'select oid from pg_type where typname=3D'lo'' [ fetched 1 rows ] [ Large Object oid =3D 22720 ] conn=3D138152060, = SQLDriverConnect(out)=3D'DSN=3DPostgreSQL;DATABASE=3Dtest;SERVER=3D10.55.= 55.1;PORT=3D5432;UID=3Dtest_user;PWD=3Dtest_user001;READONLY=3D0;PROTOCOL= =3D6.4;FAKEOIDINDEX=3D0;SHOWOIDCOLUMN=3D0;ROWVERSIONING=3D0;SHOWSYSTEMTAB= LES=3D0;CONNSETTINGS=3D' conn=3D138152060, query=3D'SELECT Config, nValue FROM MSysConf' ERROR from backend during send_query: 'ERROR: msysconf: Table does not = exist.' STATEMENT ERROR: func=3DSC_execute, desc=3D'', errnum=3D1, = errmsg=3D'Error while executing the query' ------------------------------------------------------------ hdbc=3D138152060, stmt=3D138166588, result=3D0 manual_result=3D0, prepare=3D0, internal=3D0 bindings=3D0, bindings_allocated=3D0 parameters=3D0, parameters_allocated=3D0 statement_type=3D0, statement=3D'SELECT Config, nValue FROM MSysConf' stmt_with_params=3D'SELECT Config, nValue FROM MSysConf' data_at_exec=3D-1, current_exec_param=3D-1, put_data=3D0 currTuple=3D-1, current_col=3D-1, lobj_fd=3D-1 maxRows=3D0, rowset_size=3D1, keyset_size=3D0, cursor_type=3D0, = scroll_concurrency=3D1 cursor_name=3D'SQL_CUR083C413C' ----------------QResult Info ------------------------------- CONN ERROR: func=3DSC_execute, desc=3D'', errnum=3D110, errmsg=3D'ERROR: = msysconf: Table does not exist.' ------------------------------------------------------------ henv=3D139198596, conn=3D138152060, status=3D1, num_stmts=3D16 sock=3D139198612, stmts=3D139198652, lobj_type=3D22720 ---------------- Socket Info ------------------------------- socket=3D224, reverse=3D0, errornumber=3D0, errormsg=3D'(null)' buffer_in=3D138158388, buffer_out=3D138162488 buffer_filled_in=3D42, buffer_filled_out=3D0, buffer_read_in=3D42 conn=3D138152060, query=3D'SELECT "employee"."id" FROM "employee" ' [ fetched 0 rows ] ERROR from backend during send_function: 'ERROR: lo_lseek: invalid large = obj descriptor (0) ' STATEMENT ERROR: func=3DSQLPutData, desc=3D'', errnum=3D1, = errmsg=3D'Couldnt open large object for writing.' ------------------------------------------------------------ hdbc=3D138152060, stmt=3D138166588, result=3D0 manual_result=3D0, prepare=3D0, internal=3D0 bindings=3D0, bindings_allocated=3D0 parameters=3D139200792, parameters_allocated=3D3 statement_type=3D1, statement=3D'INSERT INTO "employee" = ("id","name","picture") VALUES (?,?,?)' stmt_with_params=3D'' data_at_exec=3D0, current_exec_param=3D2, put_data=3D1 currTuple=3D-1, current_col=3D-1, lobj_fd=3D-1 maxRows=3D0, rowset_size=3D1, keyset_size=3D0, cursor_type=3D0, = scroll_concurrency=3D1 cursor_name=3D'' ----------------QResult Info ------------------------------- CONN ERROR: func=3DSQLPutData, desc=3D'', errnum=3D110, errmsg=3D'ERROR: = lo_lseek: invalid large obj descriptor (0) ' ------------------------------------------------------------ henv=3D139198596, conn=3D138152060, status=3D1, num_stmts=3D16 sock=3D139198612, stmts=3D139198652, lobj_type=3D22720 ---------------- Socket Info ------------------------------- socket=3D224, reverse=3D0, errornumber=3D0, errormsg=3D'(null)' buffer_in=3D138158388, buffer_out=3D138162488 buffer_filled_in=3D53, buffer_filled_out=3D0, buffer_read_in=3D53 conn=3D138152060, SQLDisconnect ------=_NextPart_000_000D_01BF794B.923F6700 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I have downloaded the latest ODBC = driver for=20 postgresql ( http://www.insightdist.com/= psqlodbc/ )=20 and the latest Jet Engine patch from Microsoft. I have created the = sample table=20 shown in the ODBC FAQ.
 
+++++++++++++++++++++++++++++++++++++
create type lo ( =
   =20 internallength=3D4,  externallength=3D10, =
   =20 input=3Dint4in, output=3Dint4out,
   =20 default=3D'',  passedbyvalue
);
create table employee (
    id = integer,=20
    name varchar(30), =
   =20 picture lo
);
+++++++++++++++++++++++++++++++++++++
However when I attempt to insert a small icon into the OLE object = field and=20 save the record Access gives an error "Couldn't open object for = writing.=20 Error #1"
 
I here included is the excerpt from the psqlcomm.log
 

CONN ERROR: func=3DSQLGetConnectOption, desc=3D'fOption=3D30002', = errnum=3D205,=20 errmsg=3D'Unknown connect option (Get)'

------------------------------------------------------------

henv=3D139198596, conn=3D138152060, status=3D0, num_stmts=3D16

sock=3D139198612, stmts=3D139198652, lobj_type=3D-999

---------------- Socket Info -------------------------------

socket=3D-1, reverse=3D0, errornumber=3D0, errormsg=3D'(null)'

buffer_in=3D138158388, buffer_out=3D138162488

buffer_filled_in=3D0, buffer_filled_out=3D0, buffer_read_in=3D0

CONN ERROR: func=3DSQLSetConnectOption, desc=3D'fOption=3D30002, = vParam=3D122356332',=20 errnum=3D205, errmsg=3D'Unknown connect option (Set)'

------------------------------------------------------------

henv=3D139198596, conn=3D138152060, status=3D0, num_stmts=3D16

sock=3D139198612, stmts=3D139198652, lobj_type=3D-999

---------------- Socket Info -------------------------------

socket=3D-1, reverse=3D0, errornumber=3D0, errormsg=3D'(null)'

buffer_in=3D138158388, buffer_out=3D138162488

buffer_filled_in=3D0, buffer_filled_out=3D0, buffer_read_in=3D0

conn=3D138152060, SQLDriverConnect(=20 in)=3D'DSN=3DPostgreSQL;UID=3Dtest_user;PWD=3D;DATABASE=3Dtest;SERVER=3D1= 0.55.55.1;PORT=3D5432;READONLY=3D0;PROTOCOL=3D6.4;FAKEOIDINDEX=3D0;SHOWOI= DCOLUMN=3D0;ROWVERSIONING=3D0;SHOWSYSTEMTABLES=3D0;CONNSETTINGS=3D;',=20 fDriverCompletion=3D0

DSN info:=20 DSN=3D'PostgreSQL',server=3D'10.55.55.1',port=3D'5432',dbase=3D'test',use= r=3D'test_user',passwd=3D'test_user001'

readonly=3D'0',protocol=3D'6.4',showoid=3D'0',fakeoidindex=3D'0',shows= ystable=3D'0'

conn_settings=3D''

translation_dll=3D'',translation_option=3D''

Global Options: Version=3D'06.40.0006', fetch=3D100, socket=3D4096,=20 unknown_sizes=3D0, max_varchar_size=3D254, = max_longvarchar_size=3D8190

disable_optimizer=3D1, ksqo=3D1, unique_index=3D0, = use_declarefetch=3D0

text_as_longvarchar=3D1, unknowns_as_longvarchar=3D1, = bools_as_char=3D1

extra_systable_prefixes=3D'dd_;', conn_settings=3D''

conn=3D138152060, query=3D' '

conn=3D138152060, query=3D'set DateStyle to 'ISO''

conn=3D138152060, query=3D'set geqo to 'OFF''

conn=3D138152060, query=3D'set ksqo to 'ON''

conn=3D138152060, query=3D'select oid from pg_type where = typname=3D'lo''

[ fetched 1 rows ]

[ Large Object oid =3D 22720 ]

conn=3D138152060,=20 SQLDriverConnect(out)=3D'DSN=3DPostgreSQL;DATABASE=3Dtest;SERVER=3D10.55.= 55.1;PORT=3D5432;UID=3Dtest_user;PWD=3Dtest_user001;READONLY=3D0;PROTOCOL= =3D6.4;FAKEOIDINDEX=3D0;SHOWOIDCOLUMN=3D0;ROWVERSIONING=3D0;SHOWSYSTEMTAB= LES=3D0;CONNSETTINGS=3D'

conn=3D138152060, query=3D'SELECT Config, nValue FROM MSysConf'

ERROR from backend during send_query: 'ERROR: msysconf: Table does = not=20 exist.'

STATEMENT ERROR: func=3DSC_execute, desc=3D'', errnum=3D1, = errmsg=3D'Error while=20 executing the query'

------------------------------------------------------------

hdbc=3D138152060, stmt=3D138166588, result=3D0

manual_result=3D0, prepare=3D0, internal=3D0

bindings=3D0, bindings_allocated=3D0

parameters=3D0, parameters_allocated=3D0

statement_type=3D0, statement=3D'SELECT Config, nValue FROM = MSysConf'

stmt_with_params=3D'SELECT Config, nValue FROM MSysConf'

data_at_exec=3D-1, current_exec_param=3D-1, put_data=3D0

currTuple=3D-1, current_col=3D-1, lobj_fd=3D-1

maxRows=3D0, rowset_size=3D1, keyset_size=3D0, cursor_type=3D0,=20 scroll_concurrency=3D1

cursor_name=3D'SQL_CUR083C413C'

----------------QResult Info -------------------------------

CONN ERROR: func=3DSC_execute, desc=3D'', errnum=3D110, = errmsg=3D'ERROR: msysconf:=20 Table does not exist.'

------------------------------------------------------------

henv=3D139198596, conn=3D138152060, status=3D1, num_stmts=3D16

sock=3D139198612, stmts=3D139198652, lobj_type=3D22720

---------------- Socket Info -------------------------------

socket=3D224, reverse=3D0, errornumber=3D0, errormsg=3D'(null)'

buffer_in=3D138158388, buffer_out=3D138162488

buffer_filled_in=3D42, buffer_filled_out=3D0, buffer_read_in=3D42

conn=3D138152060, query=3D'SELECT "employee"."id" FROM "employee" = '

[ fetched 0 rows ]

ERROR from backend during send_function: 'ERROR: lo_lseek: invalid = large obj=20 descriptor (0)

'

STATEMENT ERROR: func=3DSQLPutData, desc=3D'', errnum=3D1, = errmsg=3D'Couldnt open=20 large object for writing.'

------------------------------------------------------------

hdbc=3D138152060, stmt=3D138166588, result=3D0

manual_result=3D0, prepare=3D0, internal=3D0

bindings=3D0, bindings_allocated=3D0

parameters=3D139200792, parameters_allocated=3D3

statement_type=3D1, statement=3D'INSERT INTO "employee" = ("id","name","picture")=20 VALUES (?,?,?)'

stmt_with_params=3D''

data_at_exec=3D0, current_exec_param=3D2, put_data=3D1

currTuple=3D-1, current_col=3D-1, lobj_fd=3D-1

maxRows=3D0, rowset_size=3D1, keyset_size=3D0, cursor_type=3D0,=20 scroll_concurrency=3D1

cursor_name=3D''

----------------QResult Info -------------------------------

CONN ERROR: func=3DSQLPutData, desc=3D'', errnum=3D110, = errmsg=3D'ERROR: lo_lseek:=20 invalid large obj descriptor (0)

'

------------------------------------------------------------

henv=3D139198596, conn=3D138152060, status=3D1, num_stmts=3D16

sock=3D139198612, stmts=3D139198652, lobj_type=3D22720

---------------- Socket Info -------------------------------

socket=3D224, reverse=3D0, errornumber=3D0, errormsg=3D'(null)'

buffer_in=3D138158388, buffer_out=3D138162488

buffer_filled_in=3D53, buffer_filled_out=3D0, buffer_read_in=3D53

conn=3D138152060, SQLDisconnect

 
 ------=_NextPart_000_000D_01BF794B.923F6700--