Re: PQescapeBytea & PQunescapeBytea

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(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 |
*/
///////////////////////////////////////////////////////////////////////
-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, May 04, 2005 7:30 AM
To: Frost, Mr. Michael (contractor)
Cc: pgsql-interfaces(at)postgresql(dot)org
Subject: Re: [INTERFACES] PQescapeBytea & PQunescapeBytea

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