Re: BYTEA problem - ERROR: Bad input string for type bytea
От | Joe Conway |
---|---|
Тема | Re: BYTEA problem - ERROR: Bad input string for type bytea |
Дата | |
Msg-id | 3DCAF6B1.8020804@joeconway.com обсуждение исходный текст |
Ответ на | BYTEA problem - ERROR: Bad input string for type bytea (Reid Thompson <reid.thompson@ateb.com>) |
Список | 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
В списке pgsql-general по дате отправления: