Обсуждение: convert(... using windows_1251_to_utf8) - works on cli, but not in a c prog.

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

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

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.

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

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.