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

Using ADO to write a blob in VBA

From: Stephan Strauss <Stephan(dot)Strauss(at)synopsys(dot)com>
To: "pgsql-odbc(at)postgresql(dot)org" <pgsql-odbc(at)postgresql(dot)org>
Subject: Using ADO to write a blob in VBA
Date: 2009-12-07 13:57:43
Message-ID: ADF9D9ACA6C7294CBE95F1733407222102F7C6B532@DE02WXMBX1.internal.synopsys.com (view raw or flat)
Thread:
Lists: pgsql-odbc
Dear mates, 

 I have a question concerning the postgresql page 

http://psqlodbc.projects.postgresql.org/howto-vblo.html

I tried the ADO (Active X Data objects) blob version using postgres server version 8.4 (postgres ODBC client 8.04.01) with a table in two variants 

	* table (main integer, object oid)
	* table (main integer, object bytea)

but got back the error message: "Type lo does not exist" when executing with

Set rs = cmd.Execute

So, either this page is outdated or I am doing something wrong: Is there a third binary type in postgresql that I should use?

Thanks a lot for any short answer. Would be very helpful, because I have to port 
a visual basic script  (  I dislike this language by the way :)   ) to work together with 
postgresql. 

Best regards and cheers,

Stephan 


Here is the way I tried it:

----------------------------------------------------8<-----------------------------------------------



Sub Try()
    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 main As ADODB.Parameter
    Dim object As ADODB.Parameter

    ' Connect to the database using ODBC
    With cn
        .ConnectionString = "dsn=xxx; pwd=yyy"
        .Open
        .CursorLocation = adUseClient
    End With
    
    ret = cn.Execute("create table newtesttable (main integer, object oid)")

    ' Here is an example if you want to issue a direct command to the database
    '
    'Set cmd = New ADODB.Command
    'With cmd
    '    .CommandText = "delete from MYTABLE"
    '    .ActiveConnection = cn
    '    .Execute
    'End With
    'Set cmd = Nothing

    '
    ' Here is an example of how insert directly into the database without using
    ' a recordset and the AddNew method
    '
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cn
    cmd.CommandText = "insert into newtesttable(main,object) values(?,?)"
    cmd.CommandType = adCmdText

    ' The main parameter
    Set main = cmd.CreateParameter("main", adInteger, adParamInput)
    main.Value = 100 '' a random integer value ''
    cmd.Parameters.Append main

    ' Open the file for reading
    fd = FreeFile
    Open "myBlobFile.txt" For Binary Access Read As fd
    flen = LOF(fd)
    If flen = 0 Then
        Close
        MsgBox "Error while opening the file"
        End
    End If

    ' The object parameter
    '
    ' The fourth parameter indicates the memory to allocate to store the object
    Set object = cmd.CreateParameter("object", _
                                         adLongVarBinary, _
                                         adParamInput, _
                                         flen + 100)
    ReDim chunk(1 To flen)
    Get fd, , chunk()

    ' Insert the object into the parameter object
    object.AppendChunk chunk()
    cmd.Parameters.Append object

    ' Now execute the command
    Set rs = cmd.Execute

    ' ... and close all
    cn.Close
    Close

    


End Sub

----------------------------------------------------8<-----------------------------------------------

Responses

pgsql-odbc by date

Next:From: Richard BroersmaDate: 2009-12-07 15:18:38
Subject: Re: Using ADO to write a blob in VBA
Previous:From: Craig RingerDate: 2009-12-05 02:28:28
Subject: Re: Hi!

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