Bug on Dbexpoda (TSQLStoredProc, ThousandSeparador and NLS_NUMERIC_CHARACTERS)
Bug on Dbexpoda (TSQLStoredProc, ThousandSeparador and NLS_NUMERIC_CHARACTERS)
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
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
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!
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!
Idented code
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.
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:
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));