Re: Problem with client_encoding and PsqlODBC driver.

On 03/20/2015 04:38 AM, Kadri Raudsepp wrote:
> Hello,
>
> I have some trouble with client_encoding using psqlodbc driver and -
> being unable to solve it - was hoping you would be able to help me.
>
> I need to import large amount of data to SQL Server from postgres
> database. I'm using psqlodbc driver version 9.03.04.00, Microsoft SQL
> Server 2012 and postgres version 9.2. My postgres cluster has the
> encoding SQL_ASCII and database encoding is LATIN1.
>
> I tried both Unicode(x64) and ANSI(x64) drivers, writing "SET
> CLIENT_ENCODING TO 'LATIN1'" to Connect Settings textbox. For both
> drivers, connection is successful and I use openquery to read the data,
> but my text data is all garbled.

So what is the encoding set for in openquery?

>
> Looking into the driver log I can see that the client_encoding is indeed
> set to LATIN1 as expected, but after that cc_lookup_characterset is
> called and with that the encoding is changed to UTF8 with Unicode driver
> and - I'm not sure what happens with ANSI driver, but the result does
> not look like desired. I have attached a log file that contains the logs
> for both driver's connection test.
>
> Is there a setting I've overlooked or an action I could take to have the
> driver not to override my desired client_encoding? Am I doing something
> wrong altogether? Or is there maybe an older version of the driver that
> doesn't behave this way?

I would not supply the "SET CLIENT_ENCODING TO 'LATIN1'" settings. I
would use the ANSI driver because:

1) From the logs:

Right at the beginning it does:
-----ANSI-----

[6088-0.000]CC_connect: entering...
[6088-0.000]sslmode=require
[6088-0.000]LIBPQ_CC_connect: entering...
[6088-0.000]Driver Version='09.03.0400,Oct 26 2014' linking 1600 dynamic
Multithread library
[6088-0.000]extracted a client_encoding 'LATIN1' from conn_settings

and at the end:
[6088-0.156]CC_lookup_characterset: entering...
[6088-0.156]conn->unicode=0

whereas the Unicode driver does:

6088-63527.931]conn->unicode=1

2) https://odbc.postgresql.org/faq.html#4.1

>
> Thank you in advance and all the best,
> Katie
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com