Board index » delphi » BCD overflow error with Oracle calculated fields

2003-12-16 04:39:50 PM
delphi226
Hia all,
some of you might know the problem when you issue a statement like
select 1/3 from dual
to an Oracle DB from within a TSQLDataSet: .Open works perfectly, but as
soon as you try to get the value from Fields[0], as whatever type you
try (.AsFloat, .AsString, .AsVariant), you end up with a EDatabaseError
exception saying 'BCD overflow'.
The reason is somewhere in the NormalizeBcd() function in unit FMTBcd.
In the given case, it is called to convert the InBCD (Precision: 41,
SignSpecialPlaces: 40) into an OutBCD using parameters Prec = 32 and
Scale = 8.
Since output precision (the place available, if I understand that
correctly) is smaller then input precision, the following statement
if (Prec < Word(InBcd.Precision))
and (SignificantIntDigits(pIn, Word(InBcd.Precision))>Prec) then
Result := False
sets Result to false which later causes the exception.
Although I don't know exactly what SignificantIntDigits should do, I
assume it should return the number of digits before the decimal point.
In that case, the 2nd parameter of would be wrong and should be replaced by
Word(InBcd.Precision - DecDigits)
I've changed this (well, not in the Borland unit, I use a kind of
'runtime patch' which simply changes the code to a jump to a replacement
function), and it seems to work well.
Question (1) is: Is my assumption above right, or will I run into other
problems?
There are some other bugs or restrictions with this FMTBcd stuff. One is
that e. g. 1E31 (in Oracle) is returned as 1E23 in Delphi (that makes it
impossible to work with such numbers abow 1E23, of course), the other
one is that 1E32 and above end up with that 'BCD overflow' exception again.
What I didn't mention yet is that in my current project I have to read
from external DBs (i. e. I can not manipulate their field structure)
whatever fields there are.
Question (2): How can I force Delphi/DBExpress to create a Double field
instead of this FMTBcd for Oracle NUMBER fields?
TIA
-Michael
 

Have you tried it with dbExpress Plus? This has solved the problem
in most cases.
Michael Winter writes:
Thomas Miller
Delphi Client/Server Certified Developer
BSS Accounting & Distribution Software
BSS Enterprise Accounting FrameWork
www.bss-software.com
sourceforge.net/projects/dbexpressplus
 

Michael Winter writes:
they do.
SQLConnection unless you specifically open it. This is the
way it should be for good database programming style. It however
is a pain when doing quick an dirty stuff, but will save you
big time when working on a large project.
try to cast it as a specific type. I'd have to look that
syntax up for Oracle.
Thomas Miller
Delphi Client/Server Certified Developer
BSS Accounting & Distribution Software
BSS Enterprise Accounting FrameWork
www.bss-software.com
sourceforge.net/projects/dbexpressplus
 

Thomas Miller writes:
I've tried that now, but unfortunately with no success. I did the following:
I used an SQLConnectionPlus and an SQLDatasetPlus component. Version
property says 1.10 Build 1. All Conneted properties are false.
procedure TForm1.Button1Click(Sender: TObject);
begin
SQLDataSetPlus1.CommandText := 'select 1/3 from dual';
SQLDataSetPlus1.Open;
ShowMessage(SQLDataSetPlus1.Fields[0].AsString);
SQLDataSetPlus1.Close;
end;
BTW, why does this raise an 'SQLConnection not active. Unable to
contiue.' error at the Open line?
Okay, I have added
SQLConnectionPlus1.Open;
before that, and now I receive the 'BCD overflow' exception at the
AsString line just like with the original Borland SQLDataSet.
Any idea?
-Michael
 

Hello Thomas,
that's fast: you answered eight minutes before i asked ;-).
Thomas Miller writes:
means the component ignores the Connected property from the .dfm file.
But it turns out that Connection.ConnectedAtRuntime must be true to
enable the Dataset instance to tell the Connection instance to connect
when Dataset is opened.
However, that is not the problem here.
-Michael
 

Michael Winter writes:
and another live, or doing a demo, or what ever. The user / passwork
is often different too. So dbExpress Plus tries to save you from
your self, requiring you to login to the database in a separate step.
I had a really bad experience at a demo because of not having this
feature in regular TSQLConnection.
should be some default rounding or something in the framework, but there
isn't. I almost forgot, did you set TSQLConnectionPlus to always
use FmtBCD?
--
Thomas Miller
Delphi Client/Server Certified Developer
BSS Accounting & Distribution Software
BSS Enterprise Accounting FrameWork
www.bss-software.com
sourceforge.net/projects/dbexpressplus
 

Thomas Miller writes:
thread, and only wanted to hear someone's 'okay, that makes sense'. In
short, I assume a bug in the NormalizeBcd() function in unit FMTBcd.
The other question was whether there is a chance to change the field
type for Oracle Float fields away from ftFmtBcd to ftFloat or something
more native.
TSQLQueryPlus, and .AsString:
[] or [coBcdToFMTBcd]: DataType is ftFMTBcd, exception BCD overflow
[coBcdToFloat]: DataType is ftFloat, Result is 6.37897E-305
Both are not very satisfying.
-Michael
 

Michael Winter writes:
-Michael
 

Send me the first 300 lines of your SQLExpr.pas file. I need to verify
you have the source code before sending you this. Obviously, send
this to me via private email.
Michael Winter writes:
Thomas Miller
Delphi Client/Server Certified Developer
BSS Accounting & Distribution Software
BSS Enterprise Accounting FrameWork
www.bss-software.com
sourceforge.net/projects/dbexpressplus