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

Process Purpose

The Select Record process is used to retrieve a record from tables of a remote database in a specific Host server.

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 driver Microsoft OLE DB for Oracle Provider.

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 Select Record process. In this case, the properties window includes 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 list or click to create a new one. See How to Create a Host Profile or The <Default> Host Profile.
Data Source	Select the source Database from the drop-down list or click to add one. See To Add a Data Source to MCL-Designer V4.
Select … From …
Table Name	Use the drop-down list to define the table to select data from. If needed, click to open the "Join Tables" window. See To Join Tables.
DB Field Name column	Select the table's field name(s) from the drop-down list or click and select a variable with that value. See Variable Definition.
Store in Variable column	Click to select the variable that will receive the data from the table field. See To Select/Create a Variable.

Example

The records being selected are included in the "Inventory" database, more specifically in the "Locations" table (in its "Warehouse", "Location", "ItemNumber" and "Quantity" fields). The retrieved data is stored the corresponding variables ("L_Warehouse", "L_Location", "L_ItemNumber" and "L_Qty").

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 "Query" tab.

Query tab

Fill in the following options:

Where
Where Clause	Click to create the appropriate query.You can create up to 80 Where Conditions. See To use the Query Assistant.
Order By
DB Field Name	Define the result order by selecting the fields of the selected 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.

Click the "Advanced" tab to continue.

Advanced tab

Fill in the following options:

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 records selected. See To Select/Create a Variable.
Generate Result File
Generate Result File on Server	Check this option to enable the creation of a result file and then define the next set of settings.
File Name	Define name and path for the result file.
Record Format	Define the mask of a selected record.
Field Separator	Select the appropriate field separator from the drop-down list.
Max. Record(s)	Define the maximum number of records for the result file.
Add Record Number Field	Check this option to enable the creation of a field with the record number.
If … Go to
No Record	Select a target location from the drop-down or list, in case a "No Record" occurs. See Detail of a window below.
DB Error	Select a target location from the drop-down or list, in case an "ODBC Error" occurs. See Detail of a window below.
Comm Error	Select a target location from the drop-down or list, in case a "Comm Error" occurs. See Detail of a window below.

Detail of 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 setup a time out. See To Define a Time Out.

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.

Use the right click in MCL-Designer's input boxes to access certain features regarding the input box's option as well as general actions such as "Copy"; "Paste"; "Search".

Ex: If you right-click the "Variable" input box (included in a "Conversion's" properties window), you are provided with the "Search..."and "Variable Select" options.

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