Database Commands Automation

The Database Commands Automation activity allows you to perform any create, read, update, and delete (CRUD) operation using Execute Query. You can use these activities to perform the following types of task:

  • Create a connection to a database using a connection string.
  • Execute a query on a database and return the query result in a dataset.

Database Commands Automation Actions

You can use the following activities to design an RPA bot that can create a connection to a database or execute a query to fetch data from it:

This action enables you to connect to a database using a standard connection string and then perform different types of activities within it.

Properties

  • Name: Enter the display name of the action.

  • Instance Name: Specify the instance of the DB connection which you want to use as a reference for the automation job.

  • Provider: Select one of the following database types and provides the required connection string:

    • Microsoft Access

      • Database File path: Specify the path and file name of the Access file. For example, C:\mydata.mdb for a file on your local machine.

      • Password: Select this option if the Access database is password protected and provide the user’s password.

      • System Database file path (If database is password protected): Specify the path of the system database file, if the Access database is password protected.

        Only .mdb file format is supported for the Access database server. Currently, the .accdb file format is not supported.

    • SQL Server

      • Server name: Specify the name or IP address of the data source or destination server. If you are using a non-standard TCP port, enter a comma after the server name or IP address, and then enter the port number. For example, 190.190.200.100,1433.

      • Windows NT Security: Select Yes, to connect to the data source using Windows integrated authentication. If you select, No, connect using SQL Server authentication.

      • Username: Specify the User ID if you are using SQL Server authentication.

      • Password: Specify the password for the connecting user if you are using SQL Server authentication.

      • Database Name: Specify the name of the database on the server.

    • PostgreSQL

      • Server Name: Specify the hostname of the computer where the PostgreSQL database server is installed. For example, localhost is the default, if the database server is installed on the same machine.

      • Port number: Specify the TCP/IP port, which the PostgreSQL database server is listening on for request.

      • Username: Specify the User ID used for log in to database server.

      • Password: Select this option if the PostgreSQL database server is password protected and then provide the password for the connecting user.

      • Database name: Specify the name of the database on the server.

    • Oracle

      • Host Name: Specify the hostname of the computer where the Oracle database server is installed. For example, localhost is the default if the database server is installed on the same machine.

      • Port number: Specify the port which the Oracle database is listening on for request.

      • Service name: Specify the service name (or SID) of the database on the server. For example, ORCL is the default for the Oracle database.

      • Username: Specify the User ID used for log in to the database server.

      • Password: Select this option, if the Oracle database server is password protected and then provide the password for the connecting user.

 

Helps you to execute a query on a database and return the results of your queries in dataset format. You can also execute a nonquery statement on a database.

Properties

  • Name: Enter the display name of the action.
  • Instance Name/Variable: Specify the name of the database with which you have created a connection.
  • Query: The query that need to be executed.
  • Query Type: Specify how the query string is interpreted. This field is related to the Query field. You can select one of the following:
    • Return Dataset: Select this option to fetch the records from the database and populate a dataset.
    • Execute NonQuery: Execute a non-query statement on a database such as UPDATE, INSERT, or DELETE statement.
  • Variable to store result: Specify the variable to store the output of the query command.