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

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