How to Work with a Remote Database > Creating a Remote Database Profile

Overview

Using remote databases in a project requires a connection profile that defines the link (via OLE DB providers) between MCL-Designer and the remote database. All the created remote database profiles are displayed in the "Remote Database" module, in the "Select Data Source" window (the window is opened via the button, which is available in database related processes) and the "Database Connections" table displayed in the "Remote Database" tab of the project's properties window.

To Create a Remote Database Profile

Step-by-step

1. There are three ways to initiate the creation of a remote database profile:

a. In the "Remote Database" module, right-click the empty space and select "Add Database".

b. In the "Remote Database" tab of the Project Properties window, click (located to the right of the table).

c. Click wherever available (usually in the properties window of remote database related processes) and, then, (located to the right of the table).

Whatever the initial step, it opens a "Remote Database Connection properties" window. The "General" tab is open, by default.

2. In the "Name" box, enter the name for the new database profile (ex: Database2).

3. Click to create a connection string. The resulting OS "Data Link Properties" window opens with the "Connection" tab.

By default, the selected provider (in the "Provider" tab) is "Microsoft OLE DB Provider ODBC Drivers".

We recommend you select an OLE DB provider that is compatible with the type of remote database you want to use to ensure a correct communication between the application and the selected remote database.

If the OLE DB Provider's list does not include the provider you require, install the driver provided by the corresponding manufacturer (ex: for an Oracle based database, download/install the corresponding “Oracle Provider for OLE DB” provided by ORACLE).

4. Select the appropriate option and fill in the related tabs accordingly.

Depending on the selected provider, the configurations in the "Connection", "Advanced" and "All" tabs will be different. Some of the configurations are not mandatory and do NOT need to be filled in.

Check 2 examples of OLE DB Providers and their options.

Microsoft OLE DB Provider for SQL Server (to communicate with an SQL Remote Database)	Oracle Provider for OLE DB (to communicate with an Oracle Remote Database)

a. Select the “Microsoft OLE DB Provider for SQL Server” option and open the “Connection” tab.	a. Select “Oracle Provider for OLE DB” and open the “Connection” tab.

b. In parameter 1, select the server name from the drop-down or enter it. c. If the defined server requires authentication to log in, check the most appropriate authentication procedure in parameter 2 and fill in the necessary corresponding fields. d. Use parameter 3 to select a specific database included in the defined server. Check/fill in the appropriate options. e. If you want to test the connection string right away, click . f. The other 2 tabs provide advanced parameters and are optional. Fill them at your own discretion OR conclude the connection string construction by clicking to return to the “General” tab.	b. In parameter 1, enter the server name. c. If the defined server requires authentication to log in, enter the necessary “user name” and “password” related information, in parameter 2. d. If you want to test the connection string right away, click . e. The other 2 tabs provide advanced parameters and are optional. Fill them at your own discretion OR conclude the connection string construction by clicking to return to the “General” tab.

5. Back in the “General” tab, the settings you defined in the OS "Data Link Properties" window are displayed in the "Connect String" box.

If required, test the connection string by clicking or continue to step 6.

6. If required, configure the remaining options in the “General” tab:

Option
Keyword Delimiter	Select the appropriate surround characters from the drop-down.
Comment
Enter any relevant information concerning the remote database.

7. Go to the "Advanced" tab.

8. If needed, configure the available options:

Integer Field Format
Add Sign	Check the appropriate sign for this format or maintain the default option "-only".
Real Field Format
Add Sign	Check the appropriate sign for this format or maintain the default option "-only".
Decimals	Maintain the default option ("2") or define more or less decimals. Enter the new value or use the arrows.
Data and Time Field Format
Custom Format	If you want to customize the date and time format, check this option. This will activate the "Date" and "Time" options: Date - select the appropriate format for the date from the drop-down. Time - select the appropriate format for the time from the drop-down.
Binary Field Format
Custom Format	If you want to attribute other values to the "True" and "False" conditions, check the "Custom Format". This activates the "True" and "False" boxes: True - Enter the required value (1 is the default value). False - Enter the required value (0 is the default value).

9. Click to apply your choices and open another window.

10. This window allows you to import the remote database's full or partial structure (only selected tables).

Depending on the selected option, conclude as follows:

Click this button to import all the tables included in the remote database (full import of the remote database's structure).

Use this button to import only the necessary tables (partial import of the remote database's structure):

1. Click .

2. In the resulting "Get Table(s) Structure" window, check the table(s) you want to import.

3. If you want all tables to be imported, check to select all available elements.

4. Click .

5. Click to confirm the previous selection and apply.

Click this button if you do NOT want to import the remote database's structure, at this time.

If, later on, you require the structure's import, see Getting a Remote Database's Structure.

The newly created database profile is, now, listed in:

•The "Select Data Source" window.

•The "Remote Database" tab of the "Project" properties window.

•The "Remote Database" module.

This operation does not get the remote database structure/tables. It just creates the link between MCL-Designer and the remote data source.

You can get the full remote database structure all at once or import one table at a time, see Getting a Remote Database's Structure.