Database Functions
The following sets of functions are used for managing data in databases accessed via Database profiles:
Database Table functions - Look up and insert data into tables
Callable Statement functions - Execute stored procedures
Prepared Statement functions - Efficiently execute statements multiple times, and have support for failover
Database Bulk functions - Bundle multiple queries into one SQL statement
Note!
All database-related APL functions use a database connection pool and pick a connection from it for each invocation. Each function handles connection properties, such as the transaction state, internally, and these properties are never visible in APL.
These database functions work with the following data types: character, string, integer, and date, such as VARCHAR, INTEGER, NUMBER, DATE, TIMESTAMP, BOOLEAN, CLOB, and BLOB.
APL database functions do not support other database data types. The list below contains examples of such types. These types are usually very large data types, or custom objects, structures, and similar types.
User-defined structures and objects
User-defined collections and arrays
Spatial or geometry types
JSON and XML
Note!
The BLOB data is parsed and stored as a hex string, and CLOB data as a string. To insert BLOB data using APL, you must use a string or a byte array.
Database‑related APL functions support BLOB and CLOB when the database JDBC driver supports them. This behavior is tested with Oracle, SAP HANA, and PostgreSQL, and it can vary with other JDBC drivers.
Warning!
Database‑related APL functions support BLOB and CLOB sizes up to 50 MB per function call. If you use BLOB or CLOB data larger than 50 MB in a single call, the function returns an error that states that the maximum allowed size is exceeded.
To keep workflow performance acceptable, use these operations only when necessary and limit concurrent reads and writes of large BLOB and CLOB data.
Warning!
PostgreSQL does not support BLOB and CLOB data types. In PostgreSQL, the BLOB equivalent is BYTEA, and the CLOB equivalent is TEXT.
This section has the following subsections: