Using the ODBC driver for ABAP on Linux
In this blog post, I would like to show you how you can access CDS view entities in an ABAP system using SQL via ODBC on Linux. In a previous blog post, I already described the same on Windows. In the current blog I will not repeat the description of the steps required on the ABAP backend to expose CDS entities. Instead, I will concentrate on the steps that need to be performed on the Linux system where the ODBC client application resides. If you are interested in the steps required in the ABAP backend, have a look at my previous blog. For the sake of simplicity, I will reuse the SAP BTP ABAP Environment test system and the exposed CDS entities for my previous blog post.
Prerequisites on Linux
A 64-bit Linux version is required because the ODBC driver for ABAP is a 64-bit ODBC driver. To define ODBC data source names (DSN) for the ODBC driver for ABAP on Linux, you need to install the unixODBC software package on your Linux system. This software package will also provide some simple command-line tools to test an ODBC connection and to execute SQL queries.
You may also want to install the LibreOffice software package to follow my simple application scenario.
Download and unpack ODBC driver on Linux
To install the “ODBC driver for ABAP”, you need to visit the SAP Support Portal and click on “Software Downloads”. After this you may need to log on and then you can search for components “ODBC DRIVER FOR ABAP 1.0” and “SAPCRYPTOLIB”. Make sure that you download the correct SAR files for the Linux operating system. In addition you may need to download “SAPCAR” to be able to unpack the SAP files (SAPCAR -xvf <SAR file>).
Choose a directory as your ODBC driver location (for example, /home/<myuser>/ODBC_driver_for_ABAP ) and unpack the SAR files in this directory. The directory will now contain at least the following files:
Create an ODBC data source and test the ODBC connection
The unixODBC ODBC driver manager package provides several configuration files that can be used for defining an ODBC driver and DSNs. You can list the file locations by executing the command “odbcinst -j”.
> odbcinst -j unixODBC 2.3.6 DRIVERS............: /etc/unixODBC/odbcinst.ini SYSTEM DATA SOURCES: /etc/unixODBC/odbc. [MYDSN]> FILE DATA SOURCES..: /etc/unixODBC/ODBCDataSources USER DATA SOURCES..: /home/myuser/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8
Here we will use only the file /home/myuser/.odbc.ini to define both the driver and a user-specific DSN. Creating a system-wide driver definition and DSN can be done in a similar way.
The .odbc.ini file needs to contain the DSN-specific connection parameters for the ODBC driver for ABAP as described in SAP Note 3076454 . To highlight the similarity to Windows, we will create a DSN for the same test system as used in my Windows blog post.
Create the file /home/<myuser>/.odbc.ini and insert the following content:
[MYDSN] ; this is a comment Driver=/home/<myuser>/ODBC_driver_for_ABAP/ODBC_driver_for_ABAP.so HOST=<hostname> PORT=443 CLIENT=100 LANGUAGE=EN SERVICEPATH=/sap/bc/sql/sql1/sap/S_PRIVILEGED TrustAll=true CryptoLibrary=/home/<myuser>/ODBC_driver_for_ABAP/libsapcrypto.so UidType=alias
Here we just included the driver definition in the .odbc.ini file using the driver key word. As connect user for our test system, we will use the user SQL_CLIENT_USER that was created as an alias user in the SAP BTP ABAP environment. It is also possible to store the UID/PWD properties in the .odbc.ini file. However, we do not recommend this for security reasons. Again, for simplicity sake, we did not bother to create a PSE file and used the “TrustAll=True” property.
After defining the DSN, you can immediately use the unixODBC tools isql or iusql to test the ODBC connection. Both tools work but since the ODBC driver for ABAP is a Unicode-only driver, I usually prefer to use the iusql tool. If everything is configured correctly, you should see the following:
> iusql MYDSN SQL_CLIENT_USER <password> -v +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
The ODBC connection has now been opened successfully and you can use the command “help” to display all exposed CDS entities and you can also execute first SQL queries.
SQL> help +----------+------------+-----------+-----------+--------+ | TABLE_CAT| TABLE_SCHEM| TABLE_NAME| TABLE_TYPE| REMARKS| +----------+------------+-----------+-----------+--------+ | | ZOrders | OrderItems| VIEW | ORDER ITEMS| | | ZOrders | Orders | VIEW | ORDERS | +----------+------------+-----------+-----------+--------+ SQLRowCount returns 2 2 rows fetched SQL> select * from ZOrders.Orders +-----------+-------------+ | Id | CreationDate| +-----------+-------------+ | 0000000001| 20210801 | | 0000000002| 20210802 | | 0000000003| 20210803 | +-----------+-------------+ SQLRowCount returns 3 3 rows fetched
Note: The tool isql can also be used to open a DSN-less connection by providing all connection properties directly in the command line, using a semicolon separated SQLDriverManager connection string (for unknown reasons the Unicode tool iusql does not support the “-k” option).
> isql -v -k "Driver=/home/<myuser>/ODBC_driver_for_ABAP/ODBC_driver_for_ABAP.so;HOST=<hostname>;PORT=443;CLIENT=100;LANGUAGE=EN;SERVICEPATH=/sap/bc/sql/sql1/sap/S_PRIVILEGED;TrustAll=true;CryptoLibrary=/home/<myuser>/ODBC_driver_for_ABAP/libsapcrypto.so;UidType=alias;UID=SQL_CLIENT_USER;PWD=<pwd>;" +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
LibreOffice as ODBC Client Application Example
In my previous blog post about the ODBC client and Windows, I have used MS Excel as an ODBC client application example. For this blog post about the ODBC client with Linux, we will use LibreOffice as an ODBC client application.
Start LibreOffice and choose either “Database” or “Calc Spreadsheet” as a starting point. In “Calc”, choose “File” -> “New” -> “Database” and select “ODBC” under the “Connect to an existing database” option.
After choosing “Next” you can enter the DSN that you inserted into your .odbc.ini file.
As a next step, you will be asked for a user name and you can test your connection after providing a password. Note that LibreOffice will not ask you for a user name and password if you included the UID/PWD properties in your .odbc.ini file.
Now you can choose “Finish” and LibreOffice will immediately present you all exposed CDS view entities. You can click on these entities to get a data preview:
LibreOffice also provides multiple wizards to create SQL queries on the exposed entities.
If you choose “Create Query in SQL View”, you can directly enter an SQL query string and retrieve the result. Please note that LibreOffice will automatically double-quote all identifiers in the SQL query and you therefore need to use the mixed case names for the CDS entities that you defined in the ABAP backend.
With this blog post, I hope I have shown you how easy it is to use the ODBC driver for ABAP on Linux. The configuration procedure on Linux and the used ODBC driver manager software differs from Windows, but after you have finished the configuration, the ODBC driver can be used in a very similar way.
Let me know if you have more questions and feel free to provide any feedback or comments.