Database

Please read the Terms of Use for Materials on ZennoLab

1 Table of contents
2 Description
3 How to add an action to a project?
4 How to work with an action?
- - 4.1.1 Connection manager
  - 4.1.2 Data provider
  - 4.1.3 Request
- 4.2 Processing results
  - 4.2.1 Save results to:
    - 4.2.1.1 Variable
    - 4.2.1.2 List
    - 4.2.1.3 Table
    - 4.2.1.4 Variables
5 Useful links

Description

ZennoPoster allows you to work with various types of databases such as Microsoft SQL, MySql, SQLite, etc. You will be able to connect to databases on a local computer or a remote server and load the necessary data for your web tasks.

How to add an action to a project?

Via the context menu Add action → Data → Databases

Or use smart search .

How to work with an action?

Data source

In order to connect to the database, you need to properly configure the connection. It will differ depending on the DBMS.

Connection manager

You can use the Connection manager to make it easier to create a connection string. Based on the data specified in it, a connection string will be generated.

Variable macros cannot be used in the Manager.

Data provider

There are several data providers:

SqlClient - Provider for native connection to SQLServer;
MySqlClient - provider for native connection to MySQL;
OleDb - one of the standards that allows you to connect to various DBMS (including SQLServer);
Odbc is another standard for connecting to a DBMS.

Connection string

The connection string specifies various login parameters (be it login and password). Examples of connection strings for different DBMS can be viewed at this link.

In order not to create this string manually, you can use the Connection Manager (described above).

Request

Request type

There are several types of requests

No answer

Request is used for operations that do not return data from the database (for example, INSERT or DELETE). The response of such an operation is the number of records involved in the request.

Scalar request

Allows you to get a single value. For example, if you want to execute the aggregate function (select sum (price) from fruit).

Normal request

Returns a table of data.

Request text

SQL request input field.

Use parameters in request

Parameters are used to make it easier to create a request. They will be substituted in the necessary places in the request text. There are named and unnamed parameters. In the first case, the name is important, in the second - the order of the variables. Which type of parameters to use depends on the DBMS.

When using parameters, the text inside them is automatically escaped

Named parameters

An example of a query with named parameters:

Unnamed parameters

An example of a request with unnamed parameters:

Processing results

In this section, you need to choose where to save the query result.

Save results to:

Variable

All rows and columns that are returned as a result of the query will be stored in one variable.

It is also necessary to select separators with the help of which the rows and columns will be separated from each other.

List

In order to save the results to a list, it is important to specify which separator to use for the columns. As a result, each line from the base will be entered into a new element of the list, and the corresponding separator will be inserted between the columns.

Table

When writing data to a table, the cells will be filled in accordance with the request.

Variables

Line number - as a result of the query, several lines may be returned, so you must specify the number of the one that we will parse (numbering from zero!).

In the table below, you select the cell number in the row and the variable to which this cell will be saved (numbering from zero!).