HuangPeng : Data Provider和Global Convert Codeset

===========================================================

Data Provider和Global Convert Codeset

===========================================================

作者: hphubei(http://hphubei.itpub.net)
发表于: 2004.12.30 21:12
分类: Oracle开发技巧
出处: http://hphubei.itpub.net/post/2868/12346
---------------------------------------------------------------

对于Higen Miao今天早上发的邮件Global Convert Codeset说明的情况，我在今天的EWP项目测试的时候发现了一个问题：

并不是每次读取数据库都需要在SQL语句里面加上XXBG_CONVERT_CODESET_FUN(栏位名,'ZHS','O')来进行转换。据我现在测试的结果，如果我们用的数据驱动是微软提供的，必须用XXBG_CONVERT_CODESET_FUN来转换，反之，如果用Oracle公司提供的数据驱动，就能自动进行解码，不需要再加上这个函数。

提供给大家分享，如果有疑义之处，希望提出来大家讨论，谢谢！

以下是我测试的例子：

《一》在SQL Navigator里面的测试

select

SHIP_TO_CITY AS TEST,

XXBG_CONVERT_CODESET_FUN(SHIP_TO_CITY,'ZHS','O') AS SHIP_TO_CITY

from XXC0OM_SHIP_HEADER_V where ORDER_NUMBER='1003000024'

这条SQL语句，如果我们在SQL Navigator中执行，Test栏位的中文是utf-8编码，出现的是乱码，SHIP_TO_CITY出现的就是正常的简体中文字符。

《二》在VB里面的测试

<1>用Oracle的数据驱动

(安装Oracle 9i Client之后添加两个引用Oracle Inproc Server 4.0 Type Library和Oracle Object for OLE Code Wizard for Stored procedures 1.0)：

Dim rsSys As New ADODB.Recordset

Set rsSys = GetRecordSet("select SHIP_TO_CITY AS TEST,XXBG_CONVERT_CODESET_FUN(SHIP_TO_CITY,'ZHS','O') AS SHIP_TO_CITY from XXC0OM_SHIP_HEADER_V where ORDER_NUMBER='1003000024'")

Trim(rsSys .Fields("TEST"))得到的是汉字苏州，

Trim(rsSys .Fields("SHIP_TO_CITY"))得到的却是'??'

    Public Function GetRecordSet(strSql As String) As ADODB.Recordset
        Dim OracleConn As ADODB.Connection
        Dim oraCommand As ADODB.Command
        Dim OraSession As OracleInProcServer.OraSessionClass
        Dim oradatabase As OracleInProcServer.oradatabase
        Dim OraDynaset As OracleInProcServer.OraDynaset
        Dim strErrorDescription As String

        Set OraSession = New OracleInProcServer.OraSessionClass
        Set oradatabase = OraSession.OpenDatabase(strServer, strUserName & "/" & strPwd, CInt(0))
        Set OracleConn = New ADODB.Connection
        Set oraCommand = New ADODB.Command
        OracleConn.CursorLocation = 3
        OracleConn.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=APPS;PLSQLRSet=1;Persist Security Info=True;User ID=APPS;Data Source=DEV2"
        OracleConn.Open
        oraCommand.ActiveConnection = OracleConn
        oraCommand.CommandType = &H1
        oraCommand.CommandText = strSql

        Set GetRecordSet = oraCommand.Execute()

        Set OracleConn = Nothing
        Set oraCommand = Nothing
End Function

<2>用microSoft的数据驱动

Dim strCon As String
strCon = "Provider=MSDAORA.1;User ID=apps;Data Source=dev2;Persist Security Info=False;Password=apps"

            Adodc1.ConnectionString = strCon
            Adodc1.RecordSource = "select SHIP_TO_CITY AS TEST,XXBG_CONVERT_CODESET_FUN(SHIP_TO_CITY,'ZHS','O') AS SHIP_TO_CITY from XXC0OM_SHIP_HEADER_V where ORDER_NUMBER='1003000024'"
            Adodc1.Refresh

            Adodc1.Recordset.Fields("test")得到的是汉字苏州的UTF-8编码"鑻忓窞"
            Adodc1.Recordset.Fields("SHIP_TO_CITY")得到的是汉字苏州
    《三》在ASP里面的测试

<1>用Oracle的数据驱动

            dim conn
            set conn=server.createobject("ADODB.connection")
            conn.open "Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=APPS;Password=APPS;Data Source=DEV2"

            set rs=server.createobject("adodb.recordset")
            strSql="select SHIP_TO_CITY AS TEST,XXBG_CONVERT_CODESET_FUN(SHIP_TO_CITY,'ZHS','O') AS SHIP_TO_CITY "
            strSql=strSql+ "from XXC0OM_SHIP_HEADER_V where ORDER_NUMBER='1003000024'"
            set rs=conn.execute(strSql)
            response.write rs("test")&"<br>"输出的是汉字苏州
            response.write rs("SHIP_TO_CITY")输出的是 ??

<2>用microSoft的数据驱动

              dim conn
            set conn=server.createobject("ADODB.connection")
            conn.open "Provider=MSDAORA.1;User ID=apps;Data Source=dev2;Persist Security Info=False;Password=apps"

            set rs=server.createobject("adodb.recordset")
            strSql="select SHIP_TO_CITY AS TEST,XXBG_CONVERT_CODESET_FUN(SHIP_TO_CITY,'ZHS','O') AS SHIP_TO_CITY "
            strSql=strSql+ "from XXC0OM_SHIP_HEADER_V where ORDER_NUMBER='1003000024'"
            set rs=conn.execute(strSql)
            response.write rs("test")&"<br>"输出的是鑻忓窞
            response.write rs("SHIP_TO_CITY")输出的是苏州

《四》在ASP.NET里面的测试

<1>用microSoft的数据驱动

            string strConn="Provider=MSDAORA; Data Source=DEV2; User ID=apps;Password=apps;Connection Reset=FALSE;Max Pool Size=75; Min Pool Size=5";
            string strQuery="select SHIP_TO_CITY AS TEST,XXBG_CONVERT_CODESET_FUN(SHIP_TO_CITY,'ZHS','O') AS SHIP_TO_CITY from XXC0OM_SHIP_HEADER_V where     ORDER_NUMBER='1003000024'";
            OleDbConnection conn=new OleDbConnection(strConn);
            conn.Open();
            OleDbCommand OraComm=new OleDbCommand();
            OraComm.CommandType=CommandType.Text;
            OraComm.Connection=conn;
            OraComm.CommandText=strQuery;
            OleDbDataReader OracleReader=OraComm.ExecuteReader();
            OracleReader.Read();
            string strDate=OracleReader["SHIP_TO_CITY"].ToString().Trim();得到的是苏州
            string strTest=OracleReader["TEST"].ToString().Trim();得到的是鑻忓窞

            OracleReader.Close();
            conn.Close();

<2>用Oracle的数据驱动(没有成功)

            OleDbConnection oraConn=new OleDbConnection();
            oraConn.ConnectionString="Provider=OraOLEDB.Oracle.1;Data Source=DEV2;User Id=APPS;Password=APPS";
            string strQuery="select SHIP_TO_CITY AS TEST,XXBG_CONVERT_CODESET_FUN(SHIP_TO_CITY,'ZHS','O') AS SHIP_TO_CITY from XXC0OM_SHIP_HEADER_V where     ORDER_NUMBER='1003000024'";

            oraConn.Open();执行到这里就出现错误，错误信息是"未在本地计算机上注册“OraOLEDB.Oracle.1”提供程序。"

            OleDbCommand OraComm=new OleDbCommand();
            OraComm.CommandType=CommandType.Text;
            OraComm.Connection=oraConn;
            OraComm.CommandText=strQuery;
            OleDbDataReader OracleReader=OraComm.ExecuteReader();
            OracleReader.Read();

         string strDate=OracleReader["SHIP_TO_CITY"].ToString().Trim();
          string strTest=OracleReader["TEST"].ToString().Trim();
          OracleReader.Close();
          oraConn.Close();

<3>用MicroSoft的数据驱动OracleClient(没有成功)

            string strConn="Data Source=DEV2;User Id=APPS;Password=APPS;";
            string strQuery="select XXBG_CONVERT_CODESET_FUN(ITEM_DESC,'ZHS','O') AS ITEM_DESC from XXC0IN_EINV_TRX_LINE";
            OracleConnection OCConn=new OracleConnection(strConn);
            OCConn.Open();执行到这里就出现错误，错误信息是"无法在 DLL oci.dll 中找到名为 OCIEnvCreate 的入口点。"
            OracleCommand OraComm=new OracleCommand();
            OraComm.CommandType=CommandType.Text;
            OraComm.Connection=OCConn;
            OraComm.CommandText=strQuery;
            OracleDataReader OracleReader=OraComm.ExecuteReader();
            OracleReader.Read();
            string strDate=OracleReader["SHIP_TO_CITY"].ToString().Trim();
          string strTest=OracleReader["TEST"].ToString().Trim();
            OracleReader.Close();
            OCConn.Close();

hphubei 发表于:2004.12.30 21:12 ::分类: ( Oracle开发技巧 ) ::阅读:(34168次) :: 评论 (209)