Re: [firebird-support] Problems with default values...

[Ann Harrison]

On Wed, Sep 12, 2012 at 8:56 PM, fidel_filio fidel_fi...@yahoo.com wrote:

 ...
 add hor_uactu varchar(2) default extract(hour from cast('now' as date))
 not null

 I get this error:

Invalid token.
 SQL error code = -104.
 Token unknown - line 3, char 34.
 extract.

 Note:
 I am using Firebird 1.5.6


In addition to Helen's comments about using a varchar to hold a number and
using a function in a default value, you're using an ancient version of
Firebird which doesn't support the function EXTRACT at all.  You might be
able to get time by declaring the field to be of type time and assigning
'now' to it.

Do yourself a favor and use a version of Firebird that's less than 10 years
old.

Good luck,

Ann




[Non-text portions of this message have been removed]

Re: [firebird-support] Problems with default values...

[Helen Borrie]

At 01:17 AM 14/09/2012, Ann Harrison wrote:

 you're using an ancient version of
Firebird which doesn't support the function EXTRACT at all.  

Actually, Ann, we got EXTRACT() with IB 6.0, albeit with support for fewer 
time-parts than it has today.  

Cheers,
Helen

Re: [firebird-support] Problems with default values...

[Helen Borrie]

At 12:56 PM 13/09/2012, you wrote:
Hello I'm having a problems with a couple of columns, when I try to set a 
default value here is my code...


alter table cboo_pedido
add fec_uactu date default 'today' not null,
add hor_uactu varchar(2) default extract(hour from cast('now' as date)) not 
null

The first column (fec_uactu) goes OK, but on the second column I get this 
error: 
Invalid token.
SQL error code = -104.
Token unknown - line 3, char 34.
extract.

How can I add the second column with the required default?

Are you confusing a COMPUTED BY column with a defaulted column?   The DEFAULT 
attribute of a column can be a constant, a context variable or a predefined 
date literal (like your column FEC_UACTU) but not an expression.  

[FWIW, your expression for HOR_ACTU isn't valid, anyhow.  You can't extract an 
HOUR time-part from a DATE type.  It seems bizarre to be trying to cast the 
date literal 'NOW' as a date under any circumstances, since you have both 
CURRENT_DATE and 'TODAY' available.]

One way to get this data on the records is to make HOR_ACTU nullable and use a 
BEFORE INSERT OR UPDATE trigger to ensure you always get a default value when 
HOR_ACTU is null:

create trigger some_name for cboo_pedido
active before insert or update as
begin
  if (new.hor_actu is null) then
new.hor_actu = cast(extract (hour from current_timestamp) as varchar(2));
end

[I would also suggest reviewing the usefulness of storing HOR_ACTU as a 
varchar.  Do you really want '12' to be earlier than '2' when sorting by hour 
of day?]

./heLen