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:

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:

a.    In the Select An Application list, click Add New Application. The Add New Application dialog box appears.

b.    In the Application Name field, enter a desired name of the application.

c.     In the DbType list, select the type of database you want to add. Supported types are:

§           SQL

§           Oracle

§           MySQL

§           Excel

d.    If DbType selected is SQL, select the Authentication Type based on your settings. Enter the Server and DataBase Name related to your SQL server.

e.    If DbType selected is Oracle or MySQL, enter the Server, Port  and DataBase Name related to the database type selected.

f.      If DbType selected is Excel, enter the path of the excel application saved on the system.

g.    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.

a.    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.

b.    Click the (Settings) icon and then click Input Parameter. The SP Input Parameters dialog box opens.

c.     Click Add.

d.    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.

e.    In the DataType list, select the type of data. Available options are- Text,  Numeric, DataTime and Boolean.

f.      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.

g.    Click CONFIRM.

h.    Click the (Settings) icon and then click Output Parameter. The SP Input Parameters dialog box appears.

i.      Click Add.

j.      In the SP Parameter field, enter the output parameter declared in the Stored Procedure in the database.

k.    In the DataType list, select the type of data. Available options are- Text,  Numeric, DataTime and Boolean.

l.      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.

m.  Click CONFIRM.

7.    By default:

a.    In the Output field, ResultSet argument is created that stores the output of the entered query.

b.    In the Success Status field, SuccessStatus argument is created that returns the status of the entered query.

c.     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.

d.    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.

   Related Topics

Step-by-Step Guide to Use Database Application to Retrieve Row Count of the Stored Data