Search…
🆕
SQL Server connector configuration
SQL Server connector configuration is an Enterprise feature available on request that requires a valid license purchased from Ninox or a certified partner.
This feature is currently in beta testing and will be released in 3.7.0.
SQL Server connectors allow querying and creating tables in an external SQL Server database. In relation to Ninox, SQL Server connectors enable you to connect your Private Cloud or On-Premises server to an external relational database management system. Ninox offers the feature to Enterprise customers against a one-time setup fee.

Supported use cases

SQL Server connectors are particularly helpful in cases such as:
  • Production data is managed in an SQL database, CRM data is managed in Ninox. For certain reports a fitting, complex product ID needs to be fetched from the production database.
  • CRM data is managed with Microsoft Dynamics Navision which is based on an SQL database. Certain data is needed for your Ninox application. Instead of manually exporting .csv files and importing them to Ninox, transfer them automatically.

Set up and test an SQL Server connector

Set up an SQL database server

  1. 1.
    Download the MSSQL server from Microsoft—refer to External resources below.
  2. 2.
    Start the container using the command line:
docker pull mcr.microsoft.com/mssql/server
Please save the SA_PASSWORD you are using for later reference.
Alternatively, SQL Server connector is also available for the open source database management system
PostgreSQL
.

External resources

Install Docker on macOS
Runnable Docker Guides
Docker Desktop for Apple silicon
Docker Documentation
Install Docker Desktop on Windows
Docker Documentation

Set up an SQL database with a frontend tool (optional)

  1. 1.
    Connect to your new database server and setup a first database, perhaps with a first table and some sample data.
  2. 2.
    Use a visual user interface provided by a frontend tool. There's lots of frontend tools to choose from. A freely available open source solution is
    DBeaver
    .
Please save your database and table name for later reference.

Set up a Ninox server (Private Cloud or On-Premises)

Request an updated Private Cloud license if you don’t already have one. Alternatively, use your local development setup or an On-Premises server.

Get a license to use the SQL connector of your choice

If you're using a Ninox Private Cloud server, use the admin tool mentioned in Set up a Ninox server (Private Cloud or On-Premises) to create a license file with full access granted for one of these restricted Enterprise features:
  • mssqlConnections
  • pgConnections
If your setup differs, create and get a license file with Ninox's internal license management tool.

Configure your Ninox server to connect to the SQL database

  1. 1.
    Once you have the appropriate license file installed to your server, a new tab in the server administration section (UMS) called SQL Connections becomes visible.
  2. 2.
    Create a new entry with the following settings:
    • Name: whatever you like
    • Connection type: MS SQL server
    • Active: true
    • Server: localhost
    • Database: db_name_from_step2
    • User: sa
    • Password: sa_password_from_step1
  3. 3.
    Test your connection to validate your settings.
Please save your connection_name for later reference.

Implement a Ninox script to use the SQL database

Add a Ninox script

In your Ninox server, add a Ninox script, e.g., to a button called run. In the following example we assume you have the following fields in your database:
  • connection_name: Text field to reference your connection from the previous step
  • query: Text field to hold your SQL command
  • result: Multiline text field to receive the result of your query
Given these fields, your button click trigger script could look like this:
do as server
result := formatJSON(sendCommand(connection_name, query))
end

Run an SQL query

You can now run SQL queries like this:
SELECT 1
After clicking the run button the result field should be returning something like this:
{result: {"recordsets":[[{"id":2,"name":"feature a","price":99.99,"created_at":"2021-06-02T08:00:00.000Z"}]],"recordset":[{"id":2,"name":"feature a","price":99.99,"created_at":"2021-06-02T08:00:00.000Z"}],"output":{},"rowsAffected":[1]}}

Get actual record data

To get to the actual record data, use queryConnection instead of sendCommand and to get something like this:
{result: [{"id":2,"name":"feature a","price":99.99,"created_at":"2021-06-02T08:00:00.000Z"}]}