Execute sql queries

In addition to having the transaction on page execute some kind of SQL query, you may need to execute another on a different table, or on the same table to either insert a related record, to update a field or even delete a record. You can achieve this by using a custom trigger that runs the SQL query using tNG methods. Using the tNG offered functions and methods for working with SQL queries will ensure that if you ever want to change the database or other component, your code will still work.

Before adding a Custom Trigger that executes an SQL query, you must meet the following prerequisites:

1. Have MX Kollection - or one of the extensions in the bundle correctly installed.

2. Have a page with a transaction applied on it - this can be an insert, update, delete, login, register, custom or any other of the supported transactions.

3. Have basic knowledge of the application server language you intend to use - you will have to customize the examples below for your particular situation.

After creating the basic application page which contains a transaction, you must apply the Custom Trigger server behavior. This allows you to enter code that will get executed in the transaction's context. Through the Custom Trigger properties you can define the execution order. You can learn more about the Custom Trigger server behavior here.

You can use MX Kollection methods to run the query, escape values for use with SQL and even handle errors. This will mean that your triggers will still work if you change the database type or other underlying setting. Some of the methods you can use when creating a custom trigger that executes SQL:

• tNG->connection->execute("query") - this executes the actual query

• KT_escapeForSql("value","type") - in order to use a value within a sql query and not get an error you must escape it properly. This function properly encloses values based on type and the database type used.

• tNG->getColumnType("column_name") - this allows retrieving the type of a column.

The basic code for a custom trigger that will execute an sql query is as follows:

1. First define the query to run. You should assign the query to a string variable for ease of use. You can create any type of query, no matter how complex.
   
   query = "SELECT [column_list | *] FROM [table_name]"
    

2. Run the query using the tNG->connection->Execute method, and assign its result to a variable:
   
   result = tNG->connection->Execute(query)
    

3. The Custom Trigger must return a value:
   
   return RET
    

Note: Do not copy and paste code from the fragment above, as it will cause errors. Instead use some of the examples below.

In addition to this basic structure that allows you to run a query, you can also use the tNG error object to handle the case when the query execution failed. To do so, you must:

1. Check if the query execution result (the variable to which it was assigned) contains the expected results:
   
   if (result = FALSE)
    

2. Next create a new instance of the error object, and initialize it with the desired error message:
   
   query_error = new tNG_error ("The query has failed error message here), array(), array())
    

3. Next you have to exit the custom trigger and return the new error object to the dispatcher:
   
   return query_error
          

4. For the case when everything goes smoothly, you have to return NULL. Create an else branch and add the return NULL instruction:
   
   else
   return Null
         

Example: the following example uses the Discussion Board tutorial database structure, and the post message page. When posting a new message, aside the regular information you have to enter in the form fields, an additional field has to be set after the insert operation: the id_init_msg field must be set to the same value as the new message ID. The custom trigger that is executed after the insert transaction performs an update on the last record and sets the correct values through an update transaction.

• For PHP:
  
  $query = "UPDATE message_msg SET id_init_msg = ".KT_escapeForSql($tNG->getPrimaryKeyValue(), $tNG->getColumnType($tNG->getPrimaryKey()))." WHERE id_msg = ".$tNG->getPrimaryKeyValue();
  $update_result = $tNG->connection->execute($query);
  if(!$update_result) {
  $updateError = new tNG_error("Error setting the initial message ID",array(),array());
  return $updateError;
  } else {
  return NULL;
  }
   

• For ASP
  
  query = "UPDATE message_msg SET id_init_msg = " & KT_escapeForSql(tNG.getPrimaryKeyValue(),tNG.getColumnType("id_msg")) & " WHERE id_msg = " & tNG.getPrimaryKeyValue()
  On Error Resume Next
  Set update_result = tNG.connection.Execute(query)
  if Err.Number <> 0 then
  Set updateError = new tNG_error
  update_error.init "Error setting the initial message ID", Array(), Array()
  Set Trigger_Custom = updateError
  else
  Set Trigger_Custom = nothing
  end if
  On Error GoTo 0
   

• For ColdFusion you must use a different approach because you are not allowed to execute queries within <cfscript> tags. And a Custom Trigger is contained within <cfscript> tags. To still be able to use triggers that execute SQL queries, you must add a separate function within the page code that takes care of the sql related operations - let's say named AliasCustomTrigger, and in the trigger's code box only enter code to return the function's value: return Request.AliasCustomTrigger. The same objects and MX Kollection functions can be used within the function to prepare the query and handle errors. The code sample below shows the function and the custom trigger code used to call it:
  
  <cffunction name="AliasCustomTrigger">
  <cfargument name="tNG" required="true">
  <cfset var query = "">
  <cfset var result = "">
  <cfset var myerror = "">
  <cfset query = "UPDATE message_msg SET id_init_msg = " & tNG.getPrimaryKeyValue() & " WHERE id_msg = " & tNG.getPrimaryKeyValue()>
  <cflock name="ins_message_msg" type="exclusive" timeout="10">
  <cftry>
  <cfquery name="result" datasource="#tNG.connection#">
  #PreserveSingleQuotes(query)#
  </cfquery>
  <cfcatch>
  <cfset updateError= Request.tNG_CreateObject("tNG_error")>
  <cfset updateError.init ("Error setting the initial message ID", ArrayNew(1), ArrayNew(1))>
  </cfcatch>
  </cftry>
  </cflock>
  <cfif IsObject(updateError)>
  <cfreturn updateError>
  <cfelse>
  <cfreturn Request.KT_Null>
  </cfif>
  </cffunction>
  <cfset Request.AliasCustomTrigger = AliasCustomTrigger>
  
  <cfscript>
  //start Trigger_Custom trigger
  function Trigger_Custom(tNG) {
  return Request.AliasCustomTrigger(tNG);
  }
  Request.Trigger_Custom = Trigger_Custom;
  //end Trigger_Custom trigger
  </cfscript>
   

In the next section you will learn how to use fields in a transaction for a custom action.