Direct Database API

1 Introduction
2 Configuration
3 JavaScript Binding: executeSQL
4 Query Result Objects
- 4.1 Compatibility Query Results
- 4.2 MARS Query Result Mode
5 Extra Query Parameters
6 Basic Use Cases
- 6.1 Receiving Data from the Database
- 6.2 Sending Data to the Database
7 Implementation Details
- 7.1 Connection Pooling
- 7.2 Type Mapping

The Direct Database API (DDBAPI) has been available since the FIXEdge 6.17.0 release

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:

<DatabaseConnection
    Name="Odbc1"
    StorageType="ODBC"
    ConnectionString="Driver={ODBC Driver 17 for SQL Server};Server=localhost;Database=DB_NAME;UID=DB_UID;PWD=DB_PWD"
    MinPoolSize="1"
    MaxPoolSize="6"
/>

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:

var connection = "Odbc1";
var query = "DECLARE @tempInt int = ?; select @tempInt";
var params = [123];
var extraParams = {
    associativeResults: true, 
    includeResultsMetadata: true, 
    timing: true, 
    useStrictDecimal: true, 
    resultTemplate: [['BigInt'], ['String', 'String']]
};
var result = executeSQL(connection, query, params, extraParams);

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

var query = "select 123"; 		# query example
var result = {				    # query result object produced by an executeSQL call   
  "affectedRowCount": -1, 	    # int (mandatory), contains number of affected rows, if the query performs modifications
  "data": [[123]]               # array rows (mandatory) or empty array; each row in turn is an array of the selected data, for example, the selected columns.
};

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:

Single Array of Strings: Applies conversion directly to the query result in sequence.
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 :

resultTemplate Value	Target JS Type

resultTemplate Value	Target JS Type
BigInt	BigInt with int64_t backend
Number	Number with double backend
String	String with std::string backend
Null, Undefined	Read as is, no additional conversion applied

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:

SQL type	JS → SQL (parameter)	SQL → JS (result)	Comment

SQL type	JS → SQL (parameter)	SQL → JS (result)	Comment
tinyint, smallint, int, bigint	String: deserialize, Int: 1-to-1, Double: convert	Int: 1-to-1 Others - convert as is
bit	String: "true", "false" are converted, all others - failure Int: 0, 1 are converted, all others - failure Double: 0 - false, >0 - true, all other - failure	Always converted to: Int: 0, 1 Others: convert to "0"/"1", 0.0/1.0
decimal, numeric, money, smallmoney	String: ODBC-side conversion. Int: ODBC-side conversion. Double: fail if extraParams.useStrictDecimal	String: "123.456" Int: "123" Double: ~123.456, fail if extraParams.useStrictDecimal
float,real	String: deserialize, Int: convert, Double: 1-to-1.	Double: 1-to-1. Others - convert as is
date, time, datetime2, datetimeoffset, datetime smalldatetime	String: try converting from FIX date, if succeeds fill the ODBC:DATE_TIME struct; if fails pass by to ODBC as string. Int: Try to interpret as JSDateEntity, if failed treat as unix time. Pass to ODBC:DATE_TIME struct in both cases. Double: Convert to unix time and pass to ODBC:DATE_TIME struct.	If no conversion requested, returns and int, representing a JSDateEntity. String: Convert to FIX date format Int: Convert to Unix timestamp. Double: Convert to Unix timestamp.	Several useful JS bindings for manipulating JSDateEntity objects are: dateToString, getDateField, createDate, getCurrentDate, (get \| set \| change)(Year \| Month \| ... \| Sec\|MSec\|NSec)
char, varchar, nchar, nvarchar	String: 1-to-1, Int: serialize double: serialize	String: 1-to-1 Others - convert as is
text, ntext			Not supported as these types are obsolete/deprecated.
binary, varbinary, image	String: passed as is, Int: passed as binary uint64_t* buffer, Double: passed as binary double* buffer.	String: example: 0xdeadbeef -> "DEADBEEF" Others - convert as is
cursor, geography, geometry, hierarchyid, json, rowversion, sql_variant, table, uniqueidentifier, xml	All: passed as is	All: read as is
Arrays of values			Not implemented