Thread: PQescapeBytea & PQunescapeBytea
Hi. I am running 7.4.6 of postgres. I have some data that I desire to store as bytea in postgres. The data is an array of floats. I cast the array of floats to a unsigned char and call PQescapeBytea to prepare the data for ingest. I insert it into the database, and then extract it back out. I then call PQunescapeBytea and cast it back to float to return it to it's original. However, I am probably doing something incorrect, since the before / after data is not the same. So, I eliminate the ingest into the database, and basically just called the PQescapeBytea & PQunescapeBytea and I get different results. In my example, I fill an array of 10 floats with values from 0.0 to 9.0. I then cast this to unsigned char. I then call PQescapeBytea. for ( i = 0; i < 10; i++ ) prsData = ( float ) i; pData = ( unsigned char * ) prsData; pEscapedData = PQescapeBytea ( pData, 40, &newSize ); // newSize comes back to be 161. Then I call PQunescapeBytea with pEscapedData. This returns a newSize that is different from the original. pUnescapedData = PQunescapeBytea ( pEscapedData, &newSize ); // newSize comes back to be 130, but I expected it to be 40. Any insight as to what I am doing incorrectly.? Thanks, Michael Frost Computer Sciences Corporation Phone: 831-656-4723 Fax: 831-656-4769 Email: frost@nrlmry.navy.mil
"Frost, Mr. Michael (contractor)" <frost@nrlmry.navy.mil> writes:
> [ PQescapeBytea & PQunescapeBytea are not inverses ]
They're not supposed to be. PQescapeBytea creates something that can be
inserted into a SQL command as a string literal. PQunescapeBytea
deconstructs something that has been returned as a SELECT result.
There are two different levels of backslashing involved.
This doesn't directly answer your original problem, but I think we'll
need to see more of your code to figure out what you were doing wrong.
regards, tom lane
Thanks for the quick response Tom.
Here is some sample code that illustrates what I am trying to do.
Basically, I have an array of floating point values that I want to store
in a bytea field. To do this, I cast the float * to unsigned char *,
run the unsigned char *'s through PQescapeBytea, and then insert the
record. To extract it back out, I retrieve the record, run it through
PQunescapeBytea, and then cast it back to float.
The array of floats is just an example. In practice, it would just be a
pointer some arbitrary data.
Thanks,
Michael Frost
Computer Sciences Corporation
Phone: 831-656-4723
Fax: 831-656-4769
Email: frost@nrlmry.navy.mil
////////////////////////////////////////////////////////////////////////
//
#include "postgres.h"
#include <stdio.h>
exec sql include sqlca;
int main ( int argc, char **argv ) { void insertData ( ); void getData ( );
printf ( "Calling insertData\n" ); insertData ( );
printf ( "Calling getData\n" ); getData ( );
}
void getData ( ) { int newSize = 0; float *prsData = 0;
exec sql begin declare section; char myData[2000]; int nGridID = 0; int id = 1; char *pUnescapedData = 0;
exec sql end declare section;
exec sql connect to mike;
memset ( myData, 0, 2000 ); exec sql select gridid, thedata into :nGridID, :myData from grids
where gridid = :id;
pUnescapedData = PQunescapeBytea ( myData, &newSize );
prsData = ( float * ) pUnescapedData;
exec sql disconnect;
}
void insertData ( ) { float *pData = 0; int i = 0; unsigned char *pTemp = 0; int newSize = 0; int size =
40;
exec sql begin declare section; unsigned char *pEncodeData = 0, *myData = 0; int nGridID = 1;
exec sql end declare section;
exec sql connect to mike;
pData = ( float * ) calloc ( 1, 10 * sizeof ( float ) ); for ( i = 0; i < 10; i++ ) pData[i] = ( float ) i;
pTemp = ( unsigned char * ) calloc ( 1, 10 * sizeof ( float ) + 4 ); memcpy ( &pTemp[1], pData, 10 * sizeof ( float
)); memcpy ( pTemp, &size, 4 );
pEncodeData = PQescapeBytea ( pTemp, 10 * sizeof ( float ) + 4,
&newSize );
myData = ( unsigned char * ) calloc ( 1, newSize ); memcpy ( myData, pEncodeData, newSize );
exec sql delete from grids; exec sql insert into grids ( gridid, thedata ) values ( :nGridID,
:myData );
exec sql disconnect;
}
/* Table "public.grids"Column | Type | Modifiers
---------+---------+-----------gridid | integer |thedata | bytea |
*/
///////////////////////////////////////////////////////////////////////
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, May 04, 2005 7:30 AM
To: Frost, Mr. Michael (contractor)
Cc: pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] PQescapeBytea & PQunescapeBytea
"Frost, Mr. Michael (contractor)" <frost@nrlmry.navy.mil> writes:
> [ PQescapeBytea & PQunescapeBytea are not inverses ]
They're not supposed to be. PQescapeBytea creates something that can be
inserted into a SQL command as a string literal. PQunescapeBytea
deconstructs something that has been returned as a SELECT result.
There are two different levels of backslashing involved.
This doesn't directly answer your original problem, but I think we'll
need to see more of your code to figure out what you were doing wrong.
regards, tom lane
"Frost, Mr. Michael (contractor)" <frost@nrlmry.navy.mil> writes:
> Here is some sample code that illustrates what I am trying to do.
Oh, you're feeding it through ECPG eh? That's probably the source of
the impedance mismatch :-(. PQescapeBytea is going to produce things
like\\001
because it expects that its output will be inserted directly into a
literal constant in an SQL command string. I don't know a whole lot
about ECPG, but I would suppose that when you tell it to send the value
of a C string, it expects to have to apply its own level of escaping.
So what's getting sent is probably something like\\\\001
which of course isn't going to store what you want. There may be an
issue on the output side too, though I'm less sure about that.
I don't know what is the recommended procedure for inserting arbitrary
binary data through ECPG. Maybe Michael Meskes can help.
regards, tom lane
> Basically, I have an array of floating point values that I want to store
> in a bytea field. To do this, I cast the float * to unsigned char *,
> run the unsigned char *'s through PQescapeBytea, and then insert the
> record. To extract it back out, I retrieve the record, run it through
> PQunescapeBytea, and then cast it back to float.
> The array of floats is just an example. In practice, it would just be a
> pointer some arbitrary data.
> Thanks,
> Michael Frost
> Computer Sciences Corporation
> Phone: 831-656-4723
> Fax: 831-656-4769
> Email: frost@nrlmry.navy.mil
> ////////////////////////////////////////////////////////////////////////
> //
> #include "postgres.h"
> #include <stdio.h>
> exec sql include sqlca;
> int main ( int argc, char **argv ) {
> void insertData ( );
> void getData ( );
> printf ( "Calling insertData\n" );
> insertData ( );
> printf ( "Calling getData\n" );
> getData ( );
> }
> void getData ( ) {
> int newSize = 0;
> float *prsData = 0;
> exec sql begin declare section;
> char myData[2000];
> int nGridID = 0;
> int id = 1;
> char *pUnescapedData = 0;
> exec sql end declare section;
> exec sql connect to mike;
> memset ( myData, 0, 2000 );
> exec sql select gridid, thedata into :nGridID, :myData from grids
> where gridid = :id;
> pUnescapedData = PQunescapeBytea ( myData, &newSize );
> prsData = ( float * ) pUnescapedData;
> exec sql disconnect;
> }
> void insertData ( ) {
> float *pData = 0;
> int i = 0;
> unsigned char *pTemp = 0;
> int newSize = 0;
> int size = 40;
> exec sql begin declare section;
> unsigned char *pEncodeData = 0, *myData = 0;
> int nGridID = 1;
> exec sql end declare section;
> exec sql connect to mike;
> pData = ( float * ) calloc ( 1, 10 * sizeof ( float ) );
> for ( i = 0; i < 10; i++ )
> pData[i] = ( float ) i;
> pTemp = ( unsigned char * ) calloc ( 1, 10 * sizeof ( float ) + 4 );
> memcpy ( &pTemp[1], pData, 10 * sizeof ( float ) );
> memcpy ( pTemp, &size, 4 );
> pEncodeData = PQescapeBytea ( pTemp, 10 * sizeof ( float ) + 4,
> &newSize );
> myData = ( unsigned char * ) calloc ( 1, newSize );
> memcpy ( myData, pEncodeData, newSize );
> exec sql delete from grids;
> exec sql insert into grids ( gridid, thedata ) values ( :nGridID,
> :myData );
> exec sql disconnect;
> }
> /*
> Table "public.grids"
> Column | Type | Modifiers
> ---------+---------+-----------
> gridid | integer |
> thedata | bytea |
> */
> ///////////////////////////////////////////////////////////////////////