Thread: inserting blob data

inserting blob data

From
"Bruce Holt"
Date:
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


Re: inserting blob data

From
Tom Lane
Date:
"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

Re: inserting blob data

From
"Dave Page"
Date:

> -----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.

Re: inserting blob data

From
Hiroshi Inoue
Date:
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/

Re: inserting blob data

From
"Bruce Holt"
Date:
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


Re: inserting blob data

From
Tom Lane
Date:
"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

Re: inserting blob data

From
"Henshall, Stuart - Design & Print"
Date:

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

Re: inserting blob data

From
"Bruce Holt"
Date:
"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


Re: inserting blob data

From
"James N. Potts"
Date:
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


Re: inserting blob data

From
"Dave Page"
Date:

> -----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.