Using the SQL boolean macro to simplify multi-database compatibility

Currently, NexJ supports the MS SQL and Oracle databases. As a result, whenever an upgrade step is linked to a SQL script, the script needs to be verified for compatibility with MS SQL and Oracle. If there are differences, then a switch statement is used to define conforming SQL for each database. In preparation for PostgreSQL support, which is in our near-term roadmap, we also plan to maintain compatibility across the databases that we intend to support.

To separate boolean and integer literals for the upgrade scripts that include metadata boolean attributes (for example, PostgreSQL requires the use of true or false, whereas MS SQL and Oracle use 1 and 0 for the boolean attributes), you can use one of the following  methods:

• Method 1: Update the SQL script to use the boolean macros ${true} and ${false}, which are translated correctly by the corresponding database adapters.

  

  This option is the recommended option and the one that should be used for all new code.
• Method 2: Use a switch statement that redefines the SQL for PostgreSQL using boolean values, true and false, instead of integers 1 and 0.

Method examples

The following screenshot shows an example for using method 1:

The following screenshot shows an example for using method 2: