Since pure JavaScript does not have built-in methods for working with remote databases, this functionality is implemented using server-side code. Therefore, Stimulsoft Reports.JS product contains server data adapters implemented using Node.js, PHP, .NET, .NET Core, Python, Java technologies. The database adapter is a software layer between the DBMS and the client script. The adapter connects to the DBMS and retrieves the necessary data, converting it into JSON. The script running on the server (using the adapter) provides for the exchange of JSON data between the client-side JavaScript application and the server side. To use this mechanism on the client side, you should specify the URL address of the host adapter, which processes requests to a required adapter

 

Links to examples with ready data adapters, implemented for various platforms: Node.js, PHP, .NET, .NET Core, Python, Java.

 

It`s easy to use an adapter. You should run an adapter and specify the its address:

 

 

 
When requesting data from SQL data sources, the Stimulsoft.Report.Engine sends a POST request to the URL, specified in the option:

 

 

 

A JSON object with parameters is passed in the body of the request that use the following structure:

command: two variants are possible - "TestConnection" and "ExecuteQuery";

connectionString: database connection string;

queryString: query string;

database: database type;

timeout: the time of request waiting, specified in the data source;

parameters: an array of parameters as a JSON object {name, value};

escapeQueryParameters: a flag of parameters shielding before requesting.

 

 

In response, the Stimulsoft.Report.Engine expects a JSON object with data in the form of the following structure:

success: a flag of successful command execution;

notice: if the flag of command execution has the false value, this parameter will contain an error description;

rows: strings array, each element is the array of values, the index is the column number;

columns: an array of column names, index is the column number;

types: an array of column types, the index is the column number. It can take the values "string", "number", "int", "boolean", "array", "datetime".

 

 

Request and response sample:

 

 

 

Custom Data Base

Also, you may register custom data base. To do it you should invoke the following option:

 

 

 

The options are the set of properties and the process() function, which will be invoked when requesting data:

serviceName: adapter name which will be displayed in the designer when creating a new connection

designerCategory:  the category name to which the adapter will be added in the New Data Source menu. By default, the adapter can be found in the Favorites category, but you can change this by setting the option to one of the following values: "Files", "SQL", "NoSQL", "Azure", "Google", "OnlineServices", "REST".

sampleConnectionString: the sample of a connection string that is inserted in the form of setting up a new connection

process: the function, which will be invoked to prepare and transmit data to the Stimulsoft.Report.Engine

 

Two arguments are transmitted to the input of the process() function: command and callback.

 

The command argument is the JSON object, where the Stimulsoft.Report.Engine will transfer the following parameters:

command: action, which is being invoked at the moment. Possible values: "TestConnection": test the database connection from the new connection creation form "RetrieveSchema": retrieving data schema is needed to optimize a request and not only to transfer necessary data set. It is invokes after connection creation "RetrieveData": data request.

connectionString: connection string;

queryString: query string;

database: database type;

timeout: the time of request waiting, specified in the data source.

 

The callback argument is the function, which should be invoked to transmit prepared data to the Stimulsoft.Report.Engine. As the callback argument to the functions you must pass a JSON object with the following parameters:

success: the flag of successful command execution;

notice: if the flag of command execution has the false value, this parameter should contain an error description;

rows: strings array, each element is the array from values, the index is the column number;

columns: columns name array, the index is the column number;

types: the object where field name is column name and the value is the type of the column {Column_Name : "string"}. The type can take the following values "string", "number", "int", "boolean", "array", "datetime". If the columns array will be transmitted, you will be able to transmit types array to the types, the index should be column number. It doesn't work for the "RetrieveSchema".

 

If the command = "RetrieveSchema", then in addition types you should transmit table names to the types.

 

 

The sample of a request and response when receiving a schema:

 

 

 

The sample of a request and response when getting data:

 

 

 

The example of adapter registration.

 

 

Query Timeout

Also, for SQL data sources used in the report, you can specify the Query Timeout in seconds. The value of this property is stored in the report template for each SQL connection separately.

 

Below is an example of code that you may use to change the connection string for MS SQL, adjust the query, set the query timeout for the already created connection, and data sources in the report.
 

 

 

 

OData storages

You can also use data for designing reports and dashboards obtained from OData storages. In this case, you can do the authorization using a username, user password, or using a token. Authorization parameters are specified in the connection string to the OData storage using the ";" separator.

 

 

 

Also, you can specify user HTTP headers for data sources. It can be done in the onBeginProcessData event handler. A complete sample is available on our website: