In this chapter you'll learn how to load data from:
In order to load data correctly, your 'id' field in the database must be auto-increment.
APPLICABLE TO: Grid, TreeGrid, Tree, Combo, Scheduler
Loading characteristics are defined at the stage of component configuration.
There are 2 ways to specify the desired data:
When all the necessary data is placed in one database table, you should use the render_table() method:
grid.render_table("grid50","item_id","item_nm,item_cd", "extra1, extra2");
Parameters:
If you want to render all fields from DB (except for the key field), you can use a simplified command:
grid.render_table("grid50");
That's enough to make connector implement select, insert, update and delete operations.
If your SQL statement contains more than one table, connector won't be able to generate insert/update/delete operations correctly and you will need to make one of the following actions:
The 3rd approach is shown in the code snippet below:
if(gridConn.is_select_mode()){//code for loading data
gridConn.render_sql(
"Select * from tableA, tableB where tableA.id=tableB.id",
"a.id",
"name,price,other"
);
}else{//code for other operations - i.e. update/insert/delete
gridConn.render_table("tableA","id","name,price");
}
With such init code grid will be loaded with three columns of data from 2 tables, but during saving only data from the first table will be saved.
You are allowed to use any SQL statements to populate a dhtmlx component through dhtmlxConnector. For complex SQL queries we strongly encourage you to use the render_complex_sql() method:
grid.render_complex_sql(
"SELECT name from tableA WHERE dept= (SELECT dept FROM tableB where name 'John')",
"contact_id",
"name,surname,age,address",
"extra1, extra2"
);
Parameters:
In case your SQL query was against a single table, it is quite probable that insert/update/delete operations do not require any additional code. dhtmlxConnector will parse your SQL and generate insert/update/delete statements based on the used table and fields' names.
Starting from version 1.5, you can load data using in SQL statements stored procedures. The library provides a special method for this need - render_complex_sql.
grid.render_complex_sql(
"exec usp_findusers 'John'",
"id",
"name,surname,age,address,phone"
);
The last parameter of the render_array, render_sql, render_complex_sql and render_table methods allows defining a list of fields which will be extracted from database table but won't be sent to the client side.
These fields can be used as attributes or flags, mapped to different properties of records (userdata, row styles, images, etc.).
grid.render_table("tableA","id","name,price","extra1,extra2");
// or
grid.render_sql(
"Select * from tableA, tableB where tableA.id=tableB.id",
"table_a_id",
"name,price,other",
"extra1,extra2"
);
extra1 and extra2 fields will be available in all server-side events but won't be sent to client side, and won't be included in update/insert operations.
In case of Tree and TreeGrid, the render_array, render_sql, render_complex_sql methods accept one more parameter - relation ID. For default treegrid hierarchy - it's the name of the field which will be used to link parent and child records.
treeGrid.render_table("tableA","id","name,price","","parent_id");
// or
treeGrid.render_sql(
"Select * from tableA, tableB where tableA.id=tableB.id",
"a.id",
"name,price,other",
"",
"parent_id"
);
To make the usage of extracted data handier you can use aliases for DB field names (makes sense only if you use server-side events):
grid.render_table("tableA","id","name,price(product_price)");
// or
grid.render_sql(
"Select *,tableA.id as aid from tableA, tableB where tableA.id=tableB.id",
"tableA.id(aid)",
"name,price(product_price),other"
);
Starting from version 1.5, instead of database you can load data from an array which can be filled with any kind of external logic.
To load data from an array, create public object(s) of any class implementing the Iterable Interface and call the method render_array.
Beware, the method can't be used for storing data but you still can use event handlers to intercept data saving command and process them in some custom way.
Parameters:
public class SchedulerEvent {
public int event_id;
public String start_date;
public String end_date;
public String event_name;
public SchedulerEvent(int id, String start, String end, String text) {
event_id = id;
start_date = start;
end_date = end;
event_name = text;
}
}
protected void configure() {
ArrayList<Object> data = new ArrayList<Object>();
data.add(new SchedulerEvent(
1,
"2012-05-24 00:00",
"2012-05-25 00:00",
"creation time"
));
data.add(new SchedulerEvent(2, "2010-02-16", "2084-06-08", "second part"));
SchedulerConnector c = new SchedulerConnector(null);
c.render_array(data, "event_id","start_date,end_date,event_name");
}