topic Re: SELECT into table within "EXEC SQL". in Application Development and Automation Discussions

SELECT into table within "EXEC SQL".

Former Member — Wed, 03 Jun 2009 07:21:27 GMT

Hello friends,

I try to fetch data from an external database into a internal table by using that coding:

CLEAR sy-subrc.

EXEC SQL PERFORMING append_itab.

SELECT T1.TYPE, T1.ROUTE, T1.SHIPID

INTO :_int_table

FROM SAP2LVS.SV_O060 AS T1

WHERE (T1.SHIPSTAT=30)

ORDER BY T1.SHIPID

ENDEXEC.

FORM APPEND_ITAB.

APPEND inttable.

ENDFORM.

Everything works well but my question is:

Is that the only way to read data from an external database into an internal table? For every selected record the subform is called and the internal table is extended by one record.

Isn't there any possibility like 'SELECT .. INTO TABLE ..' when using external database links with "EXEC SQL".

A coding like

EXEC SQL.

SELECT T1.TYPE, T1.ROUTE, T1.SHIPID

INTO TABLE :_int_table

FROM SAP2LVS.SV_O060 AS T1

WHERE (T1.SHIPSTAT=30)

ORDER BY T1.SHIPID

ENDEXEC.

Results in an error message an the source couldn't be activated.

Thanks & regards.

Philipp

Re: SELECT into table within "EXEC SQL".

former_member209217 — Wed, 03 Jun 2009 07:24:53 GMT

Hi,

I dont know why ur using Native SQL statements. Try to use Open SQL statements.

Regards,

Lakshman

Re: SELECT into table within "EXEC SQL".

Former Member — Wed, 03 Jun 2009 07:37:02 GMT

Hi,

sorry forgot to mention that it is not a ABAP database I'm selecting from. The table is hosted on a non SAP database. The db-connection is setup in the 'DBCON'.

Before executing the SELECT I've changed the db-connection.

DATA:

i_dbs TYPE dbcon_name VALUE '<entry in DBCON>'.

EXEC SQL.

CONNECT TO :i_dbs

ENDEXEC.

EXEC SQL.

SET CONNECTION :i_dbs

ENDEXEC.

EXEC SQL.

SELECT ..

ENDEXEC.

Regards Philipp

Re: SELECT into table within "EXEC SQL".

Former Member — Wed, 03 Jun 2009 08:34:04 GMT

Yes you can do it.

Its not much different than what you are doing.

First get the resultset into a cursor.

Second: loop at the cursor and append to internal table.



exec sql.
  open dbcursor for
  SELECT MATNR MAKTX
  FROM Z_MATERIALVIEW
  WHERE MANDT = :v_symandt
endexec.

DO.
  EXEC SQL.
    FETCH NEXT dbcursor INTO :it_output-matnr,
                                 :it_output-maktx
if sy-subrc eq 0.
APPEND it_output. clear it_output.
else.
exit.
endif.

  ENDEXEC.
ENDDO.

You need to do the error handling also..

There is a standard demo also using OOPs . In SE38 check the report ADBC_DEMO

Regards,

Abdullah