Error saving changes to back-end database

Scheduler Javascript
#1

I have a shell now almost working. However, when editing an event - e.g. by double-clicking it and making changes or by dragging it to a new day - I get this error in the log:[code]Row data [1]
TimesheetID => 1
StartDate => 2017-02-20 11:15
EndDate => 2017-02-20 15:50
Notes => A note…or whatever
TaskID => 2
UserAssignedToID => 1
SubmittedOn => 2017-02-20 16:10:00
AuthorisedOn => null
AuthorisedBy => null
AuthoriseEmailSent => null
ProjectID => 1
ClientID => 1
!nativeeditor_status => updated

UPDATE t_timesheet SET StartDate=‘2017-02-20 11:15’,EndDate=‘2017-02-20 15:50’,Notes=‘A note…or whatever’,TaskID=‘2’,UserAssignedToID=‘1’,SubmittedOn=‘2017-02-20 16:10:00’,AuthorisedOn=‘null’,AuthorisedBy=‘null’,AuthoriseEmailSent=‘null’ WHERE TimesheetID=‘1’

exception ‘Exception’ with message ‘MySQL operation failed
Incorrect datetime value: ‘null’ for column ‘AuthorisedOn’ at row 1’ in D:\Projects\Web\PHP\Timesheet\IPN\1.01\Scheduler\codebase\connector\db_mysqli.php:14
Stack trace:
#0 D:\Projects\Web\PHP\Timesheet\IPN\1.01\Scheduler\codebase\connector\db_common.php(742): MySQLiDBDataWrapper->query(‘UPDATE t_timesh…’)
#1 [internal function]: DBDataWrapper->update(Object(DataAction), Object(DataRequestConfig))
#2 D:\Projects\Web\PHP\Timesheet\IPN\1.01\Scheduler\codebase\connector\dataprocessor.php(221): call_user_func(Array, Object(DataAction), Object(DataRequestConfig))
#3 D:\Projects\Web\PHP\Timesheet\IPN\1.01\Scheduler\codebase\connector\dataprocessor.php(168): DataProcessor->check_exts(Object(DataAction), ‘update’)
#4 D:\Projects\Web\PHP\Timesheet\IPN\1.01\Scheduler\codebase\connector\dataprocessor.php(97): DataProcessor->inner_process(Object(DataAction))
#5 D:\Projects\Web\PHP\Timesheet\IPN\1.01\Scheduler\codebase\connector\base_connector.php(492): DataProcessor->process(Object(DataConfig), Object(DataRequestConfig))
#6 D:\Projects\Web\PHP\Timesheet\IPN\1.01\Scheduler\codebase\connector\base_connector.php(411): Connector->render()
#7 D:\Projects\Web\PHP\Timesheet\IPN\1.01\Scheduler\data\GetTasks.php(25): Connector->render_table(‘t_timesheet’, ‘TimesheetID’, ‘StartDate,EndDa…’)
#8 {main}

Edit operation finished
0 => action:error; sid:1; tid:1;

Done in 0.0047311782836914s[/code]The relevant columns allow NULL so I know it’s not that. It seems as if the query is having every value treated as a string, hence the surrounding single-quote marks in the log. If I run the query (suitably edited to be the way it should be) using MySQL Workbench, i.e. UPDATE t_timesheet SET `StartDate`='2017-02-20 11:15',`EndDate`='2017-02-20 15:50',`Notes`='A note...or whatever',`TaskID`='2',`UserAssignedToID`='1',`SubmittedOn`='2017-02-20 16:10:00',`AuthorisedOn`=NULL,`AuthorisedBy`=NULL,`AuthoriseEmailSent`=NULL WHERE `TimesheetID`='1' the record is updated correctly.

Where/how do I tell the Scheduler how to deal correctly with NULL values?

#2

Hi,
Try running the exact query you have in log (i.e. copy it from log and execute)

`AuthorisedOn`='null'

here you have a string ‘null’ instead of an actual NULL value, so the exception is fired when mysql tries to convert string ‘null’ into DateTime.
I’m not sure if it’s dhtmlxScheduler or dhtmlxConnector issue, the current request format provides no good way to unambiguously pass NULL value as a request parameter.

If you don’t use AuthorisedOn,AuthorisedBy and AuthoriseEmailSent on the client side you can simply remove them from column list in dhtmlx connector config.
Or you can preprocess these columns on a backend before inserting:
docs.dhtmlx.com/connector__php_ … event.html

[code]$scheduler = new JSONSchedulerConnector($timesheet_db, $dbtype);

function defaultValues($action){
$nullable = array(“AuthorisedOn”, “AuthorisedBy”, “AuthoriseEmailSent”);

foreach($nullable as $column){
	if($action->get_value($column) == "null"){
		$action->set_value($column, null);
	}
}

}

$scheduler->event->attach(“beforeProcessing”,“defaultValues”);[/code]

#3

Removing those fields from the original load does, indeed, make the event update properly.

Whoa! Whoa! Whoa! I don’t have anything - the value is empty.

Waaaaaaaaaaaaaaaaaaaaaaaaaaaait a second here! I have to add code to correct code which, apparently, goes something like this: If PageElement.Value = Empty Then PageElement.Value="null"

Don’t get me wrong. I am totally knocked out with the quality of this product but seriously…who would write something like that? Wouldn’t it be better to find that code and make suitable amendments?

#4

Hi,

That is not exactly correct. Values come from client-side and if by an empty value you mean empty string - you’d have empty strings in log entry you pasted in your first message, but you have null values.

Again, not exactly.
Here is your log entry:Row data [1] TimesheetID => 1 StartDate => 2017-02-20 11:15 EndDate => 2017-02-20 15:50 Notes => A note...or whatever TaskID => 2 UserAssignedToID => 1 SubmittedOn => 2017-02-20 16:10:00 AuthorisedOn => null AuthorisedBy => null AuthoriseEmailSent => null ProjectID => 1 ClientID => 1 !nativeeditor_status => updated

  • these are the values exactly as they come from the client-side, note ‘null’ values.
    On the client you probably have following:

scheduler.attachEvent("onEventCreated", function(id){ var event = scheduler.getEvent(id); .... event.AuthorisedBy = null; event.AuthorisedOn = null; event.AuthoriseEmailSent = null; });
And if you check parameters of POST request, you’ll see something following: screencast.com/t/TOYTMJ01xwsS - null is already string here.

How it happens - when client side makes post request, it takes all properties of event object, converts their values into string and writes to request parameters (in this instance it also adds prefixes to post parameters, it’s not relevant now).
By default, converting null value to string will give you “null” string (because javascript) - which exactly what you see in logs and in code, and which causes the issue - server doesn’t know that that particular ‘null’ from request means NULL and not a string value. The same thing will probably happen for undefined, boolean and NaN types if used in property values.

Thus, since the server has no knowledge on what value was intended in case such ambiguous values, you have to define rules manually. Using “beforeProcessing” event seems to be an appropriate place for that