Database Package

Latest Version	1.1.0.0
Latest Release Date	2021-10-26
Author	Automation Studio Team
Supports Automation Studio Versions	From 7.1

Description

Use this package to connect to a database (SQL or Oracle) and manipulate and retrieve its data.

Latest Updates

1.1.0.0 - Adds supports for Oracle databases.

Summary of Contents

This package includes a single complex type, Database Support. This type has multiple properties and methods, and a single event.

Sample Project

A sample project is provided for this package. The project displays a callout that lets you test the package methods and see how the responses received can be used.

The testing database may not always be operational. However, you can still use the sample project to explore how the methods and their responses can be used.

To use this project:

Download the Database package from the Resource Center and save the file to the folder %appdata%/Nice_Systems/AutomationStudio/NuGet Packages, if you haven't already done so.
Download the sample project files here.
Unzip the download file.
Open the project in Automation Studio.
To see how the project works, click Start Project. On the callout that displays, click the various buttons to see how the package methods are used.

Package Types

Database Support

Properties

Create a single Database Support variable to use this package.

The Database Support variable includes properties required for connection to the database, as well as properties in which status and error information is returned.

Properties for Connection Details

To specify connection details, you can either use a connection string or specify individual connection details as follows.

Option	Instructions
Use a connection string	Set Use ConnectionString to True. Specify the value of ConnectionString.
Specify individual connection details	Set Use ConnectionString to False Specify the values of Data Source Name, Database Name, User Name, and Password. If connecting to an Oracle database, also specify the value of DBA Privilege if required by the database.

Option

Instructions

Use a connection string

Set Use ConnectionString to True.

Specify the value of ConnectionString.

Specify individual connection details

Set Use ConnectionString to False

Specify the values of Data Source Name, Database Name, User Name, and Password. If connecting to an Oracle database, also specify the value of DBA Privilege if required by the database.

The connection properties are listed below.

Property	Description
ConnectionString	The ConnectionString that stores information about the database and how to connect to it. Can be used as an alternative to specifying individual connection properties. Example for SQL: Data Source=someName.mssql.somee.com;Initial Catalog=someName;user id=abcd_SQLLogin_1;pwd=lsuxvjgyrz; persist security info=False; Additional info from Microsoft here Example for Oracle: Data Source=ABC-01234;User Id=sys;Password=qwER123!@#;DBA Privilege=SYSDBA; Additional info from Oracle here
Data Source Name	The server instance name For example: suriTesting.mssql.somee.com
DBA Privilege	(Relevant for Oracle databases only) The administrative privilege, either SYSDBA or SYSOPER, if required by the database
Database Name	The name of the operational database, for example: suriTesting
Password	The password for connecting to the database, for example: lsuxvjgyrz For security reasons, avoid using plaintext usernames and passwords. Instead, populate these properties with username and password data stored on the server, using an Application Login Info variable.
Provider	The database provider Values: SQL Oracle If not specified, SQL is assumed.
Use ConnectionString	Specify whether to connect to the database using the ConnectionString property (True), or using the values of the other connection properties (False).
User Name	The user name for connecting to the database, for example: ssinghengg_SQLLogin_1 For security reasons, avoid using plaintext usernames and passwords. Instead, populate these properties with username and password data stored on the server, using an Application Login Info variable.

Properties for Status and Error Information

Status and error information are returned in the properties listed below:

Property	Description
Error Details	The error details if the last method executed on the database resulted in an error (i.e. if Status was set to Error) (Read-Only)
Status	The status of the last method performed on the database Values: Connected Completed Error See Methods below. (Read-Only)

Property

Description

Error Details

The error details if the last method executed on the database resulted in an error (i.e. if Status was set to Error)

(Read-Only)

Status

The status of the last method performed on the database

Values:

Connected
Completed
Error

See Methods below.

(Read-Only)

Methods

Get Connection Status

Check the status of the connection to the database.

Prerequisites

Database details must be specified in the variable properties.

Either:

• Specify ConnectionString and set Use ConnectionString to True.

• Specify Data Source Name, Database Name, User Name, and Password, and set Use ConnectionString to False.

Parameters

None

Returns

None

Variable Values Modified

This function modifies properties of variables:

Sets Status to:

Connected: Connection to database is OK.
Error: An error occurred.

Modifies Error Details to:

The error message received if Status set to Error
Empty if Status set to Connected

Example

The onClick actions for a Verify Connection button are shown below.

When the button is clicked, the value of the Database_Support variable's Status property is written to the Value properties of two callout controls.

Execute Manipulation Query

Execute a query to add, delete, or modify database data.

Parameters

Parameters	Type	Description
Query	text	The database query

Returns

(number) Returns the number of database records affected by the query.

Variable Values Modified

This function modifies properties of variables:

Sets Status to:

Completed: The query was performed without errors.
Error: An error occurred.

Modifies Error Details to:

The error message received, if Status set to Error
Empty, if Status set to Completed

Example

The onClick actions for the Execute Manipulation Query button are shown below.

When the button is clicked:

The value of the lblResponseStatus callout control is emptied.
The Execute Manipulation Query method of the Database_Support variable is performed. Its output is written to the NumResponse variable. The query specified is:
```
update tblProduct set updatedOn=getdate() where PersonID <3
```
The value of the Database_Support variable's Status property is written to the Value property of the lblResponseStatus callout control.
The value of NumResponse (representing the number of database rows affected by the query) is written to the Value property of the lblDML callout control.

Get String Using Query

Execute a query to retrieve a single string from the database.

Parameters

Parameters	Type	Description
Query	text	The database query

Returns

(text) Returns the response to the query.

Variable Values Modified

This function modifies properties of variables:

Sets Status to:

Completed: The query was performed without errors.
Error: An error occurred.

Modifies Error Details to:

The error message received, if Status set to Error
Empty, if Status set to Completed

Example

The onClick actions for the Get String Using Query button are shown below.

When the button is clicked:

The value of the lblResponseStatus callout control is emptied.
The Get String Using Query method of the Database_Support variable is performed. Its output is written to the StringResponse variable. The query specified is:
```
select top 1 productname from tblproduct
```
The value of StringResponse is written to the Value property of the lblString callout control.
The value of the Database_Support variable's Status property is written to the Value property of the lblResponseStatus callout control.

Get Rows Using Query

Execute a query to retrieve a list of rows from the database.

Parameters

Parameters	Type	Description
Query	text	The database query

Returns

(list of rows) Returns the response to the query.

Variable Values Modified

This function modifies properties of variables:

Sets Status to:

Completed: The query was performed without errors.
Error: An error occurred.

Modifies Error Details to:

The error message received, if Status set to Error
Empty, if Status set to Completed

Example

The onClick actions for the Get Rows Using Query button are shown below.

When the button is clicked:

The value of the lblResponseStatus callout control is emptied.
The Get Rows Using Query method of the Database_Support variable is performed. Its output is written to the ListOfRows variable. The query specified is:
```
select * from tblProduct
```
A user-defined function is executed.
The value of the Database_Support variable's Status property is written to the Value property of the lblResponseStatus callout control.

Events

Exception

Triggered when an error occurs when performing any of the methods listed above.

If an error is detected before the method is sent, for example, if the User Name property has not been specified, the Exception event will not be triggered. The Status property of Database Support variable will be set to Error and the property Error Message will store the error details.

The error message is written to the Message parameter of the event.

Parameters

Parameter	Type	Description
Message	text	The error message