Invalid byte sequence for encoding when reading NpgsqlDataReader

Question

I am counting all the rows from a data reader, for that I am doing this:

  connection = new NpgsqlConnection(CS);
  connection.Open();

  command = new NpgsqlCommand(cmd, connection);

  dataReader = command.ExecuteReader();

  while (dataReader.Read())
  {
      res++;
  }

Where CS is my connection string, with the format Server=server_here;Port=port_here;User Id=username_here;Password=password_here;Database=database_here;. After a certain number of records, I get an exception with the following message:

ERROR: 22021: invalid byte sequence for encoding \"UTF8\": 0xbb

I am using postgres 9.4, and the Npgsql version (downloaded from nuget) is 3.2.2. My database encoding is SQL_ASCII, is there any way for me to successfully read the full data reader without changing the data base encoding?

Shay Rojansky · Accepted Answer · 2017-05-21 10:09:39Z

2

By default, Npgsql will set the client encoding to UTF8, which means that it is PostgreSQL's responsibility to provide valid UTF8 data, performing server-side conversions in case the database isn't in UTF8. However, the SQL_ASCII is special, in that it means "we don't know anything about characters beyond 127" (see the PG docs). So PostgreSQL performs no conversions on those.

If you know that your database is in some specific non-UTF8 encoding (say, ISO 8859-1), you can pass the Client Encoding parameter on the connection string with the name of a valid .NET encoding. This will make Npgsql correctly interpret characters beyond 127 coming from PostgreSQL. If you really don't know what encoding your database uses, well, there's nothing much you can do...

For more info, see this issue.

answered May 21, 2017 at 10:09

Shay Rojansky

17k3 gold badges44 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Allie Over a year ago

thank you, adding ";client encoding=iso-8859-1" to my connection string solved the encoding issues I was having

Michel Milezzi · Accepted Answer · 2017-05-11 11:22:26Z

0

AFAIK is not possible to enable postgres's built-in conversion from SQL_ASCII. Probably you should do it manually, using a tool like iconv ou recode.

If the client character set is defined as SQL_ASCII, encoding conversion is disabled, regardless of the server's character set. Just as for the server, use of SQL_ASCII is unwise unless you are working with all-ASCII data.

Quote from PostgreSQL documentation.

edited May 11, 2017 at 11:22

answered May 11, 2017 at 2:38

Michel Milezzi

11.4k3 gold badges24 silver badges36 bronze badges

Collectives™ on Stack Overflow

Invalid byte sequence for encoding when reading NpgsqlDataReader

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related