SQL Transmitter

Use Cases

Sometimes it is necessary to export product data directly to SQL tables. This is now possible with the SQL Transmitter in the DynamicJSON Connector. In principle, all SQL databases that can be accessed via the PHP PDO driver are supported. The trick is to install the PDO driver in PHP.

PDO Treiber

The PHP PDO driver offers the possibility to provide different database drivers in PHP. The transmitter then communicates directly with the databases via these classes.

Info: https://www.php.net/manual/de/book.pdo.php

If the installation is successful, you should see the drivers in the PHP info of your system or in the selection list of the possible database drivers.

 

Configuration of the export

Nodemaps

To configure an export, you must create a flat data map. In our example, two nodemaps are created. One contains all products that are to be created or updated and the other contains all products that are to be deleted. This depends on the workflow status of the respective product. They differ from the JSON key, which is assembled dynamically. The transmitter later uses this key to know which action it should use to extract the data record.

 

Conditions are used to split the data for the subsequent SQL commands (DELETE / INSERT / UPDATE). To do this, we use a prefix (add / delete) in the key: e.g. add_4711_DE

 

Database columns

Once you have created the nodemaps, you can configure all the database columns that you want to fill. It is important that you also define columns that you will need later to find the data record (in the WHERE part of the SQL command). In our case sku and language. When deleting, of course, only the columns for finding the data record are necessary.

 

Special Export Types – BLOBs (Files/Images)

This is a special export type that only works in conjunction with the SQL database transmitter. The BLOB data type. This loads image or file content into a database BLOB column based on the URL or image path. It is important that the URL or path is preceded by the “blob://” prefix. During the transfer, the file content is then loaded and entered in the BLOB column.

The export can be created using a MAM loop plugin with format strings, for example.

 

 

Configuration of the Transmitter

In addition to the database connection with database name, DNS of the server, user name and password, the behaviour for the export tables is also defined in the transmitter. The data records are routed to the respective SQL commands depending on the prefix (add* and delete*).

The unique column names for the WHERE clause are also defined here, separated by commas. These columns must also be present in the export!!!

 

 

Exports

The export takes place via a job that uses this transmitter. If the export was not successful, the respective SQL errors are displayed.

 

If it was successful, information is also displayed in the Job Pilot.

 

OWL Template – Getting started

If you want to configure a transfer via SQL, you are welcome to start with our OWL template. There you will find a station with a pre-configured transmitter, data map and jobs.

Was this article helpful?

0 0

How can we improve this article?

Please submit the reason for your vote so that we can improve the article.

Name

Email

Submit