ZUpdateSQL and default datetime field value

lams · Post by **lams** » 16.10.2008, 02:19

Hi,

I am using zeoslib 6.6.3 with Firebird 2.1. I try to convert my app from IBX to ZeosLib.

My table like this:

CREATE TABLE APP_MODULES 
(
  ID	INTEGER NOT NULL,
  VERSION	INTEGER NOT NULL,
  LASTUPDATED	TIMESTAMP DEFAULT 'Now' NOT NULL,
 PRIMARY KEY (MODULEID)
);

CREATE TRIGGER APP_MODULES_LASTUPDATED FOR APP_MODULES 
ACTIVE BEFORE UPDATE POSITION 0
AS BEGIN
  NEW.LASTUPDATED = "Now";
END

In my ZQuery, my sql like this:
Select * from APP_MODULES where ID = :ID

In my ZUpdateSQL, the insertSQL like this:

Code: Select all

Insert into APP_MODULES (VERSION) values (:VERSION)

When I run my application and insert a record, the LASTUPDATED value is always 30/12/1899

So I trace the problem and find out under ZDbcCacge.pas:

Code: Select all

function TZRowAccessor.GetTimestamp(ColumnIndex: Integer;
  var IsNull: Boolean): TDateTime;
begin
{$IFNDEF DISABLE_CHECKING}
  CheckColumnConvertion(ColumnIndex, stTimestamp);
{$ENDIF}
  Result := 0;
  if FBuffer.Columns[FColumnOffsets[ColumnIndex - 1]] = 0 then
  begin
    case FColumnTypes[ColumnIndex - 1] of
      stDate, stTime, stTimestamp:
        Result := PDateTime(@FBuffer.Columns[FColumnOffsets[ColumnIndex - 1] + 1])^;
      stString, stUnicodeString:
        Result := AnsiSQLDateToDateTime(GetString(ColumnIndex, IsNull));
    end;
    IsNull := False;
  end else
    IsNull := True;
end;

It will return 0 as default Timestamp value. Why don't return null as default value and let the database trigger to handle it as IBX did?

Cheers,

Tao

btrewern · Post by **btrewern** » 16.10.2008, 09:20

You could use a trigger as follows:

Code: Select all

CREATE TRIGGER APP_MODULES_DEFAULT FOR APP_MODULES
ACTIVE BEFORE INSERT POSITION 0
AS BEGIN
  IF ((NEW.LASTUPDATE IS NULL) OR (NEW.LASTUPDATE = 0)) THEN
  BEGIN
    NEW.LASTUPDATED = "Now";
  END
END

to replace your DEFAULT clause. I've had to do this when using other components as this seems to be standard practice.

Ben

Post by **seawolf** » 16.10.2008, 18:55

As sayd some times ago firebird does not have a "Now" function so I suppose that is your udf.

My question is: are you sure that function give you a correct result and not a bad timestamp? Because, in my opinion, this is not a Zeos problem but the problem is located on trigger.

Or Does the problem is the Zquery data? In this case, after an insert try to close and re-open Zquery

lams · Post by **lams** » 17.10.2008, 01:00

Good point, btrewern. Thanks for your suggestion.

lams · Post by **lams** » 17.10.2008, 01:10

Hi Seawolf,

Firebird do have a build in "now" function.

And as my first post mention, it works fine under IBX component. If you have a look the TZRowAccessor.GetTimestamp I posted, you can see its default return value is 0.

As in ADO.Net 1.1, it also set 0 as DateTime default value. That's why M$ introduce nullable data type when they release .Net 2.

Although I prefer null as default datetime value, but Btrewern should works well.

I may put this as a feature request in the ZeosLib development.

Cheers,

Post by **seawolf** » 18.10.2008, 20:20

You are right regarding 'Now' function.

But, according to that manual Language Reference Doc Released for Firebird 2, pag 93, have you tried change 'Now' to CURRENT_TIMESTAMP, or casting 'now'? Nothing happened?

lams · Post by **lams** » 18.10.2008, 22:55

Interesting. When I using 'Now' in trigger,

Code: Select all

NEW.LASTUPDATED = "Now";

It do return a date/time type for me without error. But when I do

Code: Select all

select 'Now' from rdb$database

It return a string 'Now' as Firebird manual's description.

But I will change 'Now' to CURRENT_TIMESTAMP as you suggestion.

Thanks, Seawolf.

Post by **mdaems** » 19.10.2008, 14:28

Just for everybody looking to this problem here's the excerpt from the Firebird Changes manual (http://www.firebirdsql.org/pdfrefdocs/F ... Update.pdf)

'NOW'
Available in: DSQL, PSQL, ESQL
Changed in: 2.0
Description: 'NOW' is not a variable but a string literal. It is, however, special in the sense that when you CAST()
it to a date/time type, you will get the current date and/or time. The fractional part of the time used to be always
“.0000”, giving an effective seconds precision. In Firebird 2.0 the precision is 3 decimals, i.e. milliseconds.
'NOW' is case-insensitive, and the engine ignores leading or trailing spaces when casting.
Type: CHAR(3)
Examples:
select 'Now' from rdb$database
-- returns 'Now'
select cast('Now' as date) from rdb$database
-- returns e.g. 2008-08-13
select cast('now' as time) from rdb$database
-- returns e.g. 14:20:19.6170
select cast('NOW' as timestamp) from rdb$database
-- returns e.g. 2008-08-13 14:20:19.6170
Note
Using the date/time variables CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP is generally
preferable to casting 'NOW'. Be aware though that CURRENT_TIME defaults to seconds precision; to get
milliseconds precision, use CURRENT_TIME(3).

Let's now look for a solution to the problem...

Mark

Post by **mdaems** » 19.10.2008, 19:17

Lams,

I added this patch to ZDbcInterbase6Metadata.pas :

Code: Select all

Index: ZDbcInterbase6Metadata.pas
===================================================================
--- ZDbcInterbase6Metadata.pas	(revision 495)
+++ ZDbcInterbase6Metadata.pas	(working copy)
@@ -1713,6 +1713,14 @@
             [rfIgnoreCase]));
         end;
 
+        IF UpperCase(DefaultValue)= '''NOW''' then
+          case TypeName of
+          12: DefaultValue := 'CURRENT_DATE';
+          13: DefaultValue := 'CURRENT_TIME';
+          35: DefaultValue := 'CURRENT_TIMESTAMP';
+          else begin end;
+          end;
+
         Result.MoveToInsertRow;
         Result.UpdateNull(1);    //TABLE_CAT
         Result.UpdateNull(2);    //TABLE_SCHEM

This change has a little side effect on the output of the Metadata component, because the 'NOW' default value will not be shown. But I believe the replacement texts will be even more descriptive.
So, please test this patch. I committed it to Testing branch already for the next zeoslib generation (7.X, rev. 496), but to include it in the stable version fixes I want a second opinion and somebody else to test.

Mark

lams · Post by **lams** » 19.10.2008, 21:21

Thank you very much, Mdaems. I will checkout the latest patch from svn.

lams · Post by **lams** » 19.10.2008, 22:28

Hi Mdaems,

I have tested your patch, it works fine after I add one more condition on it:

Code: Select all

+        IF (UpperCase(DefaultValue)= '''NOW''') or (UpperCase(DefaultValue)= '"NOW"') then

And also wonder, because this change only happen after Firebird upgrade to 2.0, so what happen if ZeosLib connect to 1.5 or Interbase 6? It may not work because it may not have the 'CURRENT_' functions.

Cheers,

Post by **mdaems** » 20.10.2008, 00:24

Why those double quotes? Did you manage declare your table using double quotes? I was not able to do that. Does that work right using a plain insert statement from an other query tool as well? Because that doesn't correspond to the way I understand the documentation.

Those Current_ functions already appeared in the Interbase 6 docs. However, this shouldn't have impact on FB versions before 2.0 as the 'NOW' thing shouldn't be used in the table definitions there. In that case the code above would never be used. And if 'NOW' would be used before FB2.0 the code above would even make it work on insert. (That would be a feature of zeoslib then

)

Mark

lams · Post by **lams** » 20.10.2008, 01:32

Yes, I can because my database dialect is 1. I am using FlameRobin as my db admin tool.