Thread: BYTEA problem - ERROR: Bad input string for type bytea

Could someone help me with this issue.  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'".

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;
unsigned char *PQunescapeBytea(unsigned char *from, size_t *to_length);

/* 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);
}

Reid Thompson wrote:
> 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'".

It looks like from the below debug output that somehow you are escaping the
data twice, i.e. "\\\\000\\\\000\\\\000" should look like "\\000\\000\\000".


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

I'm haven't used ecpg before, but here is a self contained example using libpq:

in psql
---------------------
test=# create table transaction(id serial, signature bytea);
NOTICE:  CREATE TABLE will create implicit sequence 'transaction_id_seq' for
SERIAL column 'transaction.id'
CREATE TABLE

byteatest.c
---------------------
#include "postgres.h"
#include "libpq-fe.h"
#include "pqexpbuffer.h"
#include "fmgr.h"

int
main()
{
    PGconn       *conn = NULL;
    PGresult   *res = NULL;
    PQExpBuffer sql    = createPQExpBuffer();
    char       *rawstr = "abc\0def\1hij";
    size_t        rawstr_len = 11;
    size_t        escstr_len;
    char       *escstr = PQescapeBytea(rawstr, rawstr_len, &escstr_len);
    int            i, j, nFields;

    appendPQExpBuffer(sql, "INSERT INTO transaction (signature) VALUES ('%s')",
escstr);
    printf("%s\n\n", sql->data);

    conn = PQconnectdb("dbname=test");
    if (PQstatus(conn) == CONNECTION_BAD)
    {
        printf("connection error: %s", PQerrorMessage(conn));
        PQfinish(conn);
    }

    res = PQexec(conn, sql->data);
    if (!res || (PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res)
!= PGRES_TUPLES_OK))
    {
        printf("sql error: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
    }
    PQclear(res);

    resetPQExpBuffer(sql);
    appendPQExpBuffer(sql, "BEGIN; DECLARE foo CURSOR FOR select id, "
                "length(signature) as sig_length, signature from transaction");

    res = PQexec(conn, sql->data);
    if (!res || (PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res)
!= PGRES_TUPLES_OK))
    {
        printf("sql error: %s", PQerrorMessage(conn));
        PQclear(res);
        PQfinish(conn);
    }
    PQclear(res);

    res = PQexec(conn, "FETCH ALL in foo");
    if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "FETCH ALL command didn't return tuples properly\n");
        PQclear(res);
        PQfinish(conn);
    }

    /* first, print out the attribute names */
    nFields = PQnfields(res);
    for (i = 0; i < nFields; i++)
        printf("%-15s", PQfname(res, i));
    printf("\n\n");

    /* next, print out the rows */
    for (i = 0; i < PQntuples(res); i++)
    {
        for (j = 0; j < nFields; j++)
            printf("%-15s", PQgetvalue(res, i, j));
        printf("\n");
    }
    PQclear(res);

    /* close the cursor */
    res = PQexec(conn, "CLOSE foo");
    PQclear(res);

    /* commit the transaction */
    res = PQexec(conn, "COMMIT");
    PQclear(res);
    PQfinish(conn);

    return 0;
}

byteatest output
---------------------
# ./byteatest
id             sig_length     signature

2              11             abc\000def\001hij


HTH,

Joe