Skip to Content

Open SQL using HANA as Secondary Connection

Hi,

when I run the below program I get the error “SAPSQL_INVALID_TABLENAME- A table name, specified in an SQL command, is unknown.”.

My question how could I access tables which are part of schema which is not default schema. i.e. I should access SAPECC.VBAK using open SQL .

how to overcome this syntax error ?

TYPES: BEGIN OF ty_vbak,
       vbeln TYPE vbak-vbeln,
       erdat TYPE vbak-erdat,
       erzet TYPE vbak-erzet,
      END OF ty_vbak,
      tt_vbak TYPE TABLE OF ty_vbak.
DATA: lr_data TYPE REF TO data,
      lt_vbak TYPE tt_vbak,
      lt_cols    TYPE adbc_column_tab,
      lv_table TYPE string VALUE 'SAPECC.VBAK'.

SELECT vbeln erdat erzet
  FROM ('SAPECC.VBAK')
CONNECTION ('HED')
INTO TABLE lt_vbak
WHERE erdat GT '20150101'.


IF lt_vbak[] IS NOT INITIAL.
  SORT lt_vbak.
  DATA lr_alv TYPE REF TO cl_salv_table.
  TRY.
      cl_salv_table=>factory(
        IMPORTING r_salv_table = lr_alv
        CHANGING  t_table      = lt_vbak ).
      lr_alv->display( ).
    CATCH cx_root.
  ENDTRY.
ENDIF.

Please note that I’m able to access above table SAPECC.VBAK from ABAP using ADBC and NATIVE SQL statements . However, I have hard time executing the existing OPEN SQL statements on HANA DB using connection ('HED')

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Mar 01, 2017 at 01:02 PM

    As a rule, Open SQL accesses only tables and views that are defined in the ABAP Dictionary in the ABAP database schema (whose name returned by FM DB_DBSCHEMA_CURRENT). For other schemas you need Native SQL (AMDP, ADBC, EXEC SQL) or secondary connections.

    If you use secondary connections to access other database tables or schemas, you must be aware, that there must also be a database table with the same name and identical type in ABAP Dictionary in the local AS ABAP. Open SQL assumes that the type information of these "remote" database tables or views corresponds exactly with that of the local database table.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Horst Keller

      Hi Horst.

      I'm playing with SFLIGHT demo data in HANA Express. All SFLIGHT demo objects in HDB are in SFLIGHT schema. Trying to connect from SAP NW with Open SQL using secondary DB connection...

      When I try to use select like this:

      select ... from ('SFLIGHT.SBOOK') 

      -> there's an error because SFLIGHT.SBOOK (obviously) does not exist in ABAP DD.

      Then I try another one:

      select ... from ('SBOOK') 

      -> I got a dump, because there's no SYSTEM.SBOOK table in HDB (dbms tries to find a table in default SYSTEM schema).

      So I have created another user in HANA DB and now I'm using secondary DB connection with this new user's credentials to connect SAP NetWeaver to HDB.

      The question is:

      How to set default schema SFLIGHT for this database user in HDB, so if I use the latter select, HANA dbms will lookup for table SBOOK in SFLIGHT schema?

      Thanks in advance.

      Kind regards,

      Adam