--- In firebird-support@yahoogroups.com, "sbdlinxg" <sbdlinxg@y...> wrote:

>
>
> I created a table called LOG_IN_TABLE. Two of the fields in the the
> table are called LOG_IN_DATE (Date type) and LOG_IN_TIME (Time type).
>
> These fields are accessed through a Visual Basic program that prevents
> me from creating a new entry unless the LOG_IN_TIME is at least 10
> minutes past the last entry.
>
> To test how this was working I edited the LOG_IN_DATE to the previous
> day and tried to create a new entry. To my surprise I see that I can't
> because my time interval is less than ten minutes.
>
> Upon further investigation I find the following....
>
> IBExpert shows
> LOG_IN_DATE 21.12.2005
> LOG_IN_TIME 08:52:00
>
> A query result from Visual Basic shows
> LOG_IN_DATE 12/21/05
> LOG_IN_TIME 12/21/05 08:52:00
>
> Using the Visual Basic Datagrid shows
> LOG_IN_DATE 12/21/05
> LOG_IN_TIME 12/21/05 08:52:00
>
> Connecting to the Firebird database with MS Access shows
> LOG_IN_DATE 12/21/05
> LOG_IN_TIME 11/30/1899 08:52:00 AM
>
> I'm not sure how IBExpert connects to the database but Visual Basic
> and MS Access connect through a driver called
>
> Firebird/Interbase(r)driver 1.02.00.69 IBPhoenix Inc
>
> Does anyone know why LOG_IN_TIME might be returning such screwy
> values?

Not too sure how access works here, but I know in Delphi 30 Nov 1899
is the magic 0 datetime. In fact I am sure Paradox worked the same
way. Anyway, it looks like access is treating a time as a timestamp.
Perhaps it is configured incorrectly or perhaps it doesn't support it,
you will need to ask in a different list what the case might be there.

Looking at your problem though, it seems like a better idea would be
to use triggers. Consistency rules like this should be implemented in
the database, or in a data abstraction layer.

----

CREATE INDEX IXLOGINTIME ON LOG_IN_TABLE(LOG_IN_TIME) DESC;

CREATE EXCEPTION ETOSOON 'Must wait 10 mins!';

SET TERM ^ ;

CREATE TRIGGER LOG_IN_TABLE_BI FOR LOG_IN_TABLE
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE LASTLOGINTIME TIMESTAMP;
BEGIN
SELECT MAX(LOG_IN_TIME)
FROM LOG_IN_TABLE
INTO :LASTLOGINTIME;

IF (:LASTLOGINTIME > CURRENT_TIME-(10.0/1440)) THEN
BEGIN
EXCEPTION ETOSOON;
END
END
^

SET TERM ;
^

COMMIT;

----

Now any time you try to insert a record, you will get an exception if
it has been too soon. Of course there are transaction isolation issues
at play. If two attempts are made to access the database at the same
time (before the first one commits anyway), the second will not see
the record generated by the first, but this is just as much an issue
with your VB approach so I assume you have some other mechanism to
prevent this from happenning.

Adam