Thread: BYTEA help- ERROR: Bad input string for type bytea]

BYTEA help- ERROR: Bad input string for type bytea]

From
Reid Thompson
Date:
Pardon my posting to the developers list, but I've been unable to
resolve my problem via the archives or posting to the GENERAL list or
the DOCS list or mutltiple search string variations on GOOGLE.

Could someone help me with this issue.  Using version 7.2.3.  I am
looking to store binary data in a bytea field.  For initial test
purposes i have created a table with a bytea field (table = transaction,
field = signature).

I open the file /usr/share/pixmaps/printer.png and read it into a
buffer.  I PQescapeBytea() this buffer and then attempt to insert the
PQescapedBytea'd buffer into the table.  I get the error "sql error
'ERROR:  Bad input string for type bytea'".

I also wish to be able to retrieve this data from the DB.  I've
downloaded 7.3b and located the PQunescapeBytea() function.  Is there
special handling required for the returned data from this function?

My assumptions are that:
I should be able to take a binary buffer, PQescapeBytea() it, and store
it in the table.
I should then be able to select this data from the table,
PQunescapeBytea() it and it should match the original binary buffer.
IE printer.png is 2344 bytes.  I escape it, it is now > 2344 bytes. I
store the escaped buffer. I retrieve it. I unescape it and the unescaped
buffer is 2344 bytes.

Is this correct?

my code is below, what am i missing/doing incorrectly?
I set the debug level to 5, the log file is attached also

#include    <stdio.h>

/* include SQL Communication Area code */
EXEC SQL INCLUDE sqlca;

FILE *fp;

/* shared variables */
EXEC SQL BEGIN DECLARE SECTION;
char            *name = "rthompso";
char            *pwd = "sigcap";
unsigned char buffer[5000];
unsigned char buffer1[10001];
unsigned char *buffer2Ptr;
int readsz = 0;
unsigned int esclen;
EXEC SQL END DECLARE SECTION;

/* print all fatal errors
EXEC SQL WHENEVER NOT FOUND DO print_not_found();
*/

EXEC SQL WHENEVER SQLERROR SQLPRINT;

void print_not_found()
{
      printf("No results were returned.\n");
}

void some_error( char *val)
{
      printf("Some error occured.[%s]\n", val);
}

int main()
{
       buffer2Ptr = &buffer1[0];

       /* open a database connection
       */
      EXEC SQL CONNECT TO tcp:postgresql://192.168.100.126:5432/sigcap AS
conn USER :name USING :pwd;
      if (!ECPGstatus(__LINE__, "conn"))
      {
          fprintf(stderr, "Unable to connect to database.\n");
          return(1);
      }

      /* execute
      */
      EXEC SQL INSERT INTO transaction (signature) VALUES ( :buffer2Ptr);
      if (sqlca.sqlcode != 0)
      {
          some_error("ERROR ON INSERT");
          printf("\nERROR [%s]\n", sqlca.sqlerrm.sqlerrmc);
      }

      if (sqlca.sqlcode < 0)
      {
          some_error("LESS THAN ZERO ERROR");
      }
      else if ( sqlca.sqlcode == 100)
      {
           some_error("EQUAL TO 100/NO DATA/END OF CURSOR");
      }
      printf("sqlca.sqlcode of  %d\n", sqlca.sqlcode);

      fclose(fp);

      /* disconnect */
      EXEC SQL DISCONNECT conn;

      return(0);
}