Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Use a variable as a table name with NATIVE SQL

Hi all,

I am trying to execute a SELECT statement in order to fetch data from an external Oracle DB table to SAP with the following instructions:

EXEC SQL.

SELECT cityfrom, cityto

INTO STRUCTURE :wa

FROM spfli

WHERE mandt = :sy-mandt AND

carrid = :p_carrid AND connid = :p_connid

ENDEXEC.

However, I need to indicate the external table name from a variable instead of the solution above. That is, declaring a variable and store the name of the table (e.q. spfli) in it. The resulting ABAP code would be something like:

EXEC SQL.

SELECT cityfrom, cityto

INTO STRUCTURE :wa

FROM <VARIABLE>

WHERE mandt = :sy-mandt AND

carrid = :p_carrid AND connid = :p_connid

ENDEXEC.

Does anybody know if is possible to do that?

If not, is there any other solution?

Thank you in advance

Former Member
Former Member replied

Yes, as Suhas said, you could use the ADBC API and his class CL_SQL_CONNECTION to achieve this...

Here is a small example:

 
PARAMETERS: p_carrid TYPE spfli-carrid,
                           p_connid TYPE spfli-connid.

DATA:
  l_con_ref      TYPE REF TO cl_sql_connection,
  l_stmt         TYPE string,
  l_stmt_ref     TYPE REF TO cl_sql_statement,
  l_dref         TYPE REF TO data,
  l_res_ref      TYPE REF TO cl_sql_result_set,
  l_col1         TYPE spfli-carrid,
  l_col2         TYPE spfli-connid,
  l_wa           TYPE spfli.

CONSTANTS:
  c_tabname  TYPE string VALUE 'SPFLI'.


* Create the connecction object
CREATE OBJECT l_con_ref.

* Create the SQL statement object
CONCATENATE 'select * from' c_tabname 'where carrid = ? and connid = ?'
       INTO l_stmt SEPARATED BY space.                           "#EC NOTEXT

l_stmt_ref = l_con_ref->create_statement( ).

* Bind input variables
GET REFERENCE OF l_col1 INTO l_dref.
l_stmt_ref->set_param( l_dref ).
GET REFERENCE OF l_col2 INTO l_dref.
l_stmt_ref->set_param( l_dref ).

* Set the input value and execute the query
l_col1 = p_carrid.
l_col2 = p_connid.

l_res_ref = l_stmt_ref->execute_query( l_stmt ).

* Set output structure
GET REFERENCE OF l_wa INTO l_dref.
l_res_ref->set_param_struct( l_dref ).

* Show result
WHILE l_res_ref->next( ) > 0.
  WRITE: / 'Result:', l_wa-carrid, l_wa-connid.
ENDWHILE.

* Close the result set object
l_res_ref->close( ).

Otherwise you can also use the FM DB_EXECUTE_SQL...

Kr,

m.

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question