The parameters are script variables. +\ Connection to the database server is carried out using a connection string. \ Access to the database occurs through ODBC and requires the presence of appropriate drivers in the OS. \ :description1: In case of an error, the transition is carried out along the “Error” branch, and the corresponding database exception code is returned to the “Error code” variable.

SQL Query Component

:icons:font :hardbreaks: :imagesdir: ../../../assets/images :toc-title: Contents :toc:

Description

Allows you to perform actions in the database, as well as pass parameters (to input and output). +\

Values are returned from the database in tabular form. Saving values into variables is carried out on the principle that the names of the columns in the last returned dataset and script variables are identical. The variables are assigned the values from the last row of the last dataset.

Table 1. System characteristics

Index

112

Short name

sql

Script types

All

Start module

r_script_component_sql

Mode

Asynchronous

Icon

112

Branching type

Branching, closing

Properties

Table 2. Properties
Specification Description

Name: Database

Code: serverType

Visibility condition: no

Default: Other

DB to connect.

Possible options:

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

  • Other (other, 2) – Another database.

Name: Driver

Code: driverType

Visibility condition: yes

Default: PSQL

Possible options:

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

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

For the database specification mode Other.

Name: Connection string

Code: sqlConnection

Visibility condition: yes

Default: ``

DB connection string. For the database specification mode Other.

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=platformpgadmin;
Pwd=123456;
or similar to the format in the configuration:
host:127.0.0.1,port:5432,login:platformpgadmin,pwd:123456,database:postgres

Name: Time, s Code: timeoutSec Visibility condition: no

Default: 30

Timeout for executing a database request.

Name: Request

Code: sqlQuery

Visibility condition: no

Default: — 

SQL query body.

ODBC example:
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 a query returns a table of values consisting of several rows, then an arbitrary row is stored in variables.

In this case, the values are compared by the names of the columns in the output dataset and variables.

The return special values null, true, false are converted to a string representation when stored in script variables.

Name: Request parameters

Code: sqlParamsV

Visibility condition: no

Default: — 

List value that sets the parameters for the request.

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

Name: Encoding Code: encoding Visibility condition: no

Default: utf-8

Encoding in which to store string values in variables.

Possible options:

  • utf-8 (1)

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

Name: Error code Code: Error code

Visibility condition: yes

Default: — 

Variable for storing the error code.

Name: Error name

Code: errCodeName

Visibility condition: yes Default: — 

Variable for storing the error name.

Name: Transition

Code: transfer

Visibility condition: no

Default: — 

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

Name: Transition, time Code: transferTimeout Visibility condition: no

Default: — 

The component to which control is transferred after the timeout expires before receiving a response to the request.

Name: Transition, error

Code: transferError

Visibility condition: no

Default: — 

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

See also