How to use the return value from a stored procedure as a default value in a column?

Question

I need to use the return value from an stored procedure in firebird 3.0 as the default value in multiple columns.

My Procedure:

CREATE OR ALTER PROCEDURE CURRENTTIME 
returns (
    stime char(12))
as
begin
    sTime = (SELECT trim(Substring(CURRENT_TIMESTAMP FROM 12 FOR 12)) from RDB$
end

So, we have to fill in the current time in some specific columns named "NewOn" and "LastChangeOn". I found a default procedure from firebird itself called Current_Time but there are the milliseconds always '0000' e.g. '12:30:10.0000'. To bad that we need the milliseconds in our database. My own procedure give us the milliseconds but we can not use it in our table.

Example of a table:

CREATE TABLE USER (
    FIRSTNAME         CHAR(30),
    LASTNAME          CHAR(50),
    IDENT             CHAR(40) NOT NULL,
    NEWAT             DATE DEFAULT Current_Date NOT NULL,
    NEWON             CHAR(13) DEFAULT Current_Time(3) NOT NULL,
    NEWFROM           CHAR(25),
    LASTCHANGEAT      DATE DEFAULT Current_Date NOT NULL,
    LASTCHANGEON      CHAR(13) DEFAULT Current_Time(3) NOT NULL,
    LASTCHANGEFROM    CHAR(25)
);

When the table is created like this it calls the firebird-own procedure that gives us only the 0000 milliseconds. I tried to call my procedure like the firebird procedure. Also I tested it with and without "suspend" in my procedure but it makes no difference.

I haven't found a solution to this problem yet, so can anyone help me here?

That's not possible. But I wonder why `LASTCHANGEON` isn't a timestamp column if you want date & time — , Feb 01 '17 at 11:53
@a_horse_with_no_name because of our programs structure, we do need the date and time seperated from each other and it is not in my hands to change something about this :x — Aoki.Miku, Feb 01 '17 at 12:16
You could create a column with a proper timestamp type and two computed columns that extract the date and time information into varchar columns. — , Feb 01 '17 at 12:47
Out of curiosity: Why do you save time in a char column, and not a time column? — Mark Rotteveel, Feb 01 '17 at 15:50
I have tested this with Firebird 3 and it works fine, if the column isn't referenced in the insert, then the default value contains milliseconds. You might want to show the code used to you insert values, because it sounds like you are actually inserting the value yourself instead of relying on the default value. — Mark Rotteveel, Feb 01 '17 at 19:33

ain · Answer 1 · 2017-02-01T14:49:23.730

According to the documentation, since Firebird 2.0 specifying a precision for CURRENT_TIME should work. Could it be that there is a bug when used in DEFAULT clause? I don't have access to Firebird right now to test it... anyway, when you need to set default values which are too complex for a ordinary DEFAULT clause, you can use trigger, ie

CREATE TRIGGER USER_Defaults
ACTIVE BEFORE INSERT OR UPDATE
AS
BEGIN
  new.LASTCHANGEON = CAST(CURRENT_TIME(3) AS CHAR(13));
END

EDIT

I now had a opportunity to test with Firebird 2.5.1 and the default value (ie field definition like LASTCHANGEON CHAR(13) DEFAULT Current_Time(3) NOT NULL) works as expected - I got value like 16:44:05.7840 for the column when omitting it from the insert statement. So if it indeed doesn't work in Firebird 3.0 (I don't have it installed so can't test) it is regression, please file a bug-report in the tracker.

Yep I read the documentation about this and tried this (in my question you can see that I used this precision with (3) behind Current_Time) but it did not helped .. The problem is that we use these columns in every table so we have to add one more trigger per table :( But okay, thank you for your answer I will suggest and discuss this solution with my leader — Aoki.Miku, Feb 01 '17 at 14:28

Mark Rotteveel · Accepted Answer · 2021-02-27T08:08:09.423

I have tested it against Firebird 3, but failed to reproduce the problem. You might want to carefully check how you insert values (for example check that you don't assign that time yourself). You could also try to drop the default and set it again.

The column default-clause only allows literal values and context variables like current_time. If you want to assign your own default value, you will need to create a trigger:

create trigger user_BIU 
   before insert or update on "USER"
as
begin
  if (new.newon is null) then
  begin
     new.newon = trim(Substring(CURRENT_TIMESTAMP FROM 12 FOR 12));
  end
end

This has a slightly different effect than a default, as this will assign a value even if explicitly set to null. However as I couldn't reproduce the problem, I don't expect this to solve your problem.

score 0 · Answer 3 · answered Feb 02 '17 at 16:50

0

Alright, thanks to all of you. We've found the problem.. The firebird library, written by my leader in 2005, build every insert- or update-statement with CURRENT_TIME instead of CURRENT_TIME(3) >_>

So sorry for that stupid selfmade problem..

answered Feb 02 '17 at 16:50

Aoki.Miku

37
1
10

How to use the return value from a stored procedure as a default value in a column?

3 Answers3