Re: ByteA for binary data?

I'm having a problem with the bytea datatype. I'm trying to store image
data inside the field in a table, and php seems to only be sending a small
portion of the image into the database.

I'm not sure how I should approach storing image data inside of Postgres.

I really need to improve the management of my images.

Has anyone else done this without the benefit of the LO support?

I've read the available documentation on LO and bytea, and both seem to
have troubles, is this something that will be fixed in a future
release? I'm currently running 7.2.

-Bob
ccrider(at)whiterose(dot)net
Systems Administrator for whiterose.net
http://www.whiterose.net

Robert Myers <ccrider(at)whiterose(dot)net> writes:

> I've read the available documentation on LO and bytea, and both seem to
> have troubles, is this something that will be fixed in a future
> release? I'm currently running 7.2.

I've been using LOs with great success since 7.1--there are no current
problems with it, though the API is a little convoluted. I prefer
them to bytea fields becauser you're not forced to load the whole
thing into memory either on the server or client side (I work with
multi-megabyte data files). I use Java on the client.

Have you read the bytea docs on proper quoting? Is your PHP install
up to date? Older versions would truncate queries at 8k I think...

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

On Sun, 24 Feb 2002, Robert Myers wrote:

> I'm having a problem with the bytea datatype. I'm trying to store image
> data inside the field in a table, and php seems to only be sending a small
> portion of the image into the database.
>
> I'm not sure how I should approach storing image data inside of Postgres.
>
> I really need to improve the management of my images.
>
> Has anyone else done this without the benefit of the LO support?
>
> I've read the available documentation on LO and bytea, and both seem to
> have troubles, is this something that will be fixed in a future
> release? I'm currently running 7.2.

to use bytea with php, encode the image data with escByteA() below before
storing it in your db. then when you retrieve the data, use php's
stripcslashes(). i've tested this on various image data and they all work
just fine.

function escByteA($binData) {
/**
* \134 = 92 = backslash, \000 = 00 = NULL, \047 = 39 = Single Quote
*
* str_replace() replaces the searches array in order.
* Therefore, we must
* process the 'backslash' character first. If we process it last, it'll
* replace all the escaped backslashes from the other searches that came
* before. tomATminnesota.com
*/
$search = array(chr(92), chr(0), chr(39));
$replace = array('\\\134', '\\\000', '\\\047');
$binData = str_replace($search, $replace, $binData);
return $binData;
}

I did this with the CVS version of PHP, v 4.2.0 dev.

I downloaded it for the function pg_escape_bytea, still no go, I can't get
the data into the field.

-Bob
ccrider(at)whiterose(dot)net
Systems Administrator for whiterose.net
http://www.whiterose.net

On Sun, 24 Feb 2002, Thomas T. Thai wrote:

> On Sun, 24 Feb 2002, Robert Myers wrote:
>
> > I'm having a problem with the bytea datatype. I'm trying to store image
> > data inside the field in a table, and php seems to only be sending a small
> > portion of the image into the database.
> >
> > I'm not sure how I should approach storing image data inside of Postgres.
> >
> > I really need to improve the management of my images.
> >
> > Has anyone else done this without the benefit of the LO support?
> >
> > I've read the available documentation on LO and bytea, and both seem to
> > have troubles, is this something that will be fixed in a future
> > release? I'm currently running 7.2.
>
> to use bytea with php, encode the image data with escByteA() below before
> storing it in your db. then when you retrieve the data, use php's
> stripcslashes(). i've tested this on various image data and they all work
> just fine.
>
> function escByteA($binData) {
> /**
> * \134 = 92 = backslash, \000 = 00 = NULL, \047 = 39 = Single Quote
> *
> * str_replace() replaces the searches array in order.
> * Therefore, we must
> * process the 'backslash' character first. If we process it last, it'll
> * replace all the escaped backslashes from the other searches that came
> * before. tomATminnesota.com
> */
> $search = array(chr(92), chr(0), chr(39));
> $replace = array('\\\134', '\\\000', '\\\047');
> $binData = str_replace($search, $replace, $binData);
> return $binData;
> }
>

On Sun, 24 Feb 2002, Robert Myers wrote:

