2

For a project, I do have to code a server in JS (Node) which return elements from a database (Postgres) on a Windows computer.

Everything is "working fine" in a way that connections are OK and elements are returned, however, I do have an encoding issue. My elements are in French, hence, some letters, such as "éàç", are not correctly "seen" by the server (I printed the result). The console is working with UTF8 (I tested it) so the problem is elsewhere.

It seems to me that this issue comes from the query to the database (HTML and JS from the client side are OK and support these letters). Here is a part of my code which returns the wrong things.

var client = new pg.Client({
user: 'xx',
password: 'yy', 
database: 'phrases',
host: 'localhost', 
client_encoding: 'utf8',
port: '5432'
});
client.connect();

client.query('SELECT id,mots from phrase WHERE langue=$1 ORDER BY anno limit 20;', ['fr'])
.then(
  function(data){
    text = data.rows[0].mots; //<= return the text needed
    console.log(text);
  }
);

I'm almost sure the problem is not from this part of code but from the database side however I may be wrong (and thus I can't see why). From the PSQL console, I do have:

show CLIENT_ENCODING; > UTF8
show SERVER_ENCODING; > UTF8

And from the database:

    Nom    | PropriÚtaire | Encodage |      Collationnement       |    Type caract.    |     Droits d'acc
-----------+-------------+----------+----------------------------+--------------------+-----------------------
phrases   | postgres    | UTF8     | French_France.1252         | French_France.1252 |

I tried from a python program (print a simple request to the DB) to see if results were the same, they are; letters aren't well printed. Hence I'm sure the problem comes from the DB and are linked with the encoding but no way to find something working.

Does anyone have an idea?

Best,

bob bab
  • 21
  • 1
  • Do the characters look right when you `SELECT` them in `psql`? Maybe node.js doesn't expect UTF-8 and you should set `client_encoding: 'WIN1252'`. – Laurenz Albe Jan 29 '19 at 10:28
  • I tried with client_encoding: 'WIN1252' but it returns an error; Unknown encoding WIN1252. Almost no doc is available about the encoding supported by 'pg.client', so hard hard... In the psql console, everything is good, surprisingly... only for out of console queries things are wrong. – bob bab Jan 29 '19 at 10:48

1 Answers1

0

I know this is an old post and the author is probably never going to see this, but I think that an answer should be written for others with this issue.

I had a very similar problem as the one you describe. Apparently, the encoding is correct, but when you do a SELECT * FROM table query some letters are rendered as gibberish. This might be because Windows does not automatically use UTF-8 encoding.

Before entering your database via cmd you could run the command chcp 65001. This will work but is generally considered inefficient and dangerous. A better solution would be to do the following:

  1. Search for the app Run and input intl.cpl
  2. Go to the Administrative Tab
  3. Click on Change System Locale
  4. Check the box in the bottom left.

Credit to this answer by @mklement0

Diogenis Siganos
  • 779
  • 7
  • 17