Re: Using ADO to write a blob in VBA

Uncheck the "bytea as LO" in your ODBC connection options. Datasource,
page 2.

Not sure what the conn string option name is though.


> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Stephan Strauss
> Sent: Monday, December 07, 2009 5:58 AM
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] Using ADO to write a blob in VBA
>
>
> 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<--------
---------------------------------------
>
> --
> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-odbc
>