Working with databases in Loginom

The simplest way to work with data in Loginom is to use files. Their use has significant advantages: minimum settings, no need for additional software, etc.

Loginom supports the following databases:

• BigQuery ;
• ClickHouse ;
• Firebird (RedDB compatible);
• InterBase ;
• MS Access ;
• MS SQL ;
• MySQL (compatible with MariaDB);
• Oracle ;
• PostgreSQL (this connection can also be used with Greenplum and Arenadata);
• SQLite .
• Working with databases in Loginom

In addition, working with sources via ODBC is supported .

Working with databases in Loginom requires additional actions related to preliminary connection setup . This mechanism allows multiple use of a once configured connection in a package during import and export, which is quite convenient.

Let’s look at working with databases in Loginom using the example of connecting to PostgreSQL, a popular free DBMS that has an implementation for many operating systems.

Connection

First, you need to create a connection that contains all the necessary settings to connect to the data source, such as password/login, database location and parameters.

To create a connection to PostgreSQL you need to:

1. Go to the Connections section .
2. Drag the PostgreSQL component into the workspace (double-click or drag and drop).
3. Enter the server host, TCP port and database name into the connection string, separating everything with colons (example: 10.200.1.193:5432:test_db).
4. Enter the login and password of the database user.
5. Test the connection and save if there are no problems.

The steps listed are clearly shown in the video:

A common mistake when setting up a connection is not to specify a specific database after the host and port number. This way, Loginom will not be able to work, since it is not known which database the user wants to connect to.

Reusing connection

Typically, companies use a small number of databases that are accessed by many users. Therefore, it is advisable to configure the connection once and then reuse it, rather than performing this operation for each new employee.

To do this, you can create a scriptless package consisting only of configured connections, set the access modifier for the connections to “Open” and place the package in a location accessible to everyone, such as a shared folder in a file storage .

Then, when it is necessary to work with databases, the user connects the package with all configured sources and uses the connections in the script.

Import data

After setting up the connection, data from the database can be imported. To do this, you need:

1. Go to Scenario .
2. In the Connections panel , select the previously configured connection and move it to the scenario construction area.
3. Select the Database component in the import section and move it to the script construction area.
4. Connect the connection ports of the PostgreSQL and Database nodes .

To transfer a Connection to a script, you need to select it, right-click and choose one of two methods:

1. Add a connection link ;
2. Add connection node .

However, you may need to change the settings. For example, specify a different user name or password. In this case, you need to add a node to the script, not a connection link. Then, in the configuration wizard, you can change the settings specified when creating the connection.

In the import wizard, you can choose to get a table or execute a SQL query .

When importing from a table, you can extract all fields or select specific ones.

Note: The logic for the “Select all fields * ” and “Check all fields” options is slightly different:

• In the first case, if fields are added or removed from the table, the import will work correctly. All existing columns will be imported.
• In the second case, when changing the table structure, there may be problems. For example, new fields will not be included in the selection, or the program will return an error that a field is missing if some previously existing field is deleted from the original table.

Thus, in the first case, all fields in the table will be imported, and in the second case, all marked fields.

 

Import using SQL query

You can also use an SQL query for import. The wizard has built-in mechanisms to help you write a query. For example, pressing CTRL+Space will display a list of available functions, fields, etc.

When writing queries, you can use variables. For example, you can insert a table name or any other fragment into an SQL query, up to the point that you can pass a fully formed query text.

Macro substitution provides more flexibility, but job seekers database requires more careful use because it is easier to make a mistake. More details in the help .

Export data

The configured connection is used not only branding pou konstwi kliyan for importing but also for exporting data. To export to the DB, you need to:

1. Move the Database component from ar numbers the Export section to the script.
2. Connect the PostgreSQL connection and DB export nodes.
3. Connect the dataset to be exported to the Database export node.
4. Go to the settings of the Database export node .
5. In the database export settings, select a table name or create a new one.
6. Select export type.
7. Specify the correspondence between the source columns and the table fields and save.

When exporting data, the export option is specified:

Creating a table in the database during export

In Loginom, when working with a database, there is the ability to create a table. The user does not need to use third-party software to create a table and fields, but he must have rights to perform this operation in the database.

To create a table when exporting data, you need:

1. Go to the settings of the Database export node .
2. Select “Create table”.
3. Enter the table name.
4. You can change the names, type and size of the field, or add a new one.
5. Click “Create table”.

Import and export from the database are clearly shown in the video:

Conclusion

Loginom provides convenient mechanisms for working with databases:

• support for popular DBMS, including ODBC sources;
• setting up and reusing connections;
• import of tables, with the ability to generate SQL queries;
• several options for data export.

Access security is ensured by DBMS tools, which have developed mechanisms for delimiting rights.