Adapter - Microsoft SQL
This page describes version 2.0.3 of the adapter.
Overview
The MSSQL adapter uses a connection string to connect to the desired Microsoft SQL Database upon the implementation of each available adapter command. The adapter allows for Selecting data from the database, making updates, inserts, and deletions, as well as calling existing stored procedures.
Properties
| Name | Display | Is Required | Comments | Type | Default Value | Maximum Value | Minimum Value | Maximum Length | Minimum Length |
|---|---|---|---|---|---|---|---|---|---|
| connectionString | MSSQL Database Connection String | True | The connection string used to connect to the MSSQL database. | string | 2048 | 1 |
Configuration
The configuration of the adapter only requires the input of a supported database connection string. Refer to Microsoft SQL documentation for more details.
Commands
MSSQL Command (sqlCommand)
Adapter command for submitting custom written MSSQL updates, inserts and deletes to the database.
Request Properties
| Name | Display | Description | Is Required | Type |
|---|---|---|---|---|
| rawSqlCommand | MSSQL Command | The MSSQL command to be run against the database. To prevent MSSQL Injection attacks, all operators in the command should be followed by a unique parameter assignment signified by @ to be replaced by the Parameter Values. ie. Select columnName from table WHERE columnName = <@parameter1> OR columnName = <@parameter2> OR columnName IS NULL. Null values need not be parameterized. | True | string |
| parameter | Parameters | Parameters to be used in the MSSQL Command. | False | array |
| parameter[0].name | Parameter Name | The name of the Parameter to be replaced by it's Value within the MSSQL Database. This name must match the value used in the MSSQL Command. | True | string |
| parameter[0].value | Parameter Value | The value to be used in place of the parameter during execution within MSSQL Database. | True | string |
Response Properties
| Name | Description | Type |
|---|---|---|
| status | Status of the adapter command. | string |
| recordsAffected | The number of records reported affected by the MSSQL Command. | string |
| code | Error code if there is one. | string |
| error | The error message if there is one. | string |
MSSQL SELECT (sqlSelect)
Adapter command for submitting custom written MSSQL SELECT statements to the database.
Request Properties
| Name | Display | Description | Is Required | Type |
|---|---|---|---|---|
| rawSqlSelect | MSSQL SELECT statement | The MSSQL SELECT statement to be run against the database. To prevent MSSQL Injection attacks, all operators in the command should be followed by a unique parameter assignment signified by '@' to be replaced by the Parameter Values. ie. Select columnName from table WHERE columnName = @parameter1 OR columnName = @parameter2 OR columnName IS NULL. Null values need not be parameterized. | True | string |
| parameter | Parameters | Parameters to be used in the MSSQL SELECT Command | False | array |
| parameter[0].name | Parameter Name | The name of the Parameter to be replaced by it's corresponding Parameter Value within the MSSQL Database. This name must match the value used in the MSSQL SELECT Command. | True | string |
| parameter[0].value | Parameter Value | The value to be used in place of the parameter during execution within MSSQL Database. | True | string |
| rows | Rows Returned | The number of rows to return. If left blank, the first 100 rows will be returned. To return more, specify a number up to 500. | False | string |
Response Properties
| Name | Description | Type |
|---|---|---|
| row | Array of rows of data returned by the Microsoft SQL database. Returned in the form of row[1].column1: value, row[1].column2:value, etc. | array |
| status | Status of the adapter command. | string |
| code | Error code if there is one. | string |
| error | The error message if there is one. | string |
MSSQL SELECT Scalar (sqlSelectScalar)
Adapter command for submitting custom written MSSQL SELECT statements to the database when a single value is wanted or expected. Only the value of the first column of the first record is returned from the database.
Request Properties
| Name | Display | Description | Is Required | Type |
|---|---|---|---|---|
| rawSqlSelect | MSSQL Select Statement | The MSSQL command to be run against the database. To prevent MSSQL Injection attacks, all operators in the command should be followed by a unique parameter assignment signified by @ to be replaced by the Parameter Values. ie. Select columnName from table WHERE columnName = <@parameter1> OR columnName = <@parameter2> OR columnName IS NULL. Null values need not be parameterized. | True | string |
| parameter | Parameters | Parameters to be used in the MSSQL Command | False | array |
| parameter[0].name | Parameter Name | The name of the Parameter to be replaced by it's Value within the MSSQL Database. This name must match the value used in the MSSQL Command. | True | string |
| parameter[0].value | Parameter Value | The value used in place of the parameter during the execution within MSSQL Database. | True | string |
Response Properties
| Name | Description | Type |
|---|---|---|
| status | Status of the adapter command. | string |
| value | Scalar value returned from MSSQL Database. | string |
| code | Error code if there is one. | string |
| error | The error message if there is one. | string |
Execute Stored Procedure (storedProcedure)
Used to call an existing stored procedure.
Request Properties
| Name | Display | Description | Is Required | Type |
|---|---|---|---|---|
| storedProcedure | Stored Procedure | The name of the stored procedure that will be called from the connected database. | True | string |
| parameter | Parameters | Parameters to be used in the MSSQL Stored Procedure. | False | array |
| parameter[0].name | Parameter Name | The name of the Parameter to be replaced by it's Value within the MSSQL Database. This name must match the value used in the MSSQL Command. | True | string |
| parameter[0].value | Parameter Value | The name of the Value used to replace the Parameter within the MSSQL Database | True | string |
| column | Columns to Return | Used to specify the order and which columns to return from the Stored Procedure. If not used, all columns returned by the Stored Procedure will be returned and in the order returned from the MSSQL Database. | False | array |
| column[0].name | Column Name | The name of the Column(s) to be returned by the Stored Procedure. | True | string |
Response Properties
| Name | Description | Type |
|---|---|---|
| row | Array of rows of data returned by the Microsoft SQL database. Returned in the form of row[1].column1: value, row[1].column2:value, etc. | array |
| status | Status of the adapter command. | string |
| recordsAffected | The number of records reported affected by the Stored Procedure. | string |
| code | Error code if there is one. | string |
| error | The error message if there is one. | string |
Release History
| Version | Type | Description | Tracking # | Date |
|---|---|---|---|---|
| 2.0.2 | Initial | First release in the Adapter Type store. | ||
| 2.0.3 | Bug fix & Maintenance | Adapter did not load and trust UIP Trusted CA certificates. Updated third-party components and improved maintainability. |
NAP-23862 NAP-23945 |
2022-11-29 |