Why BCD Overflow?

sphere · Post by **sphere** » Fri 07 Apr 2006 19:19

hello,
It seems i have a problem with TSQLStoredProc.
I have a stored procedure on Oracle, just the easy one with only one integer input param.
Let's use the following easy stored procedure on Oracle:

CREATE OR REPLACE PROCEDURE SP_TEST
( I_OPTYPE Integer) IS
BEGIN NULL; END;

I user: TSQLStoredProc(sp);

If i use like this:
sp.ParamByName('I_OPTYPE').AsInteger = 1, i get a BCD OverFlow error

If i use like this:
sp.ParamByName('I_OPTYPE').AsFTMBCD = IntegerToBcd(1) it works fine.

sp.ExecProc;

Why it doesn't work with AsInteger? or maybe i'm doing something wrong?

i'm using Kylix and dbexpress 3.0

Thanks

Challenger · Post by **Challenger** » Tue 11 Apr 2006 11:31

We could't reproduce this problem. Please specify version of Kylix you use.

EAG35 · Post by **EAG35** » Wed 31 May 2006 07:49

Hi,
I have the same error !

If I set the EnableBCD to false , nothing change.

Where can I see the Kylix version (what is Kylix ?) .

If I use the TCRSQLConnection instead of TSQLConnection, it is the same error.

Thanks for any help .

EAG35 · Post by **EAG35** » Wed 31 May 2006 08:11

I found that Kylix is for Linux platform !

I work on Windows XP platform and
I use :
Borland Builder 2006
Oracle 10 Express edition
DbExpress 3.0.3

Challenger · Post by **Challenger** » Wed 31 May 2006 15:10

This error happens because parameter type (ftBCD) doesn't match parameter value (ftInteger). When
dbExpress sets the parameter it passes type of parameter, not the type of value that is stored in
the buffer. So there is no possibility for driver to determine what value is stored in the buffer.
That's why when the driver tries to get BCD number from the buffer that stores integer value, the BCD overflow error is raised. The standard Oracle dbExpress driver has the same behavior. Therefore to avoid this problem you should either assign values that match parameter datatype or use TSQLQuery.

EAG35 · Post by **EAG35** » Thu 01 Jun 2006 13:50

Thanks for the answer.

I cannot change the database structure

I have an existing application developped under Builder5
and I convert this application to Builder 2006

So I would like to replace the BDE Builder component
by the DbExpress whith the minimum changing.

a lot of stored procedures exist in the existing oracle database
whith INTEGER type parameter.

in the object inspector the DataType is automatically replace
by ftBCD DataType.

if I call
pMyStoredProc->ParamByName("I_MYINTEGER")->AsBCD = myInteger;

I have a Oracle error ( ORA-06512).

If I call
pMyStoredProc->ParamByName("I_MYINTEGER")->AsInteger = myInteger;

I have a BCD Overflow.

Is it a way to resolve my problem ?

Challenger · Post by **Challenger** » Thu 01 Jun 2006 14:07

You can try to use EnableBCD option. When EnableBCD option is turned on all numeric parameters are represented as bcd field types. Otherwise as integer and float parameter types.

EAG35 · Post by **EAG35** » Thu 01 Jun 2006 14:58

I already try to set this parameter to False
but nothing change .
I tried to use the TCRSQLConnection component instead of TSQLConnection , but
in the Builder object inspector, the DataType of an integer is translated as a ftBCD.

Challenger · Post by **Challenger** » Mon 05 Jun 2006 14:20

Please try to disconnect CRSQLConnection, set EnableBCD property to False, delete all parameters from TSQLStoredProc. When you open "Edit Parameters" dialog next time parameter types should be ftFloat and ftInteger.