Execute Sql Statement Version 10 (Java)

Action group: Databases

Description

Executes a query to retrieve data from a table. 

Action icon

Settings of parameters

Special conditions of use

This action executes the query. The Call Stored Procedure action is used to execute stored procedures.

Parameters are set via "Parameter list". Do not insert them into the body of the request - this leads to errors.

For the "Rename duplicate headers" checkbox:

• There is a table:
  
  With "Rename duplicate headers" = true, and "With header" = True, the "Execute Sql Statement" action will return a table of 4 rows and 5 columns. The column names of this table will be:
  "Test31", ‘Test21’, ‘Test22’, ‘Test32’, ‘Test1’.

• If false, the column names will remain the same. Actions on net that return/process such tables with duplicated column names will crash with an error, because net cannot have a header with the same column names.
• For example, the source table has columns "Test1", "Test" and "Test". If we add indexes only to the current doubles, the names will become "Test1", "Test1" and "Test2", i.e. new doubles with the name "Test1" will appear. Therefore, when renaming a particular column, the action should check if another column already has such a name and if there is such a match, the current column should have the next index. That is, in this case the action will rename the columns as "Test1", "Test2" and "Test3".

Example of use  

Task

Query the database and save the result to a CSV table. Query the developer name with ID=1. 

Solution

Use the actions "Connect Standard Db", "Execute Sql Statement", "Table to CSV". 

Implementation

1. Assemble a robot scheme consisting of actions:
   
   
   

2. Set the parameters for the "Connect Standard Db" action. 

   

   
   

3. Configure parameters for the "Execute Sql Statement" action. In the "DB context" field specify the context of connection to the database. In the "Query template" field, specify the SQL expression (request the developer's name with ID=1). It may contain question marks, as in this case. And then the "Parameters list" field must be filled in. 

   

   These parameters will be substituted in place of the question marks. The fields contain the following data:

   1. name - column name
   2. DBName - database name
   3. dbo - does not change
   4. TableName - name of the table in the database
   
   
   Completed example:
   
   
   When the "With header" checkbox is checked:
   
   
   The "With header" checkbox is not checked:
   
   
   
4. Set the parameters for the "Table to CSV" action. 
   
   
   
5. Launch the robot using the "Start" button in the top panel. 

Result

The program robot completed successfully.

Data from the database is retrieved and written to a CSV table.