I get exactly the same result with supplying "SET CLIENT_ENCODING TO 'LATIN1'" setting and without, with both drivers. And I don't see question-marks (?), I see, for example "mõisa" instead of "mõisa".
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