Re: BYTEA problem - ERROR: Bad input string for type bytea - Mailing list pgsql-general

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