bug in StoredProc-Object: numerical input parameters

There is a very bad bug, which (after thourough testing) seems to be in 
the stored proc object of dBL. You don't know how lucky I have been to 
not have been bitten by that before... :)

The bug was found by Jaime Escobal, and during the last week we did some 
tests corresponding via e-mail. Tests have been done with several dBL 
versions including 2.6.1.3, different Firebird versions, and different 
brands of ODBC drivers (next to the IBPhoenix driver).

Examples for code are below!


What you need to produce this bug:
1.) A stored proc has to receive any type of decimal value as input
2.) You are using a storedproc object in dBL
3.) You hand an integer instead of a decimal to the SP object.

Note: if the param is built dynamically during the run of your 
application, there always is a chance that a decimal number without 
decimals is produced. (24.75/8.25 for example).
So, the danger is quite real!!!


Effects of the bug:
The SP object in dBL will "pad" the input parameter with a number of 
digits according to the number of decimal places requested by the server.
If the server requests 2 decimal digits, the SP object will transfer for 
example 100 instead of 1.00.
If the server requests 4 decimal digits, the SP object will transfer for 
example 10000 instead of 1.0000

This of course is desastrous because the server turns 100 into 100.00 
and 10000 into 10000.0000


temporary workarounds:
a.) always make parameters have decimal places (n + 0.00)
Example:
     // n is entered at runtime only!!!
     n = form.Entryfield1.Value / 2.5
     StoredProc1.Params["NVALUE1"].Value = n + 0.00
By adding 0.00 n will automatically be treated as decimal in dBL.
0.00 (2 decimal digits) also works for requested 4-digit-decimals...
The only important thing is: do not hand an integer to the SP object.

b.) Instead of the SP object, which seems quite broken since version 7.5 
anyway, do use a query object and hand the procedure call to the 
sql-string. This is easy with Firebird, but I don't know if this also 
works with other servers.

Example using only one requested decimal value:
      Query1.SQL = "Select OutputParameter from
StoredProcName("+n+")"
or, alternatively, by using query-params:
     Query1.SQL = "Select OutputParameter from StoredProcName(:nVar)"
     Query1.Params["nVar"].Value = n


~~~~~~~~~~~~~~~~~~~~~~~~~~

Code for the Firebird StoredProc:

CREATE PROCEDURE TEST_NUMERICS (
     NVALUE1 DECIMAL(15,2),
     NVALUE2 DECIMAL(15,2))
RETURNS (
     OP_VALUE1 NUMERIC(15,2),
     OP_VALUE2 NUMERIC(15,2))
AS
begin
   op_value1 = :nvalue1;
   op_value2 = :nvalue2;
   suspend;
end


Code in dBL:

    db = new database()
    db.databaseName := "BDEAliasName"
    db.loginstring = "SYSDBA/masterkey"
    db.active := true

    sp = new storedProc()
    sp.database := db
    sp.procedureName := "Test_Numerics"

    db.begintrans()

    local a
    local b

    a = 1 + 1
    b = 1 + 4

    sp.params["NVALUE1"].value = a
    sp.params["NVALUE2"].value = b

    sp.active := true

    db.commit()

    ? sp.rowset.fields[1].value
    ? sp.rowset.fields[2].value

    sp.active := false
    release object sp

    db.active := false

André,

Has this been provided to dBI? If not post in the dbase.bug-reports news 
group as they may not notice it in this news group. Danke!

-- 
Geoff Wass [dBVIPS]
Montréal, Québec, Canada

.|.|.|        dBASE info at http://geocities.com/geoff_wass       |.|.|.
.|.|.| ---------------------------------------------------------- |.|.|.

Geoff Wass [dBVIPS] schrieb:

> Has this been provided to dBI? If not post in the dbase.bug-reports news 
> group as they may not notice it in this news group. Danke!

Would you be so kind to copy this over for me?
I am not reading/posting there.

In article <ChhTMoXmIHA.644@news-server>, nobody@nowhere.de says...
> Geoff Wass [dBVIPS] schrieb:
> 
> > Has this been provided to dBI? If not post in the dbase.bug-reports
news 
> > group as they may not notice it in this news group. Danke!
> 
> Would you be so kind to copy this over for me?
> I am not reading/posting there.
> 
> thanks!

 
André,

It's done. Thanks.

-- 
Geoff Wass [dBVIPS]
Montréal, Québec, Canada

.|.|.|        dBASE info at http://geocities.com/geoff_wass       |.|.|.
.|.|.| ---------------------------------------------------------- |.|.|.