Re: Writing BLOBS to pgsql via ODBC using VB

[Denis Gasparin <denis(at)edinet(dot)it>]

I have tried the same thing also with ADO and the error is the same. Here 
are some debug informations from the log of the odbc driver and the queries 
issued by the postgres backend.

Why the queries needed to insert the record are not present? (nor in ODBC 
neither in backend log)

If anyone have successfully inserted a record using vb, can tell me how he 
have done? Apparently there is no way...

Denis

PS: If i use the "Insert OLE object" in MS Access, the insertion works... 
Obviously in my application i have not to use Access...

---- ODBC LOG ----
                disable_optimizer=1, ksqo=1, unique_index=1, 
use_declarefetch=0
                text_as_longvarchar=1, unknowns_as_longvarchar=0, 
bools_as_char=1
                extra_systable_prefixes='dd_;', conn_settings=''
conn=184978392, query=' '
conn=184978392, query='set DateStyle to 'ISO''
conn=184978392, query='set geqo to 'OFF''
conn=184978392, query='set ksqo to 'ON''
conn=184978392, query='select oid from pg_type where typname='lo''
    [ fetched 1 rows ]
    [ Large Object oid = 12937145 ]
conn=184978392, query='select version()'
    [ fetched 1 rows ]
    [ PostgreSQL version string = 'PostgreSQL 7.1.3 on i686-pc-linux-gnu, 
compiled by GCC 2.96' ]
    [ PostgreSQL version number = '7.1' ]
conn=184978392, 
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=0;TEXTASLONGVARCHAR=1;UNKNOWNSASLONGVARCHAR=0;BOOLSASCHAR=1;PARSE=0;CANCELASFREESTMT=0;EXTRASYSTABLEPREFIXES=dd_;'
conn=184978392, query='delete from mytable'
conn=184978392, query='select * from mytable'
    [ fetched 0 rows ]
conn=184978392, PGAPI_Disconnect

---- BACKEND LOG ----
Oct 22 12:46:56 clio postgres[17442]: [1] DEBUG:  connection: host=xxxxxxxx 
user=xxx database=test
Oct 22 12:46:56 clio postgres[17442]: [2] DEBUG:  query: set DateStyle to 'ISO'
Oct 22 12:46:56 clio postgres[17442]: [3] DEBUG:  ProcessUtility: set 
DateStyle to 'ISO'
Oct 22 12:46:56 clio postgres[17442]: [4] DEBUG:  query: set geqo to 'OFF'
Oct 22 12:46:56 clio postgres[17442]: [5] DEBUG:  ProcessUtility: set geqo 
to 'OFF'
Oct 22 12:46:56 clio postgres[17442]: [6] DEBUG:  query: set ksqo to 'ON'
Oct 22 12:46:56 clio postgres[17442]: [7] DEBUG:  ProcessUtility: set ksqo 
to 'ON'
Oct 22 12:46:56 clio postgres[17442]: [8] DEBUG:  query: select oid from 
pg_type where typname='lo'
Oct 22 12:46:56 clio postgres[17442]: [9] DEBUG:  query: select version()
Oct 22 12:46:56 clio postgres[17442]: [10] DEBUG:  query: delete from myt
Oct 22 12:46:56 clio postgres[17442]: [11] DEBUG:  query: select * from myt



At 19/10/01 18.20, you wrote:
> I am having problems while trying to insert a BLOB into a pgsql table 
> using VB.
> The error i get is:
>         S1090: [Microsoft][ODBC Driver Manager] Invalid String or buffer 
> length
>
> I am using postgresql 7.1.3, Postgresql ODBC driver 7.01.00.07 and RDO 2.0
>
> Have you any idea why i get this error?
>
>
> Additional infos:
>
> Here is the table definition:
> CREATE TABLE MYTABLE (
>         MAIN INTEGER NOT NULL PRIMARY KEY,
>         OBJECT LO
> );
>
> Here is the code:
>
> Private Sub Command1_Click()
>
> Dim rdoConn As New rdoConnection, rdoRS As rdoResultset, SQL As String
> Dim numfile As Integer
> Dim filesize As Long
> Dim query As rdoQuery
> Dim chunk() As Byte
>
> ' DB Connection
> With rdoConn
>         .Connect = "uid=myuser;pwd=mypwd;DSN=BLOB_TEST;"
>         .CursorDriver = rdUseOdbc
>         .LoginTimeout = 3
>         .EstablishConnection rdDriverNoPrompt
> End With
> Set query = rdoConn.CreateQuery("MyQuery", "select * from mytable")
> Set rdoRS = query.OpenResultset(rdOpenKeyset, rdConcurRowVer)
> If rdoRS Is Nothing Or rdoRS.Updatable = False Then
>         MsgBox "Cannot create rdoResultSet"
>         End
> End If
>
> ' Now insert the record in the table
> If rdoRS.EOF Then
>         ' Insert a new record
>         rdoRS.AddNew
>
>         ' The MAIN field
>         rdoRS!MAIN = 2678
>
>         ' Get a file number
>         numfile = FreeFile
>
>         ' Open the file i've to insert in the table
>         Open "c:\myfile.doc" For Binary Access Read As #numfile
>         filesize = LOF(numfile)
>         If filesize = 0 Then
>                 MsgBox "Unable to open the file"
>                 Close
>                 End
>         End If
>
>         ReDim chunk(filesize)
>
>         ' Read the file into the array
>         Get #numfile, , chunk()
>
>         ' Try to insert
>         rdoRS!OBJECT.AppendChunk chunk()
>
>         ' In the update statement i get the error
>         rdoRS.Update
>         Close #numfile
>         End If
> End Sub
>
>
> Regards,
>
>
> Doct. Eng. Denis 
> Gasparin                                             denis(at)edistar(dot)com
> ---------------------------------------------------------------------------------------
> Programmer & System 
> Administrator                                http://www.edistar.com
> ---------------------------------------------------------------------------------------
>                              Well alas we've seen it all before
>                               Knights in armour, days of yore
>                          The same old fears and the same old crimes
>                            We haven't changed since ancient times
>
>                               -- Iron Hand -- Dire Straits --
> ---------------------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org