FMTBcd round issue

alexer · Post by **alexer** » Tue 16 Aug 2011 10:11

Delphi7+dbexpsda(ver 4.80)

procedure TForm1.Button1Click(Sender: TObject);
begin
  SQLConnection1.Open;
  SQLStoredProc1.SQLConnection:=SQLConnection1;
  SQLStoredProc1.StoredProcName:='FU_TEST';
  SQLStoredProc1.ExecProc;
  ShowMessage(SQLStoredProc1.Params[1].AsString);
end;

SQL Server 2008R2

Code: Select all

ALTER procedure [dbo].[FU_TEST](
  @REST_AMOUNT numeric(22,7) output
) as begin
   set @REST_AMOUNT = 4.95904
end

Returns 4.959

Code: Select all

ALTER procedure [dbo].[FU_TEST](
  @REST_AMOUNT double precision output
) as begin
   set @REST_AMOUNT = 4.95904
end

Returns 4.95904

Dimon · Post by **Dimon** » Wed 17 Aug 2011 10:23

To solve the problem set the EnableBCD option to False like this:

Code: Select all

  SQLConnection.Params.Values['EnableBCD'] := 'False';

alexer · Post by **alexer** » Wed 17 Aug 2011 17:28

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  SQLConnection1.Params.Values['EnableBCD'] := 'False';
  SQLConnection1.Open;
  SQLStoredProc1.SQLConnection:=SQLConnection1;
  SQLStoredProc1.StoredProcName:='FU_TEST';
  SQLStoredProc1.ExecProc;
  ShowMessage(SQLStoredProc1.Params[1].AsString);
end;

Result still 4.959
Please check it.

P.S. If i disable BCD how Delphi will work with numeric(22,7)? Float doesnt have enough scale for it.

Dimon · Post by **Dimon** » Thu 18 Aug 2011 07:09

The problem is that the EnabledBCD option is an extended option of DbxSda. Delphi 7 has restriction of dbExpress for setting such options.
In order to overcome restrictions of dbExpress, DbxSda provides the TCRSQLConnection component. Extended options are available with TSQLConnection only at run time. To convert quickly from TSQLConnection to TCRSQLConnection you can use the "Convert to TCRSQLConnection" item of component's pop-up menu.

If you need to use TSQLConnection, then you should use the following code at run time:

Code: Select all

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

You can find more detailed information about it in Readme.html.

alexer · Post by **alexer** » Thu 18 Aug 2011 07:47

Whats about my P.S?
As i say:
MSSQL

Code: Select all

ALTER procedure [dbo].[FU_TEST](
  @REST_AMOUNT numeric(22,7) output
) as begin
   set @REST_AMOUNT = 12345678901234.1234567
end

Delphi7

Code: Select all

  SQLConnection1.Open;
  SQLConnection1.SQLConnection.SetOption(coEnableBCD, Integer(False));
  SQLStoredProc1.SQLConnection:=SQLConnection1;
  SQLStoredProc1.StoredProcName:='FU_TEST';
  SQLStoredProc1.ExecProc;
  ShowMessage(SQLStoredProc1.Params[1].AsString);

Returns 12345678901234.1

We really need normal FMTBcd!

Same problem was with your Oracle driver, it was fixed.

Dimon · Post by **Dimon** » Thu 18 Aug 2011 08:26

The point is that TSQLStoredProc passes the fldBCD data type for the REST_AMOUNT parameter instead of fldFMTBCD to our driver. You can see this processing in the SetProcedureParams procedure of the SqlExpr unit. Therefore SQL Server returns the parameter value as Currency and truncates the data.
We cannot influence this behaviour of TSQLStoredProc.

alexer · Post by **alexer** » Thu 18 Aug 2011 10:37

But you can force field change from Bcd to FMTBcd on prepare, it will be more correct

alexer · Post by **alexer** » Mon 22 Aug 2011 09:29

Why you fix this bug in Oracle driver, but dont want to fix it in MS?

Dimon · Post by **Dimon** » Mon 22 Aug 2011 14:23

We have fixed this problem. This fix will be included in the next DbxSda build.

alexer · Post by **alexer** » Mon 22 Aug 2011 17:17

Thanks