Making Queries
simple 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 the 'Base Concepts' chapter.
complex queries
When you need to define your own logic you should use one of two ways:
custom queries for an action
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_complex_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.
using server-side events
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:
//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")
transactions
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:
- 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
- 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
INSERT operation in Oracle DB
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:
- 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");
- using sequence for ID generation
$grid->sql->sequence("EMPLOYEES_INC.nextVal"); //sequence name $grid->render_table("EMPLOYEES","EMPLOYEE_ID","FIRST_NAME,LAST_NAME");