Thread: inserting blob data
Hello. I am new to PostgreSQL (running on Red Hat 8.0) and the ODBC driver for Windows (version 7.0.2.003). I need to store binary data (up to 10 MB) in a table. I have tried to insert my data, and have been successful up to 8kb of data (which is my block size). How can I store data that exceeds my block size limitation? Thanks, Bruce Holt _________________________________________________________________ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
"Bruce Holt" <bj_holt@hotmail.com> writes:
> I am new to PostgreSQL (running on Red Hat 8.0) and the ODBC driver for
> Windows (version 7.0.2.003). I need to store binary data (up to 10 MB) in
> a table. I have tried to insert my data, and have been successful up to 8kb
> of data (which is my block size). How can I store data that exceeds my
> block size limitation?
Hmm, the 8K limit has been gone for a long time. You didn't mention
your Postgres server's version, but I'm betting you need to update
either your server or your ODBC driver. Possibly both.
regards, tom lane
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: 25 November 2002 18:42 > To: Bruce Holt > Cc: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] inserting blob data > > > "Bruce Holt" <bj_holt@hotmail.com> writes: > > I am new to PostgreSQL (running on Red Hat 8.0) and the > ODBC driver for > > Windows (version 7.0.2.003). I need to store binary data > (up to 10 MB) in > > a table. I have tried to insert my data, and have been > successful up > > to 8kb > > of data (which is my block size). How can I store data > that exceeds my > > block size limitation? > > Hmm, the 8K limit has been gone for a long time. You didn't > mention your Postgres server's version, but I'm betting you > need to update either your server or your ODBC driver. Possibly both. That version of the driver is recent - there has only been one update since and most of those changes were to make it thread safe. Regards, Dave.
Bruce Holt wrote:
>
> Hello.
>
> I am new to PostgreSQL (running on Red Hat 8.0) and the ODBC driver
> for Windows (version 7.0.2.003). I need to store binary data (up
> to 10 MB) in a table.
What is your target PG type in the table ?
> I have tried to insert my data, and have been
> successful up to 8kb of data (which is my block size).
What do you mean by *block size* ?
regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/
grouplink=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
20020903 (Red Hat Linux 8.0 3.2-7)
(1 row)
Thanks,
Bruce Holt
From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Bruce Holt" <bj_holt@hotmail.com>
CC: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] inserting blob data
Date: Mon, 25 Nov 2002 13:41:35 -0500
"Bruce Holt" <bj_holt@hotmail.com> writes:
> I am new to PostgreSQL (running on Red Hat 8.0) and the ODBC driver for
> Windows (version 7.0.2.003). I need to store binary data (up to 10 MB)
in
> a table. I have tried to insert my data, and have been successful up to
8kb
> of data (which is my block size). How can I store data that exceeds my
> block size limitation?
Hmm, the 8K limit has been gone for a long time. You didn't mention
your Postgres server's version, but I'm betting you need to update
either your server or your ODBC driver. Possibly both.
regards, tom lane
_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
"Bruce Holt" <bj_holt@hotmail.com> writes:
>> Hmm, the 8K limit has been gone for a long time. You didn't mention
>> your Postgres server's version, but I'm betting you need to update
>> either your server or your ODBC driver. Possibly both.
> PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
And your ODBC driver was up to date too, right? So much for that
theory. How exactly are you trying to store the blob data? (A code
snippet would be an appropriate answer, probably.)
regards, tom lane
Tom Lane wrote:
> "Bruce Holt" <bj_holt@hotmail.com> writes:
>>> Hmm, the 8K limit has been gone for a long time. You didn't mention
>>> your Postgres server's version, but I'm betting you need to update
>>> either your server or your ODBC driver. Possibly both.
>
>> PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
>
> And your ODBC driver was up to date too, right? So much for that
> theory. How exactly are you trying to store the blob data? (A code
> snippet would be an appropriate answer, probably.)
>
> regards, tom lane
>
Ensure that max LongVarchar is suitably high.
hth,
- Stuart
"Bruce Holt" <bj_holt@hotmail.com> writes: >> Hmm, the 8K limit has been gone for a long time. You didn't mention >> your Postgres server's version, but I'm betting you need to update >> either your server or your ODBC driver. Possibly both. > PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 >And your ODBC driver was up to date too, right? So much for that >theory. How exactly are you trying to store the blob data? (A code >snippet would be an appropriate answer, probably.) I am using the ADO for VB example found at: http://odbc.postgresql.org/psqlodbc.php?DocID=howto-vblo I have tried three table structures as well: # create table MYTABLE (main integer, object bytea); # create table MYTABLE (main integer, object oid); # create table MYTABLE (main integer, object text); I can insert the data correctly, and save it to the server using the # select lo_export(#####, '/mypath/myfile'); command. I just can't retrieve the blob data via ODBC. Thanks, Bruce Holt _________________________________________________________________ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
I just wanted to chime in that I'm having the same problem, with basically the same config. In fact, I just joined this mailing list because of it. -Jim Potts ----- Original Message ----- From: "Bruce Holt" <bj_holt@hotmail.com> To: <tgl@sss.pgh.pa.us> Cc: <pgsql-odbc@postgresql.org> Sent: Wednesday, November 27, 2002 2:48 PM Subject: Re: [ODBC] inserting blob data > "Bruce Holt" <bj_holt@hotmail.com> writes: > >> Hmm, the 8K limit has been gone for a long time. You didn't mention > >> your Postgres server's version, but I'm betting you need to update > >> either your server or your ODBC driver. Possibly both. > > > PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 > > >And your ODBC driver was up to date too, right? So much for that > >theory. How exactly are you trying to store the blob data? (A code > >snippet would be an appropriate answer, probably.) > > I am using the ADO for VB example found at: > http://odbc.postgresql.org/psqlodbc.php?DocID=howto-vblo > > > I have tried three table structures as well: > > # create table MYTABLE (main integer, object bytea); > # create table MYTABLE (main integer, object oid); > # create table MYTABLE (main integer, object text); > > I can insert the data correctly, and save it to the server using the > # select lo_export(#####, '/mypath/myfile'); > command. I just can't retrieve the blob data via ODBC. > > Thanks, > Bruce Holt
> -----Original Message----- > From: Bruce Holt [mailto:bj_holt@hotmail.com] > Sent: 27 November 2002 20:48 > To: tgl@sss.pgh.pa.us > Cc: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] inserting blob data > > > "Bruce Holt" <bj_holt@hotmail.com> writes: > >> Hmm, the 8K limit has been gone for a long time. You > didn't mention >> your Postgres server's version, but I'm > betting you need to update >> either your server or your > ODBC driver. Possibly both. > > > PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC gcc > (GCC) 3.2 > > >And your ODBC driver was up to date too, right? So much for that > >theory. How exactly are you trying to store the blob data? (A code > >snippet would be an appropriate answer, probably.) > > I am using the ADO for VB example found at: > http://odbc.postgresql.org/psqlodbc.php?> DocID=howto-vblo > > > I > have tried three table structures as > well: > > # create table MYTABLE (main integer, object bytea); > # create table MYTABLE (main integer, object oid); > # create table MYTABLE (main integer, object text); > > I can insert the data correctly, and save it to the server > using the # select lo_export(#####, '/mypath/myfile'); > command. I just can't retrieve the blob data via ODBC. There is a setting in the driver called MaxLongVarChar which defaults to 8190 bytes. You are almost certainly hitting this when you use the third table definition. The second will only store an 8(?) byte integer. Dunno about the first, though I've heard bytea should work. The example you quote requires a table that looks like: CREATE TABLE MYTABLE(main integer, object lo); If you don't have a lo datatype, this will help: http://gborg.postgresql.org/project/psqlodbc/faq/faq.php?faq_id=52 Regards, Dave.