Making Queries

Simple queries

Applicable to: Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Chart, Form, DataStore сomponents

By default, the connector generates all INSERT/UPDATE/DELETE queries automatically, based on configuration.

For more details on this topic, see the 'Basic Loading' chapter.

Complex queries

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

Custom queries for an action

Applicable to: Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Chart, Form, DataStore сomponents

You can define your own SQL code for a 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:

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

Using server-side events

Applicable to: Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Chart, Form, DataStore сomponents

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 an 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")

//a 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}");
    //success() marks operation as finished and stops any further action processing
    $data->success(); 
} 
$conn->event->attach("beforeUpdate","my_update")

Transactions

Applicable to: Grid

Grid allows using transactions for data INSERT/UPDATE/DELETE operations (make sure that the used DB engine has the support of transactions).

They can be enabled in 2 modes:

1) global

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

• Component uses single transaction for all records inside of 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 has blocked any record - all the records inside of the 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

INSERT operation in Oracle DB

Applicable to: Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Chart, Form, DataStore сomponents

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

There are 2 ways to achieve 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");

Back to top