Query to Database

The Query to Database activity allows you to connect to an external database and get information from database tables, write or replace data. You can use this activity to connect to Oracle Database, SQL Server, MySQL, PostgreSQL, and FoxPro databases.

Activity settings

To open the settings window, click on the activity on the process diagram.

Parameters Tab

The Parameters tab displays the basic activity parameters.

database-query-1

Name. The activity name on the process diagram. It is set by default when adding the activity. You can change the name in this field.

Database type. Select from the list the type of database to which you are making the query.

In the Connection string and Query string field, you can enter a value manually or use the process context variables that store the values. Click on the send-mail-2 icon and specify the variable. You can select it from the list or add a new one by clicking Create parameter. The value is inserted as {$variable name}.

Connection string. Information that is required to connect to the database. A connection string is written as a set of parameters Key=<Value>. Parameters may differ depending on the database type.

Oracle Database

Data Source=<host>:<port>/XEPDB1; User Id=<login>; Password=<password>;

SQL Server

Server=<host>; Database=<login>; Trusted_Connection=True;

MySQL

Server=<host>; Database=<dbName>; Uid=<login>; Pwd=<password>;

PostgreSQL

Host=<host>; Database=<dbName>; Username=<login>; Password=<password>;

FoxPro

Provider=sqloledb; Server=<myServerName>,<myPortNumber>; Database=<myDataBase>; User Id=<myUsername>; Password=<myPassword>;

The connection string can also be written as a context variable of a String or Integer type.

Query string. This field contains information that is passed when querying the database. The query string parameters may differ depending on the database type.

The table displays examples of a select query string sent to a database.

Oracle Database

SELECT table FROM tabs OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

PostgreSQL, MySQL, SQL Server, FoxPro

SELECT * FROM table

The query string can also be written as a context variable of a String, Non-integer, or Integer type.

Timeout. Waiting time (in milliseconds) after which an attempt to query the database will stop. By default it is 1000.

Waiting for results. Select Yes if you want to wait for a response from the database after the data query. If you do not want to wait for a response (for example, when requesting an update or data entry), select No.

Variable. A context variable that will store the data from the database as a response. You can select a variable from the list or create a new one by clicking send-mail-3. This field supports the String, Date/Time, Yes/No, Non-integer, Integer, and Table type variables.

Conditions Tab

Read more about the Conditions tab in the "Basic activity settings principles" article.

Found a typo? Highlight the text, press ctrl + enter and notify us