output varchar2 charset AL32UTF8

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
luboid
Posts: 5
Joined: Mon 12 Jan 2015 14:15

output varchar2 charset AL32UTF8

Post by luboid » Mon 12 Jan 2015 14:50

Hi,

I'am using Delphi Xe7 with yours dbExpress for Oracle (evaluation) and have strange problem with output
varchar2 parameter here is database nls parameters

NLS_RDBMS_VERSION=12.1.0.2.0
NLS_NCHAR_CONV_EXCP=FALSE
NLS_LENGTH_SEMANTICS=BYTE
NLS_COMP=BINARY
NLS_DUAL_CURRENCY=$
NLS_TIMESTAMP_TZ_FORMAT=DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT=HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT=DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT=HH.MI.SSXFF AM
NLS_SORT=BINARY
NLS_DATE_LANGUAGE=AMERICAN
NLS_DATE_FORMAT=DD-MON-RR
NLS_CALENDAR=GREGORIAN
NLS_NUMERIC_CHARACTERS=.,
NLS_NCHAR_CHARACTERSET=AL16UTF16
NLS_CHARACTERSET=AL32UTF8
NLS_ISO_CURRENCY=AMERICA
NLS_CURRENCY=$
NLS_TERRITORY=AMERICA
NLS_LANGUAGE=AMERICAN

client nls_lang is set to AMERICAN_AMERICA.AL32UTF8

here is settings form form

object SQLConnection1: TSQLConnection
ConnectionName = 'Devart Oracle'
DriverName = 'DevartOracle'
Params.Strings = (
'BlobSize=-1'
'DataBase=b3grdb2'
'DriverName=DevartOracle'
'ErrorResourceFile='
'LocaleCode=0000'
'Password=b3grdb2'
'Oracle TransIsolation=ReadCommitted'
'RoleName=Normal'
'User_Name=pcb_1209'
'LongStrings=True'
'EnableBCD=True'
'InternalName='
'FetchAll=False'
'CharLength=0'
'Charset='
'UseQuoteChar=False'
'UseUnicode=True')
Left = 120
Top = 112
end

when execute this piece of code

q:=TSQLQuery.Create(self);
try
q.SQLConnection := SQLConnection1;
q.GetMetadata:=false;
q.ParamCheck:=false;
q.SQL.Text := 'begin test.testVar(:iVarChar); end;';

p := q.Params.CreateParam(ftString,'iVarChar',ptOutput);
q.Params.AddParam(p);

p :=q.Params[0];
p.ParamType := ptOutput;
p.DataType := ftString;
p.Size:=2000;
q.ExecSQL();
ShowMessage(p.AsString);
finally
q.Close;
FreeAndNil(q);
end;

ShowMesassage show empty message but when execute this

q:=TSQLQuery.Create(self);
try
q.SQLConnection := SQLConnection1;
q.GetMetadata:=false;
q.ParamCheck:=false;
q.SQL.Text := 'begin test.testVar(:iVarChar); end;';

p := q.Params.CreateParam(ftString,'iVarChar',ptOutput);
q.Params.AddParam(p);

p :=q.Params[0];
p.ParamType := ptOutput;
p.DataType := ftString;
p.Size:=2000;
p.AsString :=' ';
q.ExecSQL();
ShowMessage(p.AsString);
finally
q.Close;
FreeAndNil(q);
end;

ShowMesassage show what need to show, here p.AsString :=' '; is not empty but have 100 spaces

here is oracle package

create or replace package test is
-- Public type declarations
PROCEDURE testDate(iDate OUT DATE);
PROCEDURE testVar(iVarChar OUT VARCHAR2);
PROCEDURE testNum(iNum OUT NUMBER);
end test;

create or replace package body test is
PROCEDURE testDate(iDate OUT DATE) AS
BEGIN
iDate:= SYSDATE;
END;

PROCEDURE testVar(iVarChar OUT VARCHAR2) AS
BEGIN
iVarChar := 'qwertyuiop[] ;ςερτυθιοπ[] явертъуиопшщ';--
END;

PROCEDURE testNum(iNum OUT NUMBER) AS
BEGIN
iNum:= 1145.67801;
END;
end test;

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: output varchar2 charset AL32UTF8

Post by AlexP » Tue 13 Jan 2015 08:11

Hello,

Thank you for the sample. We have reproduced the described case and will investigate the reason for such behavior. We will inform you as soon as we have any results.

luboid
Posts: 5
Joined: Mon 12 Jan 2015 14:15

Re: output varchar2 charset AL32UTF8

Post by luboid » Tue 13 Jan 2015 14:59

Hi,

I evaluate it, to buy it

luboid
Posts: 5
Joined: Mon 12 Jan 2015 14:15

Re: output varchar2 charset AL32UTF8

Post by luboid » Tue 27 Jan 2015 11:03

Hi guys,

Is there any chance this to be done soon,

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: output varchar2 charset AL32UTF8

Post by AlexP » Wed 28 Jan 2015 08:44

We have investigated this problem in more details and found out that error occurs in Delphi, not in our components. We have reported this issue to embarcadero quality central: https://quality.embarcadero.com/browse/RSP-9950 .

As a workaround, you can use the following code:

Code: Select all

SQLQuery1.Params[0].AsString := StringOfChar(' ', 2000);

luboid
Posts: 5
Joined: Mon 12 Jan 2015 14:15

Re: output varchar2 charset AL32UTF8

Post by luboid » Thu 29 Jan 2015 11:12

Hi,

Thanks for support,

in this environment also can't be read CLOB/NCLOB

tables are :

create table BSRCHANGES1
(
tt NCLOB
)

or

create table BSRCHANGES1
(
tt CLOB
)

content of CLOB/NCLOB is

τι συμβαίνει
кирилица
latin script


sample code:

procedure TForm6.Button1Click(Sender: TObject);
var q : TSQLQuery;
s : string;
begin
q := TSQLQuery.Create(self);
try
q.SQLConnection := SQLConnection1;
q.SQL.Text := 'select * from BSRCHANGES1 t';
q.Open;

s := q.Fields[0].AsString;
ShowMessage(s);

s := q.Fields[0].AsWideString;
ShowMessage(s);

s := q.Fields[0].Value;
ShowMessage(s);
finally
FreeAndNil(q)
end;
end;

connection:

object SQLConnection1: TSQLConnection
ConnectionName = 'Devart Oracle'
DriverName = 'DevartOracle'
LoginPrompt = False
Params.Strings = (
'BlobSize=-1'
'DataBase=b3grdb2'
'DriverName=DevartOracle'
'ErrorResourceFile='
'LocaleCode=0000'
'Password=b3grdb2'
'Oracle TransIsolation=ReadCommitted'
'RoleName=Normal'
'User_Name=pcb_1209'
'LongStrings=True'
'EnableBCD=True'
'InternalName='
'FetchAll=False'
'CharLength=0'
'Charset='
'UseQuoteChar=False'
'UseUnicode=True')
Connected = True
Left = 48
Top = 40
end

with standard Oracle dbExpress driver this work only when table column is NCLOB

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: output varchar2 charset AL32UTF8

Post by AlexP » Fri 30 Jan 2015 09:47

We have reproduced the problem and will investigate the reasons for such behavior.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: output varchar2 charset AL32UTF8

Post by AlexP » Fri 30 Jan 2015 11:12

We have fixed the problem. The fix will be included into the next build.

Post Reply