Making Queries

Simple queries

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

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.

In case of dnd, connector will process an action as a sequence of 'insert' and 'delete' operations.

Complex queries

When you need to define your own logic you should use one of two ways:

Custom queries for an action

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

You can define your own SQL code for a specific action (INSERT, UPDATE or DELETE) as follows:

<cfset grid.sql.attach(
    "Update",
    "Update tableA set name='{name}', 
    price={price} where id={id}"
)> 
<!--- ... --->
<cfset grid.render_sql(" .. ","id","price,name")>

Parameters:

  • an action's name. Possible values are: 'Update', 'Insert', 'Delete'
  • SQL statement. It can use 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

To customize operations you can use the following server-side events:

<!--- data preprocessing before update --->
<cffunction name="my_update">
    <cfargument name="data">
        <cfset var price = ARGUMENTS.data.get_value("price")>
    <cfset ARGUMENTS.data.set_value("price","10")>  
</cffunction>
<cfset conn.event.attach("beforeUpdate",my_update)>


<!--- including additional field to request --->
<cffunction name="my_update">
    <cfargument name="data">
    <cfset ARGUMENTS.data.add_field("userId",1)>    
</cffunction>
<cfset conn.event.attach("beforeUpdate",my_update)>


<!--- fully custom code --->
<cffunction name="my_update">
  <cfargument name="data">
  <cfset var price = ARGUMENTS.data.get_value("price")>
  <cfset var id = ARGUMENTS.data.get_value("id")>
  <cfset request.conn.sql.query("UPDATE some_table SET price='#price#' where id=#id#")> 
  <cfset ARGUMENTS.data.success()>  
</cffunction>
<cfset request.conn.event.attach("beforeUpdate",my_update)>
<!--- we use 'request' scope to access connector within custom event--->
Back to top