ODBC | SQL Configuration
- Ada
- Adam Davis
Please make sure that when installing/upgrading Apliqo UX you also select the option to install the Apliqo Server in order to be able to connect to SQL databases.
Since version 2024.02 users have the option to store ODBC source and credentials configuration in the ContentStore. To use this functionality remember to upgrade the ContentStore with packages provided in the Upgrade process article.
Check step for the instructions.
Add new System DNS in ODBC Data Source
NOTE:
ODBC DSNs needs to be done on the server where Apliqo UX is installed.
1.1 Open ODBC Data Source Administrator
1.2 Select System DSN tab
1.3 Click Add button
1.4 Select SQL Server and click Finish
1.5 Define Data Source and click Next
Name: database name
Description: custom description, not required
Server: server name (can be selected from the dropdown list)
1.6 Select authentication method (windows or SQL server authentication) and click Next
1.7 Define default database (check the checkbox and select the database from the dropdown list) and click Next
1.8 If necessary change the language and log file location and click Finish
1.9 Now you can review the configuration and Test Data Source
1.10 After successful test click OK, new Data Source will appear in the System DSN tab
Repeat the initial configuration step to include as many new Data Sources as needed.
deafult.constant.js configuration
The default.constants.js file is located in the apq-c3-custom/js folder within the web app directory of the root install directory of your Apliqo Application server.
2.1 Configure source
source: configure type, odbc_src_name, tableArray and displayName
type: type of data source
odbc_src_name: name of the data source defined in ODBC (step 1 of the configuration)
tableArray: list the table names defined ODBC source that you want to have listed in the cube settings (can be more than one - need to be comma separated e.g. ["table1","table2"])
displayName: name that will be displayed in the Apliqo UX cube settings
2.2 Provide the credentials used to log into the SQL Database
Users have the ability to encrypt their usernames and passwords within the configuration settings.
How to Use:
To encrypt your credentials, visit Base64Encode Tool.
Input your username and password, then set
encodedtotruefor encrypted credentials.For unencrypted input, directly input your username and password without encryption and set
encodedtofalse.
u: user name used to log into the SQL Database
p: password
encoded: true/false
Example:
"source":[
{
"type":"odbc",
"odbc_src_name": 'EmployeeDB',
"tableArray":["Employees"],
"displayName": 'Employee Details',
"u":"QXBsaXFvVVhfVXNlcg==",
"p":"b3dld2R1aW0xMw==",
"encoded":"true"
}
],2.2 Repeat step 2.1 to add additional data sources
Example:
"source":[
{
"type":"odbc",
"odbc_src_name": 'EmployeeDB',
"tableArray":["Employees"],
"displayName": 'Employee Details',
"u":"QXBsaXFvVVhfVXNlcg==",
"p":"b3dld2R1aW0xMw==",
"encoded":"true"
},
{
"type":"odbc",
"odbc_src_name": 'BikeStores',
"tableArray":["production.brands","production.categories","production.products","production.stocks","sales.customers","sales.order_items","sales.orderes","sales.staffs","sales.stores"],
"displayName": 'Bike Stores',
"u":"QXBsaXFvVVhfVXNlcg==",
"p":"b3dld2R1aW0xMw==",
"encoded":"true"
},
{
"type":"odbc",
"odbc_src_name": 'AdventureWorks',
"tableArray":["HumanResources.Department"],
"displayName": 'Sample Data',
"u":"QXBsaXFvVVhfVXNlcg==",
"p":"b3dld2R1aW0xMw==",
"encoded":"true"
}
],How to store credentials in the ContentStore
Users have the option to save their credentials in the ContentStore }APQ UX UserPreference Cube. This provides a secure method for storing and managing authentication information.
When configuring the data source, if the source information is specified in both the default.constant.js file and the }APQ UX UserPreference Cube, the configuration from the ContentStore takes precedence. This means that the settings in the default.constant.js file will be overwritten by the data stored in the ContentStore.
3.1 Set up process
Users are required to first set up the array (configuration process remains consistent with the previous step, with the exception that only information between square brackets [ ] is relevant - check the example below). Updated array should be then pasted into the ODBCSource Measure for the Admin user. This ensures that the necessary ODBC configurations are correctly set up for the specified user and data sources.
[ { "type": "odbc", "odbc_src_name": "EmployeeDB", "tableArray": ["Employees"], "displayName": "Employee Details", "u": "QXBsaXFvVVhfVXNlcg==", "p": "b3dld2R1aW0xMw==", "encoded": "true" }, { "type": "odbc", "odbc_src_name": "BikeStores", "tableArray": ["production.brands", "production.categories", "production.products", "production.stocks", "sales.customers", "sales.order_items", "sales.orderes", "sales.staffs", "sales.stores"], "displayName": "Bike Stores", "u": "QXBsaXFvVVhfVXNlcg==", "p": "b3dld2R1aW0xMw==", "encoded": "true" }, { "type": "odbc", "odbc_src_name": "AdventureWorks", "tableArray": ["HumanResources.Department"], "displayName": "Sample Data", "u": "QXBsaXFvVVhfVXNlcg==", "p": "b3dld2R1aW0xMw==", "encoded": "true" } ]
How above configuration looks in Apliqo UX cube settings
