How to Work with Processes > Working with the Remote Database Processes Group

Process Purpose

The Remote Database group of processes enables the device to directly exchange data with an SQL database on the host server. Most used SQL transactions can be defined with dedicated processes for SELECT, UPDATE, INSERT and DELETE records. It is also possible to define a free SQL statement that allows you to call stored procedures, functions, complex chained SELECTs, etc.

MCL-Designer V4 establishes a direct connection to the database and extracts the table's metadata. This allows you to easily build the transactions by mapping the table fields to the variables.

To have the device connecting to the database, MCL-Net + ODBC Bridge are required in the host server.

You can create a remote database profile that mirrors the structure of the actual remote database. These profiles, located in the "Remote Database" module (see Navigating the Remote Database Module), are available to the processes that require remote databases.

There are five recurring icons which appear in these processes:

Click this icon to create a new data file. Go to Creating a Data File to see how to fill in the several options available.

Click this icon to view the content of a selected remote database/remote database profile table. See To View the Content of a Table.

This icon is used to set up a time out. It is located on the upper right corner of the process properties window. See Time Out.

Click it to test SQL scripts. See To Test SQL Scripts below.

Use this icon to view and create remote database profiles. See Creating a Remote Database Profile.

The "<Default>" Host Profile

The "<Default>" host profile option that is available in Host Services processes refers to the server's address defined in MCL-Mobility Platform (set in the "Server" sub-module and associated to a Device Group within your MCL- Mobility Platform site).

Refer to the MCL-Mobility Platform User Guide for more details.

To Test SQL Scripts

If necessary, you can test the SQL script present in certain processes. To do so, click after defining all the specific settings for the current process. This opens a message box:

Select to proceed. This message appears because the process being used affects the database (it updates, inserts or deletes data).

Depending on the tested SQL command, the next window will display the results (ex: Insert Records).

Clicking opens a new window where you can insert direct values into the variables being used within the current process. Operationally speaking, you are entering values in the selected database table.

Once you enter the required values in the "Set Variable Values" window, click .

Since the process used affects the database, there is another message box:

Click to conclude the testing or to cancel the operation.

If you click , you execute the defined SQL command.

Ex: This is a view of both tabs when testing the "Remote Database Insert Record" process:

To View the Content of a Table

Clicking provides access to the content of the available database tables.

Proceed as follows:

View Content of Table(s)
Data Source	Select the source database.
Table Name	Select the table from the selected database.
View	Check one of the available options: Data - displays the content of the selected table. Structure - displays the structure of the selected table. See Detail of a Database Viewer Window below.
SQL:	Displays the SQL statement for the selected table (default statement= SELECT * FROM). If you replace the default option with another SQL statement, click the GO button to apply/generate the new statement.

To Use the Query Assistant

Use the Query Assistant to define a query in a data source table based on Where conditions.

The "Query" tab is present in most of the Remote Database processes.

Double-click the required row or click the corresponding .

And Or	These logical operands are only available if there is more than 1 Where clause for this query. Check “And” or “Or” depending on the query's purpose.
(... ...)	If needed,check the most adequate parenthesis for your Where clause. (The parenthesis determines the order in which the defined conditions are evaluated.)
Value/Field	Select the table field to be queried from the drop-down OR click to select a variable with that value.
Operand	Select a comparison operand from the drop-down OR click to select a variable with that value.
Num.	Check this option if the Where clause requires numeric values.
Text	Check this option if the Where clause requires text values.
Value/Field	Enter the intended value OR select a table field from the drop-down OR click to select a variable with the required value/table field. Depending on the selected comparison operand, there can be more or less "Value/Field" options available. Ex: The "EQUAL" operand only requires 1 "Value/Field" option. The "BETWEEN" operand calls for 2 "Value/Field" boxes.
Click to apply these options and return to the "Query" tab.

To Join Tables

If required, when a source database has more than one table, you can join the fields of the tables to create a joined select statement.

Click to open the “Select Tables” window, wherever it is available.

Ex: The remote database includes 3 tables called "Locations", "Warehouses" and “Items”.

The selected tables to be joined are "Locations" and “Items”.

The data from the "ItemNumber" field of the "Items" table will be joined with the data of the "ItemNumber" field of the "Locations" table".

Proceed as follows:

Select Tables
Available Table(s)	This box displays the available tables to be joined. The required ones must be moved to the box to the right ("Selected Table"). Use the editing icons to move the selected table to the other box.
Selected Table(s)	This box displays the tables to be used in the “Join Tables” action. Use the editing icons to move tables from one box to the other.
Array (table)
Field column	Select the field to be joined from one table.
with Field column	Select the field to be joined from the other table.

Use the editing icons to the right of the table to move the rows up and down and to delete or add more rows.

After filling in the required options, click to conclude and go to the "General" tab or click to abort the operation.

When using an external data source, MCL-Designer V4 creates a cache file which is updated every five minutes. If, for example, you define a data source that is likely to be edited outside the MCL-Designer V4 context in your project, take the 5 minute update period into account.

The Use of a Variable to Define a Host Name

You can also use a variable to identify the necessary host name instead of selecting the actual host name. This is particularly useful if you intend to alternate between a test and production environment, for development reasons. This alternative is available in Remote Database related processes.

Proceed as follows:

Step-by-step

1. Right-click the "Host Name" option and select the "Variable Select" option.

2. In the resulting window, select an existing variable or create one.

3. Click .

When assigning the variable, please make sure to use the format <hostname_profile> (Ex: "<Primary_Server>"):

The Use of a Variable to Define a Data Source

If required, you can use a variable to identify the necessary data source instead of selecting the actual data source name. This is particularly useful if you intend to alternate between a test and production environment, for development reasons. This alternative is available in Remote Database related processes.

Proceed as follows:

Step-by-step

1. Right-click the "Data Source" option and select the "Variable Select" option.

2. In the resulting window, select an existing variable or create one and click . This opens another window where you can establish the data source template.

3. Either select the source from the drop-down (the list refers to the existing remote database profiles in the "Remote Database" module) OR click to create a new remote database profile in your project. See Creating a Remote Database Profile.

4. Click .

5. If required, you can also define a variable in the related "Table Name" option instead of selecting a table from the drop-down (the list includes the tables from the previously defined data source that have been imported into the project):

a. Right-click the "Table Name" option and select "Variable Select" in the corresponding menu.

b. Select or create a variable in the resulting window and click . This opens the "Data Table Template" window.

c. Select a table from the drop-down (the list is composed of tables imported from the previously selected data source).

If the process in question allows for the joining of tables, this window will also have a to the right of the "Table Name" box to allow you to create a customized view of 2 or more tables (depending on the tables available). See To Join Tables.

d. Apply by clicking .

The Remote Database group includes the following processes:

----- Basic SQL

Working with Insert Record Process

Working with Select Record Process

Working with Update Record Process

Working with Delete Record(s) Process

----- SQL on File

Working with Local File to DB Process

Working with DB to Local File Process

Working with Update DB from File Process

----- Advanced SQL

Working with Free SQL Statement Process

Working with Execute SQL Script Process