Direct Database API

Introduction

The Direct Database API (DDBAPI) is a feature that allows you to send data from your FIXEdge JavaScript execution environment to a Database Management System (DBMS) and retrieve the result back to JavaScript.

FIXEdge DDBAPI offers a powerful and flexible way to interact with your database. This documentation provides a starting point for understanding and utilizing this feature effectively within your FIXEdge environment.

Configuration

To configure DDBAPI, you need to define the database connection in the BL_Config.xml file using the FIXEdge\DatabaseConnection element:

The configuration for DDBAPI is found in the FIXEdge\DatabaseConnection element of your BL_Config.xml file. Here's a breakdown of the configuration options:

• Name: This case-sensitive identifier is used to reference the connection throughout your FIXEdge configuration.

• StorageType: This case-insensitive option specifies the type of database connection you are using. Currently, only ODBC is supported.

• ConnectionString: This string contains the information needed to connect to your database. The format will vary depending on your specific DBMS.

• MinPoolSize: This defines the minimum number of connections that will be available in the pool at FIXEdge system startup.

• MaxPoolSize: This sets the maximum number of connections that FIXEdge can create in the pool.

JavaScript Binding: executeSQL

The executeSQL function is a JS binding that transfers control from the JS environment to the C++ runtime to execute SQL queries.

Example:

Parameters:

• connection (string, required): Name of the database connection, must match DatabaseConnection.Name.

• query (string, required): SQL statement to execute.

• params (array, required): Query parameters to pass to the server (int, double, or string).

• extraParams (object, optional): Additional settings to control the query's result and behavior.

• result (JS object): Returned object with query results.

Query Result Objects

Compatibility Query Results

For single row set results with multiple data modification results, DDBAPI generates query result objects in compatibility mode. Here are some examples:

Example 1: Selecting a single value

• affectedRowCount: Number of affected rows (used for modification queries).

• data: Array of result rows; each row is an array of the selected data.

Example 2: Inserting multiple rows

Example 3: Deleting rows and selecting a value

MARS Query Result Mode

For queries that return multiple row sets with data modifications (Multiple Active Result Sets - MARS), the result is structured as an array of result objects:

Example 1: Multiple select statements

Example 2: Mixed DML and select statements

Extra Query Parameters

The extraParams parameter accepts an object with optional properties to customize the query behavior and result format:

associativeResults (boolean)

If set to true, this parameter includes column names in the result objects. If a column name is unavailable, the column index is used instead.

Example:

When multiple columns are queried:

includeResultsMetadata (boolean)

Setting this parameter to true includes type information for each column in the result object.

Example:

When both associativeResults and includeResultsMetadata are enabled, the metadata includes column names as well:

timing (boolean)

Setting this parameter to true adds a "time" property to the result object. The value represents the query execution time in the format "$(number_of_hours)h.$(number_of_minutes)m.$(number_of_second)s.$(second_fraction)".

Example:

The measured time includes parameter preparation, transfer, server-side processing, receiving, and parsing of the results.

useStrictDecimal (boolean)

When set to true, this parameter enforces strict type checking for fixed-point numbers (decimal, numeric, money). Any attempt to use a JS double variable to set a fixed-point value or read a fixed-point value into a JS double variable will result in an error.

resultTemplate (array /of arrays/ of strings)

The resultTemplate parameter in the executeSQL function allows precise control over how the results of a query are cast into JavaScript types. This parameter allows you to define a template for type conversion of the query results.

The resultTemplate allows multiple configurations:

1. Single Array of Strings: Applies conversion directly to the query result in sequence.

2. Array of Arrays of Strings: Useful for Multi-Active Result Sets (MARS). If the MARS count is less than the number of arrays, the last array in resultTemplate is applied to remaining results.

Example:

resultTemplate supports the following type literals, case insensitive, as provided by JavaScript data types and data structures - JavaScript | MDN :

Basic Use Cases

To use DDBAPI, your BL_Config.xml file should define a JavaScript execution action in one of its rules, like so:

Receiving Data from the Database

To fetch data from the database, you prepare a query and execute it. Here's an example of how to execute a DBMS query and print the result:

Query result:

This code snippet demonstrates the following:

• executeSQL is the function used to send queries to the DBMS.

• The first argument specifies the connection name configured in your FIXEdge BL_Config.xml.

• The second argument is the SQL query you want to execute.

• The third argument is an optional array of parameters for the query.

• The result variable will contain the data returned from the DBMS. This will be a JS object with two fields:

  • affectedRowCount: This integer indicates the number of rows affected by the query.

  • data: This array contains the actual data returned by the query. Each element in the array represents a row, and each row is itself an array containing the values for each column.

DDBAPI supports multiple active result sets, allowing you to efficiently read complex query results.

Sending Data to the Database

To store data in the database, you can employ the DBMS query language as follows:

Query result:

This code snippet demonstrates the following:

• The query string now includes placeholders (?) for the values you want to insert.

• The third argument to executeSQL is an array containing the values to be inserted.

• The result variable will contain information about the execution, including the number of rows affected.

DDBAPI automatically binds the values you provide to the corresponding placeholders in the prepared query.

Implementation Details

This section provides additional technical information about DDBAPI features in FIXEdge, including connection pooling, JS to SQL type mapping, and limitations.

Connection Pooling

In a multi-threaded environment, FIXEdge supports pooling of DBMS connections. If a connection from a different thread is accessed and there are no free connections, a new one is created, unless it exceeds the MaxPoolSize value; an exception is thrown in such case.

For each connection, up to 100 prepared queries are stored. The system will delete the least recently used queries when new ones are prepared.

Type Mapping

When sending data from JavaScript to SQL and vice versa, the following type mappings are applied: