This was posted in the dbase.sql-servers news group and I was asked to
post it here.
Subject: bug in StoredProc-Object: numerical input parameters
From: Lysander <nobody@nowhere.de>
Date: Mon, 07 Apr 2008 10:45:49 +0200
Newsgroups: dbase.sql-servers
Message-ID: <d3IOe6ImIHA.644@news-server>
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
release object db
--
Geoff Wass [dBVIPS]
Montréal, Québec, Canada
.|.|.| dBASE info at http://geocities.com/geoff_wass |.|.|.
.|.|.| ---------------------------------------------------------- |.|.|.
|