In a previous article, we learned the fundamentals of HANA Modeling. This is the 4th Part in series on SAP HANA. This article will explain the concept of Database Connectivity.
From this blog, you'll learn:
1. Difference between Native HANA SQL and Open SQL
2. What is ADBC in SAP HANA?
3. Creating ADBC with select-options
So let's begin.
1. Difference between Native HANA SQL and Open SQL
2. What is ADBC in SAP HANA?
3. Creating ADBC with select-options
So let's begin.
Difference between Native HANA SQL and Open SQL
Here arises a question.
If native SQL is better than Open SQL, why is there a need for an advanced concept of ADBC?
What is ADBC in SAP HANA
ABAP DATABASE CONNECTION (ADBC) is an object-oriented API.
ADBC speeds up the connection to the database. It also transfers the Native SQL that is executed at the database layer swiftly and in an organized manner.
Important classes to be used in SAP HANA ADBC
- CL_SQL_CONNECTION=>GET_CONNECTION.
- CL_SQL_STATEMENT->EXECUTE_QUERY.
- CL_SQL_RESULT_SET->SET_PARAM_TABLE - ->NEXT_PACKAGE.
- GET_CONNECTION is a static method that has 3 parameters.
- CON_NAME: Single character flag.
- Sharable: General flag.
- CON_REF: Connection object
- EXECUTE_QUERY is an instance method that has 3 parameters.
- Statement: Selected statement being executed.
- HOLD_CURSOR: Flag default value (space)
- RESULT_SET: Database cursor.
- SET_PARAM_TABLE is an instance method that has 3 parameters.
- ITAB_REF: Reference to the output variable.
- CORREPONDING_FIELDS: List of columns of the internal table.
- LOB_FIELDS: List of LOB fields.
- NEXT_PACKAGE is an instance method that has 2 parameters.
- UPTO: Maximum number of data records to read.
- ROWS_SET: Number of Data records read.
Below are the 8 steps which we have to follow in every ABAP Database Connectivity.
Key points to remember while working with ADBC.
- ADBC does not handle client implicitly. Hence do not forget to specify the client explicitly in the WHERE clause, join conditions.
- There are no syntax checks in Native SQL statements. Hence ensure to handle the exception CX_SQL_EXCEPTION.
- No sorted or Hashed tables are allowed. Hence uses only standard tables.
- Do not forget to close the query.
Creating ADBC with select-options
- Navigate to the ABAP perspective and create a 'New' ABAP Program.
- Below is the output from 'SFLIGHT' table using ADBC.
OUTPUT:
NOTE: ADBC needs to be done in an object-oriented approach. But here I have used subroutine to make the code easier for the beginners.
Below is the code snippet to retrieve flight details from SFLIGHT TABLE using ADBC.
LOGIC:
*&---------------------------------------------------------------------*
*& Report zrds_sflight_adbc
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zrds_sflight_adbc.
TYPES: BEGIN OF ty_sflight,
mandt TYPE s_mandt, "Client
carrid TYPE s_carr_id, "Airline code
connid TYPE s_conn_id, "Flight connection number
fldate TYPE s_date, "Flight date
price TYPE s_price, "Airfare
currency TYPE s_currcode, "Local currency of airline
planetype TYPE s_planetye, "Aircraft type
seatsmax TYPE s_seatsmax, "Maximum Capacity in economy class
seatsocc TYPE s_seatsocc, "Seats occupied in economy class
paymentsum TYPE s_sum, "Total of current bookings
seatsmax_b TYPE s_smax_b, "Maximum Capacity in business class
seatsocc_b TYPE s_socc_b, "Seats occupied in business class
END OF ty_sflight.
*Data declaration
DATA: lo_con TYPE REF TO cl_sql_connection,
lo_sql TYPE REF TO cl_sql_statement,
lv_error_text TYPE string,
lv_sql TYPE string,
lo_result TYPE REF TO cl_sql_result_set,
lr_data TYPE REF TO data,
ls_result TYPE ty_sflight,
lt_flight TYPE STANDARD TABLE OF ty_sflight.
SELECT-OPTIONS : s_carrid FOR ls_result-carrid.
PERFORM make_db_connection. "To start the db connection.
PERFORM initiate_sql_statement.
PERFORM native_sql_string. "To write the select query in native SQL
PERFORM issue_native_sql_call.
PERFORM assign_target_result.
PERFORM retrieve_result_set.
PERFORM close_query. " TO close the query.
PERFORM close_db_connection. " TO close the db connection.
PERFORM display_result. "To display output.
FORM make_db_connection.
lo_con = cl_sql_connection=>get_connection( ). "Parameter is empty since we are using the default HANA db.
ENDFORM.
FORM initiate_sql_statement.
CREATE OBJECT lo_sql
EXPORTING
con_ref = lo_con.
ENDFORM.
FORM native_sql_string.
* In line data declaration and converting selection option to a where clause string for s_carrid
DATA(lr_seltab) = cl_lib_seltab=>new( it_sel = s_carrid[] ).
DATA(lv_where_clause_sel) = lr_seltab->sql_where_condition( iv_field = 'CARRID' ).
lv_sql = | SELECT mandt, carrid, connid, fldate, price, currency, planetype, seatsmax, seatsocc, |
&& | paymentsum, seatsmax_b, seatsocc_b from SFLIGHT WHERE |
&& | MANDT = '{ sy-mandt }' |
&& | AND { lv_where_clause_sel } |
&& | ORDER BY CARRID |.
ENDFORM.
FORM issue_native_sql_call.
lo_result = lo_sql->execute_query( lv_sql ).
ENDFORM.
FORM assign_target_result.
GET REFERENCE OF lt_flight INTO lr_data.
lo_result->set_param_table(
EXPORTING
itab_ref = lr_data
* corresponding_fields =
* lob_fields =
).
ENDFORM.
FORM retrieve_result_set.
lo_result->next_package(
* EXPORTING
* upto = 0
* write_syslog_on_error = abap_true
* RECEIVING
* rows_ret =
).
ENDFORM.
FORM close_query.
lo_result->close( ).
ENDFORM.
FORM close_db_connection.
lo_con->close( ).
ENDFORM.
FORM display_result.
cl_demo_output=>display( lt_flight ).
ENDFORM.
NOTE: Here I have not used any exceptions.
NOTE: To use the pretty printer, hit shift + F1.
I hope the illustrations above help in better understanding the concept of ADBC.
In the upcoming tutorial, we will learn about ABAP Managed Database Procedure (AMDP)
Difference between Native HANA SQL and Open SQL
Here arises a question.
If native SQL is better than Open SQL, why is there a need for an advanced concept of ADBC?
What is ADBC in SAP HANA
ABAP DATABASE CONNECTION (ADBC) is an object-oriented API.
ADBC speeds up the connection to the database. It also transfers the Native SQL that is executed at the database layer swiftly and in an organized manner.
Important classes to be used in SAP HANA ADBC
- CL_SQL_CONNECTION=>GET_CONNECTION.
- CL_SQL_STATEMENT->EXECUTE_QUERY.
- CL_SQL_RESULT_SET->SET_PARAM_TABLE - ->NEXT_PACKAGE.
- GET_CONNECTION is a static method that has 3 parameters.
- CON_NAME: Single character flag.
- Sharable: General flag.
- CON_REF: Connection object
- EXECUTE_QUERY is an instance method that has 3 parameters.
- Statement: Selected statement being executed.
- HOLD_CURSOR: Flag default value (space)
- RESULT_SET: Database cursor.
- SET_PARAM_TABLE is an instance method that has 3 parameters.
- ITAB_REF: Reference to the output variable.
- CORREPONDING_FIELDS: List of columns of the internal table.
- LOB_FIELDS: List of LOB fields.
- NEXT_PACKAGE is an instance method that has 2 parameters.
- UPTO: Maximum number of data records to read.
- ROWS_SET: Number of Data records read.
Below are the 8 steps which we have to follow in every ABAP Database Connectivity.
Key points to remember while working with ADBC.
- ADBC does not handle client implicitly. Hence do not forget to specify the client explicitly in the WHERE clause, join conditions.
- There are no syntax checks in Native SQL statements. Hence ensure to handle the exception CX_SQL_EXCEPTION.
- No sorted or Hashed tables are allowed. Hence uses only standard tables.
- Do not forget to close the query.
Creating ADBC with select-options
- Navigate to the ABAP perspective and create a 'New' ABAP Program.
- Below is the output from 'SFLIGHT' table using ADBC.
OUTPUT:
NOTE: ADBC needs to be done in an object-oriented approach. But here I have used subroutine to make the code easier for the beginners.
Below is the code snippet to retrieve flight details from SFLIGHT TABLE using ADBC.
LOGIC:
*&---------------------------------------------------------------------*
*& Report zrds_sflight_adbc
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zrds_sflight_adbc.
TYPES: BEGIN OF ty_sflight,
mandt TYPE s_mandt, "Client
carrid TYPE s_carr_id, "Airline code
connid TYPE s_conn_id, "Flight connection number
fldate TYPE s_date, "Flight date
price TYPE s_price, "Airfare
currency TYPE s_currcode, "Local currency of airline
planetype TYPE s_planetye, "Aircraft type
seatsmax TYPE s_seatsmax, "Maximum Capacity in economy class
seatsocc TYPE s_seatsocc, "Seats occupied in economy class
paymentsum TYPE s_sum, "Total of current bookings
seatsmax_b TYPE s_smax_b, "Maximum Capacity in business class
seatsocc_b TYPE s_socc_b, "Seats occupied in business class
END OF ty_sflight.
*Data declaration
DATA: lo_con TYPE REF TO cl_sql_connection,
lo_sql TYPE REF TO cl_sql_statement,
lv_error_text TYPE string,
lv_sql TYPE string,
lo_result TYPE REF TO cl_sql_result_set,
lr_data TYPE REF TO data,
ls_result TYPE ty_sflight,
lt_flight TYPE STANDARD TABLE OF ty_sflight.
SELECT-OPTIONS : s_carrid FOR ls_result-carrid.
PERFORM make_db_connection. "To start the db connection.
PERFORM initiate_sql_statement.
PERFORM native_sql_string. "To write the select query in native SQL
PERFORM issue_native_sql_call.
PERFORM assign_target_result.
PERFORM retrieve_result_set.
PERFORM close_query. " TO close the query.
PERFORM close_db_connection. " TO close the db connection.
PERFORM display_result. "To display output.
FORM make_db_connection.
lo_con = cl_sql_connection=>get_connection( ). "Parameter is empty since we are using the default HANA db.
ENDFORM.
FORM initiate_sql_statement.
CREATE OBJECT lo_sql
EXPORTING
con_ref = lo_con.
ENDFORM.
FORM native_sql_string.
* In line data declaration and converting selection option to a where clause string for s_carrid
DATA(lr_seltab) = cl_lib_seltab=>new( it_sel = s_carrid[] ).
DATA(lv_where_clause_sel) = lr_seltab->sql_where_condition( iv_field = 'CARRID' ).
lv_sql = | SELECT mandt, carrid, connid, fldate, price, currency, planetype, seatsmax, seatsocc, |
&& | paymentsum, seatsmax_b, seatsocc_b from SFLIGHT WHERE |
&& | MANDT = '{ sy-mandt }' |
&& | AND { lv_where_clause_sel } |
&& | ORDER BY CARRID |.
ENDFORM.
FORM issue_native_sql_call.
lo_result = lo_sql->execute_query( lv_sql ).
ENDFORM.
FORM assign_target_result.
GET REFERENCE OF lt_flight INTO lr_data.
lo_result->set_param_table(
EXPORTING
itab_ref = lr_data
* corresponding_fields =
* lob_fields =
).
ENDFORM.
FORM retrieve_result_set.
lo_result->next_package(
* EXPORTING
* upto = 0
* write_syslog_on_error = abap_true
* RECEIVING
* rows_ret =
).
ENDFORM.
FORM close_query.
lo_result->close( ).
ENDFORM.
FORM close_db_connection.
lo_con->close( ).
ENDFORM.
FORM display_result.
cl_demo_output=>display( lt_flight ).
ENDFORM.
*& Report zrds_sflight_adbc
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT zrds_sflight_adbc.
TYPES: BEGIN OF ty_sflight,
mandt TYPE s_mandt, "Client
carrid TYPE s_carr_id, "Airline code
connid TYPE s_conn_id, "Flight connection number
fldate TYPE s_date, "Flight date
price TYPE s_price, "Airfare
currency TYPE s_currcode, "Local currency of airline
planetype TYPE s_planetye, "Aircraft type
seatsmax TYPE s_seatsmax, "Maximum Capacity in economy class
seatsocc TYPE s_seatsocc, "Seats occupied in economy class
paymentsum TYPE s_sum, "Total of current bookings
seatsmax_b TYPE s_smax_b, "Maximum Capacity in business class
seatsocc_b TYPE s_socc_b, "Seats occupied in business class
END OF ty_sflight.
*Data declaration
DATA: lo_con TYPE REF TO cl_sql_connection,
lo_sql TYPE REF TO cl_sql_statement,
lv_error_text TYPE string,
lv_sql TYPE string,
lo_result TYPE REF TO cl_sql_result_set,
lr_data TYPE REF TO data,
ls_result TYPE ty_sflight,
lt_flight TYPE STANDARD TABLE OF ty_sflight.
SELECT-OPTIONS : s_carrid FOR ls_result-carrid.
PERFORM make_db_connection. "To start the db connection.
PERFORM initiate_sql_statement.
PERFORM native_sql_string. "To write the select query in native SQL
PERFORM issue_native_sql_call.
PERFORM assign_target_result.
PERFORM retrieve_result_set.
PERFORM close_query. " TO close the query.
PERFORM close_db_connection. " TO close the db connection.
PERFORM display_result. "To display output.
FORM make_db_connection.
lo_con = cl_sql_connection=>get_connection( ). "Parameter is empty since we are using the default HANA db.
ENDFORM.
FORM initiate_sql_statement.
CREATE OBJECT lo_sql
EXPORTING
con_ref = lo_con.
ENDFORM.
FORM native_sql_string.
* In line data declaration and converting selection option to a where clause string for s_carrid
DATA(lr_seltab) = cl_lib_seltab=>new( it_sel = s_carrid[] ).
DATA(lv_where_clause_sel) = lr_seltab->sql_where_condition( iv_field = 'CARRID' ).
lv_sql = | SELECT mandt, carrid, connid, fldate, price, currency, planetype, seatsmax, seatsocc, |
&& | paymentsum, seatsmax_b, seatsocc_b from SFLIGHT WHERE |
&& | MANDT = '{ sy-mandt }' |
&& | AND { lv_where_clause_sel } |
&& | ORDER BY CARRID |.
ENDFORM.
FORM issue_native_sql_call.
lo_result = lo_sql->execute_query( lv_sql ).
ENDFORM.
FORM assign_target_result.
GET REFERENCE OF lt_flight INTO lr_data.
lo_result->set_param_table(
EXPORTING
itab_ref = lr_data
* corresponding_fields =
* lob_fields =
).
ENDFORM.
FORM retrieve_result_set.
lo_result->next_package(
* EXPORTING
* upto = 0
* write_syslog_on_error = abap_true
* RECEIVING
* rows_ret =
).
ENDFORM.
FORM close_query.
lo_result->close( ).
ENDFORM.
FORM close_db_connection.
lo_con->close( ).
ENDFORM.
FORM display_result.
cl_demo_output=>display( lt_flight ).
ENDFORM.
NOTE: Here I have not used any exceptions.
NOTE: To use the pretty printer, hit shift + F1.
I hope the illustrations above help in better understanding the concept of ADBC.
In the upcoming tutorial, we will learn about ABAP Managed Database Procedure (AMDP)
1 Comments
Great content 🔥
ReplyDelete