Skip site navigation (1) Skip section navigation (2)

Re: [ODBC] Writing BLOBS to pgsql via ODBC using VB

From: Denis Gasparin <denis(at)edinet(dot)it>
To: Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp>
Cc: pgsql-odbc(at)postgresql(dot)org, Postgresql General <pgsql-general(at)postgresql(dot)org>
Subject: Re: [ODBC] Writing BLOBS to pgsql via ODBC using VB
Date: 2001-10-23 13:52:56
Message-ID: 5.1.0.14.0.20011023152924.00a6ac10@10.1.1.2 (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-odbc
 > 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





In response to

Responses

pgsql-odbc by date

Next:From: Hiroshi InoueDate: 2001-10-24 06:05:54
Subject: Re: [ODBC] Writing BLOBS to pgsql via ODBC using VB
Previous:From: Mika MäntyläDate: 2001-10-23 08:40:35
Subject: SqlGetData problems

pgsql-general by date

Next:From: Masaru SugawaraDate: 2001-10-23 14:12:07
Subject: Re: Sequence problems during restart
Previous:From: Thomas LockhartDate: 2001-10-23 13:52:18
Subject: Re: [GENERAL] To Postgres Devs : Wouldn't changing the

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group