How to: use Recordset variable and to_json function for populating a tree node
In order to generate json data from database, record set variable and to_json function can be used as shown in the example below,
Record set must ;
have at least 3 columns: "id", "text", "parent".
If actual table column name is different from "id", "text" and "parent", column alias can be used as showed above.
Record set can optionally have;
"children" column. As stated in jsTree documentation, recorset's column value must be either "true" or "false". True means the node has child(s) node, false means node is a leaf node and cannot be expanded.
Example:
SQLX File:
Form File:
<!DOCTYPE html>
<html>
<head>
<title>HT/SQL SHOWCASE (LOCATIONS TREE VIEW)</title>
</head>
<body>
<center>
<controlblock control-block-name="controlblock">
<input name="show_all" type="button" value="All in one"/>
<input name="show_deps" type="button" value="Departments"/>
<input name="show_grid" type="button" value="Locations (Grid View)"/>
<input name="show_tree" type="button" value="Locations (Tree View)"/>
<input name="show_colors" type="button" value="Colors"/>
<input name="show_users" type="button" value="Users"/>
<br />
<br />
<input name="commit" type="button" value="Commit"/>
<input name="rollback" type="button" value="Rollback"/>
|-|
<input name="create-record" type="button" value=" + "/>
<input name="delete-record" type="button" value=" - "/>
|-|
<input name="first-page" type="button" value="<<"/>
<input name="prev-page" type="button" value=" < "/>
<input name="next-page" type="button" value=" > "/>
<input name="last-page" type="button" value=" >> "/>
|-|
<input name="enter-query" type="button" value="Enter Query"/>
<input name="execute-query" type="button" value="Execute Query"/>
<input name="cancel-query" type="button" value="Cancel Query"/>
|-|
<input name="kill-session" type="button" value="Log Out" redirect-url="/site/pages/deps/logon.frmx"/>
<br />
<br />
</controlblock>
<table style="width: 850px">
<tr>
<td style="vertical-align: top; width: 275px">
<controlblock control-block-name="cbTree">
<tree id="data"></tree>
</controlblock>
</td>
<td style="vertical-align: top; width: 250px">
<datablock data-block-name="countries" data-schema-name="htsql" data-source-name="countries"
order-by-clause="country_name" record-count="10" show-hide-mode="display" master-data-block-name="Session">
<table>
<thead>
<tr>
<td>COUNTRY ID</td>
<td>COUNTRY NAME</td>
<td>REGION ID</td>
</tr>
</thead>
<tbody>
<tr>
<td>
<input name="country_id" data-type="char" column-name="country_id" style="width: 50px"
type="text" />
</td>
<td>
<input name="country_name" data-type="char" style="width: 150px"
column-name="country_name" type="text"/>
</td>
<td>
<input name="region_id" data-type="char" style="width: 50px"
column-name="region_id" type="text" master-item-name="selectedNodeId"/>
</td>
</tr>
</tbody>
</table>
</datablock>
<datablock data-block-name="provinces" data-schema-name="htsql" data-source-name="provinces"
order-by-clause="province_name" record-count="10" show-hide-mode="display" master-data-block-name="Session">
<table>
<thead>
<tr>
<td>PROVINCE ID</td>
<td>PROVINCE NAME</td>
<td>COUNTRY ID</td>
</tr>
</thead>
<tbody>
<tr>
<td>
<input name="province_id" column-name="province_id" sequence-name="htsql.htsql"
type="text" style="width: 50px"/>
</td>
<td>
<input name="province_name" column-name="province_name" type="text" style="width: 150px"/>
</td>
<td>
<input name="country_id" column-name="country_id" type="text" style="width: 50px"
master-item-name="selectedNodeId"/>
</td>
</tr>
</tbody>
</table>
</datablock>
<datablock data-block-name="counties" data-schema-name="htsql" data-source-name="counties"
order-by-clause="county_name" record-count="10" show-hide-mode="display" master-data-block-name="Session">
<table>
<thead>
<tr>
<td>COUNTY ID</td>
<td>COUNTY NAME</td>
<td>PROVINCE ID</td>
</tr>
</thead>
<tbody>
<tr>
<td>
<input name="county_id" column-name="county_id" sequence-name="htsql.htsql"
type="text" style="width: 50px"/>
</td>
<td>
<input name="county_name" column-name="county_name" type="text" style="width: 150px"/>
</td>
<td>
<input name="province_id" column-name="province_id" type="text" style="width: 50px"
master-item-name="selectedNodeId"/>
</td>
</tr>
</tbody>
</table>
</datablock>
</td>
</tr>
</table>
</center>
</body>
</html>SQLX File:
void page.load() {
populate_tree('cbTree.data');
}
void provinces.post_delete() {
refresh_tree_node('cbTree.data', :Session.selectedNodeParentId);
}
void provinces.post_insert() {
refresh_tree_node('cbTree.data', :Session.selectedNodeId);
}
void provinces.post_update() {
refresh_tree_node('cbTree.data', :Session.selectedNodeParentId);
}
void countries.post_delete() {
refresh_tree_node('cbTree.data', :Session.selectedNodeParentId);
}
void countries.post_insert() {
refresh_tree_node('cbTree.data', :Session.selectedNodeId);
}
void countries.post_update() {
refresh_tree_node('cbTree.data', :Session.selectedNodeParentId);
}
void counties.post_delete() {
refresh_tree_node('cbTree.data', :Session.selectedNodeParentId);
}
void counties.post_insert() {
refresh_tree_node('cbTree.data', :Session.selectedNodeId);
}
void counties.post_update() {
refresh_tree_node('cbTree.data', :Session.selectedNodeParentId);
}
void cbTree.data.tree_node_selected(char node_id, char parent_id, char node_type) {
:Session.selectedNodeId = node_id;
:Session.selectedNodeParentId = parent_id;
/* message('node_id : ' || node_id || '. node_type : ' || node_type); */
if (node_id == 'no_region') then
show_page('locations_grid.frmx');
end if;
hide_block('countries');
hide_block('provinces');
hide_block('counties');
if (node_type == 'R') then
refresh_block('countries', 'region_id = :node_id');
show_block('countries');
end if;
if (node_type == 'C') then
refresh_block('provinces', 'country_id = :node_id');
show_block('provinces');
end if;
if (node_type == 'P') then
refresh_block('counties', 'province_id = :node_id');
show_block('counties');
end if;
}
char cbTree.data.tree_node_expanded(char node_id, char node_type) {
char json;
/* message('node_id : ' || node_id || '. node_type : ' || node_type); */
if (node_id == '#') then
rs = select
region_id as id,
region_name as text,
'#' as parent,
'R' as type,
case (select count(*) from htsql.countries c where c.region_id = r.region_id)
when 0 then
'false'
else
'true'
end children
from
htsql.regions r;
if (rs) then
/* */
json = to_json(rs);
else
json = '[ { "id" : "no_region", "parent" : "#", "text" : "Click here to add new region" } ]';
end if;
end if;
if (node_type == 'R') then
rs = select
country_id as id,
country_name as text,
cc.region_id as parent,
'C' as type,
case (select count(*) from htsql.countries c where c.region_id = cc.region_id)
when 0 then
'false'
else
'true'
end children
from
htsql.countries cc
where
cc.region_id = :node_id;
json = to_json(rs);
end if;
if (node_type == 'C') then
rs = select
PROVINCE_ID as id,
PROVINCE_name as text,
country_id as parent,
'P' as type,
case (select count(*) from htsql.counties c where c.province_id = cc.province_id)
when 0 then
'false'
else
'true'
end children
from
htsql.provinces cc
where
cc.country_id = :node_id;
json = to_json(rs);
end if;
if (node_type == 'P') then
rs = select
county_id as id,
county_name as text,
province_id as parent,
'false' as children
from
htsql.counties c
where
c.province_id = :node_id;
json = to_json(rs);
end if;
return json;
}See also:
For detail on json data format : https://www.jstree.com/docs/json/
Last updated
Was this helpful?