Native SQL in S4, replace EXEC SQL statement with this method

[former_member403976]

Native SQL in S4 Reports without using EXEC SQL statement and having a better performance when using this FM

By

Kingsley Hopper

03 - July - 2020

Hi All

How are you doing?

I hope everyone is doing well.

Today we are going to discuss about executing Native SQL statements from Report program , there are many instances which I come across during my ABAP journey where we dont get access to development in Hana studio/Hana DB access . So, for executing the Native SQL statements from our report program first we establish connection via EXEC SQL statement.

Example:

DATA: gr_err TYPE REF TO cx_sy_native_sql_error,

lv_err_text TYPE string.



TRY.

EXEC SQL.

SET CONNECTION DEFAULT

ENDEXEC.

CATCH cx_sy_native_sql_error INTO gr_err.

lv_err_text = gr_err->get_text( ).

MESSAGE lv_err_text TYPE 'I' DISPLAY LIKE 'S' .

ENDTRY.

 

and then we write our select query or any other Native SQL statements inside EXEC SQL statements,

Sample Native SQL select query :

TYPES:

BEGIN OF ty_grant_roles,

grantee TYPE char30,

role_name TYPE char200,

END OF ty_grant_roles.



DATA: wa_grant_roles TYPE ty_grant_roles,

wa_users TYPE ty_users.



TRY.

EXEC SQL PERFORMING loop_and_write_output.

SELECT grantee,

role_name

INTO :wa_GRANT_ROLES

FROM GRANTED_ROLES WHERE GRANTEE NOT IN ( SELECT GRANTEE FROM GRANTED_ROLES WHERE ROLE_NAME <> 'PUBLIC' )

ORDER BY GRANTEE

ENDEXEC.

CATCH cx_sy_native_sql_error INTO gr_err.

lv_err_text = gr_err->get_text( ).

MESSAGE lv_err_text TYPE 'I' DISPLAY LIKE 'S' .

ENDTRY.



FORM loop_and_write_output.

APPEND wa_grant_roles TO lt_grant_roles.

ENDFORM.

 

In the above example we are using the local structure :wa to get the data from the select query and using it in our report program to do our manipulations.

There has been a scenario in which we had to change the access control of Users which are available in Hana DB , we will have to drop user , alter user privileges from the report using Native SQL statements , some may ask DBACOCKPIT does its job but not all users are given that access to execute any/all queries in Hana DB , hence in the report program we can restrict only certain queries.

The issue I was facing while using DROP USER and ALTER USER statement was that 'SQL statement syntax error' , DROP USER <USER_NAME> is the syntax .but we were unable to use a local variable within this statement like :LV_USER_NAME

DROP USER EXEC SQL sample statement :

TRY.

EXEC SQL.

DROP USER :LV_USER_NAME

ENDEXEC.

CATCH cx_sy_native_sql_error INTO err.

err_text = err->get_text( ).

ENDTRY.

 

But we were able to successfully execute the statement if I had used below statement without using local variable and directly giving the Username in the statement.

TRY.

EXEC SQL.

DROP USER ABCDUSER

ENDEXEC.

CATCH cx_sy_native_sql_error INTO err.

err_text = err->get_text( ).

ENDTRY.

So, We had gone with another method to handle this issue to execute certain queries without using EXEC SQL statement which is using the FM : RSDU_EXEC_SQL_DB4

In this FM , we will not require to establish the connection even we can directly give the Native SQL query and the FM handles all , we need not use TRY,CATCH statements too , everything is handled via exceptions in this FM. We want a solution to execute DROP USER and ALTER USER query with our local variable and we were able to achieve using this FM.

Here is the below sample code of that FM to DROP USER and ALTER USER

data: ls_sqlstatement type RSDR0_S_ABAPSOURCE,

lt_sqlstatement type RSDR0_T_ABAPSOURCE.

DATA: lv_error(72) type c,

lv_processed(1) type c,

lv_status(150) type c.



*********DROP USER**********************

refresh : lt_sqlstatement.

clear : ls_sqlstatement,lv_error,lv_processed.

CONCATENATE 'DROP USER' lv_user_name INTO ls_sqlstatement-line SEPARATED BY space.

append ls_sqlstatement to lt_sqlstatement.

CALL FUNCTION 'RSDU_EXEC_SQL_DB4'

 EXPORTING

 I_T_STMT = lt_sqlstatement

 IMPORTING

 E_SQLERRTXT = lv_error

 CHANGING

 C_PROCESSED = lv_processed

 EXCEPTIONS

 SQL_ERROR = 1

 STATEMENT_TOO_COMPLEX = 2

 NO_STATEMENT = 3

 INHERITED_ERROR = 4

 DUPREC = 5

 OBJ_EXISTS = 6

 OBJ_NOT_FOUND = 7

 OTHERS = 8

 .

IF sy-subrc <> 0.

** Implement suitable error handling here

ENDIF.

if lv_processed is not initial.

 lv_status = 'Successfully dropped the user'.

endif.

*********DROP USER**********************



*********ALTER USER ENABLE PASSWORD**********************

refresh : lt_sqlstatement.

clear : ls_sqlstatement,lv_error,lv_processed.

CONCATENATE 'ALTER USER' lv_user_name 'ENABLE PASSWORD' INTO ls_sqlstatement-line SEPARATED BY space.

append ls_sqlstatement to lt_sqlstatement.

CALL FUNCTION 'RSDU_EXEC_SQL_DB4'

 EXPORTING

 I_T_STMT = lt_sqlstatement

 IMPORTING

 E_SQLERRTXT = lv_error

 CHANGING

 C_PROCESSED = lv_processed

 EXCEPTIONS

 SQL_ERROR = 1

 STATEMENT_TOO_COMPLEX = 2

 NO_STATEMENT = 3

 INHERITED_ERROR = 4

 DUPREC = 5

 OBJ_EXISTS = 6

 OBJ_NOT_FOUND = 7

 OTHERS = 8

 .

IF sy-subrc <> 0.

** Implement suitable error handling here

ENDIF.

if lv_processed is not initial.

 lv_status = 'Successfully enabled password for the user'.

endif.

*********ALTER USER ENABLE PASSWORD**********************

We can even check if the SQL which we have passed has any error via the importing parameters , also DEFAULT connection is enabled.

In this FM we will be able to pass multiple Native SQL statement at once and execute it.

Summary

---

In this document we have studied how to make changes in the Hana DB via Native SQL from report program , also we have studied how to make changes without having any privilege in Hana DB level.Also we have seen an example on how to use syntax DROP USER and ALTER USER with the above FM (RSDU_EXEC_SQL_DB4) .