Use SQL Binding Expressions

The SQL query text can contain $ binding expressions. Expressions based on the sql object create database-specific SQL functions or format field values according to the current database dialect. For example:

String sql = "UPDATE wr
SET time_stat_chg = ${sql.currentTime},
date_stat_chg = ${sql.currentDate},
...

SQL binding expressions exist to handle database-specific date/time values and concatenation. For a full list of SQL binding expressions,

SQL Object

For a description of the use of binding expressions in workflow rule services,

Binding Expressions

SQL binding expressions are the preferred way to create database-independent SQL. The formatSQL functions, described below, should be used when a corresponding binding expression is not available.

Use formatSQL Functions Instead of Server-specific Functions

In many cases, you will want to use a database server function to execute that function server-side. The problem is that the function has different names and parameters in different database SQL dialects. The formatSQL functions isolate you from the differences.

EventHandlerBase has the following methods that return the names of server-specific functions ready to be embedded in SQL queries. Server-independence aside, use of these queries makes your SQL statements more readable, since they simplify the function names and illustrate the intent behind operations like data arithmetic.

sql += "(CASE WHEN ("
+  formatSqlDaysBetween(context, currentDate, "reserve_rs.date_start")
+  " > cancel_days)"
+  ...

See List of formatSQL Functions .

If the server function you need is not in the list above, you can write your own method to return server-independent values using the IsOracle and isSQLServer() methods listed below. Note that many SQL functions, like RTRIM(), have identical function signature on all three database platforms, and can be used as is.

Use Conditional Server Code If Needed

Both the EventHandlerBase class and the DataSource interface have methods -- isOracle() and isSQLServer() -- for adding clauses specific for a server. You should only use these functions if there is no more generic way to implement the query. Typically, you will want to make only the conditional section of the query dependent on these tests and keep the main body of the SQL statement identical for all servers.

if ( roomDS.isOracle() ) {
sql += "ORDER BY pmsd.date_todo, pmp.pmp_id, pmpstr.tr_id";
}

You may also use these methods for writing your own “formatSQL” style functions for isolating your code from SQL function dependencies if you need to use an SQL function that is not in the list above.