Page Comparison

...

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

...

JavaScript Binding: executeSQL

...

The executeSQL function is a convenient tool within FIXEdge with a JS binding feature that allows users to transfer execution control and parameters from Javascript to transfers control from the JS environment to the 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 followsto execute SQL queries.

Example:

Code Block

...

...

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

...

language	js

...

• (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": [[

...

]]          

...

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.

...

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

...

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.

Calling Stored Procedure in the Database

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

var result = executeSQL(
    "Odbc1",
    "EXEC sp_MyProcedure @pParam1=?, @pParam2=?, @pParam3=?;",
    [1, 2.2, '3']
);
print(JSON.stringify(result));

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.

...

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