Basic Loading

In this chapter you'll find base information concerning static loading of data:

• Loading from a database table
• Loading from File System

Loading from database table

APPLICABLE TO: Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Chart, Form

Loading characteristics are defined at the stage of component configuration.

There are 2 ways to specify the desired data:

• by the render_table() method when you work with one table
• by the render_sql() method when you work with several tables (to implement complex queries)

Work with one table

When all necessary data is placed in one database table you should use the render_table() method:

<cfset grid.render_table("grid50000","item_id","item_nm,item_cd")>

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 (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:

<cfset grid.render_table("grid50000")>

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, the 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:

<!--- code for loading data --->
<cfif grid.is_select_mode()> 
   <cfset grid.render_sql(
        "Select * from tableA, 
        tableB  where  tableA.id=tableB.id", 
        "a.id","name,price,other"
   )>
<!--- code for other operations - i.e. update/insert/delete --->
<cfelse> 
    <cfset grid.render_table("tableA","id","name,price")>
</cfif>

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. In this case you should use the render_sql() method:

<cfset grid.render_sql(
    "SELECT * from tableA INNER JOIN tableB  ON tableA.id=tableB.id",
    "",
    "name,price"
)>

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 (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.

Extra data

The last parameter of the render_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.).

<cfset grid.render_table("tableA","id","name,price","extra1,extra2")>   
<!--- or --->
<cfset 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 the client side, and won't be included in update/insert operations.

Tree and TreeGrid specificity

In case of Tree and TreeGrid, both the render_sql and render_table 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.

<cfset tree.render_table("tableA","id","name,price","","parent_id")> 
<!--- or --->
<cfset tree.render_sql(
    "Select * from tableA, tableB  where  tableA.id=tableB.id", 
    "a.id",
    "name,price,other",
    "",
    "parent_id"
)>

Aliases

To make the use of extracted data handier you can use aliases for DB field names (makes sense only if you use server-side events):

<cfset grid.render_table("tableA","id","name,price(product_price)")>   
<!--- or --->
<cfset grid.render_sql(
    "Select *,tableA.id as aid from tableA, tableB  where  tableA.id=tableB.id", 
    "tableA.id(aid)",
    "name,price(product_price),other"
)>

Back to top

Loading from File System

APPLICABLE TO: Grid, TreeGrid, Tree, Combo, Scheduler, DataView, Chart, Form

Starting from version 1.0, dhtmlxConnector allows using FileSystem as datasource. It can be used with any client-side component, but the most common use-cases are grid and tree:

<cfset grid = createObject(
    "component",
    "dhtmlxConnectors.GridConnector").init("","FileSystem")>
<cfset grid.render_table(
    "../",
    "safe_name",
    "filename,full_filename,size,name,extention,date,is_folder"
)>

In the code snippet above, grid is filled with info about files in the parent folder of script location.

Parameters of the 'render-table' method:

• a folder, for which data listing is required
• the field's id. Leave it empty or use safe_name as the ID of the file
• a list of fields, possible values are:
  • filename - name of the file
  • full_filename - full path to the file
  • size - size of the file in bytes
  • name - name part of the file name
  • extension - extension part of the file name
  • date - timestamp of the file
  • is_folder - the file/folder flag

Limiting files in output

There are 3 ways to limit files in output:

1. by extension type
2. by regexp pattern
3. by meta-type

by extension type:

<cfinvoke component="dhtmlxConnectors.FileSystemTypes" method="getInstance" returnvariable="fileTypes"></cfinvoke>
<cfset fileTypes.addExtentionNot('docx')>

by regexp pattern:

<cfinvoke component="dhtmlxConnectors.FileSystemTypes" method="getInstance" returnvariable="fileTypes"></cfinvoke>
<cfset fileTypes.addPattern('com[.]*')>

by meta-type:

The following meta-types can be used:

• image - image files
• document - doc, xls, txt, rtf
• web - php, html, js, css
• audio - mp3, wav, ogg
• video - avi, mpg, mpeg, mp4
• only_dir - folders

<cfinvoke component="dhtmlxConnectors.FileSystemTypes" method="getInstance" returnvariable="fileTypes"></cfinvoke>
<cfset fileTypes.setType('web')>

Back to top

Back to top