Execute Sql Statement Version 10 (Java)

Action group: Databases

Description

Executes a query to retrieve data from a table. 

Action icon

Settings of parameters

PropertyDescriptionTypeFilling exampleMandatory field
Parameters
DB contextConnection context to the selected databaseRobin.DbContextDatabase contextYes
Query templateQuery templateRobin.String
Yes
Parameters listA collection of parameter values to be substituted into a queryRobin.Collection
No
With headerIf the value is «true», the row with the table header will be rendered separately and will not be included in the rows with the contents of the table.Robin.BooleantrueNo
Rename duplicate headersIf the value is «true», in the header of the table value that the actions return as a result, indexes will be added to the same column names. Numbering starts from 1.Robin.BooleantrueNo
Results
ResultStatement execution result as a data tableRobin.DataTable

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. 

  • Нет меток