Swedish characters from javascript to mssql - appear as question marks in db table?

Question

So I have this weird issue with node js right now and can not find any solution...

Im trying to insert values to my mssql table and some of the values includes the swedish characters "åäö". Anyhow when I do this they appear as "??????". Each special character appear as two question marks ("ö" -> "??").

Some details:

*Package I'm using is in js: msnodesqlv8

var sql = require('msnodesqlv8');
const connectionString = "server=host;Database=MyDb;Trusted_Connection=Yes;Driver={SQL Server Native Client 11.0}"
q = "Insert into [MyDb].[dbo].[MyTable] (testCol) values ('ö')"

sql.query(connectionString, q, (err,data) => {
      if (err) {
           console.log(err);
      }
      else {
           console.log('Imported row to table.');
      }
})

*The columns in the db table that will retrieve values containing "åäö" is defined as datatype nvarchar(50)

I have also trid with N'ö' in variable q and these characters (within the double quotes) appear in db table "ￃﾶ"

*console.log('ö') prints ö in console as expected

*I have tried all conversions i can think about in js (for example utf-8, latin1, etc...)

*Collation in db is Finnish_Swedish_CI_AS

*I have tried to read out swedish chrs from another table and it works fine through the package msnodesqlv8

The problem to me seems to be somewhere when sending the query from js (everything looks fine here), through package msnodesqlv8 and when mssql shall interpret the values. Im very glad if someone can help me.

Show us the actual code you are using here. `'åäö'` can be represented as a `varchar` in the collation `Finnish_Swedish_CI_AS` ([db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=17eca3dc2b75f13b1c6bd72e8f2287f5)) so somewhere you are either using a different collation, or something is happening prior to the data getting to the database. We need a [mre] here. — Thom A, Aug 27 '20 at 10:23
Considering that you are just using a literal value, then this safely suggests that the column `testCol` in the table `dbo.MyTable` is not using the collation you think it is. YOu either need to change collation of the column `testCol`, or change it to an `nvarchar` and use an `nvarchar` literal. — Thom A, Aug 27 '20 at 10:46
Im already using ```nvarchar``` as the defined datatype for column ```testCol```... — python_is_my_snake, Aug 27 '20 at 10:50
I'm afraid not, the problem appears to be related to the application layer, not SQL Server, and I don't even have any passing knowledge of JavaScript. — Thom A, Aug 27 '20 at 13:20

score 0 · Answer 1 · answered Aug 29 '20 at 13:14

Is there some reason you're not using a prepared statement? It takes care of the encoding for you, e.g.:

const sql = require('msnodesqlv8')
const connectionString = "server=YourServerIp,YourServerPort;Database=StackOverflow;Trusted_Connection=Yes;Driver={SQL Server Native Client 11.0};"
console.log('Connecting...')
sql.open(connectionString, (err, conn) => {
    console.log('Dropping...')
    conn.query('drop table if exists [dbo].[Swedish]', (err,) => {
        console.log('Creating...')
        conn.query('create table [dbo].[Swedish] (testCol nvarchar(50))', (err) => {
            console.log('Inserting...')
            conn.prepare('insert [dbo].[Swedish] (testCol) values (?)', (err, ps) => {
                ps.preparedQuery(['ö'], (err) => {
                    ps.free();
                    console.log('Selecting...')
                    conn.query('select * from [dbo].[Swedish]', (err, rows) => {
                        console.log(`${JSON.stringify(rows)}`)
                    })
                })
            })
        })
    })
})

Which yields...

> node .\swedish.js        
Connecting...
Dropping...
Creating...
Inserting...
Selecting...
[{"testCol":"ö"}]

The same character is present when queried via SSMS.

Swedish characters from javascript to mssql - appear as question marks in db table?

1 Answers1