Set up an SQL connection in Ninox

Prerequisites

1. Request a license file from Ninox for your Private Cloud or On-Premises that is granted full access for one of these Enterprise features: mssqlConnections or pgConnections .

2. Install the license file on your Private Cloud or On-Premises.

3. Get the following environment variables from your SQL Server:

   1. Server name: MSSQL_SERVER, e.g., mssqltest1.database.windows.net

   2. Database name: DB_NAME, e.g., mssql-test.ninoxdb.de

   3. Server admin: MSSQL_SA, e.g., nx-testsqlserver-admin

   4. Password: MSSQL_SA_PASSWORD

Create a new SQL connection in your Private Cloud or On-Premises

 Once you install the appropriate license file, the tab SQL Connections becomes visible in the Server Administration section in your Private Cloud or On-Premises.

1. Log in to your Private Cloud or On-Premises.

2. In Ninox, click the gear icon to access the global settings, then select Server Administration from the dropdown menu.

3. Click the SQL Connections tab, then click Create New Connection.

4. Under SQL Connection, paste the configuration details from Prerequisites into the fields below:

   1. Name: enter a new name for this connection

   2. Connection Type: select MS SQL Server from the dropdown menu

   3. Active: tick the checkbox to set to true

   4. Server: see Server name in Prerequisites

   5. Database: e.g., mssql-test.ninoxdb.de

   6. User: see Server admin in Prerequisites

   7. Password: see Password in Prerequisites

   8. Encrypted: tick the checkbox to set to true

5. Click the Save and Restart button.

Validate your SQL connection with 2 scripts

 Since we are using our own Ninox Private Cloud and SQL Server data in the validation steps, your results may look different than the ones below.

Sample database

To validate the SQL connection, we are using a sample database that contains 1 table, 1 sub-table, and 3 fields:

• SQL Connections: a table

  • Connection name: a text field to enter the name of the SQL connection

• SQL Tests: a sub-table

  • SQL Query or Command: a text field to enter an SQL query or command

  • Result or Error: a multiline text field to receive the result of the SQL query or command

sendCommand

Add a button, then add the following script that is triggered on click:

do as server
'Result or Error' := formatJSON(sendCommand('SQL Connections'.'Connection name', 'SQL Query or Command'))
end

In the sub-table SQL Tests, add a new record to run the following command in the SQL Query or Command field (1):

SELECT 1

Click the sendCommand button (2). The following result appears in the Result or Error field (3):

{"result":{"recordsets":[[{"":1}]],"recordset":[{"":1}],"output":{},"rowsAffected":[1]}}

queryConnection

Add a button, then add the following script that is triggered on click:

do as server
'Result or Error' := formatJSON(queryConnection('SQL Connections'.'Connection name', 'SQL Query or Command'))
end

In the sub-table SQL Tests, add a new record to run the following command in the SQL Query or Command field (1):

SELECT 1

Click the queryConnection button (2) to return actual record data. The following result appears in the Result or Error field (3):

{"result":[{"":1}]}