Database Toolkit and Chinese Characters

[Helmut_E]

Hi All,

I have a problem with a function where users can search inside a Microsoft SQL Server database for a specific entry.

I used the Database Toolkit with the DB Tools Execute Query.vi, users can add there keyword eg. "%blue" inside a string control and then the query is passed to the DB.

(e.g. SELECT FIELD1 FROM TABLE1 WHERE FIELD1 LIKE N'%blue')

That works fine in the german version.

Now the application is also used in China. As long as they search for ASCII characters everything works fine too. Now they have started to create entries with chinese characters (which also works) but when they try to search for some of the chinese characters they don`t get a result. (when they search for entries with chinese AND english characters and search for the english characters everything works too)

I don`t use Unicode functions and the database field is a nvarchar.

Unfortunately I don`t have a Chinese Windows Version to make some tests here.

Has anybody an idea what could be the reason (chinese codepage?) and how to solve this issue?

Thanks and best wishes,

Helmut

LabView 8.5 / Microsoft SQL Server 2008

[zou]

If you want to support Chinese characters, you have to use UNICODE.

You don't need Chinese Windows version, English version should be ok for testing Chinese characters.

All you need is a way to input Chinese characters, which is supported in XP or 7.

[Helmut_E]

Hi George, thanks for your reply,

I know that it would be better to use Unicode, but the database toolkit currently dont support it (hope sometimes in the future ...)

I´m wondering about this issue, because users in China are able to enter data from my Application to the database including chinese characters.

So UPDATE and SELECT works fine with chinese characters (I receive data there in the correct format, as long as useres have a chinese Windows version)

(see picture)

The probleme here is the LIKE in the SELECT statement.

If Field1 is for example "test 測試" and my statement is

SELECT FIELD1 from Table1 WHERE FIELD1 LIKE N'%test%'

Then I receive the correct result (the complete fields including the chinese characters)

When I try it with

SELECT FIELD1 from Table1 WHERE FIELD1 LIKE N'%測試%'

I receive nothing.

Any idea about this?

Thanks and best wishes,

Helmut

LabView 8.5

[zou]

LabVIEW does NOT fully support UNICODE is the problem.

Hope this document useful to you:

LabVIEW Unicode Programming Tools

[zou]

> When I try it with

> SELECT FIELD1 from Table1 WHERE FIELD1 LIKE N'%測試%'

> I receive nothing.

You're mixing ASCII and UNICODE.

Have you tried to force the the control to UNICODE?

[Helmut_E]

Hi George, thanks again for your help,

no, I dont have tried to set it to Unicode (the database toolkit can`t return unicode), but as you mentioned it is probably a kind of a mapping problem.

As far as I remember Windows is using different mappings depending on the language selection. Thats the reason why the special german characters in the caption of the front panel are mapped to a chinese character (here in Germany you will find TÜV as first Word 😉

But as you can see all entries in simplified chinese where shown in the table (and the entries where made with the database toolkit)

So I assume that the basic things works there (as long as I stay in the Chinese version)

Here is a picture from the SQL Server:

The problem is the LIKE statement.

I didn`t get results for e.g. LIKE N'%卷尺1%'

(and the exact entry is shown in the result set without the LIKE clause)

Do you know if there is any non-unicode conversion which kills the result or the LIKE statement?

Thanks again and best wishes,

Helmut

P.S. my SQL statement in the last example was probably not very good - I copied an element from the google translater inside the last post, that was the reason for the mixture of traditional and simplified chinese

[zou]

Split the column (split ASCII & UNICODE).

That way you can do have to use UNICODE for query.

[GabeG · Accepted solution]

You could try this test even without changine the code page (language for non-unicode programs). First change your string control to '\' codes display. In the places where Chinese characters would appear, enter some hex codes above 7F by escaping them with a '\'. These are the codes that are traditionally displayed differently depending on OS settings like language for non-unicode programs.

UPDATE table

SET foobar='test\AA\BB\CC\DD'

WHERE blah-blah

SELECT foobar FROM table WHERE foobar LIKE 'test%'

Does the returned string have \AA\BB\CC\DD after test? Now try...

SELECT foobar FROM table WHERE foobar LIKE '%\AA\BB\CC\DD'

Get anything? Now try it with the N prefix... 

[Helmut_E]

Thanks George and Gabe for your help!

I have made some tests with Gabe`s solution and it seems to work (also a good idea for testing some unicode issues)

Thanks again and best wishes,

Helmut