Direct Database API

1 Introduction
2 Configuration
3 FIXEdge JS Binding: executeSQL()
4 Usage
5 Receiving Data from the Database
6 Sending Data to the Database
7 Calling Stored Procedure in the Database
8 Implementation Details
- 8.1 Connection Pooling
- 8.2 Type Mapping

The Direct Database API (DDBAPI) has been available since the FIXEdge 6.17 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. Default: 1
MaxPoolSize: This sets the maximum number of connections that FIXEdge can create in the pool. Default: 16

FIXEdge JS Binding: executeSQL()

executeSQL is a convenient tool within FIXEdge with a JS binding feature that allows users to transfer execution control and parameters from Javascript to C++ runtime. It works as a bridge between the two environments, ensuring seamless and efficient operation.

Here's how you can use executeSQL:

The executeSQL function should be called as follows:

var connection = "Odbc1";
var query = "DECLARE @tempInt int = ?; select @tempInt";
var params = [123];
var result = executeSQL(connection, query, params);

The function call consists of the following parameters:

connection: This is a required string parameter where you specify the connection name that matches the 'DatabaseConnection.Name' in BL_Config.xml.
query: This is a required string parameter where you write the SQL statement that you want to be executed on the server-side.
params: This is a required array of integers, doubles, or strings that represent the query parameters to be sent to the server.
result: This is a required JS Object that you have to initialize as follows:

var result = {
    "affectedRowCount": -1, // Mandatory integer, denotes the number of affected rows if the query modifies any data
    "data": [[123]]         // Mandatory array, includes rows of selected data or an empty array if no data was selected.
};

The affectedRowCount field contains the number of rows affected if the query modifies any data. The data field consists of the rows of selected data (each row is an array of the selected columns). If no data is selected, it returns an empty array.

Usage

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.

Calling Stored Procedure in the Database

To invoke stored procedure in the database, you can employ the DBMS query language as follows:

This code snippet demonstrates the following:

The query string uses ODBC EXEC syntax to invoke stored procedures with SQL @namedParams bound to 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)

SQL type

JS → SQL (parameter)

SQL → JS (result)

tinyint, smallint, int, bigint

String: deserialize,

Int: 1-to-1,

Double: convert

String: serialize,

Int: 1-to-1

Double: convert

float, real

String: deserialize,

Int: convert,

Double: 1-to-1.

String: serialize,

Int: convert,

Double: 1-to-1.

char, varchar, nchar, nvarchar

String: 1-to-1,

Int: serialize

double: serialize

String: 1-to-1

Int: deserialize

double: deserialize