Postgresql – Client Encoding Mismatch when creating new database

encodingpostgresqlpostgresql-9.2utf-8

I'm upgrading a program for work that uses postgres 9.2.4. When the program is run for the first time, it creates a production database, using the existing template database. The program used to run with postgres 8, which uses sql_ascii for its client encoding. Postgres 9 uses UTF8.

I know how to fix this on the command line by changing the template database's encoding to sql_ascii, but ideally I'd like to figure out how to get the program to create the new database using the new UTF8 encoding.

I found this line in the program

sqlString = "CREATE DATABASE " & m_sProdDBName & " WITH OWNER=" & _
m_s4 & " TEMPLATE=" & m_s5 & " ENCODING='sql_ascii' LOCATION= DEFAULT;" 

And I changed sql_ascii to UTF8.

However, the 'Client encoding mismatch' error gets thrown before that line ever gets executed. It happens when the connection to the template DB is being created. I got a message box to pop up with the connection string right before the connection opens. For some reason, it is hitting the messagebox code twice, even though it should only hit it once. The first time, no connection attempt is made. The second time the program goes on and attempts to open the connection, which is when the error gets thrown. Here are the two connection strings:

DRIVER=postgresql;SERVER=Stephane-PC;PORT=5432;UID=postgres;DATABASE=remindex;PWD=

DRIVER=postgresql;SERVER=Stephane-PC;PORT=5432;UID=postgres;DATABASE=template1;PWD=

(I know that no password is needed so that isn't the problem).
Remindex is the database to be created and template1 is the template DB.

In case you need some additional information, the connection object is an ADODB.Connection object and the Provider is MSDASQL.

I'm not all that good with database stuff, so I apologize for my lack of knowledge.

Any suggestions would be greatly appreciated!

Best Answer

Client Encoding Mismatch is an error message from old versions of the PostgreSQL ODBC driver.

You should go here: http://www.postgresql.org/ftp/odbc/versions/msi/ to get the most recent version and update your system.

Also there are in fact two driver flavors that get installed: ANSI and Unicode. Presumably you need Unicode if you decided to switch to utf-8.