SQL Wizard
Creating the Configuration File
The SQL Wizard is a tool for creating the configuration file for the time-driven custom classes DefaultSQLCron, DefaultFileSQLCron and CrossSQLCron (multiple databases).
We will use the existing database tables comm_partnerelations, comm_auths, comm_auths_additional and the default database alias (in this case hub) to create a configuration file with the SQL Wizard. For each record in comm_partnerrelations, there are n records in comm_auths, which in turn can reference to n records in comm_auths_additional. Following the Entity Relationship Diagram.
Creating Profile with Input Agent 'Business Connector'
(1) The database alias to be used. Note: System constants are available.
(2) Opens the config assistant. All other settings you can be used as in the screenshot. Details can be found in section Business Connector (Input Agent cron).
Opening Config Assistant
General Data
For the example here, just use the default settings.
(2.1) Path to the configuration file (generated automatically).
(2.2) Select/view/edit configuration file .
(2.3) Timeout for the SQL statements.
(2.4) Create a new job for each row of the select.0 statement. See parameter result.split.
(2.5) Use Base64 for columns of type BLOB/CLOB. See parameter encode_blob.
(2.6) Write out raw content instead of CSV. See parameter use_raw_content.
(2.7) Replacement values for empty first result (select.0 statement) . The values have to be delimited by semicolons (;). See parameter empty.<n>.
Selecting Tables
(3) The database alias is automatically taken over from the previous dialogue and cannot be edited in this example. Note: If the option Execute SQL across multiple database sources (= CrossSQLCron) is selected above, the alias is selectable.
(4) Select the database table comm_partnerrelations.
(5) Create a new entry via the context menu.
(6) Creates the SQL command (all preselected columns can be used).
(7) The created SQL command.
(8) Use the created SQL command.
(9) Select the database table comm_auths.
(10) Create a new entry via the context menu.
(11) Use the context menu on column id_partner.
(12) Select the reference to table comm_partnerrelations and then the column id_partner.
(13) Create the SQL command.
(14) The created SQL command.
(15) Use the created SQL command.
(16) Select the database table comm_auths_additional.
(17) Create a new entry via the context menu.
(18) Use the context menu on column id_partner.
(19) Select the reference to table comm_auths and then the column id_partner.
(20) Use the context menu on column id_auth.
(19) Select the reference to table comm_auths and then the column id_auth.
(22) Create the SQL command.
(23) The created SQL command.
(24) Use the created SQL command.
(25) Now you can use all created entries, which then automatically creates the configuration file. The config assistant is then closed and you are back in the Input Agent.
(26) Via this icon you can view the created configuration file. See the following screenshot. Note: Details about the parameters in the file are described in the documentations of the above mentioned classes.
