MSSQL: Data of type `varchar` disappears when the database server character encoding is chinese

[BlueHtml]

Introduction

Hello, in MSSQL, when the field type is varchar, its value disappears. This issue occurs with both the CSV component and the JSON component.

JSON Component

The SQL is as follows:

select 'json' AS component, 'array' AS type;

select top 2 * from [Ban].[dbo].[ErrorLog];
select 'INFO' as LogLevel;
select CAST('INFO' as varchar(100)) as LogLevel;

The MSSQL execution result is as follows:

The JSON component execution result is as follows (The values of LogLevel and LogSource are missing):

[
{"ID":1,"LogTime":"2023-01-31T08:26:25.573+00:00","LogLevel":"","LogSource":"","Message":"默认数量:minWorker:4,minIOC:1;设置:300","Exception":""},
{"ID":2,"LogTime":"2023-01-31T08:26:26.100+00:00","LogLevel":"","LogSource":"","Message":"conf加载成功","Exception":""},
{"LogLevel":"INFO"},
{"LogLevel":""}
]

CSV Component

The SQL is as follows:

select 
    'csv'              as component,
    'Download my data' as title,
    'people'           as filename,
    'file-download'    as icon,
    'green'            as color,
    TRUE               as bom;

select top 2 * from [Ban].[dbo].[ErrorLog];
select 'INFO' as LogLevel;
select CAST('INFO' as varchar(100)) as LogLevel;

The MSSQL execution result is as follows:

The CSV component execution result is as follows (The values of LogLevel and LogSource are missing):

ID,LogTime,LogLevel,LogSource,Message,Exception
1,2023-01-31T08:26:25.573+00:00,,,"默认数量:minWorker:4,minIOC:1;设置:300",
2,2023-01-31T08:26:26.100+00:00,,,conf加载成功,
,,INFO,,,
,,,,,

The MSSQL table creation statement and data types are as follows:

CREATE TABLE [dbo].[ErrorLog](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[LogTime] [datetime] NOT NULL,
	[LogLevel] [varchar](100) NOT NULL,
	[LogSource] [varchar](200) NOT NULL,
	[Message] nvarchar(MAX) NOT NULL,
	[Exception] nvarchar(MAX) NOT NULL,
)

Version information

• OS: windowns 10 Simplified Chinese zh-CN
• Database: MSSQL
• SQLPage Version: v0.30.0

[lovasoa]

Thanks for reporting this issue ! I'll look into it.

[lovasoa]

Hi ! I tried looking into it, but I cannot reproduce your issue

I added a unit test for this, that tests that varchar works. It gets tested on CI automatically, on windows and on linux, on Postgres, SQLite, and Microsoft SQL Server..

[lovasoa]

I think I found the root cause. Our SQL Server driver did not support databases with character encodings other than WINDOWS-1252. I pushed a fix !

Could you please try it and confirm the problem is solved ? Here is a development build: https://we.tl/t-vKlx1UMhdW

[BlueHtml]

@lovasoa The execution result of this development version is normal.

[lovasoa]

Great !

[lovasoa]

Does it also work with chinese characters ?

[BlueHtml]

Does it also work with chinese characters ?

@lovasoa Yes

[BlueHtml]

@lovasoa However, a new issue has arisen: the value of the bit type is incorrect.

select top 2 * from [ZrAdmin].[dbo].[sys_role];;
select 'test测试' as LogLevel;
select CAST('test测试' as varchar(100)) as LogLevel;

select CAST(1 as bit) as menu_check_strictly;
select CAST(0 as bit) as menu_check_strictly;