SQL Server Character issue on Windows Server, not on Unix (� characters)

nx7onnlm 于 2023-10-15 发布在 Windows

关注(0)|答案(1)|浏览(104)

On my Windows Server 2019 In my NodeJS script I'm writing to the database using the Prisma library.

The field I'm adding this to is an NVARCHAR(1000) field. So that should accept this character, right?

The string input comes from fs.readFileSync(customJobPath, 'utf8') , I'm reading a txt file with the data

I have the following word Gardé

I've checked the collation of both the database using SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DatabaseCollation; , and in both cases it's set to SQL_Latin1_General_CP1_CI_AS
When inserting an é character manually and saving (in Tableplus), it is saved correctly in the database.

Does anyone know if there's something I'm missing?

1条答案

There's not enough information in the question to be sure, but I suspect you have one (or more) of four issues:

You're using string concatenation or interpolation to include this data in the query. This would already be wrong (you should be using query parameters), but specifically for this issue you could additionally fail if you do not include the N prefix for the literal... if you have 'Gardé' in the SQL string instead of N'Gardé' . The former is first interpreted as a basic varchar value, so the non-ASCII characters are lost. The latter is correctly interpreted as an nvarchar .
You're using parameterized queries (as you should), but setting the parameter type to varchar instead of nvarchar (perhaps implicitly, by failing to explicitly type the parameter). Again, in this situation the value is first processed as a varchar where the non-ASCII symbols are lost, before saving to the nvarchar field.
Javascript has weak handling of Unicode, and you're loading the string from the file to the nodejs string in a way that fails to preserve the original character, before then passing it on to SQL. (Hint: just because we told the readFileSync() method we needed utf8, it doesn't mean the string variable we assigned the result to was able to correctly receive this result.)
The original file is not actually stored in UTF-8, but rather UTF-16, full Unicode, or some other format with wide character support, such that interpreting it as UTF-8 mangles the character.