Subject Re: [ib-support] Re: decimal / numeric
Author Claudio Valderrama C.
""csswa"" <csswa@...> wrote in message
> --- In ib-support@y..., "Claudio Valderrama C." <cvalde@u...> wrote:
> > You have used it all the time with IB/FB.
> > :-)
> Ah-ha. So that's why IBconsole reports the field as numeric (in
> metadata) whereas IB-SQL reports it as decimal.

Have you tried isql?

SQL> create table z(a decimal, b numeric);
SQL> show table z;
A DECIMAL(9, 0) Nullable
B NUMERIC(9, 0) Nullable

IBConsole is a thing partly based on isql, partly based on new bugs (on top
of isql's known metadata extraction bugs) and may have evolved into its own
code. Since the engine doesn't care too much about both types... it only put
a value in system tables to tell the difference between numeric and decimal
in version 6. In previous versions, even the engine can't tell which was the
original declaration. You didn't say if your db was upgraded from v5 or was
created in ib6.

Against the same table shown above, IB_SQL says in the Source tab:


Hmmm, something fishy here. However, from a practical standpoint, a
numeric/decimal in the current implementation of the engine that has
precision between 5 and 9 and has scale zero is equivalent to an integer, is
stored as an integer and behaves like an integer. If I make the columns
numeric(11,0), ib_sql will say that the columns are int64, that's currently
correct, again from a practical POV, but that's wrong in formal terms.
Another example:

SQL> create table z3(a decimal(9,3), b numeric(9,3));
SQL> show table z3;
A DECIMAL(9, 3) Nullable
B NUMERIC(9, 3) Nullable

But ib_sql says:
A) Source tab:
A DECIMAL ( 9, 3 )
, B DECIMAL ( 9, 3 )

B) Fields tab:
A=> numeric(9,3)
B=> numeric(9,3)

Now, let's try IB-Console. How do you want to be consoled tonight? Metadata

"A" DECIMAL(9, 3),
"B" NUMERIC(9, 3)

At least the IBConsole version I have (338) predates the change I did to
isql to not quote names if they don't need to be quoted, but the field types
are okay. It's a consolation to know that IBConsole does something well. <g>

It seems that I got different results than you, Andrew.

Claudio Valderrama C. - -
Independent developer
Owner of the Interbase® WebRing