Update oracle dateTime field

Suite and Optimus Suite 5
#1

Hi,
I can’t update oracle datetime field using grid + dataProcessor:

Oracle needs to perform
Update TableX
Set myDateTimeField = To_Date(‘2017/12/31 23:59:59’, ‘YYYY/MM/DD HH24;MI:SS’)

I’ve tried php server side using

function formatting($row){
	//formatting a date field
	$data = $row->get_value("DTINS");
	$row->set_value("DTINS","To_date('" . $data . "','DD/MM/YYYY HH24:MI:SS')");
};

(…)
$gridConn->event->attach(“beforeUpdate”,“formatting”);

but the query sent to Oracle becomes:
Update TableX Set DTINS=‘To_date(’‘12/05/2016 00:00:00’‘,’‘DD/MM/YYYY HH24:MI:SS’‘)’

so To_date() is not evaluated but is passed as a norma string.

How can I update DateTime fields correctly?

Thank you,
Zarco.

#2

All incoming data is escaped, it is default behavior and it can’t be disabled

You can try to redefine “escape” method in db_oracle.php, so it will detect a presense of to_date and process it correctly.

#3

Ok,
I thought that saving a datetime in oracle was something more simple and widespread …

anyway, I’ve made this way:

  1. edit db_common.php at line 768:
(...)
	protected function update_query($data,$request){
(...)
// ORIGINAL
// $step_value = "'".$this->escape($data->get_value($step["name"]))."'";

// MODIFICATION
if ( strpos( strtoupper($data->get_value($step["name"])),'TO_DATE') === false ) {
    $step_value = "'".$this->escape($data->get_value($step["name"]))."'";
} else {
    $step_value = $data->get_value($step["name"]);
};
// END MODIFICATION
  1. Write a formatting function in the xxx.php page that is called by grid.load(“xxx.php”) and new dataProcessor ("xxx.php); like this:
	require_once("../js/vendor/codebase/connector/grid_connector.php");//includes related connector file
	require_once("../js/vendor/codebase/connector/options_connector.php");//includes related connector file
	require_once("../js/vendor/codebase/connector/db_oracle.php");//includes related connector file

	function formatting($row){
		//formatting a specific datetime field
		$data = $row->get_value("MYDATETIMEFIELD");
		$row->set_value("MYDATETIMEFIELD","To_date('" . $data . "','DD/MM/YYYY HH24:MI:SS')");
	};	

	$gridConn = new GridConnector($oraconn,"Oracle");                    // connector initialization
	$gridConn->enable_log("../logs/MaybeItWorks.log",true);
	$statement = "Select id,To_Char(MyDateTimeField,'DD/MM/YYYY HH24:MI:SS') as MyDateTimeField from MyTable";

	$gridConn->event->attach("beforeUpdate","formatting");
	$gridConn->render_sql($statement,"ID","ID,MYDATETIMEFIELD");

So MyDateTimeField is displayed as DD/MM/YYYY HH24:MI:SS in the grid (you should use setColTypes(“ed”) )

Moreover, if you have a form linked to the grid I’ve used this trick:

put an hidden field in the form whith the date field and an empty calendar,
set the date of the calendar in onRowSelect of the grid to maintein it alligned
set the hidden field value whith the calendar value in onBeforeValidate event of the form.

	var formStructure = [ (...)
        {type: "calendar", name: "MYDATETIMEFIELD_CALENDAR", label: "FakeDate", dateFormat: "%d/%m/%Y %H:%i:%s", serverDateFormat: "%d/%m/%Y %H:%i:%s", enableTime: true, position: "absolute", inputTop: 70, inputLeft: 5, labelLeft: 5, labelTop: 50, labelWidth: 500},
        {type: "hidden", name: "MYDATETIMEFIELD" },
	];
(...)

    myGrid.attachEvent("onRowSelect", function (id, ind) {
		myForm.setItemValue("MYDATETIMEFIELD_CALENDAR", myForm.getItemValue("MYDATETIMEFIELD"));
    });

	myForm.attachEvent("onBeforeValidate", function (id, values){
		// Copio il contenuto del calendario nel campo nascosto 
		var dhxCalendar = myForm.getCalendar("MYDATETIMEFIELD_CALENDAR");
		var S = dhxCalendar.getFormatedDate("%d/%m/%Y %H:%i:%s");
		myForm.setItemValue("MYDATETIMEFIELD", S);
		return true;
	});

It’s quite a dirty way, but it works.
Hope it helps,
Bye !

#4

The same modify should be made also for the inserting routine in db_common.php:

    protected function insert_query($data,$request){
    (....)
			if ($data->get_value($v["name"])===Null)
				$temp_v[$k]="Null";
			else {
				// original
				// $temp_v[$k]="'".$this->escape($data->get_value($v["name"]))."'";
				
 			    // Start modification
				if ( strpos( strtoupper($data->get_value($v["name"])),'TO_DATE') === false ) {
					$temp_v[$k] = "'".$this->escape($data->get_value($v["name"]))."'";
				} else {
					$temp_v[$k] = $data->get_value($v["name"]);
				};
				// End modification
			};
    (....)
#5

…and don’t forget to set the same event handler in your xxx.php file.

   $gridConn->event->attach("beforeInsert","formatting");