Page 1 of 1
AsString and mixed characters
Posted: Thu 27 Aug 2015 17:54
by sinys
Xe2, odac 9.5.18
I have query
Code: Select all
select * from user_source where name = :name
If i set parameter like
OraQuery1.AsString := 'ADD_НАШ ПЕРСОНАЛ';
the query doesn't return any rows, but if I set parameter like this
OraQuery1.Value := 'ADD_НАШ ПЕРСОНАЛ';
it is working fine.
Before (in previous versions) variant with AsString worked fine
Re: AsString and mixed characters
Posted: Fri 28 Aug 2015 07:52
by AlexP
Hello,
Please specify the versions of the server and the client (if you use the OCI mode) of Oracle, the field type (VARCHAR2 or NVARCHAR2), and the results of the following queries
Code: Select all
SELECT USERENV ('language') from dual;
SELECT * FROM V $ NLS_PARAMETERS;
Re: AsString and mixed characters
Posted: Fri 28 Aug 2015 17:39
by sinys
I use OCI mode
server
Code: Select all
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
client
Code: Select all
C:\Users\user>tnsping orcl12
TNS Ping Utility for 32-bit Windows: Version 12.1.0.1.0 - Production on 28-AUG-2
015 23:36:20
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
D:\app\client\user\product\12.1.0\client_2\network\admin\sqlnet.ora
...
Code: Select all
SQL> SELECT USERENV ('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
SQL> SELECT * FROM V$NLS_PARAMETERS;
PARAMETER VALUE CON_ID
------------------------ ----------------------------- -------
NLS_LANGUAGE AMERICAN 0
NLS_TERRITORY AMERICA 0
NLS_CURRENCY $ 0
NLS_ISO_CURRENCY AMERICA 0
NLS_NUMERIC_CHARACTERS ., 0
NLS_CALENDAR GREGORIAN 0
NLS_DATE_FORMAT DD-MON-RR 0
NLS_DATE_LANGUAGE AMERICAN 0
NLS_CHARACTERSET AL32UTF8 0
NLS_SORT BINARY 0
NLS_TIME_FORMAT HH.MI.SSXFF AM 0
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM 0
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR 0
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR 0
NLS_DUAL_CURRENCY $ 0
NLS_NCHAR_CHARACTERSET AL16UTF16 0
NLS_COMP BINARY 0
NLS_LENGTH_SEMANTICS BYTE 0
NLS_NCHAR_CONV_EXCP FALSE 0
19 rows selected
Re: AsString and mixed characters
Posted: Mon 31 Aug 2015 07:14
by AlexP
Please specify the field type (VARCHAR2 or NVARCHAR2).
Re: AsString and mixed characters
Posted: Mon 31 Aug 2015 17:57
by sinys
Code: Select all
C:\Users\user>sqlplus
SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 31 23:56:34 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: scott/tiger@orcl12
Last Successful login time: Sat Aug 29 2015 15:59:03 +06:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
SQL> desc user_source
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(128)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
ORIGIN_CON_ID NUMBER
SQL>
Re: AsString and mixed characters
Posted: Tue 01 Sep 2015 07:07
by AlexP
We can't reproduce the problem on the latest ODAC version. Please modify the following code, so that the problem is reproduced and send it back to us.
Code: Select all
program Project1;
{$APPTYPE CONSOLE}
uses
SysUtils, Ora;
var
OraSession: TOraSession;
OraQuery: TOraQuery;
begin
OraSession := TOraSession.Create(nil);
try
OraSession.ConnectString := 'scott/tiger@orcl';
OraSession.Connect;
OraQuery := TOraQuery.Create(nil);
try
OraQuery.Session := OraSession;
OraQuery.SQL.Text := 'select * from user_source where name = :name';
OraQuery.ParamByName('name').AsString := 'ADD_НАШ ПЕРСОНАЛ';
OraQuery.Open;
WriteLn(IntToStr(OraQuery.RecordCount));
finally
OraQuery.Free;
end;
finally
OraSession.Free;
readln;
end;
end.
Re: AsString and mixed characters
Posted: Sun 06 Sep 2015 07:47
by sinys
Example return 0 rows:
Code: Select all
procedure TForm2.Button1Click(Sender: TObject);
var
OraSession: TOraSession;
OraQuery: TOraQuery;
begin
OraSession := TOraSession.Create(nil);
try
OraSession.ConnectString := 'scott/tiger@orcl12';
OraSession.Connect;
OraQuery := TOraQuery.Create(nil);
try
OraQuery.Session := OraSession;
OraQuery.SQL.Text := 'select * from user_source where name = :name';
OraQuery.ParamByName('name').DataType := ftString;
OraQuery.ParamByName('name').ParamType := ptInput;
OraQuery.ParamByName('name').Size := 30;
OraQuery.ParamByName('name').AsString := 'ADD_НАШ ПЕРСОНАЛ';
OraQuery.Open;
ShowMessage(IntToStr(OraQuery.RecordCount));
finally
OraQuery.Free;
end;
finally
OraSession.Free;
end;
end;
Example return > 0 rows:
Code: Select all
procedure TForm2.Button1Click(Sender: TObject);
var
OraSession: TOraSession;
OraQuery: TOraQuery;
begin
OraSession := TOraSession.Create(nil);
try
OraSession.ConnectString := 'scott/tiger@orcl12';
OraSession.Connect;
OraQuery := TOraQuery.Create(nil);
try
OraQuery.Session := OraSession;
OraQuery.SQL.Text := 'select * from user_source where name = :name';
OraQuery.ParamByName('name').DataType := ftString;
OraQuery.ParamByName('name').ParamType := ptInput;
OraQuery.ParamByName('name').Size := 30;
OraQuery.ParamByName('name').Value := 'ADD_НАШ ПЕРСОНАЛ';
OraQuery.Open;
ShowMessage(IntToStr(OraQuery.RecordCount));
finally
OraQuery.Free;
end;
finally
OraSession.Free;
end;
end;
Re: AsString and mixed characters
Posted: Mon 07 Sep 2015 05:08
by AlexP
We couldn't reproduce the problem on our local database. If you can open access to your database, we will check this behavior again directly on your database.