topic Re: Querying an SQL Database from ABAP in Application Development and Automation Discussions

Querying an SQL Database from ABAP

Former Member — Thu, 28 Jun 2007 20:03:42 GMT

Is is possible from within ABAP in R/3 (or NetWeaver) to query a non-SAP database? For example, can I write ABAP code which will go after data in say a non SAP DB2 database at some remote location?

Kevin

Re: Querying an SQL Database from ABAP

Former Member — Thu, 28 Jun 2007 20:24:40 GMT

you can write native sql command.

do search native sql command.

Thanks

Seshu

Re: Querying an SQL Database from ABAP

Former Member — Thu, 28 Jun 2007 20:48:20 GMT

Hi,

You can write the logic, but NON sap db should be connected properly. You can write the logic in Native ABAP so that DB interface wont take care about the conversion.

For more information on native sql - check this - http://help.sap.com/saphelp_nw04s/helpdata/en/fc/eb3b8b358411d1829f0000e829fbfe/frameset.htm

regards,

reward for all useful answers.

Re: Querying an SQL Database from ABAP

Former Member — Fri, 29 Jun 2007 05:33:53 GMT

Hi,

But most recommended is dont use native SQL.

regards,

sasi

Re: Querying an SQL Database from ABAP

Former Member — Fri, 29 Jun 2007 05:40:00 GMT

Hi kevin,

Yes. You can querry a database directly using open sql.

EXEC SQL ' sql command '.

But the database connection should be ensured.

Award points if useful,

Aleem.

Re: Querying an SQL Database from ABAP

Former Member — Fri, 29 Jun 2007 16:17:17 GMT

OK, Thanks to those who responded, particularly Dj and Aleem. Now, next question. Can someone elaborate, or point me too more, on how to connect to this remote, non-SAP database to ensure the sql gets there? ("But the database connection should be ensured.")

Re: Querying an SQL Database from ABAP

Former Member — Fri, 29 Jun 2007 16:46:09 GMT

Check with below example code what i written :

Read the Mac-Pac transaction server

IF SAP_PO_NO GT SPACE.

EXEC SQL.

OPEN c1 FOR

SELECT CZORDQ

FROM Z_SAP_ED980AP1

WHERE CZORNO = :SAP_PO_NO

AND CZNORL = :SAP_REL_NO

AND CZPS = :ZWMS_GR_STRUCTURE-ITEM_TEXT

ENDEXEC.

DO.

EXEC SQL.

FETCH NEXT c1 INTO :HOLD_ORDER_QUANTITY

COUNT = COUNT + 1.

ENDEXEC.

IF sy-subrc <> 0.

EXIT.

ELSE.

HOLD_TOTAL_ORDER = HOLD_TOTAL_ORDER + HOLD_ORDER_QUANTITY.

ENDIF.

ENDDO.

EXEC SQL.

CLOSE c1

ENDEXEC.

READ_ED980AP1_1 = SY-SUBRC.

ENDIF.

EXEC SQL.

Talk to Basis or database guy,once your system is connected to non sap ,then you can write the code as above mentioned

Thanks

Seshu

Re: Querying an SQL Database from ABAP

Former Member — Mon, 02 Jul 2007 05:30:41 GMT

Connecting to an External database from SAP

Step 1: Create an entry for the External database in DBCON table using Trxn: DBCA.

Table: DBCON (Description of Database Connections)

Field Name Description Value (For: E.g.:)

CON_NAME Logical name for a database connection RAJ

DBMS Database system MSS

USER_NAME Database user <username>

PASSWORD Password for setting up the connection to the database <pwd>/<pwd>

CON_ENV Database-specific information for a database connection MSSQL_SERVER=depotserver MSSQL_DBNAME=HOF_INDORE

DB_RECO Availability type for an open database connect

Step 2: Now you can write code to connect to the external database…

Your Sample code can be something like this……

DATA: BEGIN OF wa,

c_locid(3),

c_locname(50),

c_locstate(5),

END OF wa.

EXEC SQL.

CONNECT TO 'RAJ' AS 'V'

ENDEXEC.

EXEC SQL.

SET CONNECTION 'V'

ENDEXEC.

*- Get the data from MS-SQL Server

EXEC SQL.

open C1 for

select

loc_id,

loc_name,

loc_state

from ho_loc_mast

ENDEXEC.

DO.

EXEC SQL.

FETCH NEXT C1 into :wa-c_locid, :wa-c_locname, :wa-c_locstate

ENDEXEC.

IF sy-subrc = 0.

PERFORM loop_output.

ELSE.

EXIT.

ENDIF.

ENDDO.

EXEC SQL.

CLOSE C1

ENDEXEC.

&----

*& Form LOOP_OUTPUT

&----

Output

----

FORM loop_output .

WRITE: /5 wa-c_locid, 10 wa-c_locname, 65 wa-c_locstate.

CLEAR wa.

ENDFORM. " LOOP_OUTPUT

Regards

Raj

Re: Querying an SQL Database from ABAP

Former Member — Tue, 07 Aug 2007 06:32:52 GMT

Hi All

Create an entry for the External database in DBCON table using T-Code: DBCO

(not DBCA)

Anees Ahmed

9886358645