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

Process Purpose

The "DB to Local File" process is used to export data (select records) from a database table located in a specific Host server into a data file.

The remote database profile you create within MCL-Designer V4 is responsible for the direct communication between your MCL-Designer project and a remote database.

Once the project is loaded into a device, that same profile can be used to allow communication between the device and the remote database/host server, via MCL-Net. Therefore, make sure that the connection string of the selected profile includes an OLE DB provider that is compatible with the intended remote database. Ex: If you intend to use an Oracle database, select a remote database profile with the corresponding Oracle OleDB driver.

The exported data are NOT appended to the data file, its records are updated to match the records from the source database table.

When you add a process, you are required to define its settings. This occurs in the process's properties window which is displayed automatically after having added the "DB to Local File" process. This properties window is constituted by three tabs - "General", "Query" and "Advanced".

If any subsequent edition is required, double-click the process to open its properties window and enter the necessary modifications.

"General" tab

Fill in the following options:

Host Name & Data Source
Host Name	Select a host profile from the drop-down OR click to create a new host profile or edit an existing one. See To Create a Host Profile OR To Edit a Host Profile OR The <Default> Host Profile. As an alternative, you can use a variable to define the Host Name (use the "Variable Select" option that is accessed if you right-click this box). See The Use of a Variable to Define a Host Name.
Data Source	Select the database that includes the records to be exported from the drop-down OR click to add one. See To Add a Data Source to MCL-Designer V4. As an alternative, you can use a variable to define the Data Source (use the "Variable Select" option that is accessed if you right-click this box). See The Use of a Variable to Define a Data Source. Before using the “Variable Select” alternative, consider your choices in the “Remote Database” tab (see Remote Database tab in "Project Properties"): •If the “SQL Commands option” is set to “2. Server (Save SQL statement into MQD file for server)", the “Variable Select” option is NOT applicable/supported.
Select … From …
Table Name	Select the table that includes the records to export from the drop-down. If needed, click to open the "Join Tables" window. See To Join Tables. As an alternative, you can use a variable to define the Table Name (use the "Variable Select" option that is accessed if you right-click this box). See The Use of a Variable to Define a Data Source.
Local File	Select the data file that will receive the exported records from the drop-down. If needed, click to edit the selected data file. See Editing a Data File.
Max. Record	Define the maximum number of records to select and export. The maximum number of records is 999. If you intend to export more records, enter "0" (<All>). This will override the maximum number of records.
Local Field Name column	Select the data file fields that will receive the records from the drop-down.
DB Field Name or Value(s) column	Define the table field name or value to export. Either define a fixed name/value OR click and select a variable with that value. You can export records with up to 80 values at once. See Variable Usage.

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

Click Here for an Example

Continue to the "Query" tab.

"Query" tab

Proceed as follows:

Where
Where Clause	Click to create the appropriate query. See To Use the Query Assistant.You can create up to 80 Where conditions.
Order By
DB Filed Name	Define the result order by selecting the fields of the selected table from the drop-down.

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

Proceed to the "Advanced" tab.

"Advanced" tab

SQL request name (Must be unique)
Name	Define a unique name for the SQL request. One is always suggested.
Store Number of Record(s) in Variable
Variable	Click to select the variable that will receive the number of selected records. See To Select/Create a Variable.
Specific Record Formating
Record Format	Define the mask of the selected record.
If … Go to
No Record	Select a target location from the drop-down or list, in case of "No record" found. See Detail of a window below. When defining a screen as a target destination (ex: via a “Go to” process), you CANNOT use variables to specify the name of that target screen. You must select the intended screen from the available drop-down/list.
DB Error	Select a target location from the drop-down or list, if an "ODBC Error" occurs. See Detail of a window below. When defining a screen as a target destination (ex: via a “Go to” process), you CANNOT use variables to specify the name of that target screen. You must select the intended screen from the available drop-down/list.
Comm Error	Select a target location from the drop-down or list, in case there is a communications error. See Detail of a window below. When defining a screen as a target destination (ex: via a “Go to” process), you CANNOT use variables to specify the name of that target screen. You must select the intended screen from the available drop-down/list.

Detail of a window:

"S:Menu" is a screen included in the same program as the process.

"R:Routine_1" is a routine included in the same program as the process.

If required, use the icons on the top of the properties window:

Click it to set up a time out. See Time Out.

Click it to create a new data file. See Creating a Data File.

Click it to edit a data file. See Editing a Data File.

use it to test SQL scripts. See To Test SQL Scripts.

Use the icon to attach any relevant notes to this process. Click it and enter your notes in the resulting text box. These notes will be displayed in the corresponding "Actions" tab or "Process" window (in the "Notes" field) and in the "Developer Report".

After filling in the required options, click to conclude or to abort the operation.

The added process is displayed in the corresponding "Actions" tab or "Process" window.

You can use relative paths to refer the file(s) you want to use in your project. See Working with Aliases.

If you want to use a label as a target destination, you can use the "Auto-Label" mechanism. This alternative to the "Set Label" process allows you to create a label in the properties window of a process - specifically, in the fields used to define target destinations (ex: the "If Error..." type fields). See To Automatically Create a Label.

Use the right-click in MCL-Designer's input boxes to access some related options as well as the general "Cut", "Copy"; "Paste"; "Search" actions (active/inactive according to the current context).

Ex: If you right-click the "Variable" input box (included in a "Conversion's" properties window), you are provided with general editing/search actions and other more specific options such as "Variable Select" (see "Variable Select"); "Variable Insert" (see "Variable Insert"); "Insert Special Character" (see To Insert Special Characters into a Control's Text Input Field) and "Localization Select" (see Localization List).

If you right-click another input box, it may provide other possibilities.