Using ADO to write a blob in VBA

Dear mates,=20

I have a question concerning the postgresql page=20

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=20

* 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 =3D 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=20
a visual basic script ( I dislike this language by the way :) ) to work=
together with=20
postgresql.=20

Best regards and cheers,

Stephan=20


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 =3D "dsn=3Dxxx; pwd=3Dyyy"
.Open
.CursorLocation =3D adUseClient
End With

ret =3D cn.Execute("create table newtesttable (main integer, object oid=
)")

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

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

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

' Open the file for reading
fd =3D FreeFile
Open "myBlobFile.txt" For Binary Access Read As fd
flen =3D LOF(fd)
If flen =3D 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 ob=
ject
Set object =3D 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 =3D cmd.Execute

' ... and close all
cn.Close
Close




End Sub

----------------------------------------------------8<---------------------=
--------------------------

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Using ADO to write a blob in VBA

On Mon, Dec 7, 2009 at 5:57 AM, Stephan Strauss
wrote:

> http://psqlodbc.projects.postgresql.org/howto-vblo.html
> but got back the error message: "Type lo does not exist" when executing with

The lo type is not part of the standard postgresql install. It is an
extension that you can add to your postgresql server:

http://www.postgresql.org/docs/8.4/interactive/lo.html

Once you've installed type Large Objects data type it should work.


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

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=20
> [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=20
>=20
>=20
> Dear mates,=20
>=20
> I have a question concerning the postgresql page=20
>=20
> http://psqlodbc.projects.postgresql.org/howto-vblo.html
>=20
> I tried the ADO (Active X Data objects) blob version using=20
> postgres server version 8.4 (postgres ODBC client 8.04.01)=20
> with a table in two variants=20
>=20
> * table (main integer, object oid)
> * table (main integer, object bytea)
>=20
> but got back the error message: "Type lo does not exist" when=20
> executing with
>=20
> Set rs =3D cmd.Execute
>=20
> So, either this page is outdated or I am doing something=20
> wrong: Is there a third binary type in postgresql that I should use?
>=20
> Thanks a lot for any short answer. Would be very helpful,=20
> because I have to port=20
> a visual basic script ( I dislike this language by the way=20
> :) ) to work together with=20
> postgresql.=20
>=20
> Best regards and cheers,
>=20
> Stephan=20
>=20
>=20
> Here is the way I tried it:
>=20
> ----------------------------------------------------8<--------
---------------------------------------
>=20
>=20
>=20
> 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
>=20
> ' Connect to the database using ODBC
> With cn
> .ConnectionString =3D "dsn=3Dxxx; pwd=3Dyyy"
> .Open
> .CursorLocation =3D adUseClient
> End With
> =20
> ret =3D cn.Execute("create table newtesttable (main=20
> integer, object oid)")
>=20
> ' Here is an example if you want to issue a direct=20
> command to the database
> '
> 'Set cmd =3D New ADODB.Command
> 'With cmd
> ' .CommandText =3D "delete from MYTABLE"
> ' .ActiveConnection =3D cn
> ' .Execute
> 'End With
> 'Set cmd =3D Nothing
>=20
> '
> ' Here is an example of how insert directly into the=20
> database without using
> ' a recordset and the AddNew method
> '
> Set cmd =3D New ADODB.Command
> cmd.ActiveConnection =3D cn
> cmd.CommandText =3D "insert into newtesttable(main,object)=20
> values(?,?)"
> cmd.CommandType =3D adCmdText
>=20
> ' The main parameter
> Set main =3D cmd.CreateParameter("main", adInteger, adParamInput)
> main.Value =3D 100 '' a random integer value ''
> cmd.Parameters.Append main
>=20
> ' Open the file for reading
> fd =3D FreeFile
> Open "myBlobFile.txt" For Binary Access Read As fd
> flen =3D LOF(fd)
> If flen =3D 0 Then
> Close
> MsgBox "Error while opening the file"
> End
> End If
>=20
> ' The object parameter
> '
> ' The fourth parameter indicates the memory to allocate=20
> to store the object
> Set object =3D cmd.CreateParameter("object", _
> adLongVarBinary, _
> adParamInput, _
> flen + 100)
> ReDim chunk(1 To flen)
> Get fd, , chunk()
>=20
> ' Insert the object into the parameter object
> object.AppendChunk chunk()
> cmd.Parameters.Append object
>=20
> ' Now execute the command
> Set rs =3D cmd.Execute
>=20
> ' ... and close all
> cn.Close
> Close
>=20
> =20
>=20
>=20
> End Sub
>=20
> ----------------------------------------------------8<--------
---------------------------------------
>=20
> --=20
> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
> To make changes to your subscription:=20
> http://www.postgresql.org/mailpref/pgsql-odbc
>=20

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc