topic Re: Fetch data From SQL Server table to SAP Table... Problem in Code in Application Development and Automation Discussions

Fetch data From SQL Server table to SAP Table... Problem in Code

Former Member — Tue, 01 Jan 2019 12:58:32 GMT

Hi Friends,

Connection between SQL Server and SAP is working fine, I am sending data from SAP into SQL Table successfully, but when I am fetching data from SQL Server table into SAP internal table I am facing problem. Please suggest if you have any reference code. I want to know how to write select query to fecth multiple records from SQL into internal table.

Re: Fetch data From SQL Server table to SAP Table... Problem in Code

SimoneMilesi — Tue, 01 Jan 2019 19:32:35 GMT

Hello,

If you can tell us which is the error, it would be better and we can give you a better help.

Re: Fetch data From SQL Server table to SAP Table... Problem in Code

Former Member — Wed, 02 Jan 2019 06:03:41 GMT

Hi,

I got the solution.

https://blogs.sap.com/2008/10/27/using-abap-to-access-non-sap-databases/

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,
       END OF mona_cust_addr_type.
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.
ENDDO.
* Close connection to MONA
EXEC SQL.
  CLOSE dbcur
ENDEXEC.
* Reset to "default connection"
EXEC SQL.
  SET CONNECTION DEFAULT
ENDEXEC.
* Print 20 records
LOOP AT lt_custaddr INTO ls_custaddr.
  WRITE: /,
          ls_custaddr-cno,
          ls_custaddr-title,
          ls_custaddr-name,
          ls_custaddr-zip,
          ls_custaddr-city,
          ls_custaddr-state,
          ls_custaddr-address.
  IF sy-tabix > 20.
    EXIT.
  ENDIF.
ENDLOOP.

Re: Fetch data From SQL Server table to SAP Table... Problem in Code

matt — Wed, 02 Jan 2019 08:13:54 GMT

Better to use CL_SQL_STATEMENT and associated classes than EXEC SQL.