Icon View Incident Report

Serious Serious
Reported By: David Cornelius
Reported On: 8/12/2008
For: Version 2.01 Build 4
# 2748 Functions That Return DECIMAL/NUMERIC Values with a Scale Round Off Values Incorrectly

A customer was complaining that in one area of their new program, a total amount was always rounded off, but in another area, it was accurate. I looked and ran SQL statements manually and couldn't figure out what was going on. Then I noticed the result type returned in Delphi of an EDB function call always returned whole amounts. I tested results in SQL and it was fine.

It took me several hours to figure this one out, but I finally narrowed it down to this: somewhere between the TEDBQuery and the EDB Function returning a DECIMAL(20 2), the scale gets set to 0. However, the exact same scenario returned in a stored procedure parameter works fine.

To illustrate, I created a test database with nothing other than a function returning 1.2 and a stored procedure returning 1.3:

Testing from EDB Mgr, they both work fine:

However, when a Delphi program tries to use these values, I found the function call to round off.

 SCRIPT
 BEGIN
   EXECUTE IMMEDIATE 
    'CREATE FUNCTION "DecimalValue" ()
     RETURNS DECIMAL(20,2)
     BEGIN
       RETURN 1.2;
     END';
  
   EXECUTE IMMEDIATE 
    'CREATE PROCEDURE "ReturnDecimal" (OUT "Value" DECIMAL(20,2))
     BEGIN
       SET Value = 1.3;
     END';   
 END

 Single statement to test the Function:
   SELECT DecimalValue() 
   FROM Configuration.DataTypes 
   WHERE NAME = 'Char';

 Script to test the Stored Procedure:
   SCRIPT
   BEGIN  
     DECLARE DecVal DECIMAL(20,2);
     CALL ReturnDecimal(DecVal);
     SET LOG MESSAGE TO 'Result = ' + CAST(DecVal AS VARCHAR); 
   END

Create a delphi program and setup the engine, session, and database.  Then place both a TEDBQuery and a TEDBStoreProc component on the form. Set the query's SQL value to:

 SELECT DecimalValue() 
 FROM Configuration.DataTypes 
 WHERE NAME = 'Char' --anything to return a row

and point the stored procedure to point accordingly.  (Both should prepare CURRENCY parameters.)  Then set a button's OnClick event to the following:

 var
   FNVal: Currency;
   SPVal: Currency;
 begin
   // get value from function
   EDBQuery.Open;
   FNVal := EDBQuery.FieldByName('Value').AsCurrency;

   // get value from stored procedure
   EDBStoredProc.ExecProc;
   SPVal := EDBStoredProc.ParamByName('Value').AsCurrency;

   // show results
   MessageDlg(Format('Function call returned: %5.2f'#13#10 +
                     'Stored procedure returned: %5.2f',
                     [FNVal, SPVal]), mtInformation, [mbOK], 0);
 end;

 (--ALL CODE CAN BE FOUND IN THE BINARIES NEWSGROUP--)

What you SHOULD get is 1.3 and 1.2, respectively.
Instead, you get 1.0 and 1.2.



Resolution Resolution
Fixed Problem on 8/13/2008 in version 2.01 build 5


Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

Image