› Robot building › Robot Steps › Database › Execute SQL

Execute SQL

Use this step to execute a SQL statement on the selected database. You can change records in your database using SQL commands, such as create, update, insert, delete, drop, and so forth. You can also execute stored procedures using the call command, for example call add_record("name"). To read records from your database, use the Query Database step. To store data in a database, use the Store In Database step.

Properties

Configure the Execute SQL step using the following properties.

Database Mapping

Select a database mapping from the databases specified in the Call Robot step.

SQL Statement

Type a SQL statement to execute on the selected database.

The robot does not evaluate the statement you type. Check your SQL statements beforehand or you can check the validity of the statement just by executing the step.

Do not delete or modify tables in a database used by the Store In Database or Execute SQL steps.

Changed Rows

If selected, you can specify a variable of integer type to get the number of rows changed by the SQL statement as the step output.

Timeout

If selected, you can specify timeout for submitting SQL statements in seconds. The value must be greater than zero. The timeout is enforced through DBMS and if it is reached, the DeviceIssue exception is thrown with an error message specific to the database.

Various DBMS types and JDBC drivers may handle timeouts differently. For example, not every system allows a timeout to trip in the middle of certain statements.

Statement Type

Select a statement type from the list.

Prepared Statement

Use this statement type to execute SQL statements repeatedly with high efficiency. Prepared statements are queries written with parameter placeholders instead of actual values, for example SELECT * FROM MyTable WHERE string = ?. You can specify any SQL statement, but you are limited to using only Input parameters. Also, make sure that your DBMS and JDBC driver support the prepared statements.

Stored Procedure Call

Use this statement type to invoke a stored procedure, for example: CALL db.Procedure(?), ? = CALL db.Procedure(?), where ? acts as a placeholder for parameters. Stored procedure call must start with "CALL", optionally with a return value parameter, and supports both Input and Output parameters.

Parameters

You can add various parameters to both Prepared statement and Stored procedure call statement types. The question marks (?) in the query are parameter placeholders and correspond with the parameter order.

Example

If you use a query, such as

SELECT * FROM MyTable WHERE string = ? AND int = ?

and parameters, such as

  • content

  • 123

the query is automatically converted to SELECT * FROM MyTable WHERE string = content AND int = 123.

Parameters that you define on this step can be used as an expression. This enables you to define query parameters dynamically. To prevent errors or injection from user input, we recommend using this parameter substitution in all scenarios.

For a stored procedure call statement, you can select Input, Output, or Input/Output parameters:

  • Input parameters are entered as an expression in the Robot. Their values are evaluated before being placed in the query.

  • Output parameters are used to return the values and also, they support a selection of SQL variable types. The parameter type and SQL variable type must match, otherwise an error is returned. For more information, see later in this section.

  • Input/Output parameters also support a selection of SQL variable types and function as Output parameters. However, they use the current value from the selected variable as Input.

When you select Output or Input/Output parameter types, the Manual Parameter Details check box appears, and you can optionally select the SQL parameter type as well as specify its scale. Normally, these details are retrieved automatically, but some JDBC drivers do not support this, and you should set the types manually.

When you select an SQL variable type from the list, make sure it matches the parameter type defined in the Stored procedure call field. If you cannot find a required SQL variable type in the list, it means that it cannot be used with Robots.

The scale refers to the number of digits to the right of the separator in a NUMERIC or DECIMAL value. Scale is mandatory for parameters of NUMERIC and DECIMAL types and must be specified. For example, if you enter a parameter type NUMERIC(24,12), the scale value will be 12.

Values returned from Output or Input/Output parameters are mapped to their respective Robot values. The following table lists Robot parameter types and their respective SQL variable types.

Robot parameter type SQL variable type

Integer

BIGINT, INTEGER, SMALLINT, TINYINT

Number

DECIMAL, DOUBLE, FLOAT, NUMERIC, REAL

Text or Password

CHAR, CLOB, DATALINK, LONGVARCHAR, LONGNVARCHAR, NCHAR, NCLOB, NVARCHAR, ROWID, SQLXML, VARCHAR

Boolean

BIT, BOOLEAN

Binary

BINARY, BLOB, LONGVARBINARY, VARBINARY

Time

TIME, TIME_WITH_TIMEZONE

Date

DATE

DateTime

TIMESTAMP, TIMESTAMP_WITH_TIMEZONE

The configured JDBC driver performs the conversion of Robot values into query values. The conversion process depends on various factors, such as the JDBC version, DBMS type, version, and settings. It may be necessary to convert the value in an expression or to adjust the query manually so that parameters can be applied.

Especially, date-time values may require manual conversion, because almost no JDBC driver supports date-time values with time zone by default. These are examples of such values per DBMS:

  • IBM DB2

    It may be necessary to format input date-time values as a string without a time zone.

    Example: yyyy-MM-dd HH:mm:ss.SS.

  • Microsoft SQL Server

    It may be necessary to format input date-time values as a string.

    Example: yyyy-MM-dd HH:mm:ss.SSSSSSS xxx.

    When using a Number variable type for an Input/Output parameter, the Output may attain a precision of the Input, even with explicit conversion in the stored procedure.

    Use Output parameters where possible, and use a suitably precise value for the Input parameter otherwise.

    Example: A stored procedure that sets a NUMERIC value to "1.2" may return its value as "1.0" if the parameter has been set as Input/Output, and the initial value only had a precision of 1 (such as 0.0 or 1).

  • MySQL

    It may be necessary to format date-time values as a string without a time zone.

    Example: yyyy-MM-dd HH:mm:ss.

  • Oracle

    It may be necessary to format date-time values as a string.

    Example: dd-MMM-yyyy HH:mm:ss.SS a xxx.

    Example without timezone: dd-MMM-yyyy HH:mm:ss.SS a.

    Use caution because date-time formats can be very locale-dependent.

    Returning whole integers from a stored procedure may not be supported. You can use the round() method in a Robot expression to fix this.

    Be aware that previous versions of the JDBC driver may be unable to automatically determine parameter types and scale.

  • PostgreSQL

    It may be necessary to format date-time values as a string.

    Example: yyyy-MM-dd HH:mm:ss.SS xxx.

    Also, it may require conversion within the query.

    Example: to_timestamp(?, 'yyyy-MM-dd hh24:mi:ss.us xxx').

    Be aware that all versions of the JDBC driver may be unable to automatically determine parameter types and scale. Everything is reported as type "Other", which is not supported for the Robots. Even if you select variable type manually, each parameter in the query may need to be cast explicitly.

    Example: 'call db.proc(? :: character varying)', where the parameter is of VARCHAR type.

    Only function calls are supported by default. It may be required to set the connection URL property 'escapeSyntaxCallMode' to 'call' or 'callIfNoReturn' in the Management Console to use actual stored procedures.