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

Let's see an example of retrieving row count of the data retrieved using the stored procedure created for the Employee Details table as shown below:

 

Below is the sample stored procedure:

 

 

The stored procedure is created to retrieve the details of the employees from the table whose EmployeeID is greater than the specified EmployeeID. The EmployeeID is passed as an input variable, EmpId and the data extracted from the database gets stored in a data table. You can use the out-box-activities associated with the DataTable tools if you want to view the extracted datatable.

 

Prerequisite:

1. In the Admin menu, add an application of Application Type- DataApps.
2. In the Application Properties panel:

    

   • In the DB Type list, select SQL.
   • In the DB Server field, enter the server name where database is hosted for database storage and retrieval.
   • In the DB Port field, enter the port number where the required SQL server is hosted.
   • In the DB Name field, enter the name of the required database from where you want to retrieve the information. In this example the name of the database used is DemoDB.
   • In the Display Name field, enter SQLDBDemo as the display name of the configured database in Automation Studio.
   • Other relevant fields are auto populated.
     
     

      

3. Click the (Save Properties) icon to save the application. The required database and the application is configured in Automation Studio.

 

To view the row count and extracted data from a SQL Database:

1. In the Studio menu, create a new process.
2. In the Canvas Tools pane, click Process Components to expand the tool and view the associated activities.
3. Drag the Application activity and drop on to the Flowchart designer on the Canvas.
4. In the Application Type list, select DatabaseApps. 
5. In the Select An Application list, select the SQLDBDemo database application configured above.

 

 

6. Double click the Application activity, drag the Execute DB Query activity and drop inside the Application activity.
7. By default, in the Parameter bar, ResultSet, SuccessStatus, RowCount, and ColumnCount arguments are created and mapped with the Output, Success Status, Count of Rows in result, and Count of Columns in result fields respectively.
8. In the Parameter bar, create an In arguments, MyQuery and InputEmpId to pass the stored procedure and the EmployeeID as respective inputs to the process workflow. Define their respective values.
9. Create an Out argument, OutputSP to store the data retrieved as per the stored procedure.
   
   


10. In the Select Database Operation list, select the StoredProcedure option.
11. In the Query list, select the MyQuery argument to pass the stored produce as input.
    
    


12. Click the (Settings) icon and then click Input Parameter. The SP Input Parameters dialog box opens.
13. Click Add.
    1. In the SP Parameter field, enter the EmpId parameter as per the input parameter declared in the stored procedure.
       1. In the DataType list, select the Numeric option.
       2. In the Process Arguments list, select the InputEmpId argument created above.
          
          
       
       
       3. Click CONFIRM.
14. Click the  (Settings) icon and then click Output Parameter. The SP Input Parameters dialog box appears.
    1. Click Add.
    2. In the SP Parameter field, enter the output parameter declared in the Stored Procedure in the database.
    3. In the DataType list, select the type of data. Available options are- Text,  Numeric, DataTime and Boolean.
    4. 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.
    5. Click CONFIRM.

To view the output in Automation Studio, let's add WriteLine activity. You can publish, deploy and assign this process to a robot, if you want to execute the process outside Automation Studio.

15. Add Writeline activity and in the Text field, enter the CompDetails argument.
16. Save the process.
17. Setup the environment and perform test run.

The Output console displays the