Dataprocessor tries to update a dynamic column

Suite and Optimus Suite 5
#1

PHP Server Side

I use an sql string to populate a checkbox in the grid and works fine.

Then I manually update a cell and go to update the database and it fails as column RET does not exist in the update. How can I just update the cell that was changed.

<?php 
require_once("../../codebase/connector/grid_connector.php");// includes the appropriate connector 
require_once("../../codebase/connector/options_connector.php");// includes the appropriate connector 
 
$dbCon=mysql_connect("localhost","tooling","tooling");//connects to a server that contains the desired DB
mysql_select_db("dmn_dr");// connects to the DB. 'tasks' is the name of our DB

$tool = new OptionsConnector($dbCon);
$tool->render_table("tbltools","ID","ID (value), TOOL (label)");

$myGrid = new GridConnector($dbCon,"MySQL");// connector initialization
$myGrid->enable_log("LEAVERS_TOOLS.LOG", true);

$sql = "SELECT *,1 as RET FROM tblallocatetools WHERE C_ID=".$_GET['engineer'];

$myGrid->set_options("T_ID",$tool);
$myGrid->render_sql($sql,"ID","C_ID,RET,T_ID,MAKE,MODEL,SERIAL");// data configuration  
?>

Code Snippet Client Side

			frmLeavers.attachEvent("onButtonClick", function(id) {
				var dp = new dataProcessor('php/Staff/leaverEquip.php?engineer='+gridDMN.getSelectedRowId());
				dp.init(gridLeavers);
				gridLeavers.forEachRow(function(id){
					var ret = gridLeavers.cells(id,1).isChecked();
					if(ret) {
						gridLeavers.cells(id,0).setValue("160");
						dp.setUpdated(id,true);
					}
					frmLeavers.unload();
					leaversWindow.close();

				});
				
			});

Log File:


====================================
Log started, 25/04/2013 01:04:44
====================================

SELECT `ID`,`ID` as value,`TOOL` as label FROM tbltools

SELECT  *,1 as RET FROM tblallocatetools WHERE ( C_ID=1)

Done in 0.02897310256958s



====================================
Log started, 25/04/2013 01:04:48
====================================

DataProcessor object initialized
550_gr_id => 550
550_c0 => 160
550_c1 => 1
550_c2 => 12
550_c3 => 
550_c4 => 
550_c5 => 
550_!nativeeditor_status => updated
ids => 550

Row data [550]
ID => 550
C_ID => 160
RET => 1
T_ID => 12
MAKE => 
MODEL => 
SERIAL => 
!nativeeditor_status => updated

UPDATE tblallocatetools SET `C_ID`='160',`RET`='1',`T_ID`='12',`MAKE`='',`MODEL`='',`SERIAL`='' WHERE `ID`='550' AND (( C_ID=1))



====================================
Log started, 25/04/2013 01:04:48
====================================

exception 'Exception' with message 'MySQL operation failed
Unknown column 'RET' in 'field list'' in C:\xampp\htdocs\ToolApp\codebase\connector\db_common.php:965
Stack trace:
#0 C:\xampp\htdocs\ToolApp\codebase\connector\db_common.php(637): MySQLDBDataWrapper->query('UPDATE tblalloc...')
#1 [internal function]: DBDataWrapper->update(Object(DataAction), Object(DataRequestConfig))
#2 C:\xampp\htdocs\ToolApp\codebase\connector\dataprocessor.php(222): call_user_func(Array, Object(DataAction), Object(DataRequestConfig))
#3 C:\xampp\htdocs\ToolApp\codebase\connector\dataprocessor.php(173): DataProcessor->check_exts(Object(DataAction), 'update')
#4 C:\xampp\htdocs\ToolApp\codebase\connector\dataprocessor.php(102): DataProcessor->inner_process(Object(DataAction))
#5 C:\xampp\htdocs\ToolApp\codebase\connector\base_connector.php(475): DataProcessor->process(Object(DataConfig), Object(DataRequestConfig))
#6 C:\xampp\htdocs\ToolApp\codebase\connector\base_connector.php(432): Connector->render()
#7 C:\xampp\htdocs\ToolApp\php\Staff\leaverEquip.php(17): Connector->render_sql('SELECT *,1 as R...', 'ID', 'C_ID,RET,T_ID,M...')
#8 {main}

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

Done in 0.016445875167847s
#2

You can update only one columns, as connector will update all fields at once. But you can exclude your custom field from update

function exclude_custom($action){ $action->remove_field("RET"); } $myGrid->event->attach("beforeProcessing", "exclude_custom" ); $myGrid->render...

#3

Thanks Stanislav, that worked great.

I now would like to insert a history record after each update but I get an error

<?php 
require_once("../../codebase/connector/grid_connector.php");// includes the appropriate connector 
require_once("../../codebase/connector/options_connector.php");// includes the appropriate connector 
 
$dbCon=mysql_connect("localhost","root","sh30nagh");//connects to a server that contains the desired DB
mysql_select_db("dmn_dr");// connects to the DB. 'tasks' is the name of our DB

$tool = new OptionsConnector($dbCon);
$tool->render_table("tbltools","ID","ID (value), TOOL (label)");

$myGrid = new GridConnector($dbCon,"MySQL");// connector initialization
$myGrid->enable_log("LEAVERS_TOOLS.LOG", true);

$sql = "SELECT *, 1 as RET FROM tblallocatetools WHERE C_ID=".$_GET['engineer'];

$myGrid->event->attach("beforeProcessing", "exclude_custom" );
$myGrid->event->attach("afterUpdate", "write_history" );

$myGrid->set_options("T_ID",$tool);
$myGrid->render_sql($sql,"ID","C_ID,RET,T_ID,MAKE,MODEL,SERIAL,ID");// data configuration  

function exclude_custom($action){
   $action->remove_field("RET");
}

function write_history($action){

   $cid = $action->get_value("C_ID");
   $id = $action->get_value("ID");

   $action->sql->query("INSERT tblHistorylog (C_ID, T_ID, USERNAME, NOTE) VALUES ({$cid},{$id},'SYSTEM','Tool Returned.')");
   $action->success();
}


?>
#4

Code looks fine, you can remove $action->success from write_history, as it has not sense for afterUpdate event, but it must not cause any problems anyway.

What kind of error do you have ?
What is in the log file for the problematic operation?

#5

Try to change

$action->sql->query(...

as

global $myGrid; $myGrid->sql->query(...

#6 
global $myGrid;
$myGrid->sql->query(...

Thank you Stanislav,

Above code works fine. Will DHTMLX be writing a ‘Cookbook’ on sample projects or a hardcopy user manual for the API ?

Many thanks,
Darrell Gowland

#7

We have this all described in existing doc, but structure of docs is rather confusing and complicated.
We will look how it can be improved.