Board index » delphi » How to call TIBDatabase.Has_Default_Value

How to call TIBDatabase.Has_Default_Value

Hi,
How do i call TIBDatabase.Has_Default_Value?
I'm pretty sure the field AMOUNT of table CUSTOMORDER has a defaultvalue.
But TIBDatabase.Has_Default_Value('CUSTOMORDER', 'AMOUNT') or
TIBDatabase.Has_Default_Value('"CUSTOMORDER"', '"AMOUNT"') give False.
I even try to pad the table and field name to 31 characters, but still
return value False.

Thanks,
Robert.

 

Re:How to call TIBDatabase.Has_Default_Value


IIRC it is exactly as the API gives this information back so is no quotes, case
sensitive and probably right padded to 31 characters.  I set and compare the
values directly from the API results so never worried about other people
checking, it was designed to be a performance improvement for building
TFieldDefs.

Quote
Robert wrote:

> Hi,
> How do i call TIBDatabase.Has_Default_Value?
> I'm pretty sure the field AMOUNT of table CUSTOMORDER has a defaultvalue.
> But TIBDatabase.Has_Default_Value('CUSTOMORDER', 'AMOUNT') or
> TIBDatabase.Has_Default_Value('"CUSTOMORDER"', '"AMOUNT"') give False.
> I even try to pad the table and field name to 31 characters, but still
> return value False.

> Thanks,
> Robert.

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
This sad little lizard told me that he was a brontosaurus on his mother's
side.  I did not laugh; people who boast of ancestry often have little else
to sustain them.  Humoring them costs nothing and adds to happiness in
a world in which happiness is in short supply.   (RAH)

Re:How to call TIBDatabase.Has_Default_Value


Quote
> IIRC it is exactly as the API gives this information back so is no quotes,
case
> sensitive and probably right padded to 31 characters.  I set and compare
the
> values directly from the API results so never worried about other people
> checking, it was designed to be a performance improvement for building
> TFieldDefs.

I'm not sure if HAS_DEFAULT_VALUE works properly. In the IBX code I see that
the statement

  DefaultSQL = 'Select F.RDB$COMPUTED_BLR, ' + {do not localize}
               'F.RDB$DEFAULT_VALUE, R.RDB$FIELD_NAME ' + {do not localize}
               'from RDB$RELATION_FIELDS R, RDB$FIELDS F ' + {do not
localize}
               'where R.RDB$RELATION_NAME = :RELATION ' +  {do not localize}
               'and R.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME '+ {do not
localize}
               'and ((not F.RDB$COMPUTED_BLR is NULL) or ' + {do not
localize}
               '     (not F.RDB$DEFAULT_VALUE is NULL)) '; {do not localize}

is executed. In my case this query will return zero rows. If
F.RDB$DEFAULT_VALUE is replaced with R.RDB$DEFAULT_VALUE then there will be
rows with default values. It seem you have to get de default value from
RDB$RELATION_FIELDS, not RDB$FIELDS.

Quote

> Robert wrote:

> > Hi,
> > How do i call TIBDatabase.Has_Default_Value?
> > I'm pretty sure the field AMOUNT of table CUSTOMORDER has a
defaultvalue.
> > But TIBDatabase.Has_Default_Value('CUSTOMORDER', 'AMOUNT') or
> > TIBDatabase.Has_Default_Value('"CUSTOMORDER"', '"AMOUNT"') give False.
> > I even try to pad the table and field name to 31 characters, but still
> > return value False.

> > Thanks,
> > Robert.

Re:How to call TIBDatabase.Has_Default_Value


Which version of IB?  Checking with the employee DB for IB 6.0 and 6.5 (dialect
1) F.RDB$DEFAULT_VALUE is defined, R.RDB$DEFAULT_VALUE is not which seems to
contradict what you are saying.

Quote
Robert wrote:

> > IIRC it is exactly as the API gives this information back so is no quotes,
> case
> > sensitive and probably right padded to 31 characters.  I set and compare
> the
> > values directly from the API results so never worried about other people
> > checking, it was designed to be a performance improvement for building
> > TFieldDefs.

> I'm not sure if HAS_DEFAULT_VALUE works properly. In the IBX code I see that
> the statement

>   DefaultSQL = 'Select F.RDB$COMPUTED_BLR, ' + {do not localize}
>                'F.RDB$DEFAULT_VALUE, R.RDB$FIELD_NAME ' + {do not localize}
>                'from RDB$RELATION_FIELDS R, RDB$FIELDS F ' + {do not
> localize}
>                'where R.RDB$RELATION_NAME = :RELATION ' +  {do not localize}
>                'and R.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME '+ {do not
> localize}
>                'and ((not F.RDB$COMPUTED_BLR is NULL) or ' + {do not
> localize}
>                '     (not F.RDB$DEFAULT_VALUE is NULL)) '; {do not localize}

