Problem with accentuation in postgresql with npgsql [duplicate]

Question

Problem with accentuation in postgresql with npgsql [duplicate]

Navigation

#1 by (0 votes)

1

I have a postgresql database created with encoding SQL_ASCII and template0 . When I try to select rows with accents like NOT it gives error, however if the records do not have an accent it works normally.

Errors:

Error parsing column 6 (private = 0 - String)}

Message = Can not convert bytes [C3] to index 0 of the code page specified for Unicode.

I'm using npgsql and Dapper

<packages>
  <package id="Dapper" version="1.50.2" targetFramework="net45" />
  <package id="Npgsql" version="3.1.7" targetFramework="net45" />
</packages>

I've tried:

Pass client encoding into the connection string. I tried several encodings.

var sqlBuilder = new NpgsqlConnectionStringBuilder
{
    Host = host,
    Database = database,
    Username = user,
    Password = password,
    Pooling = false,
    ClientEncoding = "SQL_ASCII" 
    //Tentei "UNICODE", "utf8", "win-1252"
};

I also tried to run a command before select to change the encoding . I tried several encodings :

 // Tentei: SQL_ASCII, win-1252, unicode
 connection.Execute("set client_encoding = 'SQL_ASCII'");

 var data = connection.Query<T>(strSQL);

Rebuilding the database with another encoding is not an option since the application will run on several banks already in production.

I've looked for other questions on the network, but nothing worked for me.

c# character-encoding postgresql npgsql

asked by anonymous 25.08.2016 / 14:44

1 answer

A plugin by the name "getServiceLocator" was not found in the plugin manager Zend \ Mvc \ Controller \ PluginManager Show Json data inside a Form Html

score 0 · Accepted Answer

After I contacted the NPG-SQL maintainers, version 3.1.8 added support for the ClientEncoding attribute, which solved the problem.

 var sqlBuilder = new NpgsqlConnectionStringBuilder
                {
                    Host = strLocal,
                    Database = strNome,
                    Username = strUser,
                    Password = strSenha,
                    Pooling = false,
                    Encoding = "windows-1252",
                    ClientEncoding = "sql-ascii"
                };

 string strConexao = sqlBuilder.ConnectionString;

However, it is recommended not to use Encoding sql-ascii for new databases. My case was a case of legacy software, and there was no possibility of changing Encoding from the bank.