Description
This function allows parameterised SQL statements to be executed against a datasource that the API server is aware of. Supported database types: MSSQL, MySQL, Postgres, and Oracle. Note that by default non-parameterised, literal values are disallowed for security reasons and will be rejected.
Parameters
| Name | Type | Description |
|---|---|---|
| datasource | String, optional | The name of the datasource to be queried. Defaults to iCM's datasource if not specified |
| sql (string, required) | String, required | The SQL to execute, see below for examples |
| parameters | An array of objects, optional depending on whether parameterised value placeholders exist within the SQL string | Each parameter object represents a parameterised value corresponding to a placeholder in the SQL, very similar to JDBC's handling of SQL parameters. A parameter object consists of both a type and a value ( {"type":"varchar", "value":"Hello, world!" } ) with an optional "subtype" property required for NULL values. Arguments of type NULL require the type of the column to be specified via a subtype, e.g. {"type":"null", "value":null, "subtype":"varchar"}. See the Types table for a list of valid argument types and their corresponding JDBC types |
| transactionId | String, optional | If specified the transactionId must correspond to a transaction identifier previously returned from a call to the beginTransaction method. Passing the transactionId to executeSQL executes the query within that transaction, regardless of the datasource parameter. See Transactional Support for more information |
| generatedKeyColumns | Array of column names, optional, Oracle only | Oracle supports returning values resulting from an INSERT statement (auto-generated IDs for example), but unlike other databases it must be told exactly which columns to return. If executing an INSERT statement on an Oracle backed datasource, and you are interested in returning auto generated IDs such as a primary key, simply supply the name(s) of the column(s) you wish to be returned in the response: { [...] "generatedKeyColumns": ["MyPrimaryKeyColumn"], [...] }. If columns are not specified via generatedKeyColumns for an Oracle database then an Oracle ROWID will be returned - this allows an extremely quick lookup of the just-inserted row via a normal SELECT statement. When inserting data into a table with an auto-generated primary key (e.g. an IDENTITY PRIMARY KEY column in MSSQL) databases other than Oracle will automatically return any IDs (typically primary keys) generated as a result of that insert. MSSQL in this example can identify which columns to return as it has been explicitly declared an IDENTITY column. Oracle however does not support the concept of an auto-generated/auto-incrementing column and instead requires a BEFORE INSERT trigger to be defined which manually calls an internal sequence object to return a new ID into the specified column. Because of this reason Oracle has no way of telling what column values have been auto-generated, and must be told the columns to return by the caller |
| queryTimeout | String, optional | The timeout for queries in seconds. Default 120 |
Types
| Parameter Type String | Corresponding JDBC Type | Notes |
|---|---|---|
| varchar | string | VARCHAR | |
| longvarchar | LONGVARCHAR | |
| byte | BYTE | Wrapped in a byte array for Oracle and Postgres as they cannot handle single byte values. |
| bytes | BYTES | Specified as an array of integers within a valid byte range (0-255). |
| short | SHORT | |
| int | integer | INTEGER | |
| long | LONG | |
| float | FLOAT | |
| double | DOUBLE | |
| decimal | bigdecimal | BIGDECIMAL | Specified and returned as a string to retain precision. Oracle returns all numbers as instances of BigDecimal as it only has one internal type - Number(n,n). |
| date | DATE | Specified and returned as ISO 8601: yyyy-mm-dd or yyyy-mm-ddThh:mm:ssZ Year, month and days retained. Hours, minutes and seconds will be set to midnight. Note: ISO-8601 often sets Milliseconds which aren't supported moment format string pattern: "YYYY-MM-DD[T]hh:mm:ss[Z]" |
| time | TIME | Specified and returned as ISO 8601: hh:mm:ss or yyyy-mm-ddThh:mm:ssZ Year, month, days, hours, minutes and seconds will be retained/set. Note: ISO-8601 often sets Milliseconds which aren't supported. moment format string: "YYYY-MM-DD[T]hh:mm:ss[Z]" |
| timestamp | TIMESTAMP | Specified and returned as a millisecond precision integer or ISO 8601: yyyy-mm-ddThh:mm:ssZ |
| boolean | BOOLEAN | |
| clob | CLOB | |
| null | NULL | value must be null, subtype must match expected non-null supported type. { "value": null, "type": "null", "subtype": "varchar" } |
Transactional Support
The Form Utilities worker supports database transactions via four methods: beginTransaction, executeSQL, commitTransaction, and rollbackTransaction. beginTransaction opens a transactional connection and returns a transactionId which identifies it. This transactionId is a parameter to the three latter functions: executeSQL, commitTransaction, and rollbackTransaction.
Passing the transactionId to executeSQL executes the query within that transaction, regardless of the datasource parameter. Passing the transactionId to commitTransaction or rollbackTransaction will commit (permanently apply all changes made in queries executed within the transaction) or rollback (permanently discard all changes made in queries within the transaction) as appropriate and close it.
Transactions are are automatically rolled back if there is no activity on a transaction (eg a successful executeSQL call) for a certain period of time. The default timeout is 60 seconds but this can be configured via the transactionTimeout worker configuration parameter.
Examples
This example demonstrates writing data into the database defined by the "test" datasource using parameterised values.
{
"id":"1",
"method":"executeSQL",
"params":{
"datasource" : "test",
"sql" : "insert into FT_Messages (Name, Email, Message) values (?, ?, ?)",
"parameters" : [
{"value":"Henry", "type":"string"},
{"value":"[email protected]", "type":"string"},
{"value":"So shaken as we are, so wan with care.", "type":"string"}
]
},
"jsonrpc":"2.0"
}
This example demonstrates writing a null value for a varchar column, a null value for an integer column and the current time using the moment library:
return await this.callWorkerMethod("formutils", "executeSQL", {
"datasource" : "test",
"sql" : "insert into example (Name, Age, EntryDate) values (?, ?, ?)",
"parameters" : [
{"value": null, "type":"null", "subtype": "varchar"},
{"value": null, "type":"null", "subtype": "int"},
{"value": moment().utc().format("YYYY-MM-DD[T]hh:mm:ss[Z]"), "type": "time" }
]
});
