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(at)postgresql(dot)org
> [mailto:pgsql-odbc-owner(at)postgresql(dot)org] On Behalf Of Stephan Strauss
> Sent: Monday, December 07, 2009 5:58 AM
> To: pgsql-odbc(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-odbc
>