> I did this with the CVS version of PHP, v 4.2.0 dev.
>
> I downloaded it for the function pg_escape_bytea, still no go, I can't get
> the data into the field.

did you try as i said below? i've tested it on php 4.0.6 - 4.1.0. i've not
used pg_escape_bytea so i can't comment on it.

> > > I'm having a problem with the bytea datatype. I'm trying to store image
> > > data inside the field in a table, and php seems to only be sending a small
> > > portion of the image into the database.
[...]
> > to use bytea with php, encode the image data with escByteA() below before
> > storing it in your db. then when you retrieve the data, use php's
> > stripcslashes(). i've tested this on various image data and they all work
> > just fine.
> >
> > function escByteA($binData) {
> > /**
> > * \134 = 92 = backslash, \000 = 00 = NULL, \047 = 39 = Single Quote
> > *
> > * str_replace() replaces the searches array in order.
> > * Therefore, we must
> > * process the 'backslash' character first. If we process it last, it'll
> > * replace all the escaped backslashes from the other searches that came
> > * before. tomATminnesota.com
> > */
> > $search = array(chr(92), chr(0), chr(39));
> > $replace = array('\\\134', '\\\000', '\\\047');
> > $binData = str_replace($search, $replace, $binData);
> > return $binData;
> > }

My biggest issue with the LO's is that I can't seem to find anything on
how to list _all_ of my LO's.

If I lose the OID, I've got a LO out there with no pointer to it.

Am I missing something on that?

On the 8K limit, I'm not even seeing 8K, I'm seeing about 15 or 16 chars,
not really enough. I'm using the latest PHP, 4.1.1, which didn't work, so
I figured I'd hit 4.2.0 in the CVS tree, no joy there either.

-Bob
ccrider(at)whiterose(dot)net
Systems Administrator for whiterose.net
http://www.whiterose.net

On 24 Feb 2002, Doug McNaught wrote:

> Robert Myers <ccrider(at)whiterose(dot)net> writes:
>
> > I've read the available documentation on LO and bytea, and both seem to
> > have troubles, is this something that will be fixed in a future
> > release? I'm currently running 7.2.
>
> I've been using LOs with great success since 7.1--there are no current
> problems with it, though the API is a little convoluted. I prefer
> them to bytea fields becauser you're not forced to load the whole
> thing into memory either on the server or client side (I work with
> multi-megabyte data files). I use Java on the client.
>
> Have you read the bytea docs on proper quoting? Is your PHP install
> up to date? Older versions would truncate queries at 8k I think...
>
> -Doug
> --
> Let us cross over the river, and rest under the shade of the trees.
> --T. J. Jackson, 1863
>

On Sun, 24 Feb 2002, Robert Myers wrote:

> My biggest issue with the LO's is that I can't seem to find anything on
> how to list _all_ of my LO's.
>
> If I lose the OID, I've got a LO out there with no pointer to it.
>
> Am I missing something on that?
>
> On the 8K limit, I'm not even seeing 8K, I'm seeing about 15 or 16 chars,
> not really enough. I'm using the latest PHP, 4.1.1, which didn't work, so
> I figured I'd hit 4.2.0 in the CVS tree, no joy there either.

sounds like an binary escape problem to me. see my prior email for the
solution with bytea.

Robert Myers <ccrider(at)whiterose(dot)net> writes:

> My biggest issue with the LO's is that I can't seem to find anything on
> how to list _all_ of my LO's.

SELECT DISTINCT loid FROM pg_largeobject;

> If I lose the OID, I've got a LO out there with no pointer to it.

