Database node is used to connect to external databases and perform various actions, such as select, insert, modify and other operations through stored procedures on the tables in external databases.

The supported databases are Oracle, PostgreSQL, MSSQL Databases.

Configuration for Database Node

1. Click the Database node to open the attributes pane.
2. In Configurations tab, enter the following details:
3. Select either Jiffy server or Cluster for Run on from the drop-down.
4. Enter the following details:
   • Configuration Name: Name for the configuration.
   • Type: Type of Database to connect to. 
   • Host: Name of the DB Server. 
   • Port: Port number in which DB is running to accept the incoming traffic. 
   • Database Name: Name of the database you wish to connect to.
   • Username: DB username to connect to database. 
   • Password: Password of the DB user to connect to database.

     Secure Vault keys can be used as username and password.

     
5. Click the VALIDATE button to test if DB connection is successful.
6. Once the connection is validated successfully, click the SAVE button to create the configuration.

Operations of Database Node

Details about the query to be executed by Database node is specified in the Operations tab. The following inputs are specified:

Operation

Three types of operations are possible:

1. Select Statement: Retrieves records from database table that match the specified query.

   To select all columns from a Customers table where LastName is Smith, the query would be:
   SELECT * FROM Customers WHERE LastName = ‘Smith’;

2. Update Statement: Modifies existing records in database table as per the specified query. You can write insert, create, update and alter queries here.

   In the Employees table, to update the Salary to 10000 if the Expereince is greater than 1, the query would be:
   Update Employees SET Salary = 10000 WHERE Expereince > 1;

3. Stored Procedure: Runs an existing stored procedure in the Database. Provide the following details:

   • Procedure Name as it exists in the DB server.
   • Input parameters and Output Parameters as <name_of_param>|<type_of_param>.
     Separate each parameter by space.

     CustomerID|INT FirstName|VARCHAR

     Specify the input and output parameters only if it is defined in the procedure.

Query

Query to be executed is specified in the Query field.

Parameters in the query can be passed in the following format:

1. String values: ‘${String}’
2. Integer values: ${Integer}

In the Employees table, to update the Salary to 10000 if the Experience is greater than value in the variable Exp, the query would be: Update Employees SET Salary = 10000 WHERE Experience > ‘${Exp }’;

Processing Type

The SQL query or the Stored Procedure can be processed in two ways:

1. Row Wise Processing: Records in the database are processed row by row.
   Specify the following details:
   • Row Limit: Output row limit. By default, it is set to 100.
   • Columns in result set (space separated): Column names to display in the result set and must be space-separated.

     This is mandatory and the specified column names should be the same as the name of the columns in the table.

   • Iterate on this node’s output?: Toggle to ON, if you want to iterate on the output records. 

     Use Row Wise Processing when you need each row in the database to be fetched as an iteration for further automation.

2. Bulk Processing: Records in the database are processed in bulk.
   Specify the following details:
   • Row Limit: Output row limit. By default, it is set to 100.
   • Table Name: Select the DataTable to which you want the records to be written. Default DataTables and Custom DataTables created in the task will be listed in the dropdown.
     To know more about Data tables, click here.
     

Use Bulk Processing when you need all the records in the database as a table for further automation.

1. If Update Statement is selected in the Query drop-down, Database node expects the input variable to be a Datatable which can be mapped to the input table.
2. In the Query field, write Query to insert/update the Datatable values into the Database.
   You can use syntax as input_table.ColumnName to get the column values from the input_table dynamically.
   

   The exact column names as defined in the Table Definition must be used.

   insert into jiraticketdata(Issue_key,Status,Assignee,Priority) values (‘${input_table.Issue_key}‘,’${input_table.Status}‘,’${input_table.Assignee}‘,’${input_table.Priority}‘)

   
   

Table Definition

All Default and Custom DataTables created in the task are displayed under the Table Definition tab.

Create the dataTable to write output of Bulk Processing operation, in case you want to use the Custom table in this tab.

Properties of the Database Node

1. Navigate to Properties and enter Name and Description.
2. Enable Mark run Failure on Node Fail and Continue on no result? options as required.

Result of Execution of Database Node

After executing the task, the Result of Execution window displays the following tabs:

• Input: The query executed is displayed.
• Output: If Row Wise Processing operation was performed in the node, output tab displays the result of the executed query consisting of columns selected in Columns in result set (space separated) field.
  Click the Iteration # to see the details of each row.
• Run Info: The Run Details and Configuration Details are displayed.
  • Run Details include Run ID, Sequence Number, Iteration ID, and Total Node Execution Time(in seconds).
  • Configuration Details include Configuration Name, JDBC Driver Class, Username, and Config Level.
• Formatted Output: If Bulk Processing operation was performed in the node, Formatted Output tab displays the result set as a table.