Using ABAP to access non-SAP databases | SAP Blogs https://blogs.sap.

com/2008/10/27/using-abap-to-access-non-sap-databases/

Follow
CommunityRSS feed Like

Ask a Question Write a Blog Post Login / Sign-up

Graham Robinson
October 27, 2008 9 minute read

Using ABAP to access non-SAP databases

8 Likes 24,284 Views 34 Comments

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

1 of 19 09/06/2021, 8:38
Using ABAP to access non-SAP databases | SAP Blogs https://blogs.sap.com/2008/10/27/using-abap-to-access-non-sap-databases/

and closing database connections as you do in many other programming languages.

Follow RSS feed Like

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 di�erent 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 �nd 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 �rst need to establish technical connectivity from our NW-ABAP
application server(s) to the Remote-DB.

Depending upon the speci�cs 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 speci�c requirements. You can �nd a list of them in this
section (http://help.sap.com/saphelp_nw04/helpdata/en/50/63d6b37bda7f4a92e2dec21b14ceee
/frameset.htm) of the SAP Help.

2 of 19 09/06/2021, 8:38
Using ABAP to access non-SAP databases | SAP Blogs https://blogs.sap.com/2008/10/27/using-abap-to-access-non-sap-databases/

Follow RSS feed Like

Database Library

Each SAP kernel includes a database speci�c 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-speci�c components.
When downloading a kernel update from the SAP Service Marketplace you need to obtain the database-
independent archive and the database-speci�c 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-
speci�c 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-speci�c and MS SQL Server database-
speci�c 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-speci�c kernel components as well.

System Con�guration

Once technical connectivity to the non-SAP database is in place the next step is to con�gure in the SAP
system the speci�c con�guration 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 speci�c 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 speci�c 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-speci�c 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 �nd another database to connect to and makes the example simpler to
implement.

Create Sample Database

3 of 19 09/06/2021, 8:38
Using ABAP to access non-SAP databases | SAP Blogs https://blogs.sap.com/2008/10/27/using-abap-to-access-non-sap-databases/

We are going to use the schema and tables that are delivered as part of the MaxDB SQL tutorial as our
Follow RSS feed Like
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
“Con�guration” 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 De�nition” you can see it is a join of the CUSTOMER and CITY tables.

4 of 19 09/06/2021, 8:38
Using ABAP to access non-SAP databases | SAP Blogs https://blogs.sap.com/2008/10/27/using-abap-to-access-non-sap-databases/

Follow RSS feed Like

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.

5 of 19 09/06/2021, 8:38
Using ABAP to access non-SAP databases | SAP Blogs https://blogs.sap.com/2008/10/27/using-abap-to-access-non-sap-databases/

Follow RSS feed Like

Connecting to MONA database

We do not need to add any database-speci�c components to our ABAP kernel because it already has the
components for MaxDB included as part of the installation.

But we do need to con�gure 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.

6 of 19 09/06/2021, 8:38
Using ABAP to access non-SAP databases | SAP Blogs https://blogs.sap.com/2008/10/27/using-abap-to-access-non-sap-databases/

Referring to the SAP Help link I mentioned earlier ( http://help.sap.com/saphelp_nw04/helpdata/en/50

Follow RSS feed Like
/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 speci�c 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 speci�c 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,

7 of 19 09/06/2021, 8:38
Using ABAP to access non-SAP databases | SAP Blogs https://blogs.sap.com/2008/10/27/using-abap-to-access-non-sap-databases/

END OF mona_cust_addr_type.
Follow RSS feed Like
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.
Alert Moderator
ENDDO.
* Close connection to MONA
EXEC SQL.
CLOSE dbcur
Assigned tags
ENDEXEC.
* Reset to "default connection"
ABAP Connectivity
EXEC SQL. | abap | sapmentors |

SET CONNECTION DEFAULT

ENDEXEC.
Related Blog20Posts
* Print records
LOOP AT lt_custaddr INTO ls_custaddr.
ABAP WRITE: /, Methods and Services
Connectivity:

8 of 19 09/06/2021, 8:38
Using ABAP to access non-SAP databases | SAP Blogs https://blogs.sap.com/2008/10/27/using-abap-to-access-non-sap-databases/

By Olga Dolinskaja , Mar 06, 2012

ls_custaddr-
Secondary database
Followconnections inRSS
ABAP
feed Like
ls_custaddr-
By Waldemar Schakiel , Sep 16, 2016
ls_custaddr-
SAP FTP CONNECTION
ls_custaddr-
By Ansari Mohammed Shah azim , Apr 09, 2020
ls_custaddr-
ls_custaddr-
ls_custaddr-
IF Questions
Related sy-tabix > 20.
EXIT.
ENDIF.
Access table using ABAP program in other database
ENDLOOP.
By Former Member , Jan 09, 2012

FTP chinese characters

By Former Member , Jun 18, 2009

Querying an SQL Database from ABAP

By Former Member , Jun 28, 2007
W
h
e
n
y
34 Comments
o
u
r You must be Logged on to comment or reply to a post.
u
n
t Thorsten Franz
h
October 27, 2008 at 12:14 pm
i
Hi,
s
very helpful blog, I'll try it out sometime soon.
p
A good use case for native SQL is if you have to handle large amounts of data with many di�erent structures
r
and want to be able to perform ad-hoc creation and deletion of entire tables (CREATE TABLE, DROP TABLE)
o
bypassing the Data Dictionary, perhaps because the tables are highly temporary.
g
Cheers,
rThorsten
a
m
Like(0)
y
o
u Graham Robinson | Post author
s
October 27, 2008 at 1:50 pm
h
Thanks
o Thorsten,
I'm glad you liked this subject. High praise indeed.
u
l
Cheers
d
Graham Robbo

9 of 19 09/06/2021, 8:38
Using ABAP to access non-SAP databases | SAP Blogs https://blogs.sap.com/2008/10/27/using-abap-to-access-non-sap-databases/

s
Like(0) Follow RSS feed Like
e
e
t
h Lars Breddemann
e
October 28, 2008 at 12:41 pm
s
a
Actually this had been done for years now, e.g. by BW/BI.
m problem here is, if you bypass the dictionary you really have to take care about EVERYTHING.
The
Creating
e the table correctly, setting all the column defaults, setting UNIQUE and NOT NULL constraints,
choose
d the right storage parameters (if applicable), handle CBO statistics and clean up everything again.
This
a took the BW developers only 3 major versions to get it really right...
t
What's even worse - if you don't invest a whole lot of time you make your code database dependent. It just
a
won't work on any other DBMS the way it should.
t
h
So one should be very careful when using this for anything else than accessing NON-SAP-databases.
a
tregards,
w
Lars
e
fLike(0)
o
u
n Graham Robinson | Post author
d
October 28, 2008 at 1:44 pm
i
Hi Lars,
n
You are correct - DB multi-connect has been available since Basis 4.0B.
t
h
I agree with you that great care needs to be taken, especially if you DDL statements such as "CREATE
e
TABLE".
C
U
I will leave any judgement on the capabilites of the BW developers to you.
S
Cheers
T
Graham
O
M
Like(0)
E
R
_
A
D Dennis Howlett
D
October 27, 2008 at 1:56 pm
R
Great
v post Graham, incredibly useful. Wee suggestion. How about popping the code into a ZIP �le that
readers
i can download?

10 of 19 09/06/2021, 8:38
Using ABAP to access non-SAP databases | SAP Blogs https://blogs.sap.com/2008/10/27/using-abap-to-access-non-sap-databases/

Like(0)
e
Follow RSS feed Like
w
w
h Graham Robinson | Post author
e
October 27, 2008 at 7:26 pm
n
Hi Dennis,
w
thanks for your suggestion. I have added a link to a Google Doc that contains the source code.
e
u
Cheers
s
Graham Robbo
e
d
Like(0)
t
h
e
S
Lars Breddemann
Q
L
October 28, 2008 at 12:45 pm
S
It's really a nice How-To on DB multiconnect, good work.
t
Anyhow - it's really important to fully grasp the fact, that there is no Netweaver transaction management
u
when using native SQL.
d
Hello transaction artifacts, hello partial rollbacks, hello deadlocks...
iBe *very* careful about what you do with this tool. You've to really know how the DBMS works that you're
o
using then!
s
Nevertheless,
e I'm always happy to see blogs on DB topics. More of this, please!
l
regards,
e
Lars
c
t
Like(0)
s
t
a Graham Robinson | Post author
t
October
e 28, 2008 at 1:32 pm

m
Thanks Lars. Glad you found this useful.
e
Cheers
n
Graham Robbo
t
Like(0)
.

Former Member

October 29, 2008 at 2:10 am

11 of 19 09/06/2021, 8:38
Using ABAP to access non-SAP databases | SAP Blogs https://blogs.sap.com/2008/10/27/using-abap-to-access-non-sap-databases/

Thanks Graham Robbo.

Follow RSS feed Like
This blog is truly a Gem.

Like(0)

Graham Robinson | Post author

October 29, 2008 at 2:46 pm

Thanks Hashir,
I am pleased you found this valuable.

Cheers
Graham Robbo

Like(0)

Former Member

January 20, 2009 at 4:56 am

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

Like(0)

Graham Robinson | Post author

January 20, 2009 at 3:44 pm

Hi Abdullah,
I hope you found my blog of value.

Certainly Native SQL supports the calling of stored procedures. This is documented in the ABAP
documentation. Start at http://help.sap.com/saphelp_nw70/helpdata/EN/fc
/eb3b8b358411d1829f0000e829fbfe/frameset.htm

Cheers
Graham Robbo

Like(0)

12 of 19 09/06/2021, 8:38
Using ABAP to access non-SAP databases | SAP Blogs https://blogs.sap.com/2008/10/27/using-abap-to-access-non-sap-databases/

Follow RSS feed Like

Former Member

January 26, 2009 at 6:19 am

This post was useful for a prototype I am building.

Thanks.

Like(0)

Graham Robinson | Post author

January 26, 2009 at 12:13 pm

Hi Shweta,
I am glad you found this useful.

Cheers
Graham Robbo

Like(0)

Former Member

November 18, 2013 at 10:17 am

Hi Graham,

My requirement is exactly same like the blog you have posted. The only di�erence is �rst 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.

13 of 19 09/06/2021, 8:38
Using ABAP to access non-SAP databases | SAP Blogs https://blogs.sap.com/2008/10/27/using-abap-to-access-non-sap-databases/

And the dump states that "Table does not exist in database" . Can you guide me what exactly i need to do for this?
Follow RSS feed Like

Like(0)

Graham Robinson | Post author

November 18, 2013 at 6:30 pm

Seems pretty clear that the table does not exist in the database. You could use DB utilities to verify the table
name.

Like(0)

Julien BOFFET

April 16, 2014 at 2:48 pm

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 � 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?

Like(0)

Graham Robinson | Post author

April 16, 2014 at 11:23 pm

That doesn't sound right. If you are sure of this behaviour I suggest you contact support.

Like(0)

Julien BOFFET

May 14, 2014 at 5:17 pm

Thank you for your help. Message sent to support....

14 of 19 09/06/2021, 8:38
Using ABAP to access non-SAP databases | SAP Blogs https://blogs.sap.com/2008/10/27/using-abap-to-access-non-sap-databases/

Like(0)
Follow RSS feed Like

Former Member

June 12, 2014 at 5:57 am

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.

Like(0)

Graham Robinson | Post author

June 13, 2014 at 12:56 am

This is all covered in the documentation. Enjoy!

Like(0)

Former Member

June 18, 2014 at 10:56 am

Hi Graham,

In the documentation i'm not able to �nd the answer for how to pass select option in native SQL.

Thanks in Advance.

Like(0)

Graham Robinson | Post author

June 18, 2014 at 1:36 pm

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

15 of 19 09/06/2021, 8:38
Using ABAP to access non-SAP databases | SAP Blogs https://blogs.sap.com/2008/10/27/using-abap-to-access-non-sap-databases/

Follow RSS feed Like

There is a link to the documentation earlier in the comment thread. Also when I Googled "sap native sql" it
was the �rst entry returned.

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

Cheers

Graham Robbo

Like(0)

Former Member

June 19, 2014 at 3:22 am

Thanks Graham

For the help. But still you didnt get my queation at all.

Anyways thanks for the help

Like(0)

Graham Robinson | Post author

June 19, 2014 at 11:39 am

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

Like(0)

16 of 19 09/06/2021, 8:38
Using ABAP to access non-SAP databases | SAP Blogs https://blogs.sap.com/2008/10/27/using-abap-to-access-non-sap-databases/

Follow RSS feed Like

Former Member

October 9, 2014 at 5:33 pm

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 di�erent 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.

Like(0)

Jelena Per�ljeva

October 9, 2014 at 6:10 pm

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...

Like(0)

Graham Robinson | Post author

October 10, 2014 at 4:05 am

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

Like(0)

Former Member

October 10, 2014 at 12:05 pm

It worked. Thanks. I wrote a FM on the receiver SAP instance and did an RFC function call.

Like(0)

17 of 19 09/06/2021, 8:38
Using ABAP to access non-SAP databases | SAP Blogs https://blogs.sap.com/2008/10/27/using-abap-to-access-non-sap-databases/

Follow RSS feed Like

Former Member

June 10, 2016 at 7:12 pm

Hi,

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

Thanks

Like(0)

Graham Robinson | Post author

June 16, 2016 at 10:06 pm

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

Like(0)

Former Member

June 18, 2016 at 2:32 pm

Thanks Graham!

Like(0)

Matt Fraser

18 of 19 09/06/2021, 8:38
Using ABAP to access non-SAP databases | SAP Blogs https://blogs.sap.com/2008/10/27/using-abap-to-access-non-sap-databases/

December 6, 2016 at 10:43 pm

Follow RSS feed Like
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

Like(1)

Graham Robinson | Post author

January 5, 2017 at 12:53 am

Glad to be of assistance Matt.

Like(1)

Find us on

Privacy Terms of Use

Legal Disclosure Copyright

Trademark Cookie Preferences

Newsletter Support

19 of 19 09/06/2021, 8:38