Basic Loading

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.

Loading from database table

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:

Work with one table

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:

  • database table name
  • name of the identity field (optional)
  • a list of fields which should be used as values of the component item (cells of grid, item label in tree, text of option in combo)
  • a list of extra fields (you may learn more about extra fields here)

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.

Work with several tables

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:

  1. Define sql for each operation manually
  2. Use server-side events to define your own processing logic
  3. Define different configs for select and update operations

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.

Complex queries

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:

  • sql statement
  • name of the identity field (optional)
  • a list of fields which should be used as values of the component item (cells for grid, item label for tree, text of option for combo)
  • a list of extra fields (you may learn more about extra fields here)
  • parent ID field name for hierarchical structures (required for tree and treegrid)

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.

Calling stored procedures

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

Extra data

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.

Tree and TreeGrid specificity

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

Aliases

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

Loading from data array

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:

  • name of an array or an array itself
  • name of the identity field (optional)
  • a list of fields which should be used as values of the component item (cells for grid, item label for tree, text of option for combo)
  • a list of extra fields (you may learn more about extra fields here)
  • parent ID field name for hierarchical structures (required for tree and treegrid)
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");
}

Back to top

Back to top