Re: [ODBC] Writing BLOBS to pgsql via ODBC using VB - Mailing list pgsql-general
From | Denis Gasparin |
---|---|
Subject | Re: [ODBC] Writing BLOBS to pgsql via ODBC using VB |
Date | |
Msg-id | 5.1.0.14.0.20011023152924.00a6ac10@10.1.1.2 Whole thread Raw |
In response to | Re: [ODBC] Writing BLOBS to pgsql via ODBC using VB (Hiroshi Inoue <Inoue@tpf.co.jp>) |
List | pgsql-general |
> Are you setting the CursorLocation memeber as adUseClient ? > >regards, >Hiroshi Inoue Ok, it worked. I think that it would be great to insert that tip in the faq at the odbc.postgresql.org. Now another question: is there possible to use lo with parameterized queries (ADO command object)? I have tried but i receive a query error. It seems like the odbc driver doesn't use the lo interface but tryies to insert the record into the db as if the field was of bytea type (Sorry for the poor English...I hope it is clear what i'm saying...) At the end of the mail there is the code and the log of the odbc pgsql driver. The table is the same of the other mail: two fields, main (of type integer) and object (of type lo) Thank for your help, Denis --- DEBUG INFORMATIONS --- At the end of the mail there is the pgsql odbc driver log. Here instead there is the code: Dim cn As New ADODB.Connection Dim rs As ADODB.Recordset Dim cmd As ADODB.Command Dim chunk() As Byte Dim fd As Integer Dim flen As Long Dim chunks As Integer Dim fragment As Integer Dim main As ADODB.Parameter Dim object As ADODB.Parameter Dim i As Long With cn .ConnectionString = "dsn=pgsql_test_blob;" .Open .CursorLocation = adUseClient End With Set cmd = New ADODB.Command With cmd .CommandText = "delete from myt1" .ActiveConnection = cn .Execute End With Set cmd = Nothing Set cmd = New ADODB.Command cmd.ActiveConnection = cn cmd.CommandText = "insert into myt1(main,object) values(?,?)" cmd.CommandType = adCmdText ' Set the main parameter Set main = cmd.CreateParameter("main", adInteger, adParamInput) main.Value = 1234 Set object = cmd.CreateParameter("object", adLongVarBinary, adParamInput) fd = FreeFile Open "mydocument" For Binary Access Read As fd flen = LOF(fd) If flen = 0 Then Close MsgBox "Error while opening the file" End End If ReDim chunk(flen) Get fd, , chunk() ' Now insert into the parameter object.AppendChunk chunk() ' Execute the insert command Set rs = cmd.Execute(, Array(main, object)) cn.Close Close ------------------------- ODBC DRIVER LOG --------------------------------- conn=63909392, PGAPI_DriverConnect( in)='DSN=pgsql_test_blob;', fDriverCompletion=0 DSN info: DSN='pgsql_test_blob',server='ADDRESS',port='5432',dbase='ctest',user='myuser',passwd='mypasswd' onlyread='0',protocol='6.4',showoid='0',fakeoidindex='0',showsystable='0' conn_settings='' translation_dll='',translation_option='' Global Options: Version='07.01.0007', fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190 disable_optimizer=1, ksqo=1, unique_index=1, use_declarefetch=1 text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 extra_systable_prefixes='dd_;', conn_settings='' conn=63909392, query=' ' conn=63909392, query='set DateStyle to 'ISO'' conn=63909392, query='set geqo to 'OFF'' conn=63909392, query='set ksqo to 'ON'' conn=63909392, query='BEGIN' conn=63909392, query='declare SQL_CUR03D00B40 cursor for select oid from pg_type where typname='lo'' conn=63909392, query='fetch 100 in SQL_CUR03D00B40' [ fetched 1 rows ] [ Large Object oid = 12937145 ] conn=63909392, query='close SQL_CUR03D00B40' conn=63909392, query='END' conn=63909392, query='BEGIN' conn=63909392, query='declare SQL_CUR03D00760 cursor for select version()' conn=63909392, query='fetch 100 in SQL_CUR03D00760' [ fetched 1 rows ] [ PostgreSQL version string = 'PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC 2.96' ] [ PostgreSQL version number = '7.1' ] conn=63909392, query='close SQL_CUR03D00760' conn=63909392, query='END' conn=63909392, PGAPI_DriverConnect(out)='DSN=pgsql_test_blob;DATABASE=ctest;SERVER=10.1.2.99;PORT=5432;UID=rts;PWD=edi;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS=;FETCH=100;SOCKET=4096;UNKNOWNSIZES=0;MAXVARCHARSIZE=254;MAXLONGVARCHARSIZE=8190;DEBUG=0;COMMLOG=1;OPTIMIZER=1;KSQO=1;USEDECLAREFETCH=1;TEXTASLONGVARCHAR=1;UNKNOWNSASLONGVARCHAR=0;BOOLSASCHAR=1;PARSE=0;CANCELASFREESTMT=0;EXTRASYSTABLEPREFIXES=dd_;' conn=63909392, query='delete from myt1' conn=63909392, query='insert into myt1(main,object) values(1234,'\\320\\317\\021\\340\\241\\261\\032\\341\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\076\\000\\003\\000\\376\\377\\011\\000\\006\\000\\00..... OTHER DATA')' ERROR from backend during send_query: 'ERROR: lo_in: error in parsing "\320\317\021\340\241\261\032\341\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\076\000\003\000\376\377\011\000\006\000\000\000\000\000\000\000\000\00\002\000\000\324Y\000\000\372\000\000\000\013U\000\000\025\000\000\000\000\000\000\0 ................ OTHER DATA ........................... 00\000\000\140\046\000\000\000\000\000\000\140\046\000\000\000\000\000\000\140\046\000\000\000\000\000\000\140\046\000\000\000\000\000\000\013U\000\000\000\000\000\000\044\051\000\000 \000\000\000\000\274\037\000\000\000\0' STATEMENT ERROR: func=SC_execute, desc='', errnum=7, errmsg='Error while executing the query' ------------------------------------------------------------ hdbc=63909392, stmt=63966672, result=63972640 manual_result=0, prepare=0, internal=0 bindings=0, bindings_allocated=0 parameters=63972496, parameters_allocated=2 statement_type=1, statement='insert into myt1(main,object) values(?,?)' stmt_with_params='insert into myt1(main,object) values(1234,'\\320\\317\\021\\340\\241\\261\\032\\341\\000\\000\\000\\000\\000\\000\ ............................ OTHER DATA ........................')' data_at_exec=-1, current_exec_param=-1, put_data=0 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_CUR03D00DD0' ----------------QResult Info ------------------------------- fields=63972720, manual_tuples=0, backend_tuples=0, tupleField=0, conn=0 fetch_count=0, fcount=0, num_fields=0, cursor='(NULL)' message='(NULL)', command='(NULL)', notice='(NULL)' status=7, inTuples=0 CONN ERROR: func=SC_execute, desc='', errnum=110, errmsg='ERROR: lo_in: error in parsing "\320\317\021\340\241\261\032\341\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\076\000\003\000\376\377\011\000\006\000\000\000\000\000\000\000\000\000\000\000\001\000\000\000n\000\000\000\000 ..................... OTHER DATA ............................ 00\000\000\013U\000\000\000\000\000\000\044\051\000\000\000\000\000\000\274\037\000\000\000\0' ------------------------------------------------------------ henv=63964800, conn=63909392, status=1, num_stmts=16 sock=63964816, stmts=63964864, lobj_type=12937145 ---------------- Socket Info ------------------------------- socket=488, reverse=0, errornumber=0, errormsg='(NULL)' buffer_in=63920280, buffer_out=63924384 buffer_filled_in=3743, buffer_filled_out=0, buffer_read_in=3743 conn=63909392, PGAPI_Disconnect
pgsql-general by date: