Characters outside ASCII range not queryable by prepared statement on varchar column

[tpng]

SQL Server 2000

Test table:

CREATE TABLE [testcase] (
[id] int,
[varchar_column] varchar(200) COLLATE Chinese_Taiwan_Stroke_CI_AS
);
INSERT INTO [testcase] ([id], [varchar_column]) values (12345, N'測試');

Go program:

package main

import (
	"database/sql"
	"testing"

	_ "github.com/alexbrainman/odbc"
)

func TestExist(t *testing.T) {
	db, err := sql.Open("odbc", "connection_string")
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()

	exist, err := db.Prepare("select [id] from [testcase] where [varchar_column] = ?;")
	if err != nil {
		t.Fatal(err)
	}
	defer exist.Close()

	row := exist.QueryRow("測試")
	var id int
	if err := row.Scan(&id); err != nil {
		t.Fatal(err)
	}
	if id != 12345{
		t.Fatalf("id = %d, want 12345", id)
	}
}

[alexbrainman]

Thank you for creating this issue. I need to think about this problem before I consider your fix.

Alex

[alexbrainman]

[varchar_column] varchar(200) COLLATE Chinese_Taiwan_Stroke_CI_AS

Shouldn't varchar(200) be nvarchar(200) here?

Alex

[tpng]

If nvarchar is used, everything works correctly. But the column is on a legacy table so I can't change the column type to nvarchar easily.

[alexbrainman]

I can't change the column type to nvarchar easily

I do not see how I can help you here. I do not know enough about storing Chinese in varchar columns. I am not even sure it is possible.

Alex

[tpng]

It seems storing Chinese in varchar column depends on the server code page setting. I understand the difficulty in supporting this, so feel free to close this if this is not feasible.

Workarounds for others encountering the same issue: cast varchar column to nvarchar in the query, e.g. select * from testcase where cast(varchar_column as nvarchar(200)) = ?

[alexbrainman]

I do not know enough about this matter. Maybe others will help you here.

I did test (on windows and linux) that https://github.com/alexbrainman/odbc/pull/82 does not help this issue.

Alex

[tpng]

Thanks for the nice package though, it helps so much in my daily work! I have closed #82 since the issue is more complicated then I originally thought.

[covrom]

same problem for cp1251 codepage - varchar columns not converted to unicode and transferred to &interface{} variable as []byte in cp1251 codepage (but i waiting for utf8)