FIREDAC CONNECTIONS IN VERSION 12

QDas-133 v-0.10 1

CONTENTS

1 Preface .......................................................................................................................... 3

2 Design of FireDAC.INI .................................................................................................. 3

3 Uninstall ........................................................................................................................ 3

4 How the installation accesses the FireDAC.ini .......................................................... 3

5 Basic architecture ........................................................................................................ 5

5.1 Local installations/server installations ............................................................................. 5

5.2 Client installations .......................................................................................................... 5

6 How to establish new connections ............................................................................. 7

6.1 How to use the dialogue box .......................................................................................... 8

6.1.1 How to establish a new Access connection ................................................................. 9

6.1.2 How to establish a new SQL connection ................................................................... 10

6.1.3 How to create a new Oracle database connection .................................................... 11

7 How to use new database connections .................................................................... 13

7.1 Specific changes in a FireDAC connection ................................................................... 13

7.1.1 Data database ........................................................................................................... 13

7.1.2 Text/configuration/licence databases ........................................................................ 14

7.2 Global changes in a FireDAC connection ..................................................................... 15

8 How to manage database connections ..................................................................... 16

8.1 Install ”FD Connection Tool“ manually .......................................................................... 16

8.2 Operate “FD Connection Tool“ ..................................................................................... 18

QDas-133 v-0.10 1

1 PREFACE

The software since version 12 applies FireDAC connections to access databases. This document shows how to establish, change and apply them to networks.

2 DESIGN OF FIREDAC.INI

Compared to the former application of UDL files, a single file now manages all database connections.

There are three different types of FireDAC connections:

• FDDBConnStr_Text

Database connection to text databases

• FDDBConnStr_Data

Database connection to data databases

• FDDBConnStr_Conf

Database connection to configuration and licence databases

3 UNINSTALL

When you uninstall a client, the system only deletes product INIs. The FireDAC connections are still part of the FireDAC.ini.

4 HOW THE INSTALLATION ACCESSES THE FIREDAC.INI

Each product or installation combination has its own %Product%.ini. In case of client installations, each client has its own %Windows_PC_NAME%_%Product%.ini. Product INI files only contain the name of the FireDAC connection and no longer the entire path. This path is saved to the respective connection in the FireDAC.ini. The entries described in this chapter refer to the entries in the product INI files.

QDas-133 v-0.10 1

The previous INI entries up to version 11 were sys_%dbtype% given as a path to an Access database (*.mdb) and sys_%dbtype%_connection given as a path to an UDL file. When the system found a sys_%dbtype%_connection with an UDL file, it ignored the sys_%dbtype%.

What is new are the entries of the FireDAC connections sys_%dbtype%_FDDBConn. They precede the previous entries (*.udl or *.mdb). The paths sys_%dbtype% and sys_%dbtype%_connection registered by default only serve your guidance. The sys_%dbtype%_FDDBConn entry links to the respective connection in the FireDAC.ini.

The information given under FireDACConnectionFile include the FireDAC connections and the path to the FireDAC.ini.

QDas-133 v-0.10 1

5 BASIC ARCHITECTURE

The applied FireDAC connections depend on the type of installation.

5.1 Local installations/server installations

Local installations and server installations on the server use the first FireDAC connections (FDDBConnStr_%dbtype%_%no%).

• Text database

FDDBConnStr_Text_001=Name=QDAS_TEXT_001

• Configuration database

FDDBConnStr_Conf_001=Name=QDAS_CONF_001

• Licence database Since the licence database is considered to be a configuration database, it is assigned to a consecutive number.

FDDBConnStr_Conf_002=Name=QDAS_LIC_001

Data databases The system always creates four data databases and are also assigned to a consecutive number. These four databases are the measured value database, the buffer database for CMM Reporting, the central database (referring to the measured value database QDAS_DATA.MDB by default) and the Monitoring database.

FDDBConnStr_Data_001=Name=QDAS_DATA_001

FDDBConnStr_Data_002=Name=QDAS_CMM_001

FDDBConnStr_Data_003=Name=QDAS_CENTRAL_001

FDDBConnStr_Data_004=Name=QDAS_MONI_001

5.2 Client installations

The connections stored in the product INI files of a standard client installation on the server refer to a local drive.

FDDBConnStr_Text_001=Name=QDAS_TEXT_001;DriverID=MSAcc;Database=C:\Q-DAS\Share\PLANT\DEFAULT\DATABASES\QDAS_TEXT.MDB;

Clients are not able to use local server paths. This is the reason why the initial setup of a client creates entries with UNC paths. The system increments the consecutive number that is part of the description (FDDBConnStr_%dbtype%_%no%).

QDas-133 v-0.10 1

In addition, the initial setup stores an individual %Windows_PC_NAME%_%Product%.ini on the server for each product or installation combination. A server-client system thus applies the newly established FireDAC connections as the client’s main connections. As a result, there will be seven additional FireDAC connections available after the first client installation.

The installation of additional clients stores an individual %Windows_PC_NAME%_%Product%.ini on the server for each product or installation combination. The system also checks whether any existing FireDAC connections are still applicable. If this is not the case, it creates new FireDAC connections for this new client.

In case your client accesses a local text database, the system also creates the respective FireDAC connection in the FireDAC.ini on the server. However, the entry is not based on an UNC path but contains the local path of the client.

The following example shows a client installed with a local text database and a client accessing the text database on the server. Since the installation of the second client cannot access the text database of the first client (local path), the system created an additional FireDAC connection including an UNC path. 1 – FireDAC connections on the server 2 – FireDAC connections of client 1 3 – additional FireDAC connection of client 2

QDas-133 v-0.10 1

6 HOW TO ESTABLISH NEW CONNECTIONS

You use the graphical user interface of any Q-DAS version 12 product (e.g. qs-STAT) to establish a FireDAC connection. Depending on the database type you apply, there are two ways to establish a connection.

• Data databases: File | Configuration | Databases | Q-DAS database The “Database connection“ dialogue box opens directly.

• Text/configuration/licence databases: To open the dialogue box shown above, select File | Configuration | Databases | Options. The following window opens.

QDas-133 v-0.10 1

Select “Copy test and config. database”. Since each type of database has a different structure, there are separate settings for each database. Click the “FD“ button of the respective database. The “Database connection“ dialogue box opens.

The “Copy text and config. database“ also provides you with the option to transfer databases to SQL or Oracle. A different document describes the respective approach.

You cannot change the FireDAC connections of the text/configuration/licence database when the software is open. Find more information in the How to establish new connections chapter.

6.1 How to use the dialogue box

The “Database connection“ dialogue box provides you with the following information.

QDas-133 v-0.10 1

Database connection name Depending on how you opened this dialogue box, this drop-down menu only shows the databases of a single type. For example, when you opened the dialogue box by clicking the “FD” button under test databases, it will only show text databases.

You may only add database connections of the same type. Please consider using the associated options to open the dialogue box.

Database driver Shows the database driver the FireDAC connection is based on.

Overview This field gives an overview of the created FireDAC connection.

New Use the “New“ button to add a new FireDAC connection.

Rename Use this button to change the name of a connection. The Q-DAS graphical user interface shows the connection name you choose and the FireDAC.ini saves it.

Connection parameters This button opens the dialogue box to change the connection parameters of the FireDAC connection.

Test Use this button to test your FireDAC connection.

The establish a permanent FireDAC connection, you have to confirm the settings in the “New database connection“ dialogue box and in the “Database connection“ dialogue box by clicking “OK“. When you create a text/configuration/licence database, you also have to save the settings you adjust under “Copy text and config. database“.

6.1.1 How to establish a new Access connection

Click on “NEW“ to open the following dialogue box.

QDas-133 v-0.10 1

Enter a name. The software specifies the MS Access database driver automatically. Click “OK“ to define the connection parameters.

When you use Access databases, you do not have to enter a name and password. Select your database and confirm your choice by clicking ”OK“.

6.1.2 How to establish a new SQL connection

The following dialogue box opens after clicking “NEW“.

Enter a name. Select MS SQL Server as a database driver and click “OK” to define the connection parameters.

QDas-133 v-0.10 1

Name/Password: When you apply a SQL authentication to access your SQL server, enter the name and password. The system saves the coded password to the FireDAC.ini.

Win.-Auth.!!: Check this box when you want to apply your Windows authentication (direct Windows login or active directory login) to access your SQL server. You do not have to enter name and password.

Server: Select your SQL server. You do not have to specify the instance for some SQL servers, e.g. MS SQL Server Express Edition

Database: Enter the name of the SQL database.

6.1.3 How to create a new Oracle database connection

Click on “NEW” to open the following dialogue box.

Select any name. Choose “Oracle” under database driver and click “OK” to open the dialogue specifying the connection parameters.

QDas-133 v-0.10 1

Name/Password: Enter name and password when you selected a password login for the server authentication.

Service!!!:: Select the service including the Oracle server instance in the TNSNAMES.ORA file.

Reg. Mode:: Select “normal”. It is not recommended to access the server as “admin” or the like to work with Q-DAS products.

QDas-133 v-0.10 1

7 HOW TO USE NEW DATABASE CONNECTIONS

There are two ways to establish new FireDAC connections.

• Data databases: You may change the connection to a data database in the graphical user interface of the respective Q-DAS software product in version 12, e.g. qs-STAT, or change the associated entry in the INI file.

• Text/configuration/licence databases: Since the running Q-DAS software program uses these types of databases, you cannot change them in the graphical user interface. You may only change the respective entries in the INI files.

7.1 Specific changes in a FireDAC connection

When you use a %product%.ini to establish a FireDAC connection, this connection only applies to a single computer and a single product. Even when you use the Q-DAS graphical user interface to establish a FireDAC connection, it only applies to the computer and the product you used to establish the connection.

See the Global changes in a FireDAC connection chapter to find out how to embed and distribute several FireDAC connections (recommended approach when applying several computers or Q-DAS products).

7.1.1 Data database

When you establish a FireDAC connection in the graphical user interface of a Q-DAS software product, the software adjusts the %product%.ini accordingly. To select a new connection, start version 12 of any Q-DAS software product, e.g. qs-STAT. Choose File | Configuration | Databases | Q-DAS database. The ”Database connection“ dialogue box opens.

Select the respective database connection name and confirm your choice by clicking “OK”.

QDas-133 v-0.10 1

7.1.2 Text/configuration/licence databases

The only way to establish a FireDAC connection for text/configuration/licence databases is to adjust the INI file accordingly.

Open the respective %poduct%.ini or, in case of a client installation the associated %Windows_PC_Name%_%product%.ini and change the name of the FireDAC connection name.

The following example shows the FireDAC connection for a text database called ”QDAS_Text_003“. The system added the ”FDDBConnStr_Text_003“ entry automatically to the FireDAC.ini but you want to change it now for O-QIS. You thus replace the ”sys_lang_FDDBConn“ entry in the product INI with the name of the new FireDAC connection.

The new entry for O-QIS looks as follows.

QDas-133 v-0.10 1

7.2 Global changes in a FireDAC connection

In most cases, we recommend you apply MS SQL or Oracle databases instead of Access databases. When you change over to MS SQL or Oracle databases, you have to establish the FireDAC connection and adapt the FireDAC.ini manually.

First you establish the FireDAC connections based on the settings provided by different dialogue boxes as described in How to establish new connections.

When you create a data database, please consider that you only establish the new FireDAC connections; you must not replace the existing connection at first. After establishing new FireDAC connections, you thus select the previous FireDAC connection before you close the ”Database connection“ dialogue box. Confirm your choice by clicking “OK“.

The following example is based on a text database and shows how to change over to a SQL database. The FireDAC.ini includes 4 entries for the text database.

QDAS_TEXT_001: The system created this entry during server installation. QDAS_TEXT_002: The system created this entry during the installation of client 1 (local text database). QDAS_TEXT_003: The system created this entry during the installation of client 2 (UNC path on the server). QDAS_SQL_TEXT: Manually established connection

We want the server currently using the first connection and all clients using the server paths (connection 3) to apply the SQL text database. We keep the local Access database for client 1. Copy the content of the SQL FireDAC connection for the server and client 2.

QDas-133 v-0.10 1

8 HOW TO MANAGE DATABASE CONNECTIONS

The “FD Connection Tool“ helps you manage database connections. When you start the tool, it reads the content of the FireDAC.INI and illustrates it on a graphical user interface. You may now adjust or test existing database connections or create new ones.

Since there are different database drivers available, there is a 32-bit variant and a 64-bit variant available. Both of them are available at https://www.q-das.de/en/service/software-downloads/. By starting a specific variant of this application without having the required database drivers installed, you may adapt the database connections or create new ones; however, you cannot test the database connection.

Compared to a local installation, a server installation generally installs the 32-bit variant and the 64-bit variant. When you use a server installation and store the “FD Connection Tool” manually, please consider copying both variants to the respective directories and create a link to both variants. The “FD Connection Tool” is not intended for the application on a client.

8.1 Install ”FD Connection Tool“ manually

Since version 12.0.2.1, the setup automatically creates shortcuts in the Windows start menu including the respective parameters while installing Q-DAS.

Applying an installation package that does not include the “FE Connection Tool”, you may add it manually. The application does not depend at all from any minor release. This is why the application is installed in the same directory as the minor release sub-folders and the Q-DAS Launcher application (QDAS_PRE_START.exe).

You just have to copy the “FD Connection Tool” application to this very folder.

To open this application manually, you enter the path of the application and add the path of the “FireDAC.INI“. We recommend you create a shortcut with the respective call parameters and save it to the same directory as the “FireDAC.INI“.

The “Target” field in the “Properties” windows shows the path of the program (displayed in blue) and the oath of the INI file (displayed in green).

QDas-133 v-0.10 1

The “Run” field shows the directory storing the “FD Connection Tool“ application.

QDas-133 v-0.10 1

8.2 Operate “FD Connection Tool“

• The first line of the application shows the path of the applied FireDAC.INI.

• “DB type” combo box

There are three different database types available combined in groups.

• Text: groups all text databases

• Config: includes all connections to configuration databases or licence databases

• Data: groups all connections to data databases

• ”New“ button

Clicking the “new” button executes the action you selected by activating the respective radio button.

• UDL

Select “UDL” to create a new FireDAC connection based on an existing UDL file.

QDas-133 v-0.10 1

• FD Connection Dialog

When you activate the “FD Connection Dialog” radio button and click “New”, you are prompted to enter a name for a new FireDAC connection. The application now opens an input screen to specify the database login parameters.

When the “Connection test not successful“ message appears after you clicked the “Test Connection” button, you either started the wrong variant of the “FD Connection Tool“ or the database is not available.

• ”Open“ button

Clicking the “new” button executes the action you selected by activating the respective radio button.

• UDL

Select “UDL”, click “Open” and select a UDL to overwrite the currently selected database connection by the UDL settings. The changes are indicated in yellow.

• FD Connection Dialog

When you activate the “FD Connection Dialog” radio button and click “New”, you may adjust the existing FireDAC connection in the “Properties” window”.

QDas-133 v-0.10 1

• ”OK“ button

Click “OK” to save all changes. New entries are displayed in green, modified entries are displayed in yellow.

• ”Driver“ button

The name of the button always indicates the bit variant of the application you started. It helps you check the drivers. Click on the button to list all database drivers. The list contains the drivers you installed on the computer and you used to start the application.

• ”Cancel“ button

When you close the application by clicking this button, the software will not save any changes.