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);
}
Attachments:
postgres.logtext/plain; name=postgres.logDownload
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