How to search into oracle database a row by chinese characters? vb.net

Question

I have a problem when I try to extract a row from oracle Database with help by a string query. If I try to search a row by normal characters, my query work, if I try to change with chinese characters my query doesn't found any row.

        conn.Open()
        cmd.Connection = conn
        cmd.CommandText = "select DIRNAME from PROJECTINFO where UPPER(NAME) = UPPER('" + projFullName + "')"
        cmd.CommandType = CommandType.Text

        dr = cmd.ExecuteReader()
        If dr.Read() Then
            strProjRawDataSharePath = dr.Item("DIRNAME")
        Else
            dr.Close()
            dr.Dispose()
        End If
        dr.Close()
        dr.Dispose()

If I search my "projFullName" from query with "Default" (projFullName = "Defaults"), my query work grate, if I change with projFullName = "中文版测试", my query doesn't return any value, although, in my data base i have a project with name projFullName = "中文版测试".

You should prefer bind variables, i.e. `cmd.CommandText = "select DIRNAME from PROJECTINFO where UPPER(NAME) = UPPER(:proj)"` and `cmd.Parameters.Add("proj", OracleDbType.Varchar2, ParameterDirection.Input).Value = projFullName` ` — Wernfried Domscheit, Feb 19 '18 at 15:08
`UPPER` is useless. `dump('中文版测试', 1016)` gives the same as `dump(upper('中文版测试'), 1016)`. Case is very specific to the Western alphabets Latin, Greek, and Cyrillic. — Wernfried Domscheit, Feb 19 '18 at 19:00
Please provide the driver/provider (or ConnectionString), then I can help you. — Wernfried Domscheit, Feb 20 '18 at 07:40
Why are you not able to tell us which driver are you using? The answer will depend on that. Apparently you are not the only one having difficulties to provide this information: https://stackoverflow.com/questions/48899023/arabic-character-not-inserted-properlylike-in-oracle-database?noredirect=1#comment84810060_48899023 — Wernfried Domscheit, Feb 21 '18 at 09:48
@WernfriedDomscheit NLS_CHARACTERSET = WE8ISO8859P1 and NLS_NCHAR_CHARACTERSET = AL16UTF16 — Vladut, Feb 21 '18 at 13:41
I did not ask for the database character set - I asked "Which provider do you use?" — Wernfried Domscheit, Feb 21 '18 at 13:51
No, I mean ODBC, OLE DB, ODP.NET, OCP.NET Managed Driver, etc. See this list: https://stackoverflow.com/questions/34803106/how-to-connect-to-oracle-11-database-from-net/34805999#34805999 — Wernfried Domscheit, Feb 21 '18 at 14:31
And which driver? The driver from Oracle or the driver from Microsoft (or even a different one)? — Wernfried Domscheit, Feb 21 '18 at 14:40
I waste my time with you. For the last time: Please show your full connection string (without password) and show the code how you open the connection to database. — Wernfried Domscheit, Feb 21 '18 at 14:45
@WernfriedDomscheit this is my connections string: Data Source=(DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ******)(PORT = 1521)))" + "(CONNECT_DATA = (SERVICE_NAME = Emp***.***)));" + "User Id=******;Password=****; sorry. — Vladut, Feb 22 '18 at 06:07

score 0 · Answer 1 · answered Feb 19 '18 at 14:31

0

You may want to consider using NLS_UPPER instead of UPPER.

NLS_UPPER is aware of language - specific rules etc, whereas UPPER will only apply to "english" characters, and (IRC) will translate these into english characters.

Running the below query will show you the data side-by-side and will potentially highlight the issue for you.

select UPPER(Input.ChineseText), NLS_UPPER(Input.ChineseText)
from   (select '中文版测试' as ChineseText from dual) Input;

Alternatively, it may be worth considering if UPPER is needed - Using UPPER will mean that no indexes are used in the query execution - but this is off topic for this question.

answered Feb 19 '18 at 14:31

Bryant1003

316
2
11

I am not Chinese, but `NLS_UPPER` and `UPPER` seem to produce the same output, i.e. it should not matter which one you use. `UPPER(Input.ChineseText) == NLS_UPPER(Input.ChineseText) == Input.ChineseText` – Wernfried Domscheit Feb 19 '18 at 15:00
Where shoud I put DIRNAME and PROJECTINFO? What is Input? – Vladut Feb 19 '18 at 15:04
@Vladut "Input" is just a sub-query i used as an example, your query would be `select DIRNAME from PROJECTINFO where NLS_UPPER(NAME) = NLS_UPPER('" + projFullName + "')`. Alternatively (if you can), Remove both the `UPPER` from the query to ensure that this is in fact the issue. – Bryant1003 Feb 19 '18 at 15:07
@Vladut Sorry what doesn't? The `NLS_UPPER` or removing `UPPER` entirely? – Bryant1003 Feb 19 '18 at 15:12
I try this select DIRNAME from MILLENNIUM.PROJECTINFO where NLS_UPPER(NAME) = NLS_UPPER('中文版测试') , is the same query like yours, but doesn't work. I don't recive any row from Oracle SQL Developer and doesn't work in vb.net :(. I Don't know why. @Bryant1003 – Vladut Feb 20 '18 at 06:52
@Bryant1003 if I try with normal characters like Default instead of '中文版测试' my query work into vb.net – Vladut Feb 20 '18 at 06:53
@vladut what about without an upper at all? Trying to establish where the issue is – Bryant1003 Feb 20 '18 at 07:08
@Bryant1003 how can I create my query without the upper? For me is not a problem to remove UPPER. I remove upper but my query doesn't return my value. :( How can i create another query? – Vladut Feb 20 '18 at 08:07
@Bryant1003 i can this is my problem ( https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:5783936214008 ), my oracle is setup for American and I need to convert somehow my characters from oracle. – Vladut Feb 20 '18 at 09:59

Dima Yankin · Answer 2 · 2018-02-26T05:23:24.463

0

Please, use N modifier as shown below to work with unicode strings:

cmd.CommandText = "select DIRNAME from PROJECTINFO where NAME = N'" + projFullName + "'"

Hope this helps!

edited Feb 26 '18 at 05:23

answered Feb 25 '18 at 23:56

Dima Yankin

365
1
12

psaraj12 · Answer 3 · 2018-02-26T08:48:30.253

0

Check NLS_SORT for chinese which is what you might need

Linguistic Sorting and String Searching

select DIRNAME from PROJECTINFO where NLS_UPPER(NAME, 'NLS_SORT =  
SCHINESE_PINYIN_M') = '中文版测试'

edited Feb 26 '18 at 08:48

answered Feb 26 '18 at 08:29

psaraj12

4,772
2
21
30

Sorry doesn't work. This work for me: select DIRNAME from MILLENNIUM.PROJECTINFO where UTL_I18N.RAW_TO_NCHAR(UTL_I18N.STRING_TO_RAW(NAME), 'ZHS16CGB231280') = '中文版测试'; – Vladut Feb 26 '18 at 10:23
Please explore some other chinese character set for NLS_SORT since it is simpler than your option – psaraj12 Feb 26 '18 at 10:55

score 0 · Answer 4 · answered Feb 26 '18 at 17:48

I'd do that:
'" + projFullName + "'
" + 'projFullName' + "

"select DIRNAME from PROJECTINFO where UPPER(NAME) = UPPER(+'中文版测试'+)"

Test this without defining, directly into code, as Quotation marks in VB code seem to be incorrect in there, they would end Oracle SQL Query.

score 0 · Accepted Answer · answered Feb 28 '18 at 13:40

select UTL_I18N.RAW_TO_NCHAR(UTL_I18N.STRING_TO_RAW(NAME), 'ZHS16CGB231280') As NAME, UTL_I18N.RAW_TO_NCHAR(UTL_I18N.STRING_TO_RAW(DIRNAME), 'ZHS16CGB231280') As DIR FROM PROJECS"

This query help me to convert de condification from oracle to Chinese characters

How to search into oracle database a row by chinese characters? vb.net

5 Answers5