How I solved ABAP dump, when trying to access HANA Table
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
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”.
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:
A careful look at the HANA catalog tells us that SFLIGHT table is present inside SFLIGHT schema.
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:
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:
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:
As 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.
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:
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.
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".
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.
Using ADBC you will need to specify the native SQL query statement before executing it.
* Get SQL Connection
lo_connect = cl_sql_connection=>get_connection( con_name = 'SFLIGHT' ).
* Instantiate SQL Query
CREATE OBJECT lo_sql
con_ref = lo_connect.
* Build the SQL Query
l_sql = |SELECT * |
&& |FROM "SFLIGHT"."SFLIGHT" |.
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.
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.
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.
Thanks for sharing the details
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 ?
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
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).
[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
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)...