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:

 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


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


Testing from EDB Mgr, they both work fine:

 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(12, 2);
     CALL ReturnDecimal(DecVal);
     SET LOG MESSAGE TO 'Result = ' + CAST(DecVal AS VARCHAR);
   END


However, when a Delphi program tries to use these values, I found the
function call to round off.  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.

I have found no way to get the decimal digits from the query that calls
a function.

Of course the work-around is to just write a stored procedure instead!


Did I miss a property somewhere?

--
David Cornelius
CorneliusConcepts.com

Furthermore, you can't set the scale of a DECIMAL/NUMERIC result type
of a function using EDB Manager's function edit dialog.  It always sets
it to zero.  You have to use SQL.  And even then, the display box that
contains a summary shows the scale at 0, while the SQL to its right
will show the correct scale.

--
David Cornelius
CorneliusConcepts.com

David


I think you're slightly wrong - try this sql in your EDBQuery

SELECT 100 * DecimalValue()  as value
 FROM Configuration.DataTypes
 WHERE NAME = 'Char'

You get 120

I've tried a few more

cast (Decimalvalue() as varchar(10)) as casted -> 1.2
1 * DecimalValue()  as value -> 1
1.00 * DecimalValue()  as value -> 1.2
cast(  DecimalValue()  as float) as value -> 1.2

So my guess is that rather than "somewhere between the TEDBQuery and the EDB Function" its the query not working out the type correctly

Roy Lambert

David,

<< Furthermore, you can't set the scale of a DECIMAL/NUMERIC result type of
a function using EDB Manager's function edit dialog.  It always sets it to
zero.  You have to use SQL.  And even then, the display box that contains a
summary shows the scale at 0, while the SQL to its right will show the
correct scale. >>

This is a display bug, and I'll make sure that it's fixed for the next
release.  If you set the value and then click on OK, it will be set properly
in the function that is created/altered.

--
Tim Young
Elevate Software
www.elevatesoft.com

David,

<< However, when a Delphi program tries to use these values, >>

The EDB Manager *is* a Delphi program, so something else must be going on in
the code.

The problem is that your persistent TField for the query has its Size
property set to 0 instead of 2.  The Size property is the scale for
DECIMAL/NUMERIC types, so changing it to 2 will fix the issue in your sample
application.

However, the root of the problem was that the Information schema was
returning incorrect Scale information via the Functions table.  This was
also the cause of the display issue in the EDB Manager.  Furthermore, there
was an issue with the loading the Scale and Collation information for return
values from functions.

Both of these issues will be fixed in the next minor release.

--
Tim Young
Elevate Software
www.elevatesoft.com

> The EDB Manager is a Delphi program, so something else must be going
> on in the code.

Why do I keep forgetting this?


> The problem is that your persistent TField for the query has its Size
> property set to 0 instead of 2.  The Size property is the scale for
> DECIMAL/NUMERIC types, so changing it to 2 will fix the issue in your
> sample application.

Ah!  Size = Scale.  I would not have guessed it.


> However, the root of the problem was that the Information schema was
> returning incorrect Scale information via the Functions table.  This
> was also the cause of the display issue in the EDB Manager.
> Furthermore, there was an issue with the loading the Scale and
> Collation information for return values from functions.
>
> Both of these issues will be fixed in the next minor release.

So I wasn't completely off base.    Thanks much!

--
David Cornelius
CorneliusConcepts.com

> The problem is that your persistent TField for the query has its Size
> property set to 0 instead of 2.  The Size property is the scale for
> DECIMAL/NUMERIC types, so changing it to 2 will fix the issue in your
> sample application.

I went back to the sample application and changed the field's size
property to 2, but got a run-time error:  "EDBQuery: Size mismatch for
field "Value", expecting: 2, actual: 0."

--
David Cornelius
CorneliusConcepts.com

> I think you're slightly wrong - try this sql in your EDBQuery
>
> SELECT 100 * DecimalValue()  as value
>   FROM Configuration.DataTypes
>   WHERE NAME = 'Char'
>
> You get 120
>
> I've tried a few more
>
> cast (Decimalvalue() as varchar(10)) as casted -> 1.2
> 1 * DecimalValue()  as value -> 1
> 1.00 * DecimalValue()  as value -> 1.2
> cast(  DecimalValue()  as float) as value -> 1.2
>
> So my guess is that rather than "somewhere between the TEDBQuery and
> the EDB Function" its the query not working out the type correctly

The SQL isn't the problem, as I showed with my SQL samples.  And I know
I can cast it to something else, but I don't want to.  What I was
saying is that the EDBQuery's field isn't getting the digits past the
decimal when I return a floating point value from a function, but it is
from a stored procedure.  Therefore, some key piece of information is
not getting from an EDB function to a EDBQuery's TField, which is why I
said "somewhere between the TEDBQuery and the EDB Function."

--
David Cornelius
CorneliusConcepts.com

David,

<< So I wasn't completely off base.    Thanks much! >>

No, not at all - it is definitely a bug.

--
Tim Young
Elevate Software
www.elevatesoft.com

David,

<< I went back to the sample application and changed the field's size
property to 2, but got a run-time error:  "EDBQuery: Size mismatch for field
"Value", expecting: 2, actual: 0." >>

Yes, sorry about that - you may have an issue with changing the value.  I'm
afraid you'll have to wait until the bug fixes are ready, which hopefully
won't be much longer.

--
Tim Young
Elevate Software
www.elevatesoft.com