Introduction
I introduced the topic of ABAP Secondary Database Connection and the various options for using this technology to access information in a HANA database from ABAP. Remember there are two scenarios where ABAP Secondary Database Connection might be used. One is when you have data being replicated from an ABAP based application to HANA. In this case the ABAP Data Dictionary already contains the definitions of the tables which you access with SQL statements.
The other option involves using HANA to store data gathered via other means. Maybe the HANA database is used as the primary persistence for completely new data models. Or it could be that you just want to leverage HANA specific views or other modeled artifacts upon ABAP replicated data. In either of these scenarios, the ABAP Data Dictionary won’t have a copy of the objects which you are accessing via the Secondary Database Connection. Without the support of the Data Dictionary, how can we define ABAP internal tables which are ready to receive the result sets from queries against such objects?
In this blog, I want to discuss the HANA specific techniques for reading the Catalog and also how the ABDC classes could be used to build a dynamic internal table which matches a HANA table or view. The complete source code discussed in this blog can be downloaded from the SCN Code Exchange.
HANA Catalog
The first task is figuring out how to read metadata about HANA tables and views. When access these objects remotely from ABAP, we need to be able to prepare ABAP variables or internal tables to receive the results. We can’t just declare objects with reference to the data dictionary like we normally would. Therefore we need some way to access the metadata which HANA itself stores about its tables, views, and their fields.
HANA has a series of Catalog objects. These are tables/views from the SYS Schema. Some of the ones which we will use are:
I introduced the topic of ABAP Secondary Database Connection and the various options for using this technology to access information in a HANA database from ABAP. Remember there are two scenarios where ABAP Secondary Database Connection might be used. One is when you have data being replicated from an ABAP based application to HANA. In this case the ABAP Data Dictionary already contains the definitions of the tables which you access with SQL statements.
The other option involves using HANA to store data gathered via other means. Maybe the HANA database is used as the primary persistence for completely new data models. Or it could be that you just want to leverage HANA specific views or other modeled artifacts upon ABAP replicated data. In either of these scenarios, the ABAP Data Dictionary won’t have a copy of the objects which you are accessing via the Secondary Database Connection. Without the support of the Data Dictionary, how can we define ABAP internal tables which are ready to receive the result sets from queries against such objects?
In this blog, I want to discuss the HANA specific techniques for reading the Catalog and also how the ABDC classes could be used to build a dynamic internal table which matches a HANA table or view. The complete source code discussed in this blog can be downloaded from the SCN Code Exchange.
HANA Catalog
The first task is figuring out how to read metadata about HANA tables and views. When access these objects remotely from ABAP, we need to be able to prepare ABAP variables or internal tables to receive the results. We can’t just declare objects with reference to the data dictionary like we normally would. Therefore we need some way to access the metadata which HANA itself stores about its tables, views, and their fields.
HANA has a series of Catalog objects. These are tables/views from the SYS Schema. Some of the ones which we will use are:
- SCHEMAS – A list of all Schemas within a HANA database. This is useful because once we connect to HANA via the Secondary Database Connection we might need to change from the default user Schema to another schema to access the objects we need.
- DATA_TYPES – A list of all HANA built-in data types. This can be useful when you need the detail technical specification of a data type used within a table or view column.
- TABLES – A list of all tables and their internal table ID. We will need that table ID to look up the Table Columns.
- TABLE_COLUMNS – A listing of columns in a Table as well as the technical information about them.
- VIEWS - A list of all views and their internal view ID. We will need that View ID to look up the View Columns. We can also read the View creation SQL for details about the join conditions and members of the view.
- VIEW_COLUMNS - A listing of columns in a View as well as the technical information about them.
Now reading these views from ABAP can be done exactly as we discussed in the previous blog. You can use the Secondary Database Connection and query them with ABDC, for example. Here is the code I use to query the SCHEMAS view:
gr_sql_con = cl_sql_connection=>get_connection( gv_con_name ).
create object gr_sql
exporting
con_ref = gr_sql_con.
data lr_result type ref to cl_sql_result_set.
lr_result = gr_sql->execute_query(
|select * from schemas| ).
data lr_schema type ref to data.
get reference of rt_schemas into lr_schema.
lr_result->set_param_table( lr_schema ).
lr_result->next_package( ).
lr_result->close( ).
Personally I figured it might be useful to have one utility class which can read from any of these various catalog views. You can download this class from here. Over the next few blogs in this series I will demonstrate exactly what I built up around this catalog utility.
ABAP Internal Tables from ABDC
I originally had the idea that I would read the TABLE_COLUMNS View from the HANA catalog and then use the technical field information to generate a corresponding ABAP RTTI and dynamic internal table. My goal was to make queries from tables which aren’t in the ABAP data dictionary much easier. As it turns out, I didn’t need to directly read this information from the catalog views because the ADBC already had functionality to support this requirement.
The ADBC result set object (CL_SQL_RESULT_SET), has a method named GET_METADATA.This returns an ABAP internal table with all the metadata about which every object you just queried. Therefore I could build a generic method which takes in any HANA Table or View and does a select single from it. With the result set from this select single, I could then capture metadata for this object.
METHOD get_abap_type.
DATA lr_result TYPE REF TO cl_sql_result_set.
lr_result = gr_sql->execute_query(
|select top 1 * from { obj_name_check( iv_table_name ) }| ).
rt_meta = lr_result->get_metadata( ).
lr_result->close( ).
ENDMETHOD.
For example if I run this method for my ABAP Schema on table SFLIGHT I get the following information back:
Of course the most value comes when you read an object which doesn’t exist in the ABAP Data Dictionary. For example, I could also read one of the HANA Catalog Views: SCHEMAS
This metadata might not seem like much information, but its enough to in turn generate an ABAP RTTI (RunTime Type Information) object. From the RTTI, I now can generate an ABAP internal table for any HANA table or view in only a few lines of code:
DATA lr_tabledescr TYPE REF TO cl_abap_tabledescr.
lr_tabledescr = cl_abap_tabledescr=>create(
p_line_type = me->get_abap_structdesc( me->get_abap_type( iv_table_name ) ) ).
CREATE DATA rt_data TYPE HANDLE lr_tabledescr.
This all leads up to a simple method which can read from any HANA table and return an ABAP internal table with the results:
METHOD get_abap_itab.
*@78\QImporting@ IV_TABLE_NAME TYPE STRING
*@78\QImporting@ IV_MAX_ROWS TYPE I DEFAULT 1000
*@7B\QReturning@ value( RT_DATA ) TYPE REF TO DATA
*@03\QException@ CX_SQL_EXCEPTION
DATA lr_result TYPE REF TO cl_sql_result_set.
IF iv_max_rows IS SUPPLIED.
lr_result = gr_sql->execute_query(
|select top { iv_max_rows } * from { obj_name_check( iv_table_name ) }| ).
ELSE.
lr_result = gr_sql->execute_query(
|select * from { obj_name_check( iv_table_name ) }| ).
ENDIF.
DATA lr_tabledescr TYPE REF TO cl_abap_tabledescr.
lr_tabledescr = cl_abap_tabledescr=>create(
p_line_type = me->get_abap_structdesc( me->get_abap_type( iv_table_name ) ) ).
CREATE DATA rt_data TYPE HANDLE lr_tabledescr.
lr_result->set_param_table( rt_data ).
lr_result->next_package( ).
lr_result->close( ).
ENDMETHOD.
CLOSING
Between the HANA Catalog objects and the ADBC functionality to read type information, I’ve now got all the pieces I need to perform dynamic queries against any HANA table or view. Ultimately I could use this functionality to build all kinds of interesting tools. In fact I’m already playing around with a generic catalog/data browser; but that’s something to look forward to in a future blog.
Source: scn.sap.com
No comments:
Post a Comment