Open SQL using HANA as Secondary Connection

[Former Member]

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')

[retired_member]

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.

[Former Member]

Thank you Horst. Just to be on the same page. Our SAP 702 system is on Oracle DB and we already have all the database tables from Oracle DB SLTed to HANADB. However these tables are now part of the schema SAPECC on HANADB. Given that we have all the application logic already written in ABAP and our SAP system is still on ORACLE DB, we want to use existing code of SELECT statements on ABAP and just use connection(HED) statement to HANA for better performance.

Since all the tables are SLTed to SAPECC.<table> , do we have to rewrite all the select statements in NATIVE SQL for connecting to ?

[retired_member]

If you create a secondary connection with a user whose default schema is your schema, you can use Open SQL to access this schema. But you must not use the schema name with delimiter as you've done above.

See also https://blogs.sap.com/2012/04/11/test-16/

[aszymanski]

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