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:

<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

var query = "insert into table1 (col1) values ('val1'); insert into table1 (col1) values ('val2')";
var result = {
  "affectedRowCount": 2,
  "data": [[]]            # Empty array as no values are selected
};

Example 3: Deleting rows and selecting a value

var query = "delete from table1; select 123";
var result = {
  "affectedRowCount": 24, # Assuming 24 rows were deleted
  "data": [[123]]
};

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

var query = "select 123; select 'a', 'b'";
var result = [
  { "data": [[123]] },      	# Result of the first select statement
  { "data": [["a","b"]] }  		# Result of the second select statement
];

Example 2: Mixed DML and select statements

var query = "select 123; insert into table1 (col1) values ('val1'); select 'a', 'b'; insert into table1 (col1) values ('val2')";
var result = [
  { "data": [[123]] },
  { "affectedRowCount": 1 }, 	# Result of the first insert statement
  { "data": [["a","b"]] },
  { "affectedRowCount": 1 }  	# Result of the second insert statement
];

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:

var Query = "select 123";
# var result = executeSQL("Odbc1", Query, [], {associativeResults: true});
var result = {
    "data":[{"1":123}],
    "affectedRowCount":-1
};

When multiple columns are queried:

var Query = "select 123; insert into table1 (col1) values (val1); select 'a', 'b' as column2";
# var result = executeSQL("Odbc1", Query, [], {associativeResults: true});
var result = [
    {
        "data":[{"1":123}]
    },
    {
        "affectedRowCount":1
    },
    {
        "data":[{"1":"a","column2":"b"}]
    }
];

includeResultsMetadata (boolean)

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

Example:

var Query = "select 123; insert into table1 (col1) values (val1); select 'a', 'b' as column2";
# var result = executeSQL("Odbc1", Query, [], {associativeResults: false, includeResultsMetadata: true});
var result = [
    {
        "data":[[123]],
        "metadata":[{"column":"1","type":"int of width 10 not null"}]
    },
    {
        "affectedRowCount":1
    },
    {
        "data":[["a","b"]],
        "metadata":[
            {"column":"1","type":"varchar of width 1 not null"},
            {"column":"column2","type":"varchar of width 1 not null"}
        ]
    }
];

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

var Query = "select 123; insert into table1 (col1) values (val1); select 'a', 'b' as column2";
# var result = executeSQL("Odbc1", Query, [], {associativeResults: true, includeResultsMetadata: true});
var result = [
    {
        "data":[{"1":123}],
        "metadata":[{"1":{"column":"1","type":"int of width 10 not null"}}]
    },
    {
        "affectedRowCount":1
    },
    {
        "data":[{"1":"a","column2":"b"}],
        "metadata":[
            {"1":{"column":"1","type":"varchar of width 1 not null"}},
            {"column2":{"column":"column2","type":"varchar of width 1 not null"}}
        ]
    }
];

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:

var Query = "select 123;";
# var result = executeSQL("Odbc1", Query, [], {timing: true});
var result = {
    "data":[[123]],
    "affectedRowCount":-1,
    "time":"0s.692618"
};

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:

var Query = "select 123; select '1', '2'; select 3.14, 2.71";
# var result = executeSQL("Odbc1", Query, [], {includeResultsMetadata: true, resultTemplate: [['String'], ['BigInt']]});
var result = [
    {
        "data":[["123"]],   # The 1st resultTemplate is used to convert the integral 123 value to string.
        "metadata":[
            {"column":"1","type":"int of width 10 not null"} # the metadata contains the original result type.
        ]
    },
    {
        "data":[[
            1,      # The 2nd resultTemplate is used to convert the char value to BigInt
            "2"     # As the 2nd resultTemplate does not contain a conversion specification for the 2nd result column, the values is returned as is.
        ]],
        "metadata":[
            {"column":"1","type":"varchar of width 1 not null"},
            {"column":"2","type":"varchar of width 1 not null"}
        ]
    },
    {
        "data":[[
            3,      # As there is no 3rd array in the resultTemplate, the last one is used.
            "2.71"
        ]],
        "metadata":[
            {"column":"1","type":"numeric of width 3 with 2 decimal digits not null"},
            {"column":"2","type":"numeric of width 3 with 2 decimal digits not null"}
        ]
    }
];

var Query = "select '1', '2', '3.1'";
# var result = executeSQL("Odbc1", Query, [], {resultTemplate: ['BigInt', 'Null', 'Number']});
var result = {
    "data":[[1,"2",3.1]],       # As the 2nd parameter is 'Null', the result is provided as is.
    "affectedRowCount":-1
};

resultTemplate supports the following type literals, case insensitive, as provided by https://developer.mozilla.org/en-US/docs/Web/JavaScript/Data_structures#primitive_values :

Basic Use Cases

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

<DefaultRule>
  <Action>
    <Script Language="JavaScript" FileName="executeSql.js"/>
  </Action>
</DefaultRule>

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:

var result = executeSQL(
    "Odbc1",
    "select 1, CAST(2.2 as real); select '3'",
    []
);
print(JSON.stringify(result));

Query result:

2024-07-01 11:04:02,845 UTC   DEBUG   [JS_interpreter]  137723922871872  executeSql.js: execute print({"affectedRowCount":-1,"data":[[1,2.200000047683716],["3"]]})

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:

var result = executeSQL(
    "Odbc1",
    "insert into dbo.Table1 (column1, column2, column3) values (?, ?, ?)",
    [1, 2.2, '3']
);
print(JSON.stringify(result));

Query result:

2024-07-01 11:04:02,845 UTC DEBUG [JS_interpreter] 137723922871872 executeSql.js: execute print({"affectedRowCount":1,"data":[]})

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: