Database Applications

This activity allows you to automate the task you perform in a database such as retrieving data,updating data, automating database administration task and so on.

 

Prerequisite: Configuring Database Application

Following are the prerequisites required for using the database application:

 

Requirement	Description
Configure Database Application  in Automation Studio	In the Admin menu, add an application of Application Type- DataApps.   In the Application Properties panel: In the DB Type list, select the type of database to configure. Based on the type of database selected, provide the other relevant details. Click the (Save Properties) icon to save the application. The DatabaseApps application is configured.
If DB Type is Excel	Software Required AccessDatabaseEngine_2007.exe (Download Link: https://www.microsoft.com/en-in/download/details.aspx?id=23734) Constraints: Deletion of a row is not supported in Excel. Selecting data from one sheet and inserting it in another sheet, in a single query is not supported. Multiple insert statements in a single query are not supported.  Guidelines to use Excel as a Database: Sheet Names in the Excel file must be used as a Table Name in “FROM” clause; Sheet Name must be in Squared Brackets and appended with a $ symbol. E.g., SELECT * FROM [Sheet1$] If there is a space in the excel column, enclose it within the square brackets.
If DB Type is SQL or Oracle	Software Required: SQL: NA Oracle: ODAC and Oracle Developer Tools for Visual Studio. If the client uses Oracle database, a 32-bit application server, then “. ODP.NET comes bundled with ODAC. (Version 11.2.0.3.0 12c)”. 64-bit ODAC for Windows x64. If the client uses Oracle database on a 64-bit application server, then “. NET comes bundled with ODAC. (Version 11.2.0.3.20 12c)”. MySQL: Copy MySQL.Data.dll from installed location to Automation Studio, Engage and robot related folder Download and install MySQL connector 8.0.15. Constraints: SQL Supported Data Types: Int VarChar(max) Bit DateTime DataTable datatype is supported only as an Output parameter Oracle Supported Data Types: Int32 VarChar(max) Char DateTime DataTable datatype is supported only as an Output parameter MySQL Supported Data Types: Int VarChar(max) Bit DateTime DataTable datatype is supported only as an Output parameter   Guidelines: SQL: SP Parameter in the mapping window (INPUT and OUTPUT) must match with the stored procedure parameters in the Database. While mapping stored procedure parameters in input/output window in Studio, '@' is not required in 'SP Parameter' E.g. In Database Stored Procedure => Create PROCEDURE dbo].[usp_InsertMessages6] @NAME varchar(20) , @Id int Output AS....... Then in the Studio, 'SP Parameter' is "NAME" and "Id" If the stored procedure returns multiple datatables then the last datatable is returned as an output in the mapped output parameter. Oracle SP Parameter in the mapping window (INPUT and OUTPUT) must match with the stored procedure parameters in the Database. While mapping stored procedure parameters in input/output window in Studio, '@' is not required in 'SP Parameter' E.g. In Database Stored Procedure => Create PROCEDURE dbo].[usp_InsertMessages6] @NAME varchar(20) , @Id int Output AS....... Then in the Studio, the 'SP Parameter' is "NAME" and "Id". If the stored procedure returns multiple datatables then the last datatable is returned as an output in the mapped output parameter. If Stored procedure output has a cursor, then it must be mapped in the Output Window with the datatable type as a parameter. MySQL SP Parameter in the mapping window (INPUT and OUTPUT) must match with the stored procedure parameters in the Database. While mapping stored procedure parameters in input/output window in Studio, ‘@' is not required in 'SP Parameter' If the stored procedure returns multiple datatables then the last datatable is returned as an output in the mapped output parameter.

 

Using Database Application Activity

1. In the Canvas Tools pane, click Process Components to expand the tool and view the associated activities.
2. Drag the Application activity and drop on to the Flowchart designer on the Canvas.
   
   


3. In the Application Type list, select DatabaseApps. You must have at least one application added for it to appears in the list. See Admin Capabilities to know how to add application before using an activity.
4. In the Select An Application list, select the database application you want to perform automation on. Alternatively, you can a add a new excel application at this point of time. To add the database application:
   1. In the Select An Application list, click Add New Application. The Add New Application dialog box appears.
      
      
   
   
   2. In the Application Name field, enter a desired name of the application.
   3. In the DbType list, select the type of database you want to add. Supported types are:
      • SQL
      • Oracle
      • MySQL
      • Excel
   4. If DbType selected is SQL, select the Authentication Type based on your settings. Enter the Server and DataBase Name related to your SQL server.
   5. If DbType selected is Oracle or MySQL, enter the Server, Port  and DataBase Name related to the database type selected.
   6. If DbType selected is Excel, enter the path of the excel application saved on the system.
   7. Click SAVE. The database application is added.
5. Double click the Application activity, drag the Execute DB Query activity and drop inside the Application activity.
   
   


6. In the Select Database Operation list, select the type of database operation you want to perform. This drop down appears only if the database application configured is of type SQL, MYSQL or Oracle. In Automation Studio, the supported data base operations are- Select, Insert and Update. Click any of the following link to see the details related to available database operations:
   • Query
   • StoredProcedure

Query

A single line of statement or query for storing, manipulating or retrieving data from a database. Additional configurations are not required for Query database operation.

In the Query list, select the parameter to which you have assigned the query. You must define the parameter in the Parameter bar to use this option. Alternatively, select the check box available beside Query field and then enter the query as default value.

StoredProcedure

A  group of one or more line of statements or query that you can save for the purpose of reusing it. An important facet of using Stored Procedure is to provide input and output parameters to map them with the parameters declared in the Stored Procedure in the database.

 

 

1. In the Query list, select the parameter to which you have assigned the name of the stored stored procedure. You must define the parameter in the Parameter bar to use this option. Alternatively, select the check box available beside Query field and then enter the query as default value.
2. Click the (Settings) icon and then click Input Parameter. The SP Input Parameters dialog box opens.

 

 

3. Click Add.
4. In the SP Parameter field, enter the input parameter declared in the Stored Procedure in the database. The input parameter name must exactly be same as that of the input parameter declared in the stored procedure without the @ symbol.
5. In the DataType list, select the type of data. Available options are- Text,  Numeric, DataTime and Boolean.
6. In the Process Arguments list, select the parameter you want to map with the Stored Procedure input parameter. You must define the parameter in the Parameter bar to use this option.
7. Click CONFIRM.
8. Click the  (Settings) icon and then click Output Parameter. The SP Input Parameters dialog box appears.
   
   


9. Click Add.
10. In the SP Parameter field, enter the output parameter declared in the Stored Procedure in the database.
11. In the DataType list, select the type of data. Available options are- Text,  Numeric, DataTime and Boolean.
12. In the Process Arguments list, select the parameter you want to map with the Stored Procedure output parameter. You must define the parameter in the Parameter bar to use this option.
13. Click CONFIRM.

By default

1. In the Output field, ResultSet argument is created that stores the output of the entered query.
2. In the Success Status field, SuccessStatus argument is created that returns the status of the entered query.
3. In the Count of Rows in result field, RowCount argument is created that stores the total number of rows present in the output of the entered query.
4. In the Count of Columns in result field, ColumnCount argument is created that stores the total number of columns present in the output of the query you entered.

The fields are configured and the Application activity for Application Type-DatabaseApps is created.

Following is a sample of the input, output and other parameters created in the database activity:

 

Database Application Properties

The properties of a windows application are listed in the following table and can be edited in the Property grid on the right pane.

 

Property Name	Usage
Control Execution
Ignore Error	When this option is set to Yes, the application ignores any error while executing the activity. If set to NA, it bypasses the exception (if any) to let the automation flow continue; however, it marks the automation status as failure, in case of an exception. By default, this option is set to No.
Delay
Wait After	Specify the time delay that must occur after the activity is executed. The value must be in milliseconds.
Wait Before	Specify the time delay that must occur before the activity is executed. The value must be in milliseconds.
Misc
Breakpoint	Select this option to mark this activity as the pause point while debugging the process. At this point, the process freezes during execution allowing you to examine if the process is functioning as expected. In large or complex processes, breakpoints help in identifying the error, if any.
Commented	Select this option to mark this activity as inactive in the entire process. When an activity is commented, it is ignored during the process execution.
DisplayName	The display name of the activity in the flowchart designer. By default, the name is set as the name of the selected application. You can change the name as required.