Recent

Author Topic: [solved] Not NULL - not zero pls!  (Read 1940 times)

Nicole

  • Hero Member
  • *****
  • Posts: 970
[solved] Not NULL - not zero pls!
« on: November 26, 2022, 04:02:11 pm »
I have a field of the type TDate (Firebird), which must not be NULL, fine.

And yes, it is not NULL, but - sobb -  '01.01.1800' (or símilar).

How can I make sure, that the double value behind the date must not be zero?
I want to enforce it within my firebird database (at the time version 2.5.)

I use FlameRobin the very recent version and used IBExpert (uninstalled) before. So I am not sure, how to do it exactly within FlameRobin, if there is a way, what is probably the case.
« Last Edit: November 26, 2022, 06:47:31 pm by Nicole »

Thaddy

  • Hero Member
  • *****
  • Posts: 14197
  • Probably until I exterminate Putin.
Re: Not NULL - not zero pls!
« Reply #1 on: November 26, 2022, 04:15:59 pm »
And yes, it is not NULL, but - sobb -  '01.01.1800' (or símilar).
It can never be that date. "similar" will give you the correct date.... as a valid default.
Defining not null ( is Pascal speak not nil) will try to insert a valid value, in this case start of time count.
Depending on platform the year is either 1899 or 1970.
In database terms, null means never accessed or possibly dropped and reset.  zero is not null.
« Last Edit: November 26, 2022, 04:19:03 pm by Thaddy »
Specialize a type, not a var.

Martin_fr

  • Administrator
  • Hero Member
  • *
  • Posts: 9791
  • Debugger - SynEdit - and more
    • wiki
Re: Not NULL - not zero pls!
« Reply #2 on: November 26, 2022, 04:18:10 pm »
I don't know Firebird... But here are a couple of general thoughts.

Many Databases have a "no default" for the declaration of a field. Together with "not null" this means the application must submit a value. However it will not stop the app to submit nonsensical values.

The firebird docs on "create table" shows that there is " CHECK (<check_condition>)". So you should be able to give a minimum value for the date.

Nicole

  • Hero Member
  • *****
  • Posts: 970
Re: Not NULL - not zero pls!
« Reply #3 on: November 26, 2022, 06:47:15 pm »
found it in Flamerobin!
Thank you, this works fine.
I just had to write there
dateField  > '1.1.1960'
and this keeps my db clean now.

Thaddy

  • Hero Member
  • *****
  • Posts: 14197
  • Probably until I exterminate Putin.
Re: [solved] Not NULL - not zero pls!
« Reply #4 on: November 26, 2022, 07:53:36 pm »
I don't think so..... :(
Specialize a type, not a var.

Nicole

  • Hero Member
  • *****
  • Posts: 970
Re: [solved] Not NULL - not zero pls!
« Reply #5 on: November 26, 2022, 09:00:19 pm »
Your comments sometimes sound not too helpful to anybody.

Zvoni

  • Hero Member
  • *****
  • Posts: 2315
Re: [solved] Not NULL - not zero pls!
« Reply #6 on: November 28, 2022, 11:47:36 am »
Your comments sometimes sound not too helpful to anybody.
What Thaddy is (probably) alluding to: If Firebird follows common Database-conventions, the Date-Datatype is an alias for Integer, but allows to store Dates in ISO-Format Representation (!!)
Thaddy doubts that your
Quote
dateField  > '1.1.1960'
is working as intended (It's not ISO-Format, it's not even proper "german" Format)
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Nicole

  • Hero Member
  • *****
  • Posts: 970
Re: [solved] Not NULL - not zero pls!
« Reply #7 on: November 28, 2022, 12:20:02 pm »
This would be indeed useful.

Do you have further information for me?
As I wrote it, I looked up a link in firebird.org (?), but unfortunately I cannot find it any more.
I thought, there would have been an exaple with this, but I may err.

So what I need is this:
- key in the field's check into Firebird by Flamerobin.

purpose:
hinder my software to write a date into my database which may be zero or - I just took it by chance - before 1.1.1960

There is no need to compare the time and there is no need to check for the 3.1.1960 or so. Need not be too exact.
The only need is a data-error capture for a value which contains a date "not really".

Zvoni

  • Hero Member
  • *****
  • Posts: 2315
Re: [solved] Not NULL - not zero pls!
« Reply #8 on: November 28, 2022, 12:50:13 pm »
No idea about FireBird,
but I'd try following tests
1) Define a Column of Type "Date" in a FB-Table
2) INSERT Dates in the Table
3) Try to SELECT INT(MyDate) As IntDate From MyTable
--> Check if it errors out. Might even be SELECT CAST(dateField AS INT) AS IntDate FROM MyTable.
--> This should tell you what the real underlying DataType is
--> As a further Check you could do a SELECT FLOAT(dateField) As FloatDate From MyTable --> or with CAST. see above.
--> If both SELECTS return someting like "245657" for the int and "245657.0000" for the Float, then you know what's under the hood.

4) do a SELECT MyDate From MyTable --> Check which Format it returns

especially the Result from 4) will give you the Hint how to "format" the Check-Condition Martin_fr mentioned
If 4) Returns "2022-11-28" then you have to "format" your check the same --> CHECK(dateField > '2022-11-01') --> Entered Value must be greater than 1st November 2022

I'd try it with different values
« Last Edit: November 28, 2022, 12:55:17 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Nicole

  • Hero Member
  • *****
  • Posts: 970
Re: [solved] Not NULL - not zero pls!
« Reply #9 on: November 28, 2022, 04:14:21 pm »
It returns 'dd.mm.yyyy,' which is the default setting of my computer.

Usually I do not touch the input or output data by Delphi (Lazarus).
I do it by ". byname... . AsDateTime", so IBX does it for me.




Thaddy

  • Hero Member
  • *****
  • Posts: 14197
  • Probably until I exterminate Putin.
Re: [solved] Not NULL - not zero pls!
« Reply #10 on: November 28, 2022, 04:18:35 pm »
It returns 'dd.mm.yyyy,' which is the default setting of my computer.

Usually I do not touch the input or output data by Delphi (Lazarus).
I do it by ". byname... . AsDateTime", so IBX does it for me.
No it is not, it is a default display setting. The internal structure is still a double and UTC. You confuse it. I warned you.
Specialize a type, not a var.

BrunoK

  • Sr. Member
  • ****
  • Posts: 452
  • Retired programmer
Re: [solved] Not NULL - not zero pls!
« Reply #11 on: November 28, 2022, 05:53:56 pm »
I may have not understood the problem, so if it is the case, ignore.

When using a db Field in FPC, you can test the TField.IsNull property.
Code: Pascal  [Select][+][-]
  1. if TheDateField.IsNull then
  2.   ShowMessage('TheDateField = null !')
  3. else
  4.   ShowMessage(FormatDateTime(TheDateField.AsDateTime));
In  a SQL query you can use DateFieldName IS NULL / <> NULL  (not is null) in the where clause.

Most databases have support for NULL condition and fcl.db.TField has for every field a TField.IsNull property.

In most cases the database field content is initialized to a zero value (which in the case of a date has a value that is likely to represent a valid date) but a null indicator is handled by the DataSet for each field. That null indicator is accessed by the TFIeld.IsNull property.


rvk

  • Hero Member
  • *****
  • Posts: 6109
Re: [solved] Not NULL - not zero pls!
« Reply #12 on: November 28, 2022, 06:04:02 pm »
So what I need is this:
- key in the field's check into Firebird by Flamerobin.

purpose:
hinder my software to write a date into my database which may be zero or - I just took it by chance - before 1.1.1960
So what was wrong with the CHECK dateField  > '1.1.1960'?

Although I would advise you to always use YYYY-MM-DD notation in SQL, never a local notation.
On screen you can have local notation but when hardcoding dates and using SQL, convert dates to YYYY-MM-DD first and it will always work.

So
CHECK datefield > '1960-01-01'
and if also can't be NULL you can add NOT NULL

Or do you have it working now already?

Nicole

  • Hero Member
  • *****
  • Posts: 970
Re: [solved] Not NULL - not zero pls!
« Reply #13 on: November 28, 2022, 06:54:03 pm »
Oh yes, it looks as if it would work fine.
Now I want to understand, if my solution needs a warning, that it leads to a mess in the long run.

To make it clear.
It is not about NULL / NIL, it is about zero.
If zero is the default value for not NIL, worsens the thing.

Since years, really since years, I struggle with this empty date in many places.
I could not check if there is an empty string in my edits, which shall be a field for a date.
Believe me, I tried it all, comparing against ' ', IsEmpty and I cannot say what else.
At the end I wrote reset routines, field by field for a check, - this kept me busy for really a long time.

The computer figured: field / value empty ► zero ► valid date.
In my problem today, the zero-value-for-date comes from an US database, which I bought.

In other words:
I want to say, "the value zero for a date is NOT valid"

My question is now, if my working solution works or only looks as it would work.
Testing this is really hard, because it takes about a quarter of an hour after having chosen "run", until I see anything.

@Thaddy can you say more about?
In this thread I read, that the internal structure of a firebird date would be an integer.
In Delphi it is a double.
Are you sure, that it is a double in Firebird as well?
And are you sure, that Firebird weill trouble me?
If yes, how exactly?

We have a type date, not Timestamp.

@rvk
Why shall I prefer  '1960-01-01'

and of course this was a typo  > '1.1.1960'?
correct is, that I wrote
 > '01.01.1960'?

I did this, because this seems format of many conversion routines of all kinds as input and output including FlameRobin



Zvoni

  • Hero Member
  • *****
  • Posts: 2315
Re: [solved] Not NULL - not zero pls!
« Reply #14 on: November 28, 2022, 08:16:29 pm »
Because there is a difference between how data is stored versus how it is displayed.
If you want to put the check-constraint directly on your column, you MUST use the format the data is stored in.

FB doesn’t care that your Format is german, italian or french or american. It‘s all the same to the database itself.
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018