Page Comparison

...

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

Code Block

language	xml

<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:

...

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

Example:

Code Block

language	js

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.

...

Example 1: Selecting a single value

Code Block

language	js

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

Code Block

language	js

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

Code Block

language	js

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

Code Block

language	js

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

Code Block

language	js

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:

...

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:

Code Block

language	js

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

When multiple columns are queried:

Code Block

language	js

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:

Code Block

language	js

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:

Code Block

language	js

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:

Code Block

language	js

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.

...

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:

Code Block

language	js

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"}
        ]
    }
];

Code Block

language	js

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 :

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:

Code Block

language	xml

<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:

Code Block

language	js

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

Query result:

Code Block
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:

...

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

Code Block

language	js

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

Query result:

Code Block
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:

...

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
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

Version	Old Version 6	New Version 7
Changes made by	author	author
Saved on	Dec 05, 2024	Dec 05, 2024

Versions Compared

Key

MARS Query Result Mode

Extra Query Parameters

includeResultsMetadata (boolean)

timing (boolean)

Basic Use Cases

Receiving Data from the Database