Different result when insert into a nvarchar column between string and bytes

[lunny]

The result is different when inserting into a nvarchar column. When giving a string parameter, it's right. But when giving a bytes parameter, the result is wrong.

sql.Exec("INSERT INTO table (nvarchar_col) VALUES (?)", stringParam) // this is right
sql.Exec("INSERT INTO table (nvarchar_col) VALUES (?)", bytesParam) // this is wrong

[tc-hib]

I think that is expected behavior. What is in bytesParam ? If it contains a LE UCS-2 string, it should work. If it contains a UTF-8 string, it should not.

You can try that in SSMS:

INSERT INTO table
VALUES
(N'é'),   -- OK (correctly typed parameter)
(0xC3A9), -- Not OK (UTF-8)
(0xE900)  -- OK (UCS-2)

[lunny]

I think that is expected behavior. What is in bytesParam ? If it contains a LE UCS-2 string, it should work. If it contains a UTF-8 string, it should not.

You can try that in SSMS:

INSERT INTO table
VALUES
(N'é'),   -- OK (correctly typed parameter)
(0xC3A9), -- Not OK (UTF-8)
(0xE900)  -- OK (UCS-2)

It's a utf8 string.

var stringParam = "data"
var bytesParam = []byte(stringParam)

[tc-hib]

The readme is a bit lacking. But I think this is good behavior: a []byte parameter should be sent as a varbinary.

[lunny]

The readme is a bit lacking. But I think this is good behavior: a []byte parameter should be sent as a varbinary.

But the column type is nvarchar, of course it's difficult to know which is the column type when execute a SQL.

[tc-hib]

Yes, but I'm afraid it's your responsibility to ensure your parameter has the correct type. The driver cannot arbitrarily convert your []byte from utf-8 to ucs-2, that would clearly be a bug. Same goes for other libraries, such as ADO.

In this case, I suppose sp_describe_undeclared_parameters (which cannot be implemented in the driver anyway, as it requires your schema) would guess the type, but in some queries it would be impossible, and so the behavior would be unpredictable.

[lunny]

It seems a workaround is always to use string and never use bytes even if it's a binary column.

[tc-hib]

No :) If you actually want to store 0xC3A9 in a varbinary, and you pass it as a string, it will become 0xE900. Say you want to store a png file in a column: don't pass it as a string, that would corrupt it.

[lunny]

No :) If you actually want to store 0xC3A9 in a varbinary, and you pass it as a string, it will become 0xE900. Say you want to store a png file in a column: don't pass it as a string, that would corrupt it.

Wow, thanks! I think this issue could be closed.