Get Free Trial
  1. API reference
Connecting to Data Source
  1. Supported data sources
  2. Connecting to other data sources
Configuring the component
  • Documentation for older versions
  • Table of contents

    Data sources guide

    This tutorial describes how to connect the Data Server used as a console application to your data. To connect the Data Server installed as a Windows/Unix service to your data, see this documentation page.

    Server configurations vary depending on the data source type:

    Connecting to databases

    Flexmonster Data Server supports the following databases: MySQL, Microsoft SQL Server, PostgreSQL, Oracle, and Microsoft Azure SQL.

    To connect to a database with Flexmonster Data Server, specify the "Type", "DatabaseType", "ConnectionString", and "Indexes" properties in the flexmonster-config.json file. For example:

    {
        "DataSources": [
            {
                "Type": "database",
                "DatabaseType": "mysql"
                "ConnectionString": 
                    "Server=localhost;Port=3306;Uid=root;Pwd=password;Database=database_name",
                "Indexes": {
                    "index_database": {
                        "Query": "SELECT * FROM tablename"
                    }
                }
            }
        ]
    }

    "index_database" is a dataset identifier. It will be used to configure the data source on the client side.

    "Query" is an SQL query to execute. It can contain a stored procedure.

    "ConnectionString" is a connection string for the database. Here are some example connection strings for each supported database type:

    • MySQL"Server=localhost;Port=3306;Uid=;Pwd=;Database= "
    • Microsoft SQL Server"Server=(localdb)\\MSSQLLocalDB;Uid=;Pwd=;Database= "
    • PostgreSQL"Server=localhost;Port=5432;Uid=;Pwd=;Database= "
    • Oracle"Data Source=ORCL;User Id=;Password=;"
    • Microsoft Azure SQLServer=tcp:myserver.database.windows.net,1433;Database= ;User ID=;Password=;Trusted_Connection=False;Encrypt=True; (to connect to Microsoft Azure SQL, set the "DatabaseType" to "mssql")

    See the full list of configurations available for the Data Server: Configurations reference.

    To start Flexmonster Data Server, refer to the Run Flexmonster Data Server section.

    To see how the connection with Flexmonster Data Server is configured in the component, refer to the Configuring the report section.

    Setting passwords with special characters in the connection string

    The Data Server parses passwords with special characters correctly unless the password contains ; or ". These symbols are treated as delimiters, so they should be escaped:

    • If your password contains a semicolon (;), enclose the password in single quotes (e.g., Pwd='123;45').
    • If your password contains a double quote ("), escape this symbol with a backslash (e.g., Pwd=123\"45).

    Calling a stored procedure

    Follow the steps below to call a stored procedure in the Data Server:

    Step 1. Create a stored procedure in your database:

    Step 2. Call the stored procedure in the "Query" property:

    For MySQL, PostgreSQL, Oracle

    {
    	// other properties
    	"Indexes": {
    		"index_database": {
    			"Query": "call storedProcedure()"
    		}
    	}
    }

    For Microsoft SQL Server

    {
    	// other properties
    	"Indexes": {
    		"index_database": {
    			"Query": "exec storedProcedure"
    		}
    	}
    }

    Learn more about calling stored procedures in your database:

    Note Stored procedures allow executing any valid SQL query from the Data Server. To avoid unauthorized access to the database, create a database user with read-only access rights and specify this user in the connection string.

    Connecting to JSON

    Flexmonster Data Server supports only a specific JSON format – an array of objects, where each object is an unordered set of "key": "value" pairs. Here is an example:

    [
       {
           "Color" : "green",
           "Country" : "Canada",
           "State" : "Ontario",
           "City" : "Toronto",
           "Price" : 174,
           "Quantity" : 22
       },
       ...
    ]

    To connect to JSON with Flexmonster Data Server, specify the "Type" and "Indexes" properties in the flexmonster-config.json file. For example:

    "DataSources": [
        {
            "Type": "json",
            "Indexes": {
                "index_json": {
                    "Path": "../data/data.json"
                }
            }
        }
    ],

    "index_json" is a dataset identifier. It will be used to configure the data source on the client side.

    To start Flexmonster Data Server, refer to the Run Flexmonster Data Server section.

    To see how the connection with Flexmonster Data Server is configured in the component, refer to the Configuring the report section.

    Connecting to CSV

    To connect to CSV with Flexmonster Data Server, specify the "Type" and "Indexes" properties in the flexmonster-config.json file. For example:

    "DataSources": [
        {
            "Type": "csv",
            "Indexes": {
                "index_csv": {
                    "Path": "../data/data.csv"
                }
            }
        }
    ],

    "index_csv" is a dataset identifier. It will be used to configure the data source on the client side.

    To start Flexmonster Data Server, refer to the Run Flexmonster Data Server section.

    To see how the connection with Flexmonster Data Server is configured in the component, refer to the Configuring the report section.

    Run Flexmonster Data Server

    To start Flexmonster Data Server, run the following command in the console:

    on Windows

    flexmonster-data-server.exe

    on macOS and Ubuntu/Linux

    ./flexmonster-data-server

    As soon as you start Flexmonster Data Server, it automatically preloads the data specified in the "Indexes" property. Thus, when Flexmonster Pivot requests the data, the server responds with already preloaded data.

    Note The Data Server keeps preloaded data in the server’s RAM, so the number of indexes you can specify is limited by the server’s RAM amount.

    Configuring the report

    On the client side, the report should be configured as follows:

    new Flexmonster({
    container: "pivotContainer",
       report: {
        dataSource: {
            type: "api",
               url: "http://localhost:9500",
               index: "index-json"
    }
    }

    });

    Note The index must match the name of the index defined when configuring Flexmonster Data Server (e.g., "index_json").

    When Flexmonster Pivot requests the data, Flexmonster Data Server sends the response and then caches it. In case the component sends the same request once again, the server responds with the data from its cache. The Data Server clears the cache when restarted.

    The Data Server’s cache has a memory limit. When the cache does not have enough memory for a new response, the Data Server server deletes one of the previously cached responses.

    Note You can manage the cache size via the “CacheSizeLimit” property.

    What’s next?