Re: Using ADO to write a blob in VBA - Mailing list pgsql-odbc
From | Sufficool, Stanley |
---|---|
Subject | Re: Using ADO to write a blob in VBA |
Date | |
Msg-id | C2F174F99918D54CA2A96E57C5079B6F01827E2C@sbc-exmsg2.sbcounty.gov Whole thread Raw |
In response to | Using ADO to write a blob in VBA (Stephan Strauss <Stephan.Strauss@synopsys.com>) |
List | pgsql-odbc |
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 >
pgsql-odbc by date: