Execute Native SQL on HANA Database from ABAP using Secondary Database Connection

I have recently created a secondary database connection to SAP HANA database with a valid HANA database user authorization data using DBCO transaction code. This tutorial shows how to run native SQL or SQLScript using an ABAP program on a SAP HANA database.

Let's create a very simple Column Table on SAP HANA database using following Create Table DDL command.
Then populate new table with sample data using INSERT commands.


create column table TestData ( id int, code varchar(10) );



insert into TestData values (1,'ANK');

insert into TestData values (2,'ESK');

insert into TestData values (3,'IST');

insert into TestData values (4,'USK');



select * from TESTDATA;

Code

Let's now try to execute a native SQL SELECT query from ABAP program.
If you refer to ABAP tutorial on how to create database connection for SAP HANA database, you will be able to create a secondary connection to the target HANA database.

Then as seen in following ABAP program, first declare your type and internal table definitions in your code that will match to sample HANA database table.

Second section includes declaration of objects from SAP ADBC ABAP Database Call Interface package including object in Database Connection CL_SQL_CONNECTION class type.
This object with type CL_SQL_CONNECTION enables ABAP programmers to create a connection to HANA database and execute SQL statements via this connection.


TYPES: BEGIN OF lty_testdata,

 id TYPE i,

 code TYPE char10,

END OF lty_testdata.

DATA lt_testdata TYPE TABLE OF lty_testdata.

DATA lr_testdata TYPE REF TO data.



* ADBC Objects and variables

DATA lo_conn TYPE REF TO cl_sql_connection.

DATA lo_sql TYPE REF TO cl_sql_statement.

DATA lo_result TYPE REF TO cl_sql_result_set.

DATA lo_root_exception TYPE REF TO cx_root.

DATA lo_sql_exception TYPE REF TO cx_sql_exception.

DATA lv_sql TYPE string.



TRY .



* Create secondary database connection to SAP HANA database

lo_conn = cl_sql_connection=>get_connection( 'KODYAZ' ).



* Create SQL statement to be executed on the current database connection

lo_sql = lo_conn->create_statement( ).



* Execute SQL statement and fetch results

lv_sql = 'SELECT id, code FROM TestData'.

lo_result = lo_sql->execute_query( lv_sql ).



* Read results into internal table

GET REFERENCE OF lt_testdata INTO lr_testdata.

lo_result->set_param_table( lr_testdata ).

lo_result->next_package( ).



* Close database connection and free resources

lo_result->close( ).

lo_conn->close( ).



CATCH cx_sql_exception INTO lo_sql_exception .

" Add error handling

CATCH cx_root INTO lo_root_exception .

" Add error handling

ENDTRY.

Code

ABAP programmers can add following objects into theri debug list and check how their properties are set with each executed ABAP code line.

Please note that when NEXT_PAGE() method is executed, the internal table is populated with data from native SQL command execution on the HANA database.

Without closing the database connection, ABAP programmers can execute a second query in a similar way to shown in below.
Please note that the programmer should add type and data definitions for the second query too, which I did not repeat the similar ABAP code lines here.


* execute first sql statement

lv_sql = 'SELECT id, code FROM TestData'.

lo_result = lo_sql->execute_query( lv_sql ).



* read results into internal table

get reference of lt_testdata into lr_testdata.

lo_result->set_param_table( lr_testdata ).

lo_result->next_package( ).



************************************

* execute second sql statement

lv_sql = 'SELECT code FROM TestData'.

lo_result = lo_sql->execute_query( lv_sql ).



* read results into internal table

get reference of lt_data into lr_data.

lo_result->set_param_table( lr_data ).

lo_result->next_package( ).

Code