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