Page 1 of 1
SQL with Chinese char does not work in direct mode.
Posted: Tue 30 Jul 2013 05:29
by popgo_mos
SQL is something like this:
SELECT LMMBASECODE, LMMBASENAME, UNIT FROM C_LMMBASE, C_LMMTYPE WHERE (C_LMMBASE.LMMTYPECODE = C_LMMTYPE.Code) AND (PID = 6221) and LMMBASENAME Like '%中文%'
TUniQuery returns no record, but record does exist in the dataBase.
If SQL condition does not contain Chinese char, result is fine.
My environment is:
delphi2007+unidac 4.6.11
TUniConnection.UseUnicode := True;
TUniConnection.Direct := True;
Please someone tell me how to resolve this problem. Many thanks!
Re: SQL with Chinese char does not work in direct mode.
Posted: Tue 30 Jul 2013 06:11
by AlexP
Hello,
Hello,
To make such query work, you should set the UseUnicode option and OCIUnicode (declared in OraCall) to True
Code: Select all
uses ..., OraCall;
....
OCIUnicode := true;
OraSession1.Options.Direct := True;
OraSession1.Options.UseUnicode := True;
OraSession1.Connect;
OraQuery1.SQL.Text := 'SELECT LMMBASECODE, LMMBASENAME, UNIT FROM C_LMMBASE, C_LMMTYPE WHERE (C_LMMBASE.LMMTYPECODE = C_LMMTYPE.Code) AND (PID = 6221) and LMMBASENAME Like ''%中文%''';
OraQuery1.Open;
Re: SQL with Chinese char does not work in direct mode.
Posted: Wed 31 Jul 2013 02:52
by popgo_mos
The problem remains.
Do you mean use OraCallUni.pas? I can not find OraCall.pas in 4.6.1.
ps: I also can not find Component named Session.
Re: SQL with Chinese char does not work in direct mode.
Posted: Wed 31 Jul 2013 10:48
by AlexP
Hello,
Yes, that meant OraCallUni and UniConnection
Please send me results of the following queries:
Code: Select all
SELECT USERENV ('language') from dual;
SELECT * FROM V$NLS_PARAMETERS;
Re: SQL with Chinese char does not work in direct mode.
Posted: Thu 01 Aug 2013 01:28
by popgo_mos
1.AMERICAN_AMERICA.ZHS16GBK
2.
<rs:data>
<z:row PARAMETER='NLS_LANGUAGE' VALUE='AMERICAN' />
<z:row PARAMETER='NLS_TERRITORY' VALUE='AMERICA' />
<z:row PARAMETER='NLS_CURRENCY' VALUE='$' />
<z:row PARAMETER='NLS_ISO_CURRENCY' VALUE='AMERICA' />
<z:row PARAMETER='NLS_NUMERIC_CHARACTERS' VALUE='.,' />
<z:row PARAMETER='NLS_CALENDAR' VALUE='GREGORIAN' />
<z:row PARAMETER='NLS_DATE_FORMAT' VALUE='DD-MON-RR' />
<z:row PARAMETER='NLS_DATE_LANGUAGE' VALUE='AMERICAN' />
<z:row PARAMETER='NLS_CHARACTERSET' VALUE='ZHS16GBK' />
<z:row PARAMETER='NLS_SORT' VALUE='BINARY' />
<z:row PARAMETER='NLS_TIME_FORMAT' VALUE='HH.MI.SSXFF AM' />
<z:row PARAMETER='NLS_TIMESTAMP_FORMAT' VALUE='DD-MON-RR HH.MI.SSXFF AM' />
<z:row PARAMETER='NLS_TIME_TZ_FORMAT' VALUE='HH.MI.SSXFF AM TZR' />
<z:row PARAMETER='NLS_TIMESTAMP_TZ_FORMAT' VALUE='DD-MON-RR HH.MI.SSXFF AM TZR' />
<z:row PARAMETER='NLS_DUAL_CURRENCY' VALUE='$' />
<z:row PARAMETER='NLS_NCHAR_CHARACTERSET' VALUE='AL16UTF16' />
<z:row PARAMETER='NLS_COMP' VALUE='BINARY' />
<z:row PARAMETER='NLS_LENGTH_SEMANTICS' VALUE='BYTE' />
<z:row PARAMETER='NLS_NCHAR_CONV_EXCP' VALUE='FALSE' />
</rs:data>
Re: SQL with Chinese char does not work in direct mode.
Posted: Thu 01 Aug 2013 09:06
by AlexP
Hello,
Try setting the charset explicitly in the following way:
Code: Select all
UniConnection1.SpecificOptions.Values['Charset'] := 'ZHS16GBK';
Re: SQL with Chinese char does not work in direct mode.
Posted: Fri 02 Aug 2013 06:07
by popgo_mos
I am sorry, I made a mistake.
The problem can be resolved by setting OCIUniCode, UseUnicode to True.
It works, thanks a lot.
Re: SQL with Chinese char does not work in direct mode.
Posted: Fri 02 Aug 2013 06:21
by AlexP
Hello,
Glad to see that the problem was solved. If you have any other questions, feel free to contact us.