SQL query component

Table of Contents

Description

Allows to perform actions in the database, as well as to pass parameters (to input and to output).
Scenario variables act as parameters.
Connection to the database server is performed by the connection string. The database is accessed via ODBC and requires the appropriate drivers in the OS.

In case of an error, the transition is performed along the "Error" branch, and the corresponding database exception code is returned to the "Error code" variable.

Return values from the database are returned in tabular form. The values are saved to variables based on the identity of the column names in the last returned dataset and scenario variables. Variables are assigned values from the last row of the last dataset.

Table 1. System Characteristics

Index

112

Short title

sql

Types of scenarios

All of them

Starter module

r_script_component_sql

Mode

Asynchronous

Icon
112

Branching pattern

Branching, closing

Properties

Table 2. Properties
Specification Description

Title: Database
Code: serverType
Visibility: no
Default: `Other'

DB to connect to.
Possible options:

  • Project (project_psql, 4) - Project database of the domain (PosgtreSQL), the connection string to which is taken from the domain settings (settings → project_postgresql_connstr).

  • Other (other, 2) - Other DB.

Title: Driver
Code: driverType
Visibility: yes
Default: PSQL

Possible options:

  • ODBC (odbc, 0) – driver connection ODBC.

  • PSQL (psql, 1) – connection via internal adapter to PostgreSQL.

For the Other' database indication mode.

Title: Connection string
Code: sqlConnection
Visibility: yes
Default: ``

Database connection string.
For the Other' database indication mode.

Example for odbc driver
Driver={SQL Server Native Client 11.0};
Server=192.168.0.73,14350;
Database=testbase;
UID=test;
PWD=123;
Example for psql driver
Driver={PostgreSQL Unicode(x64)};
Server=127.0.0.1;
Port=5433;
Database=era_dummy_central_common;
Uid=erapgadmin;
Pwd=123456;
or similar to the format in the configuration:
host:127.0.0.1,port:5432,login:erapgadmin,pwd:123456,database:postgres

Title: Time, s
Code: timeoutSec
Visibility: no
Default: 30

Database query execution timeout.

Title: Request
Code: sqlQuery
Visibility: no
Default: — 

SQL query body.

Example for ODBC:
INSERT INTO EMPLOYEE (NR, FIRSTNAME, LASTNAME, GENDER)
VALUES (?, ?, ?, ?)
Example for PSQL:
SELECT name as a, birthdate::varchar as b,Where id = $1 or code = $2

If the query returns a table of values from multiple rows, an arbitrary row is stored in variables.
In this case, the value mapping is done by column names in the issue and variable dataset.

The returned special values null, true, false are converted to string representation when saved to script variables.

Title: Query Parameters
Code: sqlParamsV
Visibility: no
Default: — 

A list value that sets the parameters for the query.

The order of parameters strictly corresponds to the order in which the parameters are applied in the query.

Title: Coding
Code: encoding
Visibility: no
Default: utf-8

Encoding in which to save string values in variables.
Possible options:

  • utf-8 (1)

  • cp1251 (2) – Code page windows-1251 (Cyrillic alphabet)

Title: Error Code
Code: Error code
Visibility: yes
Default: — 

Variable to store the error code.

Title: Error name
Code: errCodeName
Visibility: yes
Default: — 

Variable to store the name of the error.

Title: Transition
Code: transfer
Visibility: no
Default: — 

The component to which control is passed after successful completion of the request.

Title: Transition, Time
Code: transferTimeout
Visibility: no
Default: — 

The component to which control is passed after the timeout expires before a response to the request is received.

Title: Transition, Error
Code: transferError
Visibility: no
Default: — 

The component to which control is passed if an error occurs in preparing or executing a request.

See also

  • Personalization
  • Increase font size
  • Decrease font size