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.

Complex queries

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

• defining custom SQL code for operation
• using server-side events to customize operations

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:

• beforeUpdate
• beforeInsert
• beforeDelete
• beforeProcessing

<!--- 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