output varchar2 charset AL32UTF8
Posted: 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;
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;