topic Re: Dynamic table name in native SQL in Application Development and Automation Discussions

Dynamic table name in native SQL

Former Member — Thu, 12 Nov 2009 03:43:26 GMT

Hi,

How can i use dynamic table name in native SQL?

My req is to select data from a external database table , but the table name will be only poulated during runtime.

How can i acheive this?

Regards,

Arun.

Re: Dynamic table name in native SQL

Former Member — Thu, 12 Nov 2009 05:49:31 GMT

Hi,

You will probably find this Blog from Horst Keller very helpful: [ABAP Geek 15 - ADBC|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/15837] [original link is broken] [original link is broken] [original link is broken]; .. there's also a demo program called ADBC_DEMO which has examples too.

Jonathan

Re: Dynamic table name in native SQL

Former Member — Thu, 12 Nov 2009 06:36:31 GMT

Thanks for your reply...i will go through the blog...Is ADBC is the only way to do this or any other solution is there...Please advise...so that i can concentrate only on ADBC..

Re: Dynamic table name in native SQL

Former Member — Fri, 13 Nov 2009 06:06:18 GMT

The blog indicates that the "exec sql" won't accept dynamic table names but, technically, you could probably achieve something if you really needed by creating some logic in a [dynamic program|http://help.sap.com/saphelp_nw04/helpdata/en/9f/db996e35c111d1829f0000e829fbfe/content.htm] at runtime. But ADBC would seem to be the best way forward.

Jonathan

Re: Dynamic table name in native SQL

Former Member — Fri, 13 Nov 2009 07:28:27 GMT

Thanks for ur reply...i had used cl_sql_statement....but here also if i use a variable instead of field i cannot retreive ths data...

but if iam giving the table name and fieldname directly it is selecting the data...i had pasted the code below...Please help

TRY.
* Make a connection to the database. If parameter is empty, this is the
*standard connected database

      RF_CONNECTION = CL_SQL_CONNECTION=>GET_CONNECTION( ).

      RF_STATEMENT = RF_CONNECTION->CREATE_STATEMENT( ).

      RF_RESULTSET = RF_STATEMENT->EXECUTE_QUERY(

      'select :V_TABLE from ZODS_DELV_DUE_TBL'
      ).
* Get the data from the resultset.

      GET REFERENCE OF WA_BUKRS INTO DR_BUKRS.

      RF_RESULTSET->SET_PARAM_STRUCT( DR_BUKRS ).

      WHILE RF_RESULTSET->NEXT( ) > 0.

        WRITE: / WA_BUKRS.

      ENDWHILE.

* close the connection with the database.

      RF_CONNECTION->CLOSE( ).

    CATCH CX_SQL_EXCEPTION INTO RF_CX_SQL.

      TP_MESSAGE = RF_CX_SQL->GET_TEXT( ).

    CATCH CX_ROOT INTO RF_CX_ROOT.

      TP_MESSAGE = RF_CX_ROOT->GET_TEXT( ).

  ENDTRY.

Re: Dynamic table name in native SQL

Former Member — Mon, 16 Nov 2009 00:00:21 GMT

It should work OK - see demo below.

Jonathan


report zsdn_jc_adbc_test.

start-of-selection.
  perform demo_lookup.

form demo_lookup.
  data:
    l_error_msg          type string,
    ls_t001              type t001, "Company
    ls_t003              type t003. "Doc types
  perform dynamic_lookup
    using
      'T001'
    changing
      ls_t001
      l_error_msg.
  write: / l_error_msg.
  perform dynamic_lookup
    using
      'T003'
    changing
      ls_t003
      l_error_msg.
  write: / l_error_msg.
endform. 

form dynamic_lookup
  using
    i_tabname            type tabname
  changing
    os_data              type any
    o_error_msg          type string.
*
* Use ADBC to select data
*
  data:
    l_mandt_ref          type ref to data,
    l_result_ref         type ref to data,
    l_mandt              type symandt,
    l_tabname            type tabname,
    l_sql_statement      type string,
    lo_cx_root           type ref to cx_root,
    lo_cx_sql            type ref to cx_sql_exception,
    lo_connection        type ref to cl_sql_connection,
    lo_statement         type ref to cl_sql_statement,
    lo_result_set        type ref to cl_sql_result_set.

  clear: os_data, o_error_msg.

  get reference of l_mandt into l_mandt_ref.
  get reference of os_data into l_result_ref.

  l_mandt   = '222'.   "i.e. select from client 222
  l_tabname = i_tabname.

  try.
      lo_connection = cl_sql_connection=>get_connection( ).
      lo_statement  = lo_connection->create_statement( ).
* Set criteria for select:
      lo_statement->set_param( l_mandt_ref ).
      concatenate
        'select * from' l_tabname
        'where mandt = ?'
        into l_sql_statement separated by space.
* Execute
      call method lo_statement->execute_query
        exporting
          statement   = l_sql_statement
          hold_cursor = space
        receiving
          result_set  = lo_result_set.
* Get the data from the resultset.
      lo_result_set->set_param_struct( l_result_ref ).
      while lo_result_set->next( ) > 0.
        write: / os_data.
      endwhile.
* Tidy up:
      lo_result_set->close( ).
      lo_connection->close( ).
    catch cx_sql_exception into lo_cx_sql.
      o_error_msg = lo_cx_sql->get_text( ).
    catch cx_root into lo_cx_root.
      o_error_msg = lo_cx_root->get_text( ).
  endtry.
endform.