Re: Currency Type problem

David,

did you try using NUMERIC(14,4) instead? It works about the same. Also, 
you may as well declare your monetary type as NUMERIC(18,4) since it 
takes the same space on the hard drive as (14,4), and let's your numbers 
be bigger if you end up with some larger amounts.

Hi

I'm having trouble retrieving meta data for currency types.

I've tried on IB 6.5 and 7.1 with the same results.

To replicate, create a new database and run the following SQL in 2
transactions

/* 1. create the table */
CREATE TABLE "Person" 
(
  "Class"	VARCHAR(32) NOT NULL,
  "Wage"	DECIMAL(14, 4),
 PRIMARY KEY ("Class")
);

/* commit transaction*/

/* 2. now fetch the meta data, we're expecting 2 rows - right ?  */

select   RF.RDB$FIELD_NAME, RF.RDB$NULL_FLAG,   T.RDB$TYPE_NAME, F.
RDB$FIELD_SUB_TYPE, F.RDB$FIELD_LENGTH,   F.RDB$FIELD_SCALE,
F.RDB$CHARACTER_LENGTH 
from   RDB$RELATION_FIELDS RF join   RDB$FIELDS F on
RF.RDB$FIELD_SOURCE = F.
RDB$FIELD_NAME join   RDB$TYPES T on   F.RDB$FIELD_TYPE = T.RDB$TYPE
and T.
RDB$FIELD_NAME = 'RDB$FIELD_TYPE' where   RF.RDB$RELATION_NAME =
'Person' order by   
RF.RDB$FIELD_POSITION;



I'm only getting the first column of the Person table returned.

The problem seems to be there is no definition for type DECIMAL in the
RDB$TYPES table so the join fails.

Can anyone shed any light on this, or provide a work around ?

Thanks

D

David Moorhouse
Moorhouse Works ltd

More info

If I use a left join as in

... left join   RDB$TYPES T on   F.RDB$FIELD_TYPE = T.RDB$TYPE  ...

Then it works (i.e. because the non existant RDB$TYPES record is
ignored).

Is this what I should expect or is it a bug ?


D

David Moorhouse
Moorhouse Works ltd

Hi,

DECIMAL or NUMERIC are stored inside the DB as INT64(Type 16)/LONG (Type 
8) (dialect 3/1).  There is no internal type for DECIMAL / NUMERIC, if 
#digits > #digits in internal structs (INT64/LONG) then data will be 
stored as DOUBLE.

You must examine
rdb$field_type
rdb$field_precision
rdb$field_scale

example:
NUMERIC(10,2)

LONG : -2147483648..2147483647 (10 Digits, 9 are full supported))
INT64: -9223372036854775808..9223372036854775807 (19 Digits, 18 are full 
supported)

Examples;
DECIMAL(9,3) --> Internal LONG
NUMERIC(18,0) and BIGINT are internal the same


PRECISION TYPE                DIALECT 3 DIALECT 1
--------- ----                --------- ---------
1 to 4    NUMERIC             SMALLINT  SMALLINT
1 to 4    DECIMAL             INTEGER   INTEGER
5 to 9    NUMERIC and DECIMAL INTEGER   INTEGER
10 to 18  NUMERIC and DECIMAL BIGINT    DOUBLE PRECISION

Van den Wouwer Danny

David M wrote:
> On Sat, 25 Nov 2006 21:13:30 -0800, Loren Szendre wrote:
> 
> 
>> did you try using NUMERIC(14,4) instead? It works about the same. Also,

>> you may as well declare your monetary type as NUMERIC(18,4) since it 
>> takes the same space on the hard drive as (14,4), and let's your
numbers 
>> be bigger if you end up with some larger amounts.
>>
> 
> Thanks for your suggestion, but the DECIMAL(x,y) datatype stores at
> least x digits - i.e. will hold more if required.  
> Chapter 4 of the Data Defn Guide covers this in some detail.
> {I will need to investigate what is the possible value range we need
> to support.}
> 
> I'm still puzzled though - as to whether this is a bug in the IB meta
> data - or omitted by design.  If so, why ?
> 
> Cheers
> 
> D
> 
> David Moorhouse
> Moorhouse Works ltd

On Sat, 25 Nov 2006 21:13:30 -0800, Loren Szendre wrote:


>did you try using NUMERIC(14,4) instead? It works about the same. Also, 
>you may as well declare your monetary type as NUMERIC(18,4) since it 
>takes the same space on the hard drive as (14,4), and let's your numbers 
>be bigger if you end up with some larger amounts.
>

Thanks for your suggestion, but the DECIMAL(x,y) datatype stores at
least x digits - i.e. will hold more if required.  
Chapter 4 of the Data Defn Guide covers this in some detail.
{I will need to investigate what is the possible value range we need
to support.}

I'm still puzzled though - as to whether this is a bug in the IB meta
data - or omitted by design.  If so, why ?

Cheers

D

David Moorhouse
Moorhouse Works ltd