> is executed. In my case this query will return zero rows. If
> F.RDB$DEFAULT_VALUE is replaced with R.RDB$DEFAULT_VALUE then there will be
> rows with default values. It seem you have to get de default value from
> RDB$RELATION_FIELDS, not RDB$FIELDS.

> > Robert wrote:

> > > Hi,
> > > How do i call TIBDatabase.Has_Default_Value?
> > > I'm pretty sure the field AMOUNT of table CUSTOMORDER has a
> defaultvalue.
> > > But TIBDatabase.Has_Default_Value('CUSTOMORDER', 'AMOUNT') or
> > > TIBDatabase.Has_Default_Value('"CUSTOMORDER"', '"AMOUNT"') give False.
> > > I even try to pad the table and field name to 31 characters, but still
> > > return value False.

> > > Thanks,
> > > Robert.

--
Jeff Overcash (TeamB)
      (Please do not email me directly unless  asked. Thank You)
This sad little lizard told me that he was a brontosaurus on his mother's
side.  I did not laugh; people who boast of ancestry often have little else
to sustain them.  Humoring them costs nothing and adds to happiness in
a world in which happiness is in short supply.   (RAH)

Re:How to call TIBDatabase.Has_Default_Value


"Jeff Overcash (TeamB)" <jeffoverc...@mindspring.com> schreef in bericht
news:3D02577D.EE908519@mindspring.com...

Quote
> Which version of IB?  Checking with the employee DB for IB 6.0 and 6.5
(dialect
> 1) F.RDB$DEFAULT_VALUE is defined, R.RDB$DEFAULT_VALUE is not which seems
to
> contradict what you are saying.

Hi Jeff,
I use IB 6.0 (WI-O6.0.2.0), dialect 3 and further IBX 5,03, Windows ME.

It seems RDB$DEFAULT_VALUE in RDB$FIELDS is filled when the default value
for the field is inherited from the domain.
And RDB$DEFAULT_VALUE in RDB$RELATION_FIELDS is filled when the default
value is declared in the table itself.

To demonstrate I executed the folowing SQL (with IBConsole) on table
DEPARTMENT in the employee DB

SELECT R.RDB$FIELD_NAME, F.RDB$DEFAULT_VALUE, R.RDB$DEFAULT_VALUE
FROM RDB$RELATION_FIELDS R, RDB$FIELDS F
WHERE R.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME
  AND ((F.RDB$DEFAULT_VALUE IS NOT NULL) OR (R.RDB$DEFAULT_VALUE IS NOT
NULL))
  AND R.RDB$RELATION_NAME = 'DEPARTMENT'

This is the result:

RDB$FIELD_NAME     RDB$DEFAULT_VALUE    RDB$DEFAULT_VALUE1
BUDGET                        (BLOB)                                 <NULL>
PHONE_NO                  <NULL>                               (BLOB)

For the DEPARTMENT table field BUDGET and PHONE_NO are declared as:

CREATE TABLE DEPARTMENT
(
  {...}
  BUDGET BUDGET,
  PHONE_NO PHONENUMBER  DEFAULT '555-1234',
  {...}
)

And domain BUDGET is declared as:

CREATE DOMAIN BUDGET AS NUMERIC(15, 2)
  DEFAULT 50000
  CHECK (VALUE > 10000 AND VALUE <= 2000000);

So, my conclusion:
It seems RDB$DEFAULT_VALUE in RDB$FIELDS is filled when the default value
for the field is inherited from the domain.
And RDB$DEFAULT_VALUE in RDB$RELATION_FIELDS is filled when the default
value is declared in the table itself.

Bye,
Robert.

Quote

> > I'm not sure if HAS_DEFAULT_VALUE works properly. In the IBX code I see
that
> > the statement

> >   DefaultSQL = 'Select F.RDB$COMPUTED_BLR, ' + {do not localize}
> >                'F.RDB$DEFAULT_VALUE, R.RDB$FIELD_NAME ' + {do not
localize}
> >                'from RDB$RELATION_FIELDS R, RDB$FIELDS F ' + {do not
> > localize}
> >                'where R.RDB$RELATION_NAME = :RELATION ' +  {do not
localize}
> >                'and R.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME '+ {do not
> > localize}
> >                'and ((not F.RDB$COMPUTED_BLR is NULL) or ' + {do not
> > localize}
> >                '     (not F.RDB$DEFAULT_VALUE is NULL)) '; {do not
localize}

> > is executed. In my case this query will return zero rows. If
> > F.RDB$DEFAULT_VALUE is replaced with R.RDB$DEFAULT_VALUE then there will
be
> > rows with default values. It seem you have to get de default value from
> > RDB$RELATION_FIELDS, not RDB$FIELDS.

> --
> Jeff Overcash (TeamB)
>       (Please do not email me directly unless  asked. Thank You)
> This sad little lizard told me that he was a brontosaurus on his mother's
> side.  I did not laugh; people who boast of ancestry often have little
else
> to sustain them.  Humoring them costs nothing and adds to happiness in
> a world in which happiness is in short supply.   (RAH)

Re:How to call TIBDatabase.Has_Default_Value


"Jeff Overcash (TeamB)" <jeffoverc...@mindspring.com> schreef in bericht
news:3D02577D.EE908519@mindspring.com...

Quote
> Which version of IB?  Checking with the employee DB for IB 6.0 and 6.5
(dialect
> 1) F.RDB$DEFAULT_VALUE is defined, R.RDB$DEFAULT_VALUE is not which seems
to
> contradict what you are saying.

Hi Jeff,
I use IB 6.0 (WI-O6.0.2.0), dialect 3 and further IBX 5,03, Windows ME.

It seems RDB$DEFAULT_VALUE in RDB$FIELDS is filled when the default value
for the field is inherited from the domain.
And RDB$DEFAULT_VALUE in RDB$RELATION_FIELDS is filled when the default
value is declared in the table itself.

To demonstrate I executed the folowing SQL (with IBConsole) on table
DEPARTMENT in the employee DB

SELECT R.RDB$FIELD_NAME, F.RDB$DEFAULT_VALUE, R.RDB$DEFAULT_VALUE
FROM RDB$RELATION_FIELDS R, RDB$FIELDS F
WHERE R.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME
  AND ((F.RDB$DEFAULT_VALUE IS NOT NULL) OR (R.RDB$DEFAULT_VALUE IS NOT
NULL))
  AND R.RDB$RELATION_NAME = 'DEPARTMENT'

This is the result:

RDB$FIELD_NAME     RDB$DEFAULT_VALUE    RDB$DEFAULT_VALUE1
BUDGET                        (BLOB)                                 <NULL>
PHONE_NO                  <NULL>                               (BLOB)

For the DEPARTMENT table field BUDGET and PHONE_NO are declared as:

CREATE TABLE DEPARTMENT
(
  {...}
  BUDGET BUDGET,
  PHONE_NO PHONENUMBER  DEFAULT '555-1234',
  {...}
)

And domain BUDGET is declared as:

CREATE DOMAIN BUDGET AS NUMERIC(15, 2)
  DEFAULT 50000
  CHECK (VALUE > 10000 AND VALUE <= 2000000);

So, my conclusion:
It seems RDB$DEFAULT_VALUE in RDB$FIELDS is filled when the default value
for the field is inherited from the domain.
And RDB$DEFAULT_VALUE in RDB$RELATION_FIELDS is filled when the default
value is declared in the table itself.

Bye,
Robert.

Quote

> > I'm not sure if HAS_DEFAULT_VALUE works properly. In the IBX code I see
that
> > the statement

> >   DefaultSQL = 'Select F.RDB$COMPUTED_BLR, ' + {do not localize}
> >                'F.RDB$DEFAULT_VALUE, R.RDB$FIELD_NAME ' + {do not
localize}
> >                'from RDB$RELATION_FIELDS R, RDB$FIELDS F ' + {do not
> > localize}
> >                'where R.RDB$RELATION_NAME = :RELATION ' +  {do not
localize}
> >                'and R.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME '+ {do not
> > localize}
> >                'and ((not F.RDB$COMPUTED_BLR is NULL) or ' + {do not
> > localize}
> >                '     (not F.RDB$DEFAULT_VALUE is NULL)) '; {do not
localize}

> > is executed. In my case this query will return zero rows. If
> > F.RDB$DEFAULT_VALUE is replaced with R.RDB$DEFAULT_VALUE then there will
be
> > rows with default values. It seem you have to get de default value from
> > RDB$RELATION_FIELDS, not RDB$FIELDS.

> --
> Jeff Overcash (TeamB)
>       (Please do not email me directly unless  asked. Thank You)
> This sad little lizard told me that he was a brontosaurus on his mother's
> side.  I did not laugh; people who boast of ancestry often have little
else
> to sustain them.  Humoring them costs nothing and adds to happiness in
> a world in which happiness is in short supply.   (RAH)

Other Threads