SQL queries limitations in customizations

Last updated
Save as PDF

There are some use cases where you might want to do a direct SQL query to the Production or Archive database. In this case, do not use the connection string directly. It may be changed in the future and then your customization will be broken.

There are some options on how you can achieve the same result without directly referencing the connection string.

Workflow Studio PowerShell activities
If you need access to the Production database from PowerShell activity, you can use a predefined keyword "m42Store", for instance:
```
$connectionString = ([System.Configuration.ConfigurationManager]::ConnectionStrings['m42Store']).ConnectionString
```
See also, Matrix42 Workflow Studio: General Workflow Activities.
Execute SQL Workflow Activities
Connection strings alias names could be used in Workflow Activities "ExecteSQLNonQuery", "ExecuteSqlQuery"
Generic Data Import Export with MS SQL source type
To access the system database from GDIE, enable the "Use application database" option. See also, Create an Import Definition page.
History data access
Avoid using direct queries to a History database and its tables, because in the near future history data will be moved to the new storage, and the history database will be deleted.
Avoid using database names directly
Any system database name can be changed in the future, so it is safer to not use it explicitly.

Adding Custom Connection Strings

In addition to the standard connection strings, the system allows you to define custom connection strings. These custom strings can be referenced by their alias names across various application modules.

Custom connection strings are stored in the production database table PDRDwpConfigurationClass, using a key that follows the naming pattern:

Workflow.ConnectionString-{alias}

Example

To add a custom connection string, insert a new record into the PDRDwpConfigurationClass table as shown below:

insert into PDRDwpConfigurationClass([Key], Value, UsedInTypeSPSGlobalConfigurationType)
values ('Workflow.ConnectionString-release2', 'data source=.;initial catalog=OpenAPI;integrated security=True', 'CC0EA0CD-A84A-487C-AE1D-A68EF33E7DE1')