Many times we need to determine the data type
of a field, including the precision and decimal places of
a "numeric" type.
In the past we counted on the field's PRECISION and SIZE values.
This worked for IB,FB, SQL SERVER and ORACLE in D7.
Now exploring tuniquery we are getting unexpected Precision values from IB and FB.
Create simple table:
CREATE TABLE TEST (
NUM15 NUMERIC(15,2),
NUM14 NUMERIC(14,2),
NUM13 NUMERIC(13,2),
NUM12 NUMERIC(12,2),
NUM11 NUMERIC(11,2),
NUM10 NUMERIC(10,2),
NUM9 NUMERIC(9,2),
NUM8 NUMERIC(8,2) );
Using tuniconnection <- txquery
with
options.enableBCD:=TRUE;
options.EnableFMTBCD:=TRUE;
Select all fields from table TEST
then check each field's precision and size value.
Example code is below, but what happens is:
Oracle and SQL server produce the values just
as they appeared in the CREATE statement, but IB and FB
do not.
========================================
s:='';
q.sql.add('select * from test');
q.open;
for i:=0 to q.fields.count-1 do
begin
case q.Fields[i].datatype of
ftfmtBCD: w:= tfmtBCDfield(q.Fields[i]).precision;
ftBCD: w:= tBCDfield(q.Fields[i]).precision;
else
w:=0;
end;
s:= s+ 'NUMERIC('+ inttostr(w) + ','+inttostr(q.fields[i].size)+')'#10;
end;
showmessage(s);
Oracle and SQL server produce the values just
as they appeared in the CREATE statement.
BUT the message shown for IB and FB is:
NUMERIC(18,2)
NUMERIC(18,2)
NUMERIC(18,2)
NUMERIC(18,2)
NUMERIC(18,2)
NUMERIC(18,2)
NUMERIC(9,2)
NUMERIC(9,2)
What do you think? Do you get this pattern?
Thank you for all your efforts,
Sincerely,
tonyM
Precision and Size values across Databases
-
AndreyZ