Skip to Content

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.

Platform availability can be checked on the SAP Product Availability Matrix at http://service.sap.com/pam  (http://service.sap.com/pam)

DB Connectivity

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.

Database Library

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.

System Configuration

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.

Load MaxDB tutorial objects

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.

HOTEL.CUSTOMER_ADDR Properties

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.

HOTEL.CUSTOMER_ADDR contents

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.

DBCO

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.

To report this post you need to login first.

34 Comments

You must be Logged on to comment or reply to a post.

  1. Thorsten Franz
    Hi,
    very helpful blog, I’ll try it out sometime soon.
    A good use case for native SQL is if you have to handle large amounts of data with many different structures and want to be able to perform ad-hoc creation and deletion of entire tables (CREATE TABLE, DROP TABLE) bypassing the Data Dictionary, perhaps because the tables are highly temporary.
    Cheers,
    Thorsten
    (0) 
    1. Lars Breddemann
      Actually this had been done for years now, e.g. by BW/BI.

      The problem here is, if you bypass the dictionary you really have to take care about EVERYTHING.
      Creating the table correctly, setting all the column defaults, setting UNIQUE and NOT NULL constraints, choose the right storage parameters (if applicable), handle CBO statistics and clean up everything again.
      This took the BW developers only 3 major versions to get it really right…

      What’s even worse – if you don’t invest a whole lot of time you make your code database dependent. It just won’t work on any other DBMS the way it should.

      So one should be very careful when using this for anything else than accessing NON-SAP-databases.

      regards,
      Lars

      (0) 
      1. Graham Robinson Post author
        Hi Lars,

        You are correct – DB multi-connect has been available since Basis 4.0B.

        I agree with you that great care needs to be taken, especially if you DDL statements such as “CREATE TABLE”.

        I will leave any judgement on the capabilites of the BW developers to you. 🙂

        Cheers
        Graham

        (0) 
  2. Lars Breddemann
    It’s really a nice How-To on DB multiconnect, good work.
    Anyhow – it’s really important to fully grasp the fact, that there is no Netweaver transaction management when using native SQL.
    Hello transaction artifacts, hello partial rollbacks, hello deadlocks…

    Be *very* careful about what you do with this tool. You’ve to really know how the DBMS works that you’re using then!

    Nevertheless, I’m always happy to see blogs on DB topics. More of this, please!

    regards,
    Lars

    (0) 
  3. ZAFCO ABAP
    Hi,

    If you execute a stored procedure, how will u get the output of the query in an internal table? Can you please throw some light on this?

    Warm Regards,
    Abdullah

    (0) 
      1. prachi gupta

        Hi Graham,

         

        My requirement is exactly same like the blog you have posted. The only difference is first i am deleting all the entries of the Schema table and then enteringnew value.

         

        But my problem is it is giving dump.

        I have written code like this :

        EXEC SQL.
        CONNECT
        TO ‘D01’
        ENDEXEC.
        EXEC SQL.
        Delete from DB1.arcust
        ENDEXEC.

        EXEC SQL.
        commit
        ENDEXEC.

         

        And the dump states that “Table does not exist in database” . Can you guide me what exactly i need to do for this?

         

         

        (0) 
  4. Julien BOFFET

    Hello Graham,

     

    We face the following situation : it seems that during runtime and after the instruction connect to <non-sap db>, the non sap db becomes the default db connection for everyone in the system. Is it a normal behaviour?

    It means that if somebody posts a fi document during the execution of the program, SAP tries to update BKPF in the non sap database (situation observed on the system last week).

    Is there a workaround for this?

    (0) 
  5. hemalatha manickam

    Hi Graham,

     

    Can you please tell how to pass the select options value to where condition in

    Native SQL.

    Example. Select-option: S_BUKRS for T001-BUKRS.
    how to pass this to where condition in selct query of native SQL.

    (0) 
        1. Graham Robinson Post author

          Well I don’t think you are trying very hard.

           

          There is a link to the documentation earlier in the comment thread. Also when I Googled “sap native sql” it was the first entry returned.

           

          And please understand this blog was written in 2008. These days you should be looking at ADBC.

           

          Cheers

          Graham Robbo

          (0) 
            1. Graham Robinson Post author

              Sorry if I am misunderstanding your question. You need to pass native SQL to the database interface whether you choose to use the inline EXEC SQL syntax or the ADBC classes. So you need to parse any input data, such as select options, and produce the necessary native SQL.

               

              Cheers

              Graham Robbo

              (0) 
  6. newABAP Developer

    Graham,

    I am working on a project to connect to SAP database and fetch data from a table.  Bascially, our sister company is in a different SAP instance but same hosting company.  The hosting company has setup a ABAP Connection to the sister company’s SAP.  Is there a documentation or know of one where I can connect and fetch data from RFC Call?Thank you in advance.

    (0) 
    1. Jelena Perfiljeva

      I’m not Graham, but if you have an RFC connection between two SAP systems why would you need (or want) to use Native SQL? Can’t you just call an RFC function? Not sure what data are you trying to get but there could be a standard FM available or your counterparts could write a custom one in that other SAP system, it’s not that hard…

      (0) 
    2. Graham Robinson Post author

      Jelena’s advice is good. If you want to get SAP data either use an existing SAP interface such as RFC-enabled function modules – possibly even BAPI’s – or build your own.

       

      Cheers

      Graham Robbo

      (0) 
  7. Femina Shah

    Hi,

     

    Thanks for your blog! It was really helpful. I have a question though;if I want to retrieve a table from different schemas how should I change this code in order to have dynamic schemas depending on the environment?

     

    Thanks

    (0) 
    1. Graham Robinson Post author

      Thanks Femina,

       

      I’m glad you found this useful. As mentioned in a previous comment this blog was written many years ago and used native SQL in the example. These days you should be looking at ADBC which supports dynamic SQL much better.


      Cheers

      Graham Robbo

       

      (0) 
  8. Matt Fraser

    Well, Graham, the blog may be old, and the examples out-of-date, but this is still helpful to us non-developer basis types who are just trying to test a DBCON connection before handing it over to the ‘real’ developers (who, presumably, will use ADBC instead).

    Cheers,
    Matt
     

    (1) 

Leave a Reply