SQL queries for external objects

Although Platform Private cloud can automatically read the structure of an external table, the actual design of the external database remains unknown to Platform. For this reason, during the process of creating external objects, you have the option to adjust the SQL queries that Platform generates automatically. If Platform experiences a SQL error while working with external objects, an error will be displayed. The Adjust SQL page allows you to fix those errors by editing the SQL directly. For existing external objects, the Adjust SQL page is available directly from the More Actions drop-down list on an object view page. If you add or modify a field for an external object, the Adjust SQL page opens automatically.

To access and manipulate data records in external tables, Platform uses SELECT, INSERT, UPDATE, and DELETE SQL queries. To adjust SQL queries, you must be familiar with SQL, details of your database implementation, and how Platform formulates SQL queries.

Note the following about how Platform generates and handles an SQL query:

• Platform encloses table and column names in double quotes whenever the names contain characters not recognized as an identifier by SQL syntax or when the names are reserved keywords. This conforms to the ANSI standards. Therefore, you must ensure that your external database supports double quotes in an SQL query.

  By default, the latest versions of the databases certified by Platform support double quotes in queries. If your database does not support double quotes for tables and column names, then you must configure it to do so. For example, as MySQL does not support double quotes by default, you must add ANSI to the comma-separated values of the SQL Mode property.

• If Platform encounters an identifier that is not listed as a reserved SQL keyword or a special character in its Shared Properties, but in reality is an SQL reserved keyword or a special character in your database, then the SQL query might result in an error because the column or table name will not be enclosed with double quotes in the SQL query.

  In such cases, you must manually edit the SQL query in the Adjust SQL page or add those SQL keywords and special characters to Shared Properties.

  Infinite Blue recommends that you verify and add any reserved keywords and special characters in the Shared Properties before generating SQL queries for External objects. In Shared Properties, you must locate SQLKeywords and SQLSpecialChars code snippets and then add comma-separated keywords and special characters respectively. You must restart Platform for any update in Shared Properties to take effect.

You can use stored procedures with parameters to replace the default INSERT, UPDATE, and DELETE SQL queries. Platform will use these queries exactly the way they're provided. However, since SELECT queries are necessary for filtering and sorting, a SELECT query cannot be replaced with a stored procedure. The query or stored procedure must fetch an ID for new records.

SQL queries must use actual database column names. To supply data to the database, INSERT and UPDATE queries must use template tokens for Platform fields. To preview available tokens and corresponding column names use the View Table Columns button which brings up helper information as shown below. The Preview Query button allows you to see whether a query is working correctly.

If you are using an external database in a multi-tenant environment you need to ensure isolation of customers' data. You can use the helper tokens {!#CURR_CUSTM.id} and {!#CURR_CUSTM.name} in WHERE clauses. For instance, if you're using column CUST_ID to store customer's ID, add the following to the SELECT query:

.. WHERE CUST_ID={!#CURR_CUSTM.id}

Please use only tokens listed in the helper table. Other tokens will be ignored, making the SQL syntax invalid. At runtime these tokens will be replaced with actual values from the records. This offers the most flexible way to build queries which may include calls to stored procedures.

The following table explains the formats used by different types of data fields for tokens in external queries.

Field Type	Format	Example
String	Text in single quotes. Single quite inside text replaced by two.	'TEST' 'O''Neal'
Numeric	Number	123.45
Date	'yyyy-mm-dd'	'2012-06-15'
Date/Time	'yyyy-mm-dd hh:mm:ss.000'	'2012-06-15 18:45:12.000'
Foreign Key	Number or text in single quotes	123456 or 'XYZ'