Re: Using ODBC and VBA to pull data from a large object

From: Adam C Falkenberg <ACFalkenberg(at)uss(dot)com>
To: Andrew Satori <dru(at)druware(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Using ODBC and VBA to pull data from a large object
Date: 2013-09-17 16:25:18
Message-ID: OF7731716E.E5581F65-ON85257BE9.0059397D-85257BE9.005A33CA@notes.uss.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Sorry about that. Here's the driver information and some code. Thanks.

Driver Name: PostgreSQL ANSI
Version: 9.02.01.00

constr = "Driver={PostgreSQL ANSI}; Server=servername; Port=5432;
Database=databasename; Uid=username; Pwd=password;"
With conn
.ConnectionString = (constr)
.Open
End With

SQL = "SELECT data FROM pg_largeobject WHERE loid = " & id & " ORDER BY
pageno"
rs.Open SQL, conn

stream.Type = adTypeBinary
stream.Open

' Loop through the recordset and write the binary data to the stream
While Not rs.EOF
stream.Write rs.Fields("data").Value
rs.MoveNext
Wend

Adam

From: Andrew Satori <dru(at)druware(dot)com>
To: Adam C Falkenberg <ACFalkenberg(at)uss(dot)com>,
Date: 09/17/2013 12:02 PM
Subject: Re: [GENERAL] Using ODBC and VBA to pull data from a large
object

You don't say with which driver.

ODBC can be a bit twitchy with data types, and I have seen several drivers
fail when they attempt to read the .Value, some of the driver don't pass
through the adTypeBinarry and allocate a MAX_LENGTH string of 255 for the
read buffer. I haven't tested the current driver from pg.org, but when I
did a few months ago, it correctly handled the .Type field and allocated
the length appropriately.

Some version information and source would make this far easier to resolve.

On Sep 17, 2013, at 11:51 AM, Adam C Falkenberg <ACFalkenberg(at)uss(dot)com>
wrote:

> Thanks for the response. The example you sent is what I tried to follow
when I originally worked on this. It works great for the first 255 bytes,
but after that it returns 0's. Is there any way to get all of the data in
a large object returned to a recordset (not just the first 255 bytes)?
Thanks again.
>
> Adam
>
>
>
> From: Bret Stern <bret_stern(at)machinemanagement(dot)com>
> To: Adam C Falkenberg <ACFalkenberg(at)uss(dot)com>,
> Cc: pgsql-general(at)postgresql(dot)org
> Date: 09/17/2013 10:06 AM
> Subject: Re: [GENERAL] Using ODBC and VBA to pull data from a
large object
>
>
>
> On Tue, 2013-09-17 at 08:32 -0400, Adam C Falkenberg wrote:
> > Good Morning,
> >
> > I had a question about using ODBC with large objects. When I query
> > the pg_largeobject table and return data to an ADO recordset, I can
> > only get the first 255 bytes even though the record contains 2048
> > bytes of data (all the bytes after the first 255 show as 0). When I
> > checked the type of the record, it was a VarBinary. Is there a way to
> > have all of the data returned to the recordset? Thanks for any help.
> >
> > Adam
>
>
> Microsofts sample
> http://support.microsoft.com/kb/258038
>
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Igor Neyman 2013-09-17 16:44:52 Re: How to compare the results of two queries?
Previous Message Lonni J Friedman 2013-09-17 16:19:29 upgrade from 9.2.x to 9.3 causes significant performance degradation