What is a Datasource
A Datasource is a connection to a database or alternative source of information (such as a file-system), it holds the authentication information as needed. Datasources are used primarily by Processes and Server Pages to retreive and store information from the given Datasource.
Datasource Types
Type | Description |
---|---|
Database | These are direct connections to a source database. Accessible on a public IP address or after establishing a Virtual Private Network (VPN). |
File-System | This is a direct link to the files system on a given instance. This Datasouce is used when text files have been uploaded to the instance. |
Datasource Usage
Datasource for a Process
Datasources are primarily used by processes as the source of the information they process. Database type Datasources support both sourcing a full table of information or data from a user-input SQL
query.
Process with the Internal Datastore
Datasource selected.
Datasource queries in JavaScript
Both Processes and Custom Server Pages can use the scripting functions in jAvaScript to return or modify data from any Database type Datasource.
js
datasource.insert(datastoreName, query, [...prompts])
datasource.select(datastoreName, query, [...prompts])
datasource.update(datastoreName, query, [...prompts])
datasource.insert(datastoreName, query, [...prompts])
datasource.select(datastoreName, query, [...prompts])
datasource.update(datastoreName, query, [...prompts])
Here is an example script which selects records from a Datasource using the datasource.select
function in conjunction with an SQL query.
js
/*
This example will run the following SQL query and return
the results as an array of objects.
SELECT
account_code,
account_name,
account_type
FROM
performance_management.accounts
WHERE
account_code = 'REV';
The parameterisation is used to ensure all inputs are sanitized
from SQL injection and are data integrity is maintained
when the function encounters unescaped characters.
*/
let records = JSON.parse(
datasource.select(
`Internal Datastore`,
`SELECT account_code, account_name, account_type FROM
performance_management.accounts WHERE account_type = ?`,
[`REV`]
)
);
console.log(records);
/*
This will log the following:
[
{"account_code":"43050","account_name":"Revenue - Subscription Sales","account_type":"REV"},
{"account_code":"43051","account_name":"Revenue - Direct Sales","account_type":"REV"},
{"account_code":"43052","account_name":"Revenue - Services","account_type":"REV"},
]
*/
/*
This example will run the following SQL query and return
the results as an array of objects.
SELECT
account_code,
account_name,
account_type
FROM
performance_management.accounts
WHERE
account_code = 'REV';
The parameterisation is used to ensure all inputs are sanitized
from SQL injection and are data integrity is maintained
when the function encounters unescaped characters.
*/
let records = JSON.parse(
datasource.select(
`Internal Datastore`,
`SELECT account_code, account_name, account_type FROM
performance_management.accounts WHERE account_type = ?`,
[`REV`]
)
);
console.log(records);
/*
This will log the following:
[
{"account_code":"43050","account_name":"Revenue - Subscription Sales","account_type":"REV"},
{"account_code":"43051","account_name":"Revenue - Direct Sales","account_type":"REV"},
{"account_code":"43052","account_name":"Revenue - Services","account_type":"REV"},
]
*/
Adding a Datasource
Adding new Datasources via Gateway
To add a new datasource to MODLR, navigate to the Manage Datasources
page.
- Click
New Datasource
- Fill out the Datasource connection information.
- Test the connection details. If the Datasource cannot be reached, it will not be possible to save the Datasource.
- Save the Datasource