HomeGuidesRecipesAPI
HomeGuidesAPILog In

SQL Server and PostgreSQL

SmartIQ can connect directly to Microsoft SQL Server and PostgreSQL tables, views and stored procedures/functions to to read and write data. Available Actions that use SQL connections deliver data to an SQL Server via a Stored Procedure via OLEDB.

Setup

SQL Server Data Sources require a valid connection attributes containing the location and authentication information of the server, i.e. Persist Security Info=True;Initial Catalog=SampleData;Data Source=server001

String KeyDescription
ServerSpecifies the server/database name
Persist Security InfoIt is recommended to set this to False when the user ID and password are included in the connection string
EncryptEncrypts SQL server data connections/actions. By default, this is set to ‘true’.
TrustServerCertificateSet to ‘true’ if the SSL Provider connection mentions the authority or certificate name.
User IDLogin ID
PasswordLogin Password
Initial CatalogThe SQL database name

The connection attributes for SQL Server, PostGres, and OLEDB get used as a standard SQL-style connection string. External documentation: https://www.connectionstrings.com/sql-server/ , https://www.connectionstrings.com/postgresql/, https://www.connectionstrings.com/microsoft-ole-db-provider-for-sql-server-sqloledb/

Settings

SQL Server Settings

Credentials

No credentials

Simple username and password

Data Objects

🚧

SQL Server and PostgreSQL impose a limit of 2100 parameters per query

In certain cases, particularly when using Aggregate data filters, SmartIQ may exceed this limit. There is no workaround to increase the parameters, only the recommendation to limit the number of values that are passed to SQL Server as filters.

Table/View Data Objects

Tables can be used as a read only or read/write data objects.

Rows within SQL tables containing a single Primary Key or Identity Column, with access set to read/write, can be used with Writable Data Objects to add, modify, and delete data.

Stored Procedure/Function Data Objects

SQL Stored procedures/Functions provide read only access to data, with their input parameters defining the filter fields. Data Object Name / Definition: The name of the stored procedure/function, with valid sample inputs for each parameter, separated by commas surrounded by brackets, e.g function(valid input, ...)

📘

Sample Parameter Inputs

As stored procedures do not provide a static response, the data object must be provided with valid sample input parameters so that a sample response can be processed by the data object. Ensure the sample parameter inputs are valid and will return an efficient, consistent response.