I decided to write this blog after regularly seeing forum questions about accessing external databases from ABAP.
I will try my best to describe how to connect to non-SAP databases from ABAP, and also encourage you to try it yourself using an example I built with the SAP NetWeaver 7.01 ABAP Trial Version.
You can obtain the SAP NetWeaver 7.01 ABAP Trial Version from here (https://www.sdn.sap.com/downloads/netweaver/abap/disclaimer.html). If you need help installing the ABAP Trial Version refer to this blog (ABAP Trial Version for Newbies: Part 1 ‘ Download and installation of the Trial Version ‘) by Manfred Lutz.
DB MultiConnect Overview
The SAP NW Web Application Server ABAP (NW-ABAP) is built on a 3-tiered architecture: presentation, application and database. The database layer of a SAP system is a central database with a database management system (DBMS) and the database storage and content.
The work processes of the SAP application layer have a database interface that communicates with the database layer. When a SAP application server is started the default database connection to the central database of the SAP instance is opened.
This default database connection makes life very easy for the ABAP developer because there is always a database connection ready and available for them. They do not need to worry about opening, administrating and closing database connections as you do in many other programming languages.
NW-ABAP also has the capability to connect to DBMS’s other than the one that SAP is actually running on. SAP calls this “DB MultiConnect”, but I have also seen them write it as “Multiconnect”, “Multi-connect” and “Connect”.
For the remainder of this blog I will use the terms “SAP database” and “non-SAP database” to refer to these two different types of database connections.
Non-SAP databases may or may not be running on the same server as the SAP database. They may or may not be running on the same DBMS platform as the SAP database. In fact the non-SAP database could actually be physically located on the same database instance as the SAP database if you wanted – as in the example I describe later in this blog.
For DB MultiConnect to work the non-SAP database must be a DBMS that is supported by the SAP ABAP kernel. Currently this means DB2, Informix, MS SQL Server, Oracle or SAP DB.
Importantly, the running SAP kernel must also support the non-SAP DBMS. This means, for example, that to connect to a MS SQL Server non-SAP database the NW-ABAP application server must be running Windows. This is because the only SAP ABAP kernel that supports MS-SQL is the windows kernel. On the other hand if the non-SAP database is running on Oracle, DB2, SAP DB or Informix you will find that most SAP kernels support these DBMS platforms. It does not matter what OS platform the non-SAP database is running on, as long as the DBMS is supported by the SAP kernel you are running.
To access a non-SAP database we first need to establish technical connectivity from our NW-ABAP application server(s) to the Remote-DB.
Depending upon the specifics of the database platform this may require the installation of some DB Client Tools. For example in the case of a non-SAP database running on Oracle you would need to setup Oracle Net8 (SQL*Net V2) on the SAP application server so it can connect to the database.
There are a series of SAP notes that explain DB specific requirements. You can find a list of them in this section (http://help.sap.com/saphelp_nw04/helpdata/en/50/63d6b37bda7f4a92e2dec21b14ceee/frameset.htm) of the SAP Help.
Each SAP kernel includes a database specific library that it uses to connect to database management systems.
Many of you will be familiar with the process of downloading and installing ABAP kernel updates. The SAP kernel includes a set of database-independent components and a set of database-specific components. When downloading a kernel update from the SAP Service Marketplace you need to obtain the database-independent archive and the database-specific archive that relates to your database platform. Both these archives are extracted and combined to form the complete ABAP kernel.
When you need to connect to multiple databases you must ensure you include the relevant database-specific components for all the databases you connect to. For example if you are running your SAP application server on an Oracle database and you also want to connect to a MS SQL Server database you would need to combine the database-independent, Oracle database-specific and MS SQL Server database-specific components to form your complete SAP kernel. If you wanted to also connect to an Informix database then you need to include the Informix database-specific kernel components as well.
Once technical connectivity to the non-SAP database is in place the next step is to configure in the SAP system the specific configuration and authentication details of the non-SAP database. This is so the ABAP runtime environment knows how to access the database.
This information is held in table DBCON which can be maintained with transaction DBCO.
Again you will need to refer to the specific SAP Note for the database platform to determine the exact format of the details to place in this table. You may also need assistance from your database administrator to verify the specific details for your database.
Let’s do it!
Okay let’s try it for ourselves. As mentioned, this example was built using the SAP NetWeaver 7.01 ABAP Trial Version (NSP) but if you wish you could try this with any SAP NW-ABAP system. You will just need to modify the platform-specific parts of the example if your non-SAP database is not on the MaxDB platform.
In this example we are going to do a “loopback” connection to the same MaxDB database that NSP is running on. This saves us having to find another database to connect to and makes the example simpler to implement.
Create Sample Database
We are going to use the schema and tables that are delivered as part of the MaxDB SQL tutorial as our sample non-SAP database.
Firstly, if you have not already done so, you need the Database Manager and SQL Studio tools installed. Again Manfred Lutz has written [this blog | ABAP Trial Version for Newbies: Part 16 ‘ MaxDB: Database Manager and SQL Studio ‘] on how to install and use these tools.
To install the database objects run the Database Manager tool, and connect to the NSP database. Click the “Configuration” bar on the left-hand side menu and select “Load Tutorial”. This will execute a script that will create the tutorial objects in the database.
Now let’s have a look at the sample database. Start SQL Studio and connect to the database using username “MONA” and password “RED”.
Use the tree navigation to expand the “Tables” branch and then the “HOTEL” schema. You will see several objects in here including the view called “CUSTOMER_ADDR”. If you right-click on this view and select “Open Object Definition” you can see it is a join of the CUSTOMER and CITY tables.
In the SQL Dialog window to the right enter “*select * from hotel.customer_addr” and click the execute (!*) button. This will show you the contents of the CUSTOMER_ADDR view.
Connecting to MONA database
We do not need to add any database-specific components to our ABAP kernel because it already has the components for MaxDB included as part of the installation.
But we do need to configure the connections to the “MONA” schema so the ABAP runtime can successfully connect to it.
To do this we execute SAP transaction DBCO and add a line into the DBCON table for the new connection.
I have called my connection “MONA”. The DBMS is “ADA” for MaxDB (or SAP DB if you prefer). Enter “MONA” for the username and “RED” for the password. Yes it is case-sensitive.
Referring to the SAP Help link I mentioned earlier (http://help.sap.com/saphelp_nw04/helpdata/en/50/63d6b37bda7f4a92e2dec21b14ceee/frameset.htm) you will see that the connection information for SAP DB should be of the format <server_name>-<db_name>. So for us “localhost-NSP” will do the job. This points the new connection at the local NSP database and connects us as the user MONA.
If you are not using the NSP database as your non-SAP database you will need to adjust these settings to suit your specific requirements.
Accessing a non-SAP database
There are two database interfaces available to the ABAP programmer. These are called Open SQL and Native SQL. Open SQL provides a database-independent method for accessing the SAP database. This means ABAP developers do not need to make allowance for DBMS specific implementations of SQL but can code in the certain knowledge that their programmes will run on any SAP supported DBMS. The Open SQL interface handles all database connections implicitly and only connects to the SAP database.
Native SQL is essentially a direct path to the DBMS. When using Native SQL the ABAP developer needs to build their SQL code exactly as the targeted DBMS expects it. When accessing a non-SAP database the ABAP programmer can only use Native SQL.
Native SQL has commands for setting up, opening and closing a database connection. When a new connection to a non-SAP database is opened, a new database transaction is started automatically on this connection. This transaction is independent of the transaction currently running on the SAP default connection. Any transaction currently running on the SAP database is not closed and any Open SQL commands will continue to be processed against the SAP database. Similarly any Native SQL commands will be executed on the newly opened non-SAP database connection.
So here is a simple program to access the MONA database from ABAP.
REPORT zmona_read_customer_addr. TYPES: BEGIN OF mona_cust_addr_type, cno(4) TYPE n, title(7) TYPE c, name(40) TYPE c, zip(5) TYPE c, city(3) TYPE c, state(2) TYPE c, address(40) TYPE c, END OF mona_cust_addr_type. DATA: ls_custaddr TYPE mona_cust_addr_type, lt_custaddr TYPE TABLE OF mona_cust_addr_type. * Connect to MONA database EXEC SQL. CONNECT TO 'MONA' ENDEXEC. IF sy-subrc <> 0. MESSAGE 'Unable to connect to MONA' TYPE 'E' DISPLAY LIKE 'I'. RETURN. ENDIF. * Define database cursor EXEC SQL. OPEN dbcur FOR SELECT cno, title, name, zip, city, state, address FROM HOTEL.CUSTOMER_ADDR ENDEXEC. * Fill customer itab DO. EXEC SQL. FETCH NEXT dbcur INTO :ls_custaddr-cno, :ls_custaddr-title, :ls_custaddr-name, :ls_custaddr-zip, :ls_custaddr-city, :ls_custaddr-state, :ls_custaddr-address ENDEXEC. IF sy-subrc <> 0. EXIT. ELSE. APPEND ls_custaddr TO lt_custaddr. ENDIF. ENDDO. * Close connection to MONA EXEC SQL. CLOSE dbcur ENDEXEC. * Reset to "default connection" EXEC SQL. SET CONNECTION DEFAULT ENDEXEC. * Print 20 records LOOP AT lt_custaddr INTO ls_custaddr. WRITE: /, ls_custaddr-cno, ls_custaddr-title, ls_custaddr-name, ls_custaddr-zip, ls_custaddr-city, ls_custaddr-state, ls_custaddr-address. IF sy-tabix > 20. EXIT. ENDIF. ENDLOOP.
When you run this program you should see the same data that we found in the CUSTOMER_ADDR view when we used the SQL Studio select statement.