Lazarus

Programming => Databases => Topic started by: Nicole on November 26, 2022, 04:02:11 pm

Title: [solved] Not NULL - not zero pls!
Post by: Nicole 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.

Title: Re: Not NULL - not zero pls!
Post by: Thaddy on November 26, 2022, 04:15:59 pm

Quote from: Nicole on November 26, 2022, 04:02:11 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.

Title: Re: Not NULL - not zero pls!
Post by: Martin_fr 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.

Title: Re: Not NULL - not zero pls!
Post by: Nicole 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.

Title: Re: [solved] Not NULL - not zero pls!
Post by: Thaddy on November 26, 2022, 07:53:36 pm

I don't think so..... :(

Title: Re: [solved] Not NULL - not zero pls!
Post by: Nicole on November 26, 2022, 09:00:19 pm

Your comments sometimes sound not too helpful to anybody.

Title: Re: [solved] Not NULL - not zero pls!
Post by: Zvoni on November 28, 2022, 11:47:36 am

Quote from: Nicole on November 26, 2022, 09:00:19 pm

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)

Title: Re: [solved] Not NULL - not zero pls!
Post by: Nicole 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".

Title: Re: [solved] Not NULL - not zero pls!
Post by: Zvoni 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

Title: Re: [solved] Not NULL - not zero pls!
Post by: Nicole 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.

Title: Re: [solved] Not NULL - not zero pls!
Post by: Thaddy on November 28, 2022, 04:18:35 pm

Quote from: Nicole 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.

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.

Title: Re: [solved] Not NULL - not zero pls!
Post by: BrunoK 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][+]

if TheDateField.IsNull then
  ShowMessage('TheDateField = null !')
else
  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.

Title: Re: [solved] Not NULL - not zero pls!
Post by: rvk on November 28, 2022, 06:04:02 pm

Quote from: Nicole on November 28, 2022, 12:20: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?

Title: Re: [solved] Not NULL - not zero pls!
Post by: Nicole 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

Title: Re: [solved] Not NULL - not zero pls!
Post by: Zvoni 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.

Title: Re: [solved] Not NULL - not zero pls!
Post by: rvk on November 28, 2022, 08:23:36 pm

Quote from: Nicole on November 28, 2022, 06:54:03 pm

@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

Yes. The dot notification usually does refer to DD.MM.YYYY in Firebird. But does it also in Windows?
Is there a possible MM.DD.YYYY notification in Windows?

I learned early on to just ALWAYS use YYYY-MM-DD and you are always safe.
It also can't be confused with switching DD and MM.
(For when displaying text it's also handy for sorting but that's another thing)

https://firebirdsql.org/en/firebird-date-literals/

Quote from: Nicole on November 28, 2022, 06:54:03 pm

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.

0 en NULL are not the same thing in Firebird.
That's why you would also need to define NOT NULL (of you don't want NULL to be possible).
In that case you would ALWAYS need to specify a correct date.

If you don't want to specify a date then don't use "NOT NULL" and you can set NULL and the TDateEdit should just be empty.

You can check the field with TField.IsNull.

I'm not sure what your problem is now exactly.

Quote from: Nicole on November 28, 2022, 06:54:03 pm

Testing this is really hard, because it takes about a quarter of an hour after having chosen "run", until I see anything.

Mmm, then there is something really wrong. When I run my program (including Build) it comes up in about 10 seconds (and it is a really large program).

Title: Re: [solved] Not NULL - not zero pls!
Post by: Nicole on November 29, 2022, 08:49:02 am

Thank you for the explanations.
I will change the format and my habits.

Just to explain for understanding (the problems are all solved now, at least I hope so).
I wok in different layers:

1) (kind of, you do not want to know details) US-database = input
2) FP reads it
3) FP works with it and fights those zeros for the first fights
4) FP sends new data to IBX
5) IBX sends the data to Firebird
x) Flamrobin is the helping hand between 4 and 5

What my target is: Firebird must not accept any default value as input for a not existing date, which may come originally from the US-database or my own errors.

And yes, the time needed is ok. The data for this operation are back to 1983. Until the stuff is converted (and filtered and sorted....), it takes this time. I will have a coffee and not do it again after it is converted (I hope so).

In German we say, "the hope is it, what dies at last".
What I hope(d): As Flamerobin checks the date and give correct results in "where" for e.g.
where datum > '10.11.2022'

Is there a chance, that it passes on to Firebird as well "meant" conditions? And "corrects" the format internally?
I am aware, that this where clauses accepts different strange inputs as well, I tried some (but got wrong results).

[Window Title]
About FlameRobin

[Content]
FlameRobin 0.9.3 (git hash 375bec5f) Unicode (x64)
Database administration tool for Firebird RDBMS

This tool uses IBPP library version 2.5.2.1
wxWidgets library version 3.1.4

Copyright (c) 2004-2020 FlameRobin Development Team
http://www.flamerobin.org

[OK]

Title: Re: [solved] Not NULL - not zero pls!
Post by: rvk on November 29, 2022, 10:43:45 am

Quote from: Nicole on November 29, 2022, 08:49:02 am

In German we say, "the hope is it, what dies at last".
What I hope(d): As Flamerobin checks the date and give correct results in "where" for e.g.
where datum > '10.11.2022'

Is there a chance, that it passes on to Firebird as well "meant" conditions? And "corrects" the format internally?
I am aware, that this where clauses accepts different strange inputs as well, I tried some (but got wrong results).

Maybe I am the only one here... but I find your question (or translation of it) really hard to understand.

Do you have a source file with data with dates and you want to transfer them to Firebird?
How does Flamerobin come into all this?
Could you give an example with a few lines?

(Maybe giving the original German question along side your translated one could give more clarity. Did you translate with Google Translate?)

Title: Re: [solved] Not NULL - not zero pls!
Post by: Zvoni on November 29, 2022, 10:52:41 am

Just for curiosity's sake:
Is it possible to set FlameRobin, say, to english (US)?
And then check the Database, and the Format it returns for Dates?
Because if it returns "MM/DD/YYYY" ........

Title: Re: [solved] Not NULL - not zero pls!
Post by: Nicole on November 29, 2022, 12:48:58 pm

smile:
The "US-sample" would be possible, but answers one question and opens 1000 new ones. Believe me, you do not want to see this. I do not want to see it neither, but I have to.

Let us look forward instead to the new db:
Do you have any idea, what happens between Flame-Robin and Firebird, when I enter such a field-check?
The error-message looks, as if the format settings (1960-01-01 and 1.1.1960 and 01.01.1960) would be "the same".
See screenshot

Title: Re: [solved] Not NULL - not zero pls!
Post by: Zvoni on November 29, 2022, 01:09:47 pm

Looks like you're trying to use a Constraint-Name "CHK_DATUM" twice within the same table

Quote

The "US-sample" would be possible, but answers one question and opens 1000 new ones. Believe me, you do not want to see this. I do not want to see it neither, but I have to.

Maybe, but it would answer one very important question: If FB stores a date agnostic of locale representation (which would make so much sense!)

Title: Re: [solved] Not NULL - not zero pls!
Post by: rvk on November 29, 2022, 03:14:17 pm

Quote from: Nicole on November 29, 2022, 12:48:58 pm

The error-message looks, as if the format settings (1960-01-01 and 1.1.1960 and 01.01.1960) would be "the same".

Besides what Zvoni said about the duplicate constraint-name, you NEED to quote the date. The date isn't a field or constant. So you need to do

Code: SQL [Select][+]

CHECK (datum > '1960-01-01')

(And if NULL isn't allowed you should add NOT NULL too (if it's not done already in the original table)

BTW. If there are dates before 01-01-1960 you will get an error committing this constraint.
You need to UPDATE your table to change all invalid dates to valid ones.

Title: Re: [solved] Not NULL - not zero pls!
Post by: Zvoni on November 29, 2022, 03:16:46 pm

Quote from: rvk on November 29, 2022, 03:14:17 pm

BTW. If there are dates before 01-01-1960 you will get an error committing this constraint.
You need to UPDATE your table to change all invalid dates to valid ones.

...and do that before applying the constraint

Title: Re: [solved] Not NULL - not zero pls!
Post by: Thaddy on November 29, 2022, 03:18:39 pm

Quote from: rvk on November 29, 2022, 03:14:17 pm

BTW. If there are dates before 01-01-1960 you will get an error committing this constraint.

1970, be proverbial Goof, or is it mine again? Well , the goof is if you use Windows. It is one day off even in 1899.
Please ALWAYS use UTC formerly known as GMT.

Title: Re: [solved] Not NULL - not zero pls!
Post by: rvk on November 29, 2022, 03:23:01 pm

Quote from: Thaddy on November 29, 2022, 03:18:39 pm

Quote from: rvk on November 29, 2022, 03:14:17 pm
BTW. If there are dates before 01-01-1960 you will get an error committing this constraint.
1970, be proverbial Goof, or is it mine again?

What do you mean?
(We are way past the NULL and 1899 discussion.)

TS wants to constraint dates inside a database to be after 01-01-1960. What's so strange about that.

When adding that constraint... ALTER ... CHECK datum > '1960-01-01' and there is a date before 1960 in the before 1960, you will get an exception from the database.

Title: Re: [solved] Not NULL - not zero pls!
Post by: Thaddy on November 29, 2022, 03:26:49 pm

I just referred to Unix standard time.

Title: Re: [solved] Not NULL - not zero pls!
Post by: Nicole on November 29, 2022, 04:40:36 pm

An exception - this is, what I want.

What is this thing about 1970?

Not much difference for my purpose, if I set 1.1.1960 or 1.1.1970.
At the moment the first in MY definition valid date is some-when in the 1980ths.

So if there are better reasons for it, I can set it to > 1970-01-01.
Who and where may it be, that the date starts in 1970?
At the moment I use Windows, but we all know the reasons for a change to open source in the long run.

Title: Re: [solved] Not NULL - not zero pls!
Post by: Zvoni on November 29, 2022, 04:43:31 pm

Quote from: Nicole on November 29, 2022, 04:40:36 pm

What is this thing about 1970?

1970-01-01 is the "Start"-Date of the Unix-Timestamp
https://www.unixtimestamp.com/
...and is only relevant if you actually store Date/Time as a Unixepoch inside your Database

Title: Re: [solved] Not NULL - not zero pls!
Post by: rvk on November 29, 2022, 04:44:20 pm

Quote from: Nicole on November 29, 2022, 04:40:36 pm

An exception - this is, what I want.

Well, that's what you will get when you set a constraint like we showed with CHECK and want to add a record which doesn't pass the check.

So just alter the table like you showed (but check the current constraints because there was already a CHK_DAT and use quotes).

Quote from: Nicole on November 29, 2022, 04:40:36 pm

What is this thing about 1970?

Just forget about this remark for now.
1970 is the begindate of the Unix in seconds. But that's not an issue now.