AsString and mixed characters

[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

[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;

[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

[AlexP]

Please specify the field type (VARCHAR2 or NVARCHAR2).

[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>

[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.

[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;

[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.