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

Process Purpose

Local Databases are databases located in a device which can include one or more tables. MCL-Designer V4 allows you to create the necessary tables in the "Local Database" module (see How to Work with a Local Database).

The Local Database processes group includes specific operations that concern the tables contained in a local database.

There are some recurring icons which appear in these processes:

Click this icon to create a new table in the local database. Go to Creating a Table to see how to fill in the several options available.

Click this icon to view and/or edit the data included in the selected table. Go to Viewing/Editing a Table to see how to fill in the several options available.

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

To Test SQL Scripts

Depending on the process, after defining all the specific settings, press . This opens a message box:

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

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

If required, click to open a new window where you can insert direct values in the “Template” fields. These values are sent, directly, to the selected tables in the database.

If required, click to execute the defined SQL command.

Ex: “A1B123”; “1000”; “Test product” and “121231234” were directly keyed into the “Template” fields.

Click to conclude the value insertion. Since the process used affects the database, there is another message box:

Click to conclude the testing or to cancel the operation.

Ex: This is a view of both tabs when testing the SQL script concerning a "Local Database Insert Record" process:

To Use the Query Assistant

Use the Query Assistant to define a query in a local database table based on "where" conditions.

The "Query" tab is present in most of the Local 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

When a local database has more than one table/view, you can join the fields of the tables/views to create a joined "select statement".

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

Ex: There are two tables selected ("Warehouse" and "Table_1"). The fields displayed in the "Join" section ("code", "description", "qtd" and "localization" included in the "Warehouse" table and "code" and "description" included in the "Table_1" table) will merge.

Proceed as follows:

Select Tables
Available Tables	This box displays the available tables and views to be joined. The required ones must be moved to the box to the right ("Selected Table"). Use the editing icons or a drag-and-drop to move the selected table(s)/view(s) to the other box.
Selected Tables	This box displays the tables to be used in the “Join Tables” action. Use the editing icons or a drag-and-drop to move tables from one box to the other.
Join (table)
Field column	Select the field(s) to be joined from one table.
with Field column	Select the field(s) 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 return to the "General" tab or click to abort the operation.

The Local 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

-----Advanced SQL

Working with Free SQL Statement

Working with Attach/Detach

Working with Get Schema Version

-----SQL on File

Working with Local File to DB

Working with DB to Local File