There's a utility in contrib/ called vacuum_lo that can help with
that. You can also put a trigger on your referring table to make sure
LOs get deleted (that's what I do).

> Am I missing something on that?

As I said, the API is a little convoluted, but it's not hard to use
and works well.

> On the 8K limit, I'm not even seeing 8K, I'm seeing about 15 or 16 chars,
> not really enough. I'm using the latest PHP, 4.1.1, which didn't work, so
> I figured I'd hit 4.2.0 in the CVS tree, no joy there either.

I'm guessing you're not quoting the bytea strings properly, but I've
not used PHP or bytea myself so I can't really help.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

Yes, this is helpful.

This _really_ should be in the docs somewhere, I read and read, and
thought I had some of it figured out, but I didn't. :)

Thanks again.

-Bob
ccrider(at)whiterose(dot)net
Systems Administrator for whiterose.net
http://www.whiterose.net

On 24 Feb 2002, Doug McNaught wrote:

> Robert Myers <ccrider(at)whiterose(dot)net> writes:
>
> > My biggest issue with the LO's is that I can't seem to find anything on
> > how to list _all_ of my LO's.
>
> SELECT DISTINCT loid FROM pg_largeobject;
>
> > If I lose the OID, I've got a LO out there with no pointer to it.
>
> There's a utility in contrib/ called vacuum_lo that can help with
> that. You can also put a trigger on your referring table to make sure
> LOs get deleted (that's what I do).
>
> > Am I missing something on that?
>
> As I said, the API is a little convoluted, but it's not hard to use
> and works well.
>
> > On the 8K limit, I'm not even seeing 8K, I'm seeing about 15 or 16 chars,
> > not really enough. I'm using the latest PHP, 4.1.1, which didn't work, so
> > I figured I'd hit 4.2.0 in the CVS tree, no joy there either.
>
> I'm guessing you're not quoting the bytea strings properly, but I've
> not used PHP or bytea myself so I can't really help.
>
> -Doug
> --
> Let us cross over the river, and rest under the shade of the trees.
> --T. J. Jackson, 1863
>

Yes, if I use your function it works. NP.

The problem I have now, is what do I have to do special to get the data
back out in proper format? Or does it come out in useable form?

-Bob
ccrider(at)whiterose(dot)net
Systems Administrator for whiterose.net
http://www.whiterose.net

On Sun, 24 Feb 2002, Thomas T. Thai wrote:

> On Sun, 24 Feb 2002, Robert Myers wrote:
>
> > I did this with the CVS version of PHP, v 4.2.0 dev.
> >
> > I downloaded it for the function pg_escape_bytea, still no go, I can't get
> > the data into the field.
>
> did you try as i said below? i've tested it on php 4.0.6 - 4.1.0. i've not
> used pg_escape_bytea so i can't comment on it.
>
> > > > I'm having a problem with the bytea datatype. I'm trying to store image
> > > > data inside the field in a table, and php seems to only be sending a small
> > > > portion of the image into the database.
> [...]
> > > to use bytea with php, encode the image data with escByteA() below before
> > > storing it in your db. then when you retrieve the data, use php's
> > > stripcslashes(). i've tested this on various image data and they all work
> > > just fine.
> > >
> > > function escByteA($binData) {
> > > /**
> > > * \134 = 92 = backslash, \000 = 00 = NULL, \047 = 39 = Single Quote
> > > *
> > > * str_replace() replaces the searches array in order.
> > > * Therefore, we must
> > > * process the 'backslash' character first. If we process it last, it'll
> > > * replace all the escaped backslashes from the other searches that came
> > > * before. tomATminnesota.com
> > > */
> > > $search = array(chr(92), chr(0), chr(39));
> > > $replace = array('\\\134', '\\\000', '\\\047');
> > > $binData = str_replace($search, $replace, $binData);
> > > return $binData;
> > > }
>
>
>

On Sun, 24 Feb 2002, Robert Myers wrote:

> Yes, if I use your function it works. NP.
>
> The problem I have now, is what do I have to do special to get the data
> back out in proper format? Or does it come out in useable form?

please re-read my original message included below. it stated that you need
to use PHP function stripcslashes() on the data from the db before you can
use it. all of this was said in my ORIGINAL email to you. please also note
that 'c' in that function. that's NOT the usual stripslashes();

> On Sun, 24 Feb 2002, Thomas T. Thai wrote:
>
> > On Sun, 24 Feb 2002, Robert Myers wrote:
> >
> > > I did this with the CVS version of PHP, v 4.2.0 dev.
> > >
> > > I downloaded it for the function pg_escape_bytea, still no go, I can't get
> > > the data into the field.
> >
> > did you try as i said below? i've tested it on php 4.0.6 - 4.1.0. i've not
> > used pg_escape_bytea so i can't comment on it.
> >
> > > > > I'm having a problem with the bytea datatype. I'm trying to store image
> > > > > data inside the field in a table, and php seems to only be sending a small
> > > > > portion of the image into the database.
> > [...]
> > > > to use bytea with php, encode the image data with escByteA() below before
> > > > storing it in your db. then when you retrieve the data, use php's
> > > > stripcslashes(). i've tested this on various image data and they all work
> > > > just fine.
> > > >
> > > > function escByteA($binData) {
> > > > /**
> > > > * \134 = 92 = backslash, \000 = 00 = NULL, \047 = 39 = Single Quote
> > > > *
> > > > * str_replace() replaces the searches array in order.
> > > > * Therefore, we must
> > > > * process the 'backslash' character first. If we process it last, it'll
> > > > * replace all the escaped backslashes from the other searches that came
> > > > * before. tomATminnesota.com
> > > > */
> > > > $search = array(chr(92), chr(0), chr(39));
> > > > $replace = array('\\\134', '\\\000', '\\\047');
> > > > $binData = str_replace($search, $replace, $binData);
> > > > return $binData;
> > > > }
> >
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

---
Thomas T. Thai | Minnesota.com | tom(at)minnesota(dot)com | 612.220.6220
Visit http://www.minnesota.com/

Robert Myers wrote:
> Yes, if I use your function it works. NP.
>
> The problem I have now, is what do I have to do special to get the data
> back out in proper format? Or does it come out in useable form?
>
>
> -Bob

Thomas mentioned it earlier in the thread: use stripcslashes().

Joe

You were right. I had blown off your intial email becuase I thought that
the pg_escape_bytea was the same as your function, so I disregarded the
rest. :)

Sorry about that.

I've got it all working now, thanks again for putting up with my
inadquecies. :)

-Bob
ccrider(at)whiterose(dot)net
Systems Administrator for whiterose.net
http://www.whiterose.net

On Sun, 24 Feb 2002, Thomas T. Thai wrote:

> On Sun, 24 Feb 2002, Robert Myers wrote:
>
> > Yes, if I use your function it works. NP.
> >
> > The problem I have now, is what do I have to do special to get the data
> > back out in proper format? Or does it come out in useable form?
>
> please re-read my original message included below. it stated that you need
> to use PHP function stripcslashes() on the data from the db before you can
> use it. all of this was said in my ORIGINAL email to you. please also note
> that 'c' in that function. that's NOT the usual stripslashes();
>
> > On Sun, 24 Feb 2002, Thomas T. Thai wrote:
> >
> > > On Sun, 24 Feb 2002, Robert Myers wrote:
> > >
> > > > I did this with the CVS version of PHP, v 4.2.0 dev.
> > > >
> > > > I downloaded it for the function pg_escape_bytea, still no go, I can't get
> > > > the data into the field.
> > >
> > > did you try as i said below? i've tested it on php 4.0.6 - 4.1.0. i've not
> > > used pg_escape_bytea so i can't comment on it.
> > >
> > > > > > I'm having a problem with the bytea datatype. I'm trying to store image
> > > > > > data inside the field in a table, and php seems to only be sending a small
> > > > > > portion of the image into the database.
> > > [...]
> > > > > to use bytea with php, encode the image data with escByteA() below before
> > > > > storing it in your db. then when you retrieve the data, use php's
> > > > > stripcslashes(). i've tested this on various image data and they all work
> > > > > just fine.
> > > > >
> > > > > function escByteA($binData) {
> > > > > /**
> > > > > * \134 = 92 = backslash, \000 = 00 = NULL, \047 = 39 = Single Quote
> > > > > *
> > > > > * str_replace() replaces the searches array in order.
> > > > > * Therefore, we must
> > > > > * process the 'backslash' character first. If we process it last, it'll
> > > > > * replace all the escaped backslashes from the other searches that came
> > > > > * before. tomATminnesota.com
> > > > > */
> > > > > $search = array(chr(92), chr(0), chr(39));
> > > > > $replace = array('\\\134', '\\\000', '\\\047');
> > > > > $binData = str_replace($search, $replace, $binData);
> > > > > return $binData;
> > > > > }
> > >
> > >
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
>
> ---
> Thomas T. Thai | Minnesota.com | tom(at)minnesota(dot)com | 612.220.6220
> Visit http://www.minnesota.com/
>
>
>