Re: PQescapeBytea & PQunescapeBytea

"Frost, Mr. Michael (contractor)" <frost(at)nrlmry(dot)navy(dot)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(at)nrlmry(dot)navy(dot)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 |
> */
> ///////////////////////////////////////////////////////////////////////