Skip to Content

Introduction

Excited to try out ABAP on HANA, I set up an Application Server (ABAP 7.02) in my local system. HANA One was set up in Amazon Web Services (AWS). My local machine had HANA Studio, HANA client installed, and was able to connect to HANA instance. Everything seemed to be going so well, until I tried accessing a table (SFLIGHT) from ABAP. I got an ABAP dump (DBIF_RSQL_TABLE_UNKNOWN) .

This article discusses how I was able to get around this issue, though this may not be the ideal solution.  But, to experiment with HANA tables, hopefully, this post will get you started.

Articles I followed to get through Pre-Requisites of this Post

Set up HANA developer edition on Amazon Web Services

Excellent  post by Juergen Schmerder on how to set up HANA in AWS cloud.

Get your own SAP HANA, developer edition on Amazon Web Services

Install Netweaver AS with ABAP 7.02

Excellent posts by John  Moy on how to set up Netweaver AS. My set up different in that, I did not use AWS, but my own local machine, running Windows 8 Professional (Hey.. it’s a monster Alienware machine designed for high performance, Gaming, Graphics and Development – It can run the Application Server J without any problems! )

Installing Trial SAP NetWeaver Gateway 2.0 SP04 with ABAP 7.02 on AWS – Part 1

Installing Trial SAP NetWeaver Gateway 2.0 SP04 with ABAP 7.02 on AWS – Part 2

Installing Trial SAP NetWeaver Gateway 2.0 SP04 with ABAP 7.02 on AWS – Part 3

Setting up ABAP on HANA

Very informative post by Thorsten Franz on configuring secondary database to access HANA. This post also includes importing SFLIGHT data into HANA instance.

Experience the Magic: How to Setup Your Own ABAP on HANA in the Cloud

The Problem

After instructing the ABAP program to use remote “SFLIGHT” connection, I tried executing the Z-report program.

SELECT * FROM SFLIGHT CONNECTION ('SFLIGHT')
           INTO TABLE lt_sflight

However, I promptly received a dump, stating that there was no table named “SFLIGHT”.

Dump_screenshot.jpg

My DBCON table setup from SM30 (as detailed in Experience the Magic: How to Setup Your Own ABAP on HANA in the Cloud) was as follows:

DBCON.jpg

A careful look at the HANA catalog tells us that SFLIGHT table is present inside SFLIGHT schema.

Catalogs.jpg

It should be noted that, at this time, there are no tables inside SYSTEM schema. Problem is, when we use the SFLIGHT DB connection (as configured in DBCON in screen shot above), the schema that is searched for table is the same as what is specified in “User Name”. So, in this case, when ABAP statement “SELECT * FROM SFLIGHT” is executed, the assumption is, SFLIGHT table is available within “SYSTEM” schema.

Failed Attempts to Solve the Problem

Set Up a new user named “SFLIGHT” in HANA, and configure DBCON to use this new UserID

Since it appeared that the “User Name” in DBCON should match the schema name, I decided to create a new user. However, this failed, because the schema name “SFLIGHT” was already present, as is shown in the screen shot of error message:

SFLIGHT_User_creation_error.jpg

Use Fully Qualified Table Name in ABAP

In HANA using SQL script, we can fully qualify a table using “<Schema Name>.<Table Name>”. So, to access “SFLIGHT” table within SFLIGHT schema, I tried using the following SELECT statement:

SELECT * FROM ('SFLIGHT.SFLIGHT') CONNECTION ('SFLIGHT')
           INTO TABLE lt_sflight

While the above did compile, when running the report, I got the following ABAP Dump:

Dump_Join.jpg

Solution/What Worked (For Now)…

The solution to this issue would be to be able to specify the fully qualified table name (including schema name), in this case, “SFLIGHT.SFLIGHT”, in the SELECT statement.

Since I was unable to do this, my next attempt was to copy the table SFLIGHT from its original schema (SFLIGHT), to SYSTEM schema. I used the following SQL Script:

CopySchemaTable.jpgAs shown in the execution result, the script created the new table, and copied all the data into SYSTEM.SFLIGHT. Most of the entries in table have client ID (MANDT) set to 300. However, my application server after installation only had client “001” (I have very little BASIS knowledge…so, one of my TO DOs is to figure out how to set up other clients). There were indeed couple of entries with client ID = 001.

New_schema_Contents.jpg

Now, for moment of truth…. I came back to the ABAP program. Here is the code that was executed:

REPORT  zgopal_hana_test.
DATA:
  lt_sflight TYPE TABLE OF  sflight,
  lr_salv    TYPE REF TO    cl_salv_table.
START-OF-SELECTION.
  SELECT * FROM sflight CONNECTION ('SFLIGHT')
           INTO TABLE lt_sflight
    .
  CALL METHOD cl_salv_table=>factory
    IMPORTING
      r_salv_table = lr_salv
    CHANGING
      t_table      = lt_sflight.
  lr_salv->display( ).

And here are the results:

Execution_Result.jpg

The Begining….

While I was able to access data from HANA using ABAP, I am pretty sure, what I described here, is NOT a practical solution.

The question remains… “How do I access data from different schemas”?

But for now, with access to data, its a  step forward for me in terms of combining ABAP and power of HANA, esp for heavy data intensive calculations. It will be amazing to see all the computations being handled in HANA, and ABAP, PHP, Android, Joomla components, HTML5, iOS…etc receiving the computed result set….

How do I do it??? Well.. the search has begun, and with so many experts in SCN, I am sure its just a matter of time.

To report this post you need to login first.

11 Comments

You must be Logged on to comment or reply to a post.

  1. Yeu Sheng Teo

    Hi

    You can consider to use ADBC to extract table record using the secondary DB connection.

    The SQL statement format you can used: SELECT * from “schema_name”.table”.

    Regards.

    YS

    (0) 
    1. Gopal Nair Post author

      Hi Yeu,

        The syntax for specifying a table within a schema in SQL is indeed “schema_name”.”table”. However, in ABAP, the double quotes (“) represents start of a comment, and the entire line will be commented out, resulting in a syntax error during ABAP program activation.

      (0) 
      1. Yeu Sheng Teo

        Hi Nair,

        Using ADBC you will need to specify the native SQL query statement before executing it.

        Something like:

        TRY.

        * Get SQL Connection
            lo_connect = cl_sql_connection=>get_connection( con_name = ‘SFLIGHT’ ).

        * Instantiate SQL Query
            CREATE OBJECT lo_sql
              EXPORTING
                con_ref = lo_connect.

        * Build the SQL Query
            l_sql = |SELECT * |
                       && |FROM “SFLIGHT”.”SFLIGHT” |.

        ENDTRY.

        So you fetch table record from other schema as long as the DB connection user has access to that table.

        Similiar article for reading http://scn.sap.com/community/hana-in-memory/blog/2012/04/11/test#comment-356075.

        Regards.

        YS

        (0) 
  2. Jens Weiler

    Hi,

    please check, that your system user has access to the schema “sflight”.

    The system user does not automatically get access to newly created schemas.

    You must logon as “sflight” user and assign system user access to the “sflight” schema.

    Afterwards access to sflight schema and the table should work ! 

    PS: It is also not recommended to use SYSTEM user and if you have to use the system user also do not create or move tables to “system” schema.

    Cheers

    Jens

    (0) 
    1. Gopal Nair Post author

      Hi Jens,

        Thanks for the comment. I did try creating a new SFLIGHT user (see failed attempts section in the post). I also did assign access to SFLIGHT schema and table to SYSTEM user, but that did not solve the issue.

      I agree with you that SYSTEM user should not be used in a real world scenario. However, in this case, I am the only one using the HANA instance, for learning purposes.

      (0) 
  3. Frédéric Cincet

    Hi Gopal,

    Thanks for sharing.

    I was just working on the same case and ended up with the same unsatisfying solution (copy the tables to the only schema I can access).

    I just can’t believe we can’t do this with open SQL (be able to specify the wanted schema at the connection level or at the abap level).

    Any improvement on this subject in the last versions ?

    Regards,

    FCI

    (0) 
  4. Fernando Da Ros

    Hi guys,

    There’s a third solution for this without copying the table.

    Create a synonym (public or for user dbcon account) pointing to original schema so in this case you don’t need to copy it but you can access (if you have the rights, of course).

    Regards, Fernando Da Rós

    (0) 
    1. Frédéric Cincet

      Thanks Fernando. This is working like a charm and clearly a better option. Still, you have to create your synonym.

      In my particular case, my schema was generated by SLT (so was the associated password and user). As I don’t know the password (and don’t want to change it), I could not create a db connection with this user on my ECC.

      I finally succeed in creating this connection by copying the crypted password from the dbcon content in my SLT to my dbcon content in my ECC (ABAP update).

      Regards,

      Frederic

      (0) 
      1. Fernando Da Ros

        [OFF-TOPIC] just for my curiosity…

        Hmmm, another trick… I thought that crypted password has additional infos to works only on system which encrypted it. You got it from SLT machine and put it on ECC machine right?

        Regards, Fernando Da Rós

        (0) 
        1. Frédéric Cincet

          I was afraid of something like that but, no, the password works on both system.

          Note that my SLT and ECC have the same installation number (I may be in a particular situation)…

          Regards,

          (0) 

Leave a Reply