Making Queries

Applicable: Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Chart, Form, DataStore, DHTMLX Touch Components

By default, connector generates all INSERT/UPDATE/DELETE queries automatically, based on configuration.
For more details of this topic, see Base Concepts chapter.

When you need to define your own logic you should use one of two ways:

• defining custom SQL code for operation
• using server-side events to customize operations

Applicable to: Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Chart, Form, DataStore, DHTXML Touch Components

You can define your own SQL code for specific action (INSERT, UPDATE or DELETE) as follows:

$grid->sql->attach("Update","Update tableA set name='{name}', price={price} where id={id}");
//...
$grid->render_sql(" .. ","id","price,name");

Parameters:

• action name. Possible values are: 'Update', 'Insert', 'Delete'
• SQL statement. It can use fields(or their aliases) which were mentioned in render_sql or render_table method while loading data.

Applicable to: Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Chart, Form, DataStore, DHTMLX Touch Components

To customize operations you can use the following server-side events:

• beforeUpdate
• beforeInsert
• beforeDelete
• beforeProcessing

//data preprocessing before update
function my_update($data){
       $price = $data->get_value("price");
       $price = intval($price);
       $data->set_value("price","10");
} 
$conn->event->attach("beforeUpdate","my_update")

//including additional field to request
function my_update($data){
       $data->add_field("userId",1); //will be included in update processing
} 
$conn->event->attach("beforeUpdate","my_update")

//fully custom code
function my_update($data){
       global $conn;
       $price=$data->get_value("price");
       $id=$data->get_value("id");
       $conn->sql->query("UPDATE some_table SET price='{$price}' where id={$id}");
       $data->success(); //success() marks operation as finished and stops any further action processing
} 
$conn->event->attach("beforeUpdate","my_update")

APPLICABLE TO: Grid

Grid allows to use transactions , for data INSERT/UPDATE/DELETE operations ( be sure that used DB engine has support for transactions).
They can be enabled in 2 modes:

1. global
   
   

   $conn->sql->set_transaction_mode("global"); 

   • Component uses single transaction for all records inside single request.
   • If any operation fails - all record operations will be rolled back, all updated rows will be returned with error status
   • If custom validation was assigned and block any record - all records inside request will be blocked
2. record
   
   

   $conn->sql->set_transaction_mode("record");

   • Component uses separate transactions for each record in request
   • If any operation fails, it will not affect other operations

Applicable to: Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Chart, Form, DataStore, DHTMLX Touch Components

Oracle doesn't have auto ID generation functionality, so you need to provide some custom ID for each insert operations.

There are 2 ways to achive this:

1. custom ID generation - id generated by PHP code
   
   

   function pre_insert($data){
       	$new_id = time()%10000; //any other ID generation logic can be used here
   	$data->add_field("EMPLOYEE_ID",$new_id);
   }
   $grid->event->attach("beforeInsert","pre_insert");
   $grid->render_table("EMPLOYEES","EMPLOYEE_ID","FIRST_NAME,LAST_NAME");

2. using sequence for ID generation
   
   

   $grid->sql->sequence("EMPLOYEES_INC.nextVal"); //sequence name
   $grid->render_table("EMPLOYEES","EMPLOYEE_ID","FIRST_NAME,LAST_NAME");