Thread: libpq - characterset encoding error from selecting BYTEA
My understanding of this part of documentation "operations on binary strings process the actual bytes, whereas the processing of character strings depends on locale settings." is that reading BYTEA column does not involve character set conversion especially when "1", indicating binary, is passed to parameter "resultFormat" by calling libpq function PQexecParams(). Given CREATE TABLE t1 ( PRIMARY KEY (c1,c2), c1 SMALLINT, c2 SMALLINT, c3 BYTEA ); PGconn* first switches to "BIG5" client encoding: PQexec(conn,"SET CLIENT_ENCODING TO BIG5"); version 1: const char *pValues[]={"5","6"}; PGresult *r=PQexecParams(conn,"SELECT c3::BYTEA FROM t1 WHERE c1=$1 AND c2=$2",2,NULL,pValues,NULL,NULL,1); What is strange is that above PQexecParams() works in one portion of my program but in another portion it yields the following error: ERROR: character with byte sequence 0x98 0xe1 in encoding "BIG5" has no equivalent in encoding "UTF8" version 2: PQgetvalue(r,0,0) returns the correct BYTEA value from this version: PGresult *r=PQexecParams(conn,"SELECT c3::BYTEA FROM t1 WHERE c1=5 AND c2=6",0,NULL,NULL,NULL,NULL,1); I simply can not find any intrinsic difference between the above two versions. Why the first version is "unstable"? Best Regards, CN -- http://www.fastmail.com - Or how I learned to stop worrying and love email again
CN <cnliou9@fastmail.fm> writes: > PGconn* first switches to "BIG5" client encoding: > PQexec(conn,"SET CLIENT_ENCODING TO BIG5"); > version 1: > const char *pValues[]={"5","6"}; > PGresult *r=PQexecParams(conn,"SELECT c3::BYTEA FROM t1 WHERE c1=$1 AND > c2=$2",2,NULL,pValues,NULL,NULL,1); > What is strange is that above PQexecParams() works in one portion of my > program but in another portion it yields the following error: > ERROR: character with byte sequence 0x98 0xe1 in encoding "BIG5" has no > equivalent in encoding "UTF8" Given the way the complaint is phrased, the problem is with data going *to* the server, not *from* the server. I don't think the returned bytea is your issue at all; it must be either in the SQL query string or the parameter values being sent. Probably you should review how you're setting up the parameter strings. regards, tom lane
On Tue, Mar 15, 2016, at 09:57 PM, Tom Lane wrote: > > PGconn* first switches to "BIG5" client encoding: > > PQexec(conn,"SET CLIENT_ENCODING TO BIG5"); > > > version 1: > > > const char *pValues[]={"5","6"}; > > PGresult *r=PQexecParams(conn,"SELECT c3::BYTEA FROM t1 WHERE c1=$1 AND > > c2=$2",2,NULL,pValues,NULL,NULL,1); > > > What is strange is that above PQexecParams() works in one portion of my > > program but in another portion it yields the following error: > > > ERROR: character with byte sequence 0x98 0xe1 in encoding "BIG5" has no > > equivalent in encoding "UTF8" > > Given the way the complaint is phrased, the problem is with data going > *to* the server, not *from* the server. I don't think the returned bytea > is your issue at all; it must be either in the SQL query string or the > parameter values being sent. Probably you should review how you're > setting up the parameter strings. Thanks a lot for your always prompt and professional help! I must have made the unforgivable mistake - miss using temporary, like so: std::ostringstream oss1,oss2; oss1 << 5; oss2 << 6; const char *pValues[]={oss1.str().c_str(),oss2.str().c_str()}; PGresult *r=PQexecParams(conn,"SELECT c3 FROM t1 WHERE c1=$1 AND c2=$2",2,NULL,pValues,NULL,NULL,1); Humm... Maybe it is my compiler's issue. Maybe I should take a break now. Anyway, the error seems to be gone with the following slightly modified version: std::ostringstream oss1,oss2; oss1 << 5; oss2 << 6; std::string s1(oss1.str()),s2(oss2.str()); const char *pValues[]={s1.c_str(),s2.c_str()}; PGresult *r=PQexecParams(conn,"SELECT c3 FROM t1 WHERE c1=$1 AND c2=$2",2,NULL,pValues,NULL,NULL,1); Best Regards, CN -- http://www.fastmail.com - Or how I learned to stop worrying and love email again