Error with date field [message #7796] |
Tue, 28 February 2023 06:01 ![Go to next message Go to next message](/theme/default/images/down.png) |
htManager
Messages: 427 Registered: May 2014
|
Senior Member |
|
|
Hi Tony,
I have one more problem. If I want to delete a record with date columns, I get the error that the values aren't valid. See attached picture. The date columns are empty/null in dictionary and this is allowed. The value 0000-00-00 is automatically inserted.
|
|
|
|
|
|
|
|
|
|
|
Re: Error with date field [message #7812 is a reply to message #7804] |
Thu, 23 March 2023 10:51 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
htManager
Messages: 427 Registered: May 2014
|
Senior Member |
|
|
I created a new table with the following specifications:
$fieldspec['id'] = array('type' => 'integer', 'type_native' => 'int','size' => 10, 'minvalue' => -2147483648, 'maxvalue' => 2147483647, 'pkey' => 'y', 'required' => 'y');
$fieldspec['test_datum'] = array('type' => 'date', 'size' => 12);
// primary key details
$this->primary_key = array('id');
When I insert a new record with my application, the line 1.631 in dml.mysqli.class.inc: $result = mysqli_query($this->dbconnect, $this->query); is responsible for inserting the record in my database with the right id and the value 0000-00-00 in the field 'test_datum', if I leave this empty in my screen. I examined the database table before and after executing this line. Before, the record wasn't visible/inserted, afterwards it was.
These variables shows my debugger before executing the line:
$this->query = INSERT INTO test_datum SET `id`='2', `test_datum`=''
client_info = mysqlnd 8.2.0
server_info = 10.4.27-MariaDB
Where else can I search for the problem? My debugger doesn't show any further information. I only can see the inserted record in the database table.
|
|
|
Re: Error with date field [message #7813 is a reply to message #7812] |
Fri, 24 March 2023 05:28 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
Nowhere in the framework does is set any date fields to the value '0000-00-00'. I have found two 'IF' statements in std.datevalidation.class.inc at line # 893 and std.validation.class.inc at line # 575, but that's all.
What is your CREATE TABLE script for thois table?
You could also turn on SQL logging for the task which adds records to this table. See Update Task for details.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
Re: Error with date field [message #7815 is a reply to message #7814] |
Sat, 25 March 2023 05:27 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
The CREATE TABLE script is the script which you used to create the table in your database. Every table has one. You can use phpMyAdmin to export these details.
You need to log the SQL query which creates the record with '0000-00-00' in its date field. If the query generated by the framework does not contain this value then it can only be inserted by MySQL.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Error with date field [message #7826 is a reply to message #7825] |
Sun, 02 April 2023 04:43 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
When you say Quote:I set the $fieldarray['foobar'] to '' in the _cm_pre_updateRecord() and _cm_pre_deleteRecord() methods does this mean that this date field already contains the value '0000-00-00'. If it does then you need to investigate where it is coming from. Is it coming from the database or the HTML form?
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
Re: Error with date field [message #7828 is a reply to message #7827] |
Mon, 03 April 2023 04:37 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
That is because an empty string is being treated as an invalid date which is then being converted to '0000-00-00'. I have tried the same query on my PC and the query fails instead of inserting '0000-00-00'. You have two problems:
- Your code should not be trying to insert an empty string into a date field.
- The MySQL query should fail if you do.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
Re: Error with date field [message #7830 is a reply to message #7829] |
Tue, 04 April 2023 04:44 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
If a date field is only valid when certain conditions are met then it should be nullable (not defined with NOT NULL). I am using MySQL version 8.0.32 with SQL_MODE set to
ANSI_QUOTES,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION and this prevents me from setting a date field to '' instead of NULL.
As I said in an earlier post, if you do not have NO_ZERO_IN_DATE,NO_ZERO_DATE set then inserting an invalid date (such as an empty string) MySQL will instead replace it with '0000-00-00'.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|