I am using this query to calculate a value (Punkte = integer, Punktwert = float, Verlust = float):

   Punktwert := 1.1;
   with dm.ActionQuery do
   begin
      sql.add('update Gewinnanteile set Verlust = Punkte * :Punktwert );
      ...
      ParamByName('Punktwert').asFloat := Punktwert;
      ExecSQL;
   end;

Amazingly the result is wrong, it seems to be rounded (the result is 100 while Punkte = 100).

When hardcoding the value like this:

   with dm.ActionQuery do
   begin
      sql.add('update Gewinnanteile set Verlust = Punkte * 1.1 );
      ...
      ParamByName('Punktwert').asFloat := Punktwert;
      ExecSQL;
   end;

The result of the "Verlust"-Column is 101 like expected.

What is wrong here?

Uli

Uli,

<< I am using this query to calculate a value (Punkte = integer, Punktwert =
float, Verlust = float):

   Punktwert := 1.1;
   with dm.ActionQuery do
   begin
      sql.add('update Gewinnanteile set Verlust = Punkte * :Punktwert );
      ...
      ParamByName('Punktwert').asFloat := Punktwert;
      ExecSQL;
   end;

Amazingly the result is wrong, it seems to be rounded (the result is 100
while Punkte = 100). >>

Actually, it's not really amazing once you consider that EDB is expecting
the :Punktwert parameter to be an INTEGER, not a FLOAT type (not yelling,
just using SQL convention for casing .  Use this instead:

update Gewinnanteile set Verlust = CAST(Punkte AS FLOAT) * :Punktwert

and you'll get the desired result.

EDB "figures out" the parameter data types based upon the operation in which
the parameter is used, and sometimes you need to explicitly tell EDB what
parameter type it should use by a CAST().

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<
Actually, it's not really amazing once you consider that EDB is expecting
the :Punktwert parameter to be an INTEGER, not a FLOAT type (not yelling,
just using SQL convention for casing .
>>

Hmm... I don't understand this at all.

1. The column "Punkte" is the only integer-field in this calculation - the result-field "Verlust" is a float-field!
So: why should EDB assume that the result has to be an integer value?
2. And: why does the same query work with the hard-coded value? Since the param-type of punktwert is given, it should be the same result.
3. You write <<EDB is expecting the :Punktwert parameter to be an INTEGER, not a FLOAT type...>> How about
"ParamByName('Punktwert').asFloat := Punktwert;"?

Regards Uli

Uli,

<< 1. The column "Punkte" is the only integer-field in this calculation -
the result-field "Verlust" is a float-field! >>

Yes, but EDB does not determine the parameter based upon where the result of
an expression is being assigned.  It can't, really.  At least not without a
50% chance of still being wrong.

<< So: why should EDB assume that the result has to be an integer value? >>

It has to assume so because it has to derive the data type from something,
and in this case it does so by looking at the data type of the other value
involved in the calculation.

<< 2. And: why does the same query work with the hard-coded value? >>

Because EDB does not have to figure out the data type in that case.  There's
an actual value being used, not a parameter marker.

<< Since the param-type of punktwert is given, it should be the same result.
>>

You're thinking of DBISAM here.  DBISAM did not figure out parameter data
types automatically, whereas EDB does.  The primary reason for this behavior
in EDB is the ODBC driver and other client interfaces.  They work optimally
when the data type of parameters is determined by the engine and is not
provided by the client.

<< 3. You write <<EDB is expecting the :Punktwert parameter to be an
INTEGER, not a FLOAT type...>> How about "ParamByName('Punktwert').asFloat
:= Punktwert;"? >>

Again, the parameter data type that you assign in Delphi is a different
thing than the data type that EDB assigns the parameter during compilation
of the query.  To force the parameter type to be a specific data type,
instead of the inferred type, you must use the syntax that I showed you with
the CAST.  Just setting the parameter's data type in Delphi is not enough.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

thank you for the detailed explanation. I try to understand this all
(though it will be hard for me

Regards Uli

Uli,

<< thank you for the detailed explanation. I try to understand this all
(though it will be hard for me >>

Yes, it's especially hard since a lot of the reasoning behind this isn't
something that a Delphi developer would know/care about.  It's mainly a
multi-interface type of issue.

--
Tim Young
Elevate Software
www.elevatesoft.com