Data Sources under Linux

Just as under Windows, the connection of the databases via ODBC is also available under Linux. However, since only a few free ODBC drivers are available under Linux, also the direct connection of the most common databases was implemented. The several database connections must first be installed under Linux. ODBC and the direct connections to MySQL, PostgreSQL and TDS are provided directly by the package management software of Linux. For installation, the scripts in the Plugins folder of Setup_S4A_Linux.zip can be executed.

ODBC under Linux

Under Linux, a connection to a database can be established via ODBC. Since a free ODBC driver for connection to a Microsoft SQL Server is available, the procedure is explained by taking this connection as an example.

This guide describes how a connection from a Linux to a MS SQL Server can be established via ODBC. One computer is running the SQL4automation Connector under Ubuntu, the other computer a Microsoft SQL Server on a Windows platform. For the connection, the drivers FreeTDS (www.freetds.org) and UnixODBC (www.unixodbc.org) are used.

Configure MS SQL-Server for Remote Access

With a freshly installed Microsoft SQL Server, remote access is disabled by default. With the Microsoft SQL Server Management Studio, remote access can be enabled.

Right-clicking on the server and selecting properties opens the settings window.

Under connections, the checkbox allow remote connections to this server can be selected.

Also, TCP/IP option must be activated under the settings in the SQL Server Configuration Manager:

Testing the Connection to the Port of the MS SQL Server

Testing whether the port of the MS SQL Server is available first can be done with the Linux computer. As in Windows, this is done with telnet.

telnet <IP-Address> <Port number>
Example: telnet 192.168.25.1 1433

Installing Tools

If a connection to this port cannot be established, the corresponding port must be opened in the firewall settings.

The tools FreeTDS and UnixODBC must be installed.

sudo apt-get install freetds-dev tdsodbc unixodbc unixodbc-dev

…or simply execute the script PluginODBC-inst in the Plugins folder of the file Setup_S4A_Linux.zip.

The settings of the connection must be adjusted in two files. These two files are odbc.ini and odbcinst.ini. In our example, the two files are located in the path /etc. The files can be opened with the pico text editor. Example: sudo pico odbc.ini

odbcinst.ini

In the odbcinst.ini, the path of the ODBC driver is specified.

[<Treiberbezeichnung>]
Description = <TreiberDescription>
Driver = <Pfad des Treibers>

Beispiel:

[FreeTDS]
Description = FreeTDS
Driver = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so

odbc.ini

Die odbc.ini describes the connection to the MS SQL Server

[<Verbindungsbezeichnung>]
description = <VerbindungsDescription>
Driver = <Treiberbezeichnung aus odbcinst.ini>
Server = <IP-Adresse des MS SQL Servers>
Port = <Post des MS SQL Servers>
Database = <Datenbankbezeichnung>

Beispiel:

[SQL4automation]
description = "Verbindung zu MS SQL Server SQL4automation"
Driver = FreeTDS
Server = 192.168.25.1
Port = 1433
Database = SQL4A

Testing the ODBC Connection

To test whether the tools have been installed correctly and the ODBC connection has been configured correctly, the command isql is executed.

isql -v <Verbindungsbezeichnung> <Benutzername MS SQL Server Benutzer> <Passwort MS SQL Server>

Beispiel:

isql -v SQL4automation root sql4automation

It should be possible to successfully establish the connection:

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

Selecting the ODBC Connection in the SQL4automation Connector

In the link settings of the SQL4automation Connector, the ODBC connections entered into the odbc.ini can be selected. As may be the case, the display must be reloaded first by clicking the "Update" button.

Direct Database Connection under Linux

Under Linux, the SQL4automation Connector provides direct database connection for the databases MySQL, SQLite, IBM DB2, Borland InterBase, Oracle OCI and Sybase Adaptive Server TDS. However, the database drivers must be installed first with the package management software of Linux. The respective script can be executed in the folder Plugins of Setup_S4A_Linux.zip.

Creating a new direct Database Connection under Linux

Under Linux, DB Admin can be selected in the link settings.

By clicking "Add", a new database connection can be created, clicking "Rename" allows changing its name.

The following entries are necessary:

Server: IP of the database server, if the server is located on the same PC as the Connector: 127.0.0.1. For SQLite databases, the path to the database must be entered.

Database: Name of the database, with SQLite databases, here the name of the database is entered.

User: User name

Password: Password

Selecting the Database Connection in the SQL4automation Connector

In the link settings of the SQL4automation Connectors, the database connections created in DB Admin can be selected. In some cases the display must be reloaded first by clicking the "Update" button.