Murders of database character sets

before the development of the time, we are all using SQL Server,.net development, has been using no problem, but recently developed with the Oracle, originally developed a good, method written also no problem, but once again installed a system, I found that my program can not query according to the man query, if the SQL statement appeared in the man, then the result of the query is empty, but the table inside the name has data, and later found that the original database character set to make ghosts. Do not know if you have encountered this problem, online search a lot of information, after a practice, found a few methods.

What is a character set:

Perhaps the concept of the character set is not clear, but we must know that ASCII, he is a character set, there is UTF-8, and so on, because the computer to deal with different languages, some people develop pure English, but we Chinese development or will appear in Chinese, computer to deal with different words, So there are a lot of character sets, so that the computer according to our settings, to specific processing certain text, but if the character set and you write farfetched, so sorry, garbled out, so we become the time to appear garbled. Having said so much, let's fix it.

Method One, set the database character set.

If there is a man in the query statement, but the result of the return is a null, then we can first consider whether the character set of our database is a problem, so let's try to change it first.

Query Character Set

Open Sqlplus
Sql> select * from V$nls_parameters;

then look at our character set, if inconsistent with the server to be set to the same. the most important parameter that affects the Oracle database character set is the Nls_lang parameter. Its format is as follows: Nls_lang = Language_territory.charset

It has three components (language, geography, and character set), each of which controls the characteristics of the NLS subset.

which

Language: Specifies the language of the server message, whether the effect prompt is Chinese or English

Territory: Specifies the date and number format of the server,

Charset: Specifies the character set.

such as: AMERICAN _ AMERICA. Zhs16gbk

modifying character sets
In Sql*plus, log on as DBA

Conn User name as Sysdba

Then execute the following command

>shutdown immediate; (the database was stopped)

>startup Mount; (Re-open the database to change the situation)

>alter system enable restricted session;

>alter system set job_queue_processes=0;

>alter system set aq_tm_processes=0;

>alter database open;

>alter database character set UTF8;

OR

>alter database character set Internal_use UTF8;

>shutdown immediate;

>startup; (Re-open normal Oracle)

From the composition of Nls_lang we can see that the real impact of the database character set is actually the third part.

So the character set between the two databases can import and export data to each other as long as the third part, the only thing that affects the message is the Chinese or English. Of course, we change in the database, in the environment variable inside also to change, right-click My Computer-"Advanced settings-" Environment variables-"new-" Nls_lang=chinese_china. ZHS16GBK.

It seemed to me that this method was not in my power, and then I found another way.

Method Two, modify the program

Because we are co-development, I am mainly responsible for the interface, and the other person provides me with an interface, because I can not need all the data, he returned the DataTable information I need to filter, so I each time is dt.select (xxxx= ' xxxx '), But the inside is the Chinese character is not possible. So find a lot of methods, finally can only modify the program, start our way is directly write a SQL statement, such as ' Select T.sex from t_student where t.studentname = ' Zhang San ', directly in the database query no problem, But in the program can not, and later we found that perhaps, net character set and database character set is not the same, this is not caused by the resolution of the problem, and then use the method of the parameters, below is the code of our program, we can look at.

       StringBuilder sql2 = new StringBuilder ();       Sql2. Append ("Select C.s_keypartid from Kpm_dict_i_scale C");       Sql2. Append ("where C.s_traintempid =:s_traintempid");       Sql2. Append ("and   C.s_repairlevel =: S_repairlevel");       oracleparameter[] Parameters = {New OracleParameter (": S_traintempid", oracletype.varchar,20),                        new OracleParameter (": S_repairlevel", oracletype.varchar,20)};                      Parameters[0]. Value = S_traintempid;      PARAMETERS[1]. Value = "bogie revenue";       DataTable Dtpart = Oraclehelper.executequery (oraclehelper.connectionstring, CommandType.Text, Sql2. ToString (), parameters);        if (Dtpart! = null && dtpart. Rows.Count > 0)        {              ***//data conversion        }

The above program is to give me the interface person's program, but after this call, I found that there is no problem, because with the OracleParameter OracleType, so that only two programs of the same assembly.

Later on the internet to find a lot of information, there is no other way to solve, so can only modify the program. So after this experience, we develop, if it is their own development, directly set up their own computer character set can be, but if it is cooperative development, we must pay attention to, must and the server encoding way, so that will not appear later to change the code of sorrow.

Thinking

When I started to find this problem, I thought it was my own database problems, and later found that the database can be checked out, it is certainly not the database itself installed problems, then Next is my program problem, my program does not translate the SQL statements correctly, So that the SQL statement changes when it is uploaded to the database, so there is a problem in the middle.

So the first thing we think about is that the compilation of the intermediate computer is not properly executed, since a statement in the database can be executed, in the program does not, then is the middle of the character set of the process, because the program is compiled according to the character set. So the first thing we think about is the setting of the character set in the database, but if the setting does not work, since the modification of the database can not, it can only modify the program, so the next step is to use the parameters, and then on the one hand can set the character set type of the parameter, on the one hand can also prevent SQL statement injection.

This process looks simple, but it also wasted a lot of my strength, hoping to help the group to think of me as a program ape.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Murders of database character sets