Using ODBC and VBA to pull data from a large object
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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 C. Falkenberg
Quality Engineer
Great Lakes Works
Phone: (313) 749 - 3758
Cell: (313) 910 - 3195
From: Bret Stern <bret_stern@machinemanagement.com>
To: Adam C Falkenberg <ACFalkenberg@uss.com>,
Cc: pgsql-general@postgresql.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
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@druware.com>
To: Adam C Falkenberg <ACFalkenberg@uss.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@uss.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@machinemanagement.com>
To: Adam C Falkenberg <ACFalkenberg@uss.com>,
Cc: pgsql-general@postgresql.org
Date: 09/17/2013 10:06 AM
Subject: Re: [GENERAL] Using ODBC and VBA to pull data from a
large object
Show quoted text
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
Import Notes
Reply to msg id not found: B5FFE005-38AE-49F8-88CA-9C59A8309362@druware.com
Le mardi 17 septembre 2013 ￜ 12:25 -0400, Adam C Falkenberg a ￜcrit :
Sorry about that. Here's the driver information and some code.
Driver Name: PostgreSQL ANSI
Version: 9.02.01.00constr = "Driver={PostgreSQL ANSI}; Server=servername; Port=5432;
Database=databasename; Uid=username; Pwd=password;"
With conn
.ConnectionString = (constr)
.Open
End WithSQL = "SELECT data FROM pg_largeobject WHERE loid = " & id & " ORDER
BY pageno"
rs.Open SQL, connstream.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
You don't say where you use that recordset (Excel, Access?)
A google search will return several discussions about this problem; here
is an example :
--
Salutations, Vincent Veyron
http://gdlc.fr/logiciels
Applications de gestion des contentieux juridiques et des dossiers de sinistres assurance
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I'm using Excel. I needed to set the MAXVARCHARSIZE parameter in the
connection string to take care of my issue (MAXVARCHARSIZE=2048 for me).
That allowed the defined size of the field to equal the actual size.
Thanks everyone for your help!
Adam
From: Vincent Veyron <vv.lists@wanadoo.fr>
To: Adam C Falkenberg <ACFalkenberg@uss.com>,
Cc: pgsql-general@postgresql.org
Date: 09/19/2013 04:59 AM
Subject: Re: [GENERAL] Using ODBC and VBA to pull data from a large
object
Sent by: pgsql-general-owner@postgresql.org
Le mardi 17 septembre 2013 à 12:25 -0400, Adam C Falkenberg a écrit :
Sorry about that. Here's the driver information and some code.
Driver Name: PostgreSQL ANSI
Version: 9.02.01.00constr = "Driver={PostgreSQL ANSI}; Server=servername; Port=5432;
Database=databasename; Uid=username; Pwd=password;"
With conn
.ConnectionString = (constr)
.Open
End WithSQL = "SELECT data FROM pg_largeobject WHERE loid = " & id & " ORDER
BY pageno"
rs.Open SQL, connstream.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
You don't say where you use that recordset (Excel, Access?)
A google search will return several discussions about this problem; here
is an example :
--
Salutations, Vincent Veyron
http://gdlc.fr/logiciels
Applications de gestion des contentieux juridiques et des dossiers de
sinistres assurance
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general