Recent

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

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: [solved] Not NULL - not zero pls!
« Reply #15 on: November 28, 2022, 08:23:36 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/

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.

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).

« Last Edit: November 28, 2022, 08:26:38 pm by rvk »

Nicole

  • Hero Member
  • *****
  • Posts: 970
Re: [solved] Not NULL - not zero pls!
« Reply #16 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]


rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: [solved] Not NULL - not zero pls!
« Reply #17 on: November 29, 2022, 10:43:45 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?)


Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: [solved] Not NULL - not zero pls!
« Reply #18 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" ........
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 #19 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


Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: [solved] Not NULL - not zero pls!
« Reply #20 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!)
« Last Edit: November 29, 2022, 01:11:20 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

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: [solved] Not NULL - not zero pls!
« Reply #21 on: November 29, 2022, 03:14:17 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][+][-]
  1. 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.
« Last Edit: November 29, 2022, 03:17:40 pm by rvk »

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: [solved] Not NULL - not zero pls!
« Reply #22 on: November 29, 2022, 03:16:46 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
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

Thaddy

  • Hero Member
  • *****
  • Posts: 14204
  • Probably until I exterminate Putin.
Re: [solved] Not NULL - not zero pls!
« Reply #23 on: November 29, 2022, 03:18:39 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.
« Last Edit: November 29, 2022, 03:25:29 pm by Thaddy »
Specialize a type, not a var.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: [solved] Not NULL - not zero pls!
« Reply #24 on: November 29, 2022, 03:23:01 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.

Thaddy

  • Hero Member
  • *****
  • Posts: 14204
  • Probably until I exterminate Putin.
Re: [solved] Not NULL - not zero pls!
« Reply #25 on: November 29, 2022, 03:26:49 pm »
I just referred to Unix standard time.
Specialize a type, not a var.

Nicole

  • Hero Member
  • *****
  • Posts: 970
Re: [solved] Not NULL - not zero pls!
« Reply #26 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.


Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: [solved] Not NULL - not zero pls!
« Reply #27 on: November 29, 2022, 04:43:31 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
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

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: [solved] Not NULL - not zero pls!
« Reply #28 on: November 29, 2022, 04:44:20 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).

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.


 

TinyPortal © 2005-2018