topic Re: Access data from external database in Application Development and Automation Discussions

Access data from external database

Former Member — Tue, 31 Aug 2010 07:59:46 GMT

Hi everyone,

I have to access data from an external oracle database from SAP system running DB2.

I found few threads

[Access external Oracle database with ABAP|;

This uses native SQL to open connection, and don't want to use this option.

Another one describes about Remove views

[API to connect to external database|;

but could not find more information in SCN searching with "Remote views".

How can i implement this "Remote views" ? Any tips welcome

Thanks

Re: Access data from external database

Former Member — Tue, 31 Aug 2010 08:59:50 GMT

Hi,

there is a trick you can use, but you need a DBlink to the external database. This is the same as it is described if you want to use the Native SQL.

The trick is that you define the tables with the same name and structure as the remote tables in se11. Of course you need to use the customer namespace. Then you can also define views in se11. after activation you drop the tables on SAP database level.

In ABAP program you switch the DB connection to the DBLink if you want to access the remote tables. Then you can access them with with Open SQL. Switch back to standard database connection before processing SAP internal tables.

This solution has disadvantages. When activating a table in SE11 the table will also be created in SAP database also in case you transport it to another sytem. This means after changing the structre in se11 and activation the table will be created by SAP again.

Regards

Michael

Re: Access data from external database

Former Member — Tue, 31 Aug 2010 09:43:11 GMT

Hi again,

just found a sample I made years ago. The right object is to create a synonym.

The coding create and tests such a link. The link must be defined on database level as described in the OSS for the native SQL solution.

REPORT zsmbtest_dbsyn LINE-SIZE 80.

***********************************************************************

To Do's:
1. Create table with the name of the synonym in SE11 and activate
2. Call Function module DB_DROP_TABLE in SE37 with TABNAME =
created table
3. Create synonym with this program
After this you cant activate the table anymore but you can
access with open Open SQL

***********************************************************************

PARAMETERS: p_syn TYPE dbcon_name DEFAULT 'ZALTRES_FPDEV',

p_conn TYPE dbcon_name DEFAULT 'I2ADW_FPDEV',

p_switch AS CHECKBOX DEFAULT 'X',

p_test RADIOBUTTON GROUP mode,

p_drop RADIOBUTTON GROUP mode,

p_create RADIOBUTTON GROUP mode.

IF p_test IS INITIAL.

PERFORM create_syn USING p_create

p_syn

'alt_res@i2adw_fpdev'.

ELSE.

PERFORM test_syn USING p_syn

p_conn

p_switch.

ENDIF.

*&----

*& Form create_syn

*&----

text

*----

--> p1 text
<-- p2 text

*----

FORM create_syn USING value(pmode)

value(psyn)

value(plink).

DATA: len TYPE i,

sql_code TYPE i,

sql_msg TYPE c,

dummy(254).

IF pmode = 'X'. "Create

CONCATENATE 'CREATE SYNONYM' psyn 'FOR' plink

INTO dummy SEPARATED BY space.

ELSE. "Drop

CONCATENATE 'DROP SYNONYM' psyn

INTO dummy SEPARATED BY space.

ENDIF.

COMPUTE len = strlen( dummy ).

CALL FUNCTION 'DB_EXECUTE_SQL'

EXPORTING

stmt = dummy

stmt_ln = len

IMPORTING

sql_code = sql_code

sql_msg = sql_msg

EXCEPTIONS

sql_error = 1

not_found_or_duplicate = 2

internal_error = 3

OTHERS = 4.

IF sy-subrc = 1.

WRITE: / 'SQL DB-Error :', sql_code, sql_msg.

ELSE.

WRITE: / 'SQL DB-Return :', sy-subrc.

ENDIF.

ENDFORM. " create_syn

*&----

*& Form test_syn

*&----

text

*----

-->P_PSYN text

*----

FORM test_syn USING p_psyn

p_pconn

p_pswitch.

DATA: wa_alt_res TYPE zpp_alt_res_i2t,

ta_alt_res TYPE STANDARD TABLE OF zpp_alt_res_i2t,

wa_dbfields TYPE dbfield,

ta_dbfields TYPE STANDARD TABLE OF dbfield.

DATA: l_dbcnt TYPE sy-dbcnt,

l_subrc TYPE sy-subrc,

wa_syn(1000) TYPE c,

sql_code TYPE i,

dummy(254).

EXEC SQL.

SELECT COUNT(*) FROM all_synonyms

INTO :l_dbcnt

ENDEXEC.

WRITE: / 'Einträge in Tabelle all_synonyms der SAP DB :',

l_dbcnt.

IF p_pswitch = 'X'.

EXEC SQL.

connect to :p_pconn

ENDEXEC.

CHECK sy-subrc = 0.

EXEC SQL.

SELECT COUNT(*) FROM all_synonyms

INTO :l_dbcnt

ENDEXEC.

WRITE: / 'Einträge in Tabelle all_synonyms der Remote DB:',

l_dbcnt.

CALL FUNCTION 'DB_GET_TABLE_FIELDS'

EXPORTING

tabname = 'ALT_RES'

IMPORTING

subrc = l_subrc

TABLES

dbfields = ta_dbfields.

IF NOT ta_dbfields[] IS INITIAL.

ULINE.

WRITE: / 'Struktur der Remote-Tabelle ALT_RES'.

ULINE.

LOOP AT ta_dbfields INTO wa_dbfields.

WRITE : / wa_dbfields-name, wa_dbfields-type,

wa_dbfields-length, wa_dbfields-decimals.

ENDLOOP.

ULINE.

ENDIF.

SELECT * FROM (p_psyn) INTO TABLE ta_alt_res.

WRITE: / 'Einträge in Remote DB mit Synonym', p_psyn, ':', sy-dbcnt.

IF NOT ta_alt_res[] IS INITIAL.

ULINE.

LOOP AT ta_alt_res INTO wa_alt_res.

WRITE: / wa_alt_res-werks, wa_alt_res-arbpl_nom,

wa_alt_res-arbpl_alt, wa_alt_res-thckn_min_in,

wa_alt_res-width_min_in.

IF sy-tabix > 50.

EXIT.

ENDIF.

ENDLOOP.

ULINE.

ENDIF.

COMMIT WORK.

IF p_pswitch = 'X'.

EXEC SQL.

disconnect :p_pconn

ENDEXEC.

ENDIF.

ENDFORM. " test_syn

Re: Access data from external database

SuhaSaha — Tue, 31 Aug 2010 09:55:32 GMT

As mentioned by Michael, for these types of requirement you've to create a Database connection for the external DB. (Tcode DBCO).

Once the connection params are maintained you can use the ADBC(ABAP DataBase Connectivity) classes to access the data. Check the demo program ADBC_DEMO.

BR,

Suhas

Re: Access data from external database

Former Member — Thu, 02 Sep 2010 05:13:59 GMT

Hi Suhas / Michael,

We have decided to go for flat file as there are lot of calculations involved to arrive at the result and a select from SAP would not be good.

Information provided was very helpful...Thanks guys.