Thread: convert(... using windows_1251_to_utf8) - works on cli, but not in a c prog.
convert(... using windows_1251_to_utf8) - works on cli, but not in a c prog.
From
"Alexander Farber"
Date:
Hello, I'm programming a small Flash game with PostgreSQL 8.1.4 and phpBB serving as backend. The data in the database is in windows_1251 encoding. For my game I have to convert it into utf8, and at the command prompt it seems to work (I recon this because 7 win1251 chars seem to produce 14 utf8 bytes): phpbb=> show client_encoding; client_encoding ----------------- SQL_ASCII (1 row) phpbb=> select username from phpbb_users where user_id=179; ---------- Василий (1 row) phpbb=> select convert(username using windows_1251_to_utf8) from phpbb_users where user_id=179; convert_using ---------------- Р'асилий (1 row) But when I call the same command from my C program, I get the result in Windows-1251 and not in utf8: #define SQL_FETCH_USER \ "select (username using windows_1251_to_utf8), " \ "user_avatar from phpbb_users where user_active = 1 " \ "and user_id = $1 and user_password = $2 and user_id not in " \ "(select ban_userid from phpbb_banlist where ban_userid is not null)" PQprepare(....); PQexecPrepared(....); [ and as result I get only 7 bytes, in Win1251 encoding: %C2%E0%F1%E8%EB%E8%E9 ] I realize that my question is a bit chaotic, but maybe someone already knows, why wouldn't convert() work in my C program? Otherwise I'll try to prepare a simple test case Thank you Alex -- http://preferans.de
Re: convert(... using windows_1251_to_utf8) - works on cli, but not in a c prog.
From
"Alexander Farber"
Date:
I started to prepare a test case and realized I had a bug. So convert() works for me, sorry for my previous message! Regards Alex -- http://preferans.de
Re: convert(... using windows_1251_to_utf8) - works on cli, but not in a c prog.
From
Alvaro Herrera
Date:
Alexander Farber wrote: > I started to prepare a test case and realized I had a bug. > So convert() works for me, sorry for my previous message! In any case, it's probably saner if you SET client_encoding at the start of the session instead of using convert() everywhere. The server_encoding should be correctly set to Win1251 though! (Using SQL_ASCII, while not technically incorrect, is probably not doing you any favor). -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Re: convert(... using windows_1251_to_utf8) - works on cli, but not in a c prog.
From
"Alexander Farber"
Date:
Hello Alvaro, On 12/24/06, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Alexander Farber wrote: > > I started to prepare a test case and realized I had a bug. > > So convert() works for me, sorry for my previous message! > > In any case, it's probably saner if you SET client_encoding at the start > of the session instead of using convert() everywhere. The > server_encoding should be correctly set to Win1251 though! (Using > SQL_ASCII, while not technically incorrect, is probably not doing you > any favor). thanks for your comment! I've dropped my db and recreated it with encoding set to WIN1251: pref:afarber> psql Welcome to psql 8.1.4, the PostgreSQL interactive terminal. ..... phpbb=> \l+ List of databases Name | Owner | Encoding | Description -----------+-------------+-----------+--------------------------- phpbb | _postgresql | WIN1251 | postgres | _postgresql | SQL_ASCII | template0 | _postgresql | SQL_ASCII | template1 | _postgresql | SQL_ASCII | Default template database (4 rows) phpbb=> show client_encoding; client_encoding ----------------- WIN1251 (1 row) Seems to work ok... My prepared query seems to work too: #define SQL_FETCH_USER \ "select convert(username using windows_1251_to_utf8), " \ "user_avatar from phpbb_users where user_active = 1 " \ "and user_id = $1 and user_password = $2 and user_id not in " \ "(select ban_userid from phpbb_banlist where ban_userid is not null)" Regards Alex -- http://preferans.de
Re: convert(... using windows_1251_to_utf8) - works on cli, but not in a c prog.
From
Alvaro Herrera
Date:
Alexander Farber wrote: > Hello Alvaro, > > On 12/24/06, Alvaro Herrera <alvherre@commandprompt.com> wrote: > >Alexander Farber wrote: > >> I started to prepare a test case and realized I had a bug. > >> So convert() works for me, sorry for my previous message! > > > >In any case, it's probably saner if you SET client_encoding at the start > >of the session instead of using convert() everywhere. The > >server_encoding should be correctly set to Win1251 though! (Using > >SQL_ASCII, while not technically incorrect, is probably not doing you > >any favor). > > thanks for your comment! I've dropped my db > and recreated it with encoding set to WIN1251: Well, what I was suggesting (not explicitely enough, now that I look) was that you SET client_encoding to UTF8, and then use the query this way: > #define SQL_FETCH_USER \ > "select username, " \ > "user_avatar from phpbb_users where user_active = 1 " \ > "and user_id = $1 and user_password = $2 and user_id not in " \ > "(select ban_userid from phpbb_banlist where ban_userid is not null)" Good to hear that it worked anyway. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.