topic Re: Connection to an external database. in Application Development and Automation Discussions

Connection to an external database.

Former Member — Mon, 18 Sep 2006 12:12:04 GMT

Hi we r using native sql commands and are connecting to external database. As far as I know from the discussions here i thought that these sort of connections are maintained in the table dbcon. But i don't see any entries in that table.

WHere as i could connect to that database succesfully and getting the records.

The table name for the external system is NOVARTIS.TABLE_ADDRESS@ANK like this.

Where could be that settings made.

Re: Connection to an external database.

Former Member — Mon, 18 Sep 2006 12:20:05 GMT

Try this Forum Thread:-

1. In developer server you have to configure the tnsnames.ora. For doing this you should talk with a person who knows how to configure this file. In the register you should especify: protocol, host and port to connect to the other database system.

2. Then, you enter a register in the DBCON table. In this entrie you especify user, password, database id (in my case I wrote ORA because i am connecting to an oracle database), and in the enviroment field you write the name or alias of the register in the tnsnames.ora

3. In the abap program you use native sql sentences for connecting to the external database. This is an example:

EXEC SQL.

CONNECT TO :p_conexion AS 'C1'

ENDEXEC.

IF SY-SUBRC EQ 0.

EXEC SQL.

SET CONNECTION 'C1'

ENDEXEC.

EXEC SQL PERFORMING save_data.

SELECT COD_CGE, COD_SAX, TO_CHAR(FECHA,'YYYYMMDD'),

VALOR, HORA, TIPO, PAIS, SOCIEDAD

INTO :wa_serv_aux

FROM TEST_TABLE

WHERE FECHA >= TO_DATE( :W_FEC1 , 'YYYY/MM/DD')

AND FECHA <= TO_DATE( :W_FEC2 , 'YYYY/MM/DD')

ENDEXEC.

EXEC SQL.

ROLLBACK

ENDEXEC.

EXEC SQL.

DISCONNECT 'C1'

ENDEXEC.

In the form save_data i insert the work area into the internal table.

And that`s all. The most important thing is to configurate the tnsnames.ora file on the server side.

Note:- One small suggestion, Before Posting Thread in SDN, search for the related topics in SDN forums and then post your thread, it would be helpfull for everybody.

Regards:-

Santosh

Message was edited by: Santosh (INDIA)

Re: Connection to an external database.

Former Member — Mon, 18 Sep 2006 12:20:22 GMT

It is true that all connection to external databases are maintained in table DBCON.

Can you paste your code? You must be using a Connection to connect to the external database.

Regards,

Raj

Re: Connection to an external database.

Former Member — Mon, 18 Sep 2006 12:23:22 GMT

Hi!

Everything depends on the database platforms.

For example if your SAP is based on Oracle database, and the external is based on an Oracle database, you can set up a "database-link" between the two systems. In this case you don't have to fill the DBCON table, just call the native SQL within ABAP.

If you want to reach from SAP an external MSSQL server, you have to specify the DBCON table like this:

CON_NAME: logical name, can be anything

DBMS: MSS

USERNAME: MSSQL_USER (must be defined in the MSSQL)

PASSWORD: password of MSSQL_USER

CONENV: MSSQL_SERVER=1.2.3.4 MSSQL_DBNAME=MSSQL_SERVER

After setting up the correct settings you can use the native SQL to reach the tables within the database defined in the CON_NAME.

You can refer the following SAP notes in this task: 323151 (Multi-connect), and 178949 (MSSQL)

Best regards

Tamá

Re: Connection to an external database.

Former Member — Tue, 19 Sep 2006 04:07:34 GMT

Hi Tamás,

Can u pls tell how is that database link maintained? As you have said SAP database and the external database are on oracle platform only.