Bug on Dbexpoda (TSQLStoredProc, ThousandSeparador and NLS_NUMERIC_CHARACTERS)

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for Oracle in Delphi and C++Builder
Post Reply
romeu
Posts: 10
Joined: Wed 09 Jan 2008 13:06
Location: Belo Horizonte, MG, Brazil
Contact:

Bug on Dbexpoda (TSQLStoredProc, ThousandSeparador and NLS_NUMERIC_CHARACTERS)

Post by romeu » Wed 09 Jan 2008 13:13

Hello,

We use the Dbexpoda (version "4.20.0.6") and we're with a problem (a bug on "Dbexpoda.dll"). To see the bug, read the instructions below:

1) On Oracle, create a StoredProcedure with a numeric parameter for input. Example:
CREATE OR REPLACE PROCEDURE FC_SIMPLE_TEST(INPUT IN FLOAT, OUTPUT OUT FLOAT) IS
BEGIN
SELECT INPUT INTO OUTPUT FROM DUAL;
END FC_SIMPLE_TEST;

2) On Delphi, set DecimalSeparator to '.' and ThousandSeparator to ',' with de code:
SysUtils.DecimalSeparator := '.';
SysUtils.ThousandSeparator := ',';

3) Set NLS_NUMERIC_CHARACTERS with de configuration:
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'

4) Execute a TStoredProcedure pointing to the created stored procedure on item 1;

This couses the message:
---------------------------
Dbxodaerror
---------------------------
SQL Server Error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2
---------------------------

If necessary, we can send a program example.

Thanks,
Romeu Junior - Fácil Informática
Belo Horizonte, MG, Brazil

Plash
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Thu 10 Jan 2008 09:08

We could not reproduce the problem. Please send to odac*crlab*com a complete small sample that demonstrates the problem.

romeu
Posts: 10
Joined: Wed 09 Jan 2008 13:06
Location: Belo Horizonte, MG, Brazil
Contact:

Post by romeu » Thu 10 Jan 2008 11:24

Hi Plash!

With the unit bellow, you can reproduce the problem.
Just execute the "StoredProcTest". See examples:

If SQLConnection uses a DbExpoda (Core Lab) driver:
StoredProcTest(SQLConnection, 12.00, False) -> Result = 12.00
StoredProcTest(SQLConnection, 12.34, False) -> Result = 12.34
StoredProcTest(SQLConnection, 12.00, True) -> Result = 12.00
StoredProcTest(SQLConnection, 12.34, True) -> ORA-06502 exception

If SQLConnection uses a DbExpora (Borland) driver:
StoredProcTest(SQLConnection, 12.00, False) -> Result = 12.00
StoredProcTest(SQLConnection, 12.34, False) -> Result = 12.34
StoredProcTest(SQLConnection, 12.00, True) -> Result = 12.00
StoredProcTest(SQLConnection, 12.34, True) -> Result = 12.34

// ---------------------------------------------------------------------------------
// Init of uDbxOdaViewError.pas
// ---------------------------------------------------------------------------------
unit uDbxOdaViewError;

interface

uses
SqlExpr;

function StoredProcTest(SqlConnection: TSQLConnection; InputValue: Double;
UseLatinSettings: Boolean): Double;

implementation

uses
SysUtils, Windows;

procedure InitLatinSettings(SqlConnection: TSQLConnection);
var
DefaultLCID: Cardinal;
begin
DefaultLCID := Windows.GetThreadLocale;

// --------------------------------------------------------------------------
// DecimalSeparator and ThousandSeparator of Latin Countries.
// --------------------------------------------------------------------------
Windows.SetLocaleInfo(DefaultLCID, Windows.LOCALE_STHOUSAND, ',');
Windows.SetLocaleInfo(DefaultLCID, Windows.LOCALE_SDECIMAL, '.');
// --------------------------------------------------------------------------

SysUtils.GetFormatSettings;

SqlConnection.Execute(
'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ''.,''', nil);
end;

procedure InitDefaultSettings(SqlConnection: TSQLConnection);
var
DefaultLCID: Cardinal;
begin
DefaultLCID := Windows.GetThreadLocale;

// --------------------------------------------------------------------------
// DecimalSeparator and ThousandSeparator of Latin Countries.
// --------------------------------------------------------------------------
Windows.SetLocaleInfo(DefaultLCID, Windows.LOCALE_STHOUSAND, '.');
Windows.SetLocaleInfo(DefaultLCID, Windows.LOCALE_SDECIMAL, ',');
// --------------------------------------------------------------------------

SysUtils.GetFormatSettings;

SqlConnection.Execute(
'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '',.''', nil);
end;

procedure CreateTestStoredProcedure(SqlConnection: TSQLConnection);
const
CREATE_PROCEDURE_FC_SIMPLE_TEST =
'CREATE OR REPLACE PROCEDURE FC_SIMPLE_TEST( ' +
' INPUT IN FLOAT, OUTPUT OUT FLOAT) IS ' +
'BEGIN SELECT INPUT INTO OUTPUT FROM DUAL; ' +
'END FC_SIMPLE_TEST;';
begin
SqlConnection.Execute(CREATE_PROCEDURE_FC_SIMPLE_TEST, nil);
end;

procedure DeleteTestStoredProcedure(SqlConnection: TSQLConnection);
const
DROP_PROCEDURE_FC_SIMPLE_TEST = 'DROP PROCEDURE FC_SIMPLE_TEST';
begin
SqlConnection.Execute(DROP_PROCEDURE_FC_SIMPLE_TEST, nil);
end;

function ExecuteTestStoredProcedure(SqlConnection: TSQLConnection;
InputValue: Double): Double;
var
SQLStoredProc: TSQLStoredProc;
begin
SQLStoredProc := TSQLStoredProc.Create(nil);
try
SQLStoredProc.GetMetadata := False;
SQLStoredProc.SQLConnection := SqlConnection;
SQLStoredProc.StoredProcName := 'FC_SIMPLE_TEST';

SQLStoredProc.Params.ParamByName('INPUT').AsBCD := InputValue;

SQLStoredProc.ExecProc;

Result := SQLStoredProc.Params.ParamByName('OUTPUT').AsFloat;
finally
SQLStoredProc.Free;
end;
end;

(**
* Behaviors of the "StoredProcTest" function:
* 1) If SqlConnection is using a dbexpora (Borland) driver,
* then the program AWAYS work fine
* 2) If SqlConnection is using a dbexpoda (Core Lab) driver, then...
* 2.1) (InputValue = 12.34) and (UseLatinSettings = True) then [FAIL]
* 2.2) (InputValue = 12.00) and (UseLatinSettings = True) then [OK]
* 2.3) (InputValue = 12.34) and (UseLatinSettings = False) then [OK]
* 2.4) (InputValue = 12.00) and (UseLatinSettings = False) then [OK]
**)
function StoredProcTest(SqlConnection: TSQLConnection; InputValue: Double;
UseLatinSettings: Boolean): Double;
begin
if (UseLatinSettings) then begin
// If pass here, the program CAN raise the ORA-06502 exception (see 2.1)
InitLatinSettings(SqlConnection);
end else begin
// If pass here, the program work fine
InitDefaultSettings(SqlConnection);
end;

CreateTestStoredProcedure(SqlConnection);
try
Result := ExecuteTestStoredProcedure(SqlConnection, InputValue);
finally
DeleteTestStoredProcedure(SqlConnection);
end;
end;

end.
// ---------------------------------------------------------------------------------
// End of uDbxOdaViewError.pas
// ---------------------------------------------------------------------------------

I await your reply.
Thanks!

romeu
Posts: 10
Joined: Wed 09 Jan 2008 13:06
Location: Belo Horizonte, MG, Brazil
Contact:

Idented code

Post by romeu » Thu 10 Jan 2008 11:28



unit uDbxOdaViewError;

interface

uses
SqlExpr;

function StoredProcTest(SqlConnection: TSQLConnection; InputValue: Double;
UseLatinSettings: Boolean): Double;

implementation

uses
SysUtils, Windows;

procedure InitLatinSettings(SqlConnection: TSQLConnection);
var
DefaultLCID: Cardinal;
begin
DefaultLCID := Windows.GetThreadLocale;

// --------------------------------------------------------------------------
// DecimalSeparator and ThousandSeparator of Latin Countries.
// --------------------------------------------------------------------------
Windows.SetLocaleInfo(DefaultLCID, Windows.LOCALE_STHOUSAND, ',');
Windows.SetLocaleInfo(DefaultLCID, Windows.LOCALE_SDECIMAL, '.');
// --------------------------------------------------------------------------

SysUtils.GetFormatSettings;

SqlConnection.Execute(
'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ''.,''', nil);
end;

procedure InitDefaultSettings(SqlConnection: TSQLConnection);
var
DefaultLCID: Cardinal;
begin
DefaultLCID := Windows.GetThreadLocale;

// --------------------------------------------------------------------------
// DecimalSeparator and ThousandSeparator of Latin Countries.
// --------------------------------------------------------------------------
Windows.SetLocaleInfo(DefaultLCID, Windows.LOCALE_STHOUSAND, '.');
Windows.SetLocaleInfo(DefaultLCID, Windows.LOCALE_SDECIMAL, ',');
// --------------------------------------------------------------------------

SysUtils.GetFormatSettings;

SqlConnection.Execute(
'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '',.''', nil);
end;

procedure CreateTestStoredProcedure(SqlConnection: TSQLConnection);
const
CREATE_PROCEDURE_FC_SIMPLE_TEST =
'CREATE OR REPLACE PROCEDURE FC_SIMPLE_TEST( ' +
' INPUT IN FLOAT, OUTPUT OUT FLOAT) IS ' +
'BEGIN SELECT INPUT INTO OUTPUT FROM DUAL; ' +
'END FC_SIMPLE_TEST;';
begin
SqlConnection.Execute(CREATE_PROCEDURE_FC_SIMPLE_TEST, nil);
end;

procedure DeleteTestStoredProcedure(SqlConnection: TSQLConnection);
const
DROP_PROCEDURE_FC_SIMPLE_TEST = 'DROP PROCEDURE FC_SIMPLE_TEST';
begin
SqlConnection.Execute(DROP_PROCEDURE_FC_SIMPLE_TEST, nil);
end;

function ExecuteTestStoredProcedure(SqlConnection: TSQLConnection;
InputValue: Double): Double;
var
SQLStoredProc: TSQLStoredProc;
begin
SQLStoredProc := TSQLStoredProc.Create(nil);
try
SQLStoredProc.GetMetadata := False;
SQLStoredProc.SQLConnection := SqlConnection;
SQLStoredProc.StoredProcName := 'FC_SIMPLE_TEST';

SQLStoredProc.Params.ParamByName('INPUT').AsBCD := InputValue;

SQLStoredProc.ExecProc;

Result := SQLStoredProc.Params.ParamByName('OUTPUT').AsFloat;
finally
SQLStoredProc.Free;
end;
end;

(**
* Behaviors of the "StoredProcTest" function:
* 1) If SqlConnection is using a dbexpora (Borland) driver,
* then the program AWAYS work fine
* 2) If SqlConnection is using a dbexpoda (Core Lab) driver, then...
* 2.1) (InputValue = 12.34) and (UseLatinSettings = True) then [FAIL]
* 2.2) (InputValue = 12.00) and (UseLatinSettings = True) then [OK]
* 2.3) (InputValue = 12.34) and (UseLatinSettings = False) then [OK]
* 2.4) (InputValue = 12.00) and (UseLatinSettings = False) then [OK]
**)
function StoredProcTest(SqlConnection: TSQLConnection; InputValue: Double;
UseLatinSettings: Boolean): Double;
begin
if (UseLatinSettings) then begin
// If pass here, the program CAN raise the ORA-06502 exception (see 2.1)
InitLatinSettings(SqlConnection);
end else begin
// If pass here, the program work fine
InitDefaultSettings(SqlConnection);
end;

CreateTestStoredProcedure(SqlConnection);
try
Result := ExecuteTestStoredProcedure(SqlConnection, InputValue);
finally
DeleteTestStoredProcedure(SqlConnection);
end;
end;

end.


Plash
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Fri 11 Jan 2008 09:45

We have reproduced this problem. DbxOda passes BCD parameters to the database as strings. But it uses NLS_NUMERIC_CHARACTERS value that it has read just after connecting to the database.

We are working this problem. As a workaround you can set EnableBCD driver option to False. To set the driver option, you can use TCRSQLConnection component, or execute the following code after connecting to the database:

Code: Select all

const
  coEnableBCD = TSQLConnectionOption(102);
...
  SQLConnection1.SQLConnection.SetOption(coEnableBCD, Integer(False));

romeu
Posts: 10
Joined: Wed 09 Jan 2008 13:06
Location: Belo Horizonte, MG, Brazil
Contact:

Post by romeu » Fri 11 Jan 2008 10:46

Very good!

We will use the work around, but await for the new version with the problem solved.

Is there a date forecast for the new version of Dbexpoda?

Thanks!

romeu
Posts: 10
Joined: Wed 09 Jan 2008 13:06
Location: Belo Horizonte, MG, Brazil
Contact:

Post by romeu » Mon 14 Jan 2008 10:53

Hello again!

Excuse the insistence, but is there a release date of the new version of DBExpoda with the problem solved?

Thanks.

Plash
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Mon 14 Jan 2008 11:52

We are planning to release new DbxOda build in two weeks.

romeu
Posts: 10
Joined: Wed 09 Jan 2008 13:06
Location: Belo Horizonte, MG, Brazil
Contact:

Post by romeu » Tue 15 Jan 2008 12:42

Thanks!

romeu
Posts: 10
Joined: Wed 09 Jan 2008 13:06
Location: Belo Horizonte, MG, Brazil
Contact:

Post by romeu » Wed 06 Feb 2008 16:20

Hi again!

Well, just for remember:
"We are planning to release new DbxOda build in two weeks."

We are awaiting for 3 weeks and 2 days now.
Is there some news about the new version?
Thanks any way!

Plash
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Thu 07 Feb 2008 15:06

Sorry for delay. Now the new build of DbxOda with the fix is released.

romeu
Posts: 10
Joined: Wed 09 Jan 2008 13:06
Location: Belo Horizonte, MG, Brazil
Contact:

Post by romeu » Fri 08 Feb 2008 11:37

Thanks!

Post Reply