Database coding notes

It is important to consider the following things when you are developing database code for FTM:

• For some databases, the IBM® App Connect Enterprise ODBC driver returns INTEGER fields as DECIMAL, which triggers type errors in the application flow. To avoid these errors, explicitly cast the affected fields to integer types in the ESQL code. For example,

  CAST(refObj.ID TO INTEGER)

• Using AS when a table alias is specified, is not universally supported by all database vendors. While Db2® supports specifying a table alias with or without AS, it is better to code without AS if portability is a concern.
• Some field names are reserved words in a database. To avoid confusing the database with them and creating errors, it is good practice to enclose table names and field names between double quotation marks. For example, use "OBJ"."ID" instead of OBJ.ID.
• Use READ ONLY queries to minimize locks and resources by using:

  Db2

  Use the With UR clause.

  The FTM Core project detects the type of database automatically. FTM Core includes a function in SQLHelper.esql, called GetReadOnlyQuery(), that formats an SQL select statement to the correct read-only syntax for Db2.