Community Aska Question Write a Blog Post Graham Robinson October 27,2008 9 minute read Using ABAP to access non-SAP databases RSS feed Like Likes 21,881 Views 34 Comments I decided to write this blog after regularly seeing forum questions about accessing external databases from ABAP. 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 | built with the SAP NetWeaver 701 ABAP Trial Version. You can obtain the SAP NetWeaver 701 ABAP Trial Version from here (https:/wwwsdn.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 | have also seen them write it as "Multiconnect’, “Multi-connect” and‘Connect’. For the remainder of this blog | 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 | 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 Net (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/63d6b37bda7f4a92e2dec21bl4ceee/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 toa 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-specitic 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 doit! Okay let's try it for ourselves. As mentioned, this example was built using the SAP NetWeaver 701 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: Manager and SQL Studio] on how to install and use these tools. Jatabase ‘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, ee erate Brow “Bore soon 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.-$8k Gesson Yew ‘Slecon Hele ‘| sis Digicie|#/felS 8) koa a(n ow Spr Si Te aoe Saas one aa 5 pens Pe cee ra rman ie |_saa_ze 1 ; wane ie Foe et — ewok — THR ‘Baw SM Son AR ML a3 i anion an Cogs Gat — Suit Changs — Ca aps 2M San Rr Tmt Eston tort Sen eG Sk Senor MoM Renakh ise Capo LS MANSTRET a7 Brora Fabs Sem Tb ae tate fate soon S00 xen age Geral OW ama SeenON Bane Soe Sa) amet 0h i room Bane owe al TBP sng Sueno ty om hie St Shoe Dee Sad ssnen Sd fie Sse ne Lagens cha aT 8 calen izowr rece art ue eae ams art 2 Bt Ponta £00 roy TODtwe 21s Sie wk 2 Gl ae Ses TH sane” te SB Shree SaPatan. El seen SHE Comy Mephines te uw Tipe +a tome 12 gay Oey TS Schmah 1X 20a I sal ortuee eo coyoy napa ont eat te movaeaeet i odes font ostone 26 J4 Commi Or el linmnat Sfteneaee sl feome I Las Statement Statement suc enec ra ee 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. Display View "Description of Database Connections": eens = Ihave 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 | mentioned earlier (http://help.sap.com/saphelp_nw04/helpdata/en/50/63d6b37bda714a92e2dec21bl4ceee/frameset.htm) you will see that the connection information for SAP DB should be of the format -. 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 anew 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( 42) TYPE c, 2ip() TYPE c, city(3) TYPE c, state(2) TYPE c, address (40) TYPE ¢, END OF mona_cust_addr_type. DATA: 1s_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-subre <> 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 custoner stab Do. EXEC SQL. FETCH NEXT docur INTO :1s_custaddér-cno, Is_custaddr-title, 1s_custaddr-nane, Is_custaddr-zip, Is_custaddr-city, Is_custaddr-state, As_custaddr-address ENDEXEC. IF sy-subre © @. EXIT. ELSE. APPEND 1s_custaddr TO 1t_custader. ENDIF. ENDO. * Close connection to MONA EXEC SQL CLOSE dbcur ENDEXEC. * Reset to “default connection’ EXEC SQL SET CONNECTION DEFAULT ENDEXEC * Print 20 records LOoP AT 1t_custaddr INTO 1s_custaddr. WRITE: /, As_custaddr-cno, Is_custaddr-title, 1s_custaddr-nane, Is_custaddr-zip, Is_custaddr-city, Is_custaddr-state, As_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. Alert Moderator Assigned tags ABAP Connectivity | abap | sapmentors | Related Blog Posts ABAP Connectivity: Methods and Services By Olga Dolinskaja , Mar 06, 2012 Secondary database connections in ABAP 1, Sep 16, 2016 By Waldemar Schal ‘Accessing an External MSSQL Data using Simple ABAP Program By Former Member , Oct 29, 2013 Related Questions Access table using ABAP program in other database By Former Member , Jan 09. 2012 FTP chinese characters By Former Member , Jun 18, 2009 ABAP AND DATABASES By Former Member , Apr 29, 2008 34 Comments You must be Logged on to comment or reply to a post. Q Thorsten Franz October 27, 2008 at 12:14 pm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 ike (0) @ _ ceaham Robinson | Post author October 27,2008 at 150 pm Thanks Thorsten, I'm glad you liked this subject. High praise indeed. Cheers Graham Robbo ike (0 @ _tarseredderann October 28, 2008 at 12:41 pm Actually this had been done for years now, e.g. by BW/Bl 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 @ _ siaham Robinson | Post author (october 28,2008 at 1:44 pm Hi Lars, You are correct ~ DB multi-connect has been available since Basis 4.08. | agree with you that great care needs to be taken, especially if you DDL statements such as “CREATE, TABLE”,Iwill leave any judgement on the capabilites of the BW developers to you. @ Cheers Graham @ venis Homiert October 27, 2008 at 1:56 pm Great post Graham, incredibly useful. Wee suggestion. How about popping the code into a ZIP file that readers can download? @ sistem Robinson | Post author october 27,2008 at 7:26 pm Hi Dennis, thanks for your suggestion. | have added a link to a Google Doc that contains the source code. Cheers Graham Robbo @ __txssreddemann October 28, 2008 at 12:45 pm 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 ike (0) @ _ ceaham Robinson | Post authorOctober 28, 2008 at 1:32 pm Thanks Lars. Glad you found this useful. Cheers, Graham Robbo ike (0) | Former Member Author's ‘Peestler 29, 2008 at 2:10 am hote Bhanks Graham Robbo. is logis truly a Gem. ike (0) @ sistem Robinson | Post author October 29,2008 at 2:46 pm Thanks Hashir, lam pleased you found this valuable. Cheers Graham Robbo Like (0) r= Former Member Author's ‘pxailey 20, 2009 at 4:56 am oto Faham Kyou execute a stored procedure, how willu get the output of the query in an internal table? Can you please throw some light on this? ‘Warm Regards, Abdullah Like (0) @ _ ciaham Robinson | Post author January 20,2009 at 3:44 pm Hi Abdullah, Thope 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/tc/eb3b8b358411d1829f0000e829tbfe/frameset.htm Cheers Graham Robbo Like (0) Former Member Author's riley 26, 2009 at 6:19 am hoto Tie was useful for a prototype | am building. @ BBN ike (0) @ _ cisham Robinson | Post author January 26,2009 at 1213 pm Hi Shweta, lam glad you found this useful. Cheers Graham Robbo Like (0) Former Member Author's pBoofilaber 18, 2013 at 10:17 am ar eiahem. Robinson 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 itis giving dump. Ihave written code like this : EXEC SQL. CONNECT TO'DO!" ENDEXEC,EXEC SQL. Delete from DB1arcust 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? @ _ cizham 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, lien BOFFET Authoi'g'e" BO (pole, 2014 at 2:48 pm lo granam, Robinson We face the following situation : it seems that during runtime and after the instruction connect to , 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? @ _ craham Robinson | Post author April 16, 2014 at 11:23 pm.That doesn't sound right. If you are sure of this behaviour | suggest you contact support. ike (0) * FI Rathoeien BOFFET pile 2016 at 5:27 pm r Bhar you for your help. Message sent to support. Robinson ike (0) Ketnofgrmer Member rosie, 2014 at 5:57 am hoto BiStapem. Robinson Can you please tell how to pass the select options value to where condition in Native SQL. Example. Select-option: S_BUKRS for TOO1-BUKRS. how to pass this to where condition in selct query of native SQL. ike (0) @ craham Robinson | Post author June 13, 2014 at 12:56 am This is all covered in the documentation. Enjoy! ike (0) "=| Former Member Author's ‘puragile, 2014 at 10:56 am hoto iSiahem. Robinson In the documentation i'm not able to find the answer for how to pass select option in native SQL. Thanks in Advance,ike (0) @ _ cisham Robinson | Post author June 18, 2014 at 1:36 pm. Well | don't think you are trying very hard. There is a link to the documentation earlier in the comment thread. Also when | 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 eofgrmer Member rofile, 2014 at 3:22 am harks Graham Robinson For the help. But still you didnt get my queation at all Anyways thanks for the help Like (0) @ _ sisham Robinson | Post author June 19,2014 at 1:39 am Sorry if | 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 ike (0) Former Member Author's profiler 9, 2014 at $:33 pm hoto Esham Robinson Tam 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 | can connect and fetch data from RFC Call? Thank you in advance. mors elena Perfiljeva Poosiler 9, 2014 at 6:10 pm fg Graham, but if you have an RFC connection between two SAP systems why would you need (or want) He.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) @ _ siaham Rovinson | 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 ike (0) % Former Member Author @™er Member (Pestle 10, 2014 at 12:05 pm hoto, Eiwpeked. Thanks. | wrote a FM on the receiver SAP instance and did an RFC function call Robinson ike (0)4. Former Member ‘Author's brofile, 2016 at 7:12 pm hoto Haham, Robinson Thanks for your blog! It was really helpful. | have a question thoughiif | want to retrieve a table from different schemas how should | change this code in order to have dynamic schemas depending on the environment? Thanks ike (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) 2. Former Member Author's profile, 2016 2232 pm hoto Eahtks Graham! Robinson Uke (0)Matt Fraser Author's Peotileder 6, 2016 at 10:43 pm hoto 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 ike (1) @ craham Rovinson | Post author January 5, 2017 at 12:53 am Glad to be of assistance Matt. ike (1) ind us on Privacy Terms of Use Legal Disclosure Copyright Trademark Cookie Preferences Newsletter Support