Data Management: Direct SQL Database Access

Business data can be directly loaded from and saved to a standard SQL database. A set of functions provide the lowest level of access to a database without any automation.

These functions give you full control over queries, which must be built manually. Object-relational mappings must be implemented manually, too.

Directly accessing a database is not a best practice, although it is sometimes inevitable. If possible, consider implementing a service-oriented facade instead (e.g. SOAP or REST).

This document explains how to:

• execute the SQL statements INSERT, UPDATE, SELECT, and DELETE
• handle database transactions
• map data retrieved from the database to FNZ Studio data entities
• import data from CSV file to the database, and export it back to the CSV format
• convert dates to an SQL date type.
• debug database access

Data Source

To access an SQL database from FNZ Studio, define a JDBC / JNDI Data Source as described in Data Management: Data Sources.

SinceFNZ Studio is a Java web application, you can use any database providing a driver compatible with JDBC 4. For FNZ Studio's SQL functions to work properly, the database must comply with the basic SQL-92 set of database instructions.

Functions reference the Data Source by name. As a best practice, avoid passing the Data Source name as a constant value to a function. Instead, set a custom configuration property and use the CONF() function to retrieve the configurable Data Source name. This makes your app independent of the environment, enabling you to switch easily between e.g. TEST, UAT and PRODUCTION databases.

Function getDS(String contextName:='default'): String Begin
   Return CONF('db.' & contextName & '.source');
End 

Example: function retrieving the Data Source for a given business context from a configuration property

SQL Statement Execution

The SQL statements INSERT, UPDATE, SELECT, and DELETE can be executed either via an SQL query or by calling a stored procedure.

SQL Queries

The "SqlQuery" function executes SQL queries. SELECT statements returns an indexed collection (result set) of named collections (single results). DELETE, INSERT and UPDATE statements return the number of modified or deleted table rows.

SqlQuery($datasource, $query)

The parameters are:

• $datasource (String) - Name of the Data Source. Example: 'TestDB' Note: If you want to manage the transactionality with SqlOpen and autocommit false, set the DataSourceName to null as a first parameter of the SqlQuery, otherwise a new SqlContext is created and the autocommit option is set to true.
• $query (String) - SQL query Example: 'SELECT COUNT(entityId) FROM entity'
• $params (Indexed Any, Optional) - Parameters of the query for a prepared statement
• $limit (Integer, Optional) - Limit to the number of result. The default value is 0 (no limit). Example: 100
• $keyColumnIndex (Integer, Optional) - Index of the auto-generated key column. The default value is null (non-specified). Example: 2
• $queryTimeout (Integer, Optional) - Timeout for the query in seconds; sets the number of seconds the driver will wait for a running statement complete. Use null or 0 for unlimited queries. By default, there is no limit. If the limit exceeded, a java.sql.SQLTimeoutException nested inside a com.nm.sdk.NmRuntimeException is thrown. Example: 10

The return value is:

• Indexed Any - Result of the given SQL query (see Result Set for details).

Examples:

Indexed Any $resultSet := SqlQuery(getDS(), $sqlQuery, $params, null, $timeoutSeconds)

SqlQuery(getDS() ,'SELECT COUNT(entityId) FROM entity') 

//Returns: Any[ Any{'COUNT(entityId)':123322}]

Queries and Parameters

As a best practice queries should be written using "?" as parameter holder. Do:

String $sql := 'SELECT * FROM Person WHERE name = ? AND birthDay = ?';
Indexed Any $params := ['O\'Donnal', TODAY()]:Any;
Indexed Any $resultSet := SqlQuery(getDS(), $sql, $params);

Don't:

String $sql := 'SELECT * FROM Person WHERE name = ' & 'O\'Donnal' & ' AND birthDay = ' & TODAY();
Indexed Any $resultSet := SqlQuery(getDS(), $sql);

Result Set

The following examples show how to iterate through the result set of a select statement. Please, note that the content of a result set varies depending on the SQL statement executed and that the structure of the result set might vary slightly depending on the database and driver versions. For example, certain driver versions wrap the result set of a SELECT statement into a collection of result sets, which is then reflected in the structure returned by the SQLQuery Script Function.

SELECT

SELECT statements return a named collection of column values wrapped in an indexed collection.

String $sql := 'SELECT * FROM Person WHERE name = ?';
Indexed Any $params := ['O\'Donnal']:Any;

Indexed Any $resultSet := SqlQuery(getDS(), $sqlQuery, $params);

Indexed Person $persons := new Indexed Person;
ForEach Named Any $result In $resultSet Do
 Person $person := new Person;
 $person.name:=$result['name'];
 $person.birthDay:=$result['birthDay'];
 $persons.addElement($person);
End

INSERT and UPDATE

INSERT and UPDATE statements return the number of updated table rows.

String $sql := 'INSERT INTO Person VALUES(?,?)';
Indexed Any $params := ['Mendel','1843-05-09']:Any;

SqlQuery(getDS(), $sql, $params);

// Returns: [   {'keys' = []:String, 'updated' = 1}:Any ]:Any

--

String $sql := 'UPDATE Person  SET birthDay = ? WHERE name = ?';
Date $birthDay := TIMEADD(NOW(), '-' & CEIL(RANDOM() * 10000) & 'D');
Indexed Any $params := [ToSqlDate($birthDay), 'Mendel']:Any;

SqlQuery(getDS(), $sql, $params);

// Returns: [   {'keys' = []:String, 'updated' = 1}:Any ]:Any

DELETE

DELETE statements return the number of deleted table rows.

String $sql := 'DELETE FROM Person WHERE name = ?';
Indexed Any $params := ['Mendel']:Any;
SqlQuery(getDS(), $sql, $params);
// Returns: [ {'keys' = []:String, 'updated' = 1}:Any ]:Any

Stored Procedures

The functions SqlCall and CallOracleProcedure are available for calling stored procedures. See also "Calling Stored Procedures" for more information. This is an example procedure defined in the database:

-- --------------------
-- MySql Routine DDL �-
-- --------------------
DELIMITER $$

CREATE PROCEDURE `getPersonBirthDay`
(IN PERSON_NAME VARCHAR(50), OUT PERSON_BIRTHDAY date)
BEGIN
  SELECT birthDay INTO PERSON_BIRTHDAY
  FROM Person p WHERE p.name = PERSON_NAME;
END $$

DELIMITER ;

SQL Call

The function SqlCall returns the result of the given SQL call.

SqlCall($datasource, $query)

Examples:

SqlCall('dataSourceId', '{call schema.procedureName(?)}', [{'type'='in', 'value'=$argValue}:Any]:Any, $timeout)

SqlCall('dataSourceId', '{call truncateWeeklyStats()}')

SqlCall('dataSourceId', '{call removeAccount(?)}', [{'type'='in', 'value'=$accountId}:Any]:Any)

SqlCall('dataSourceId', 'call transferMoney(?,?,?)', [{'type'='in', 'value'=$accountIdFrom}:Any, {'type'='in', 'value'=$accountIdTo}:Any, {'type'='in', 'value'=$amountToBeTransferred}:Any]:Any);

SqlCall('dataSourceId', 'call getConfig()', [{'type'='out', 'sqlType'='varchar'}:Any]:Any);

Oracle Procedure Call

The function CallOracleProcedure returns the result of the given SQL call to an Oracle Procedure. It uses Oracle-specific values.

CallOracleProcedure($datasource, $query)

Example:

CallOracleProcedure('xe', 'Begin TESTS.CURSOR_TEST (?, ?); End;', [{'type'='in', 'value'='2'}:Any, {'type'='out/cursor'}:Any]:Any)

Transaction Support

Transactions let you execute multiple statements in one go. In a script, start a transaction with the SqlOpen command. Subsequent calls with SqlQuery belong to the same transaction. Within a transaction, functions modifying data on the database (e.g. SqlQuery, SaveDataEntity) must use "null" as Data Source name.

End the transaction with either SqlCommit or SqlRollback. Use a Try-Catch-Finally structure to handle exceptions: commit the operations if they were successful, or roll back any changes if they weren't. Both SqlCommit and SqlRollback immediately open a new transaction, so there is no need to use SqlOpen again in the same context.

Use SqlClose to close the database connection when all transactions have been committed or rolled back and no more transactions are needed.

SqlOpen(getDS(), false); 
Try  
    SqlQuery(null, �);  
    SqlCommit(); 
Catch  
    SqlRollback(); 
Finally  
    SqlClose(); 
End

Template script for a database transaction

SQL contexts used for handling transactions build a stack. When using SqlOpen, a new context is pushed onto the stack. All subsequent SQL commands use the top-level context. The SqlClose command will pop the top-level context from the stack.

For an example of use, see Use database transactions

Open

The function SqlOpen starts a new transaction context.

SqlOpen($datasource)

Here are the Parameters:

• $datasource(String) - Name of the Data Source.
  • Example: 'TestDB'
• $autoCommitOrRollback(Boolean, Optional) - If true, statements are automatically committed. If false, SqlCommit and SqlRollback commands must be used.

Return value: nothing

Example:

SqlOpen('TestDB', false)

Commit

The function SqlCommit commits the current transaction. It takes no parameters and has no return value.

SqlCommit()

Roll Back

The function SqlRollback rolls back the current transaction. It takes no parameters and has no return value.

SqlRollback()

Close

The function SqlClose closes the current context-associated connection. It takes no parameters and has no return value.

SqlClose()

Data Mapping

When directly accessing a database via SQL, you must manually map the queried data onto Data Entities for "SELECT" operations. In reverse, Data Entities must be mapped back to the query for "INSERT" or "UPDATE" operations. There are several ways you can do this:

• Script Function: Write a Script Function to create Data Entities from database records. Straightforward direct data mapping, but inefficient and hard to maintain for many Data Classes.
• SqlLoad function: Automatically create Data Entities if the database table's column names match the Data Class property names.
• Mapping model: create a model which uses the Data Class definition as reference for properties

Function-based Mapping

The simplest way to map database data onto FNZ Studio data is to create a dedicated Script Function. This Script Function takes a database record as input, and outputs the required Data Entity structure.

Function CreatePerson(Named Any $record) : Person Begin 
   Person $p := new Person;
   $p.firstName := $record[firstName];
   $p.lastName := $record[lastName];
   Return $p;
End

Example of Script Function creating "Person" objects from database table records

This simple, straightforward mapping strategy provides the most fine-grained data mapping possible; any special case can be taken into account. However, it results in lots of code for many different Data Classes and is difficult to maintain.

Simple Automatic Mapping

The SqlLoad function loads database records as instances of a Data Class. It only works if the database table's column names match the Data Class property names. For example, for a Person Data Class with the properties "irstName and lastName, there must be a database table with a firstName and a lastName column.

SqlLoad($dataClass, $dataSource, $query)

Here are the parameters:

• $dataClass(String) - Data Class
  • Example: Customer
• $dataSource(Boolean, Optional) - Name of the Data Source
  • Example: 'CRM'
• $query(String) - SQL query
  • Example: 'SELECT * FROM Customers'
• $parameters(Indexed Any, Optional) - SQL query parameters
  • Example: $params
• $limit (Integer, Optional) - Maximum number of returned Data Objects
  • Example: 100

Here is the return value:

• Indexed Any - Indexed collection of Data Objects

Examples:

SqlLoad(Customer, 'CRM', 'SELECT * FROM Customers')

Indexed Person $persons := SqlLoad(Person, getDS(), 'SELECT * FROM Person', []:Any, 10)

Model-Based Mapping

A more complex option is to create a model which uses the Data Class definition as reference for properties.
Below is an example of simple generic Script Function to achieve a model-based mapping. It assumes that Data Class property names and database table column names match, and that Data Classes only have Primitive Type properties.

Function CreateObject(Named Any $record, String $className) : Any Begin
  BusinessObject $bo := 
    GETBUSINESSOBJECT('Data Class', $className, VersionFilter());
  com.nm.sdk.data.dataitems.DataStructure $ds :=
    CAST(com.nm.sdk.data.dataitems.DataStructure, $bo);
  Any $result := NewObject($className);
  ForEach String $propName In ToIndexed($ds.getPropertyNames(), String) Do
     GETPROPERTY($result, $propName) := $record[$propName];
  End 
  Return $result;
End

You can then use the CreateObject Script Function like this:

Indexed Person $persons := []:Person;
Indexed Any $results := SqlQuery(getDS(), 'SELECT * FROM Person');
ForEach Named Any $result In $results Do
   $persons.addElement(CAST(Person, CreateObject($result, 'Person')));
End

It is also possible to map nested structures, as long as the database record contains all properties in the nested structure. For example, take a Data Class Person with an address property of type Address. The database query joins the two table records into one record:

Indexed Person $persons := []:Person;
Indexed Any $results := SqlQuery(getDS(), 'SELECT * FROM Person p, Address a WHERE p.id=a.personId');
ForEach Named Any $result In $results Do
   $persons.addElement(CAST(Person, CreateObject($result, 'Person')));
End

The data mapping Script Function looks as follows:

Function CreateObject(Named Any $record, String $className) : Any Begin 
  BusinessObject $bo := GETBUSINESSOBJECT('Data Class', $className, VersionFilter());
  com.nm.sdk.data.dataitems.DataStructure $ds := CAST(com.nm.sdk.data.dataitems.DataStructure, $bo);
  com.nm.sdk.data.dataitems.DataProperty $dp;
  Indexed Any $dps := ToIndexed(CONTEXT().getDataClassManager().getDataProperties($ds, VersionFilter()), Any);
  String $propName;
  Any $result := NewObject($className);
  ForEach $dp In $dps Do 
     $propName := $dp.getName();
     If $dp.isSingleElement() Then 
        If GETBUSINESSOBJECT('Primitive Type', $dp.getType(), VersionFilter()) != null Then 
           GETPROPERTY($result, $propName) := $record[$propName];
        Else
           GETPROPERTY($result, $propName) := CreateObject($record, $dp.getType());
        End
     End
  End
  Return $result;
End

This data mapping procedure can be further improved to address the following issues:

• Database table column names do not always match Data Class property names. Create an indirection which maps column names to property names, for example using a catalog, or a resource file with mapping instructions.
• A collection of Script Functions dealing with data mapping is still difficult to maintain. Create a DataMapping Data Class instead, which contains all functions dealing with data mappings (static or non-static).
• SDK APIs in script code are not best practice (e.g. com.nm.sdk.data.dataitems.DataStructure, CONTEXT().getDataClassManager().getDataProperties(...)). This tightly couples the script to a specific API version. It is better to put this kind of code into a Java-implemented function using the SDK and extensions.

Bulk CSV Import and Export

Functions allow you to import data from a CSV file into a database table (SqlImport), or export data returned by a query to a CSV file (SqlExport).

Import Data from CSV

The SqlImport function performs a bulk import of CSV data to one database table at a time. This simple import can only handle a single table at a time. If you have tables with foreign keys which require simultaneous update, use the SqlQuery function instead.

SqlImport($filePath, $dataSource, $tableName)

Here are the Parameters:

• $filePath (String) - Path to the CSV file
  • Example: 'work/tmp/export.csv'
• $dataSource (String) - Name of the Data Source
  • Example: 'CRM'
• $tableName (String)- Name of the SQL database table
  • Example: 'Customers'
• $ignoreErrors (Boolean, Optional) - Set to "true" to ignore SQL errors.
  • Example: false
• $separator (String, Optional) - Field separator character. Default value: ';'
• $escape (String, Optional) - Escape character. Default value: '"'
• $quote (String, Optional) - Quote character. Default value: same as the escape character.
  • Example: '"'
• $encoding (String, Optional) - File encoding format. Default value: 'UTF-8'

Examples:

SqlImport('work/tmp/export.csv', 'CRM', 'Customers', true)

SqlImport('{DATA_HOME}/work/export.csv', getDS(), 'Person')

Export Data to CSV

The SqlExport function performs a simple bulk export of data to a CSV file. It creates a CSV file containing all records returned by the given query.

SqlExport($filePath, $dataSource, $query)

Examples:

SqlExport('work/tmp/export.csv', 'CRM', 'SELECT * FROM Customers')

SqlExport('{DATA_HOME}/work/export.csv', getDS(), 'SELECT * FROM Person WHERE lastName LIKE ?', ['A%']:Any)

"Date" Type Conversion

Databases are picky when it comes to date handling. The following functions can be used to convert FNZ Studio Date to the specific SQL type.

• SQL DATE
• SQL TIME
• SQL TIMESTAMP

SQL DATE

The function ToSqlDate creates a wrapper around a millisecond value that allows JDBC to identify this as an SQL DATE value. A millisecond value represents the number of milliseconds that passed since January 1, 1970 00:00:00.000 GMT. To conform with the definition of SQL DATE, the values wrapped by a Date instance must be normalized by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.

ToSqlDate($argument)

Examples:

ToSqlDate(PARSEDATE('2014-09-10T11:18:14.987+0200'))

SQL TIME

The function ToSqlTime creates a wrapper around a Date or timestamp, allowing the JDBC API to identify this as an SQL TIME value. The date components should be set to the "zero epoch" value of January 1, 1970.

ToSqlTime($argument)

Examples:

ToSqlTime(NOW())

SQL TIMESTAMP

The function ToSqlTimestamp creates a wrapper around a Date or timestamp that allows the JDBC API to identify this as an SQL TIMESTAMP value. This type adds the ability to hold fractional seconds, which can be specified to a precision of nanoseconds.

ToSqlTimestamp($argument)

Examples:

ToSqlTimestamp(TIMESTAMP())

Debugging Database Access

Debugging database access is often a complicated task. This section lists options and best practices to minimize debugging effort.

Single Access Point

Make sure that databases are accessed from one location only in your app.

A best practice is to create a DataBaseAccess Data Class, and create functions to access the database there instead of global Script Functions. This way, you can later modify access functions at this location without having to worry about dispersed database code.

Output Log

Use the LOGGER function to create an output log. When creating a new log entry, take advantage of the third parameter of the LOGGER function to define the name of the logger used for output.

LOGGER('info', 'executing SQL query: ' & $sql, 'com.nm.sql.query.Accounts')

Breakpoints

Use breakpoints during development to step through your scripts and debug in real time. See "Enable breakpoints" for more information.

Log4jdbc

Log4jdbc is a JDBC driver wrapper. Use it to trace JDBC actions directly at the driver level.

Create a new JDBC data source and use the new driver. To set it up:

1. Prepend the connection URL with "jdbc:log4jdbc:"
2. Use the driver name "net.sf.log4jdbc.DriverSpy"

The logs can be configured in the log4j property file located under <data-home>/conf/log4j.properties.