Question Re: Create ABAP program to send SQL command to another HANA DB in Technology Q&A

Create ABAP program to send SQL command to another HANA DB

saumel — Thu, 20 Feb 2025 13:47:07 GMT

Hello,

Is there any possibility to create an ABAP program for instance with a select screen where we can write a SQL command then execute this SQL command into x hana DB ?

Thank you for your help.

Re: Create ABAP program to send SQL command to another HANA DB

RaymondGiuseppi — Thu, 20 Feb 2025 14:10:54 GMT

Why not use the SQL Console for SAP HANA to execute queries against SAP HANA databases?

Have you talked to your security officer about SQL injection?

Re: Create ABAP program to send SQL command to another HANA DB

saumel — Thu, 20 Feb 2025 14:16:31 GMT

Hi Raymond ! It's a request from basis team in order to do this action in few hana DB with only 1 sql command. They already checked if we can do this with SQL console but is not possible. I mean, imagine you need to create one user in x hana DB you need to do this x times. This is the reason of my request.

Re: Create ABAP program to send SQL command to another HA...

RemiKaimal — Sat, 22 Feb 2025 22:58:39 GMT

Hi @Samuel
The easier option is to use HANA SQL Console/HANA Studio, what is the need to write a ABAP report when there are tools to query. There is option to query huge volume of data too
Data lake often resort to Standard SAP tools for a query
Answering the question :
Check list
1) If your user has S_DBCON and S_TABU_DIS authorizations, then that means you have execution within SAP's Open SQL or native SQL framework.
2) DML operations like (INSERT, UPDATE, DELETE) should be blocked / handled inside your custom report

Here is the sample code for your question :

REPORT zsql_exe.
TABLES: dd02l.

DATA: gv_sql TYPE string, " ------->>>>replace with your SQL statement
gt_results TYPE STANDARD TABLE OF dd02l, " Table to store results
gv_message TYPE string.

SELECTION-SCREEN: BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
PARAMETERS: p_sql TYPE string LOWER CASE OBLIGATORY. ---->important
SELECTION-SCREEN: END OF BLOCK b1.

START-OF-SELECTION.

"-------------- avoid harmful commands---- throw error otherwise
IF p_sql CS 'DELETE' OR p_sql CS 'UPDATE' OR p_sql CS 'INSERT'
OR p_sql CS 'DROP' OR p_sql CS 'ALTER'.
gv_message = 'Only SELECT statements are allowed!'.
MESSAGE gv_message TYPE 'E'.
EXIT.
ENDIF.

" Store SQL Command
gv_sql = p_sql.

TRY.
EXEC SQL.
EXECUTE IMMEDIATE :gv_sql INTO TABLE :gt_results
ENDEXEC.

IF gt_results IS NOT INITIAL.
cl_demo_output=>display_data( gt_results ). " Display output
ELSE.
gv_message = 'No data found for the query'.
MESSAGE gv_message TYPE 'I'.
ENDIF.

"handle error
CATCH cx_sy_dynamic_osql_error INTO DATA(lo_error).
gv_message = lo_error->get_text( ).
MESSAGE gv_message TYPE 'E'.
ENDTRY.

If an exception is thrown that means you may not have access
1) check su53 results after running the above sample code
2) or else execute excecute function module: DB_CONNECTION_TEST.
with parameter DB connection = DEFAULT.
If it fails, direct SQL access may be blocked.

Hope this helps! Cheers

Re: Create ABAP program to send SQL command to another HA...

saumel — Mon, 24 Feb 2025 07:02:27 GMT

Hi Remi ! thank you so much for your reply I really appreciate this !

The request was to create a program to execute SQL commands to create or delete hana users not to execute sql commands in tables. Did you already do something similar ? The idea was to excute DBCO connection to a specific system when executing the abap program, then execute sql commande to insert or delete hana user then close DBCO connection.

I never did something like this, this is why I posted this request.

I received the information that we cannot do this kind of action in mass so execute this kind of sql action in many systems one after one. If we want to do something like this with a console, we need to connect into each system then execute sql command and could be a waist of time.

Thank you so much for your help !

Re: Create ABAP program to send SQL command to another HA...

RaymondGiuseppi — Mon, 24 Feb 2025 10:48:34 GMT

I would add a OR p_sql NS 'SELECT'. to your checklist 😉

Re: Create ABAP program to send SQL command to another HANA DB

RaymondGiuseppi — Mon, 24 Feb 2025 15:11:48 GMT

Basically, you want to create a wizard for Managing Database Users ?

Re: Create ABAP program to send SQL command to another HA...

RaymondGiuseppi — Mon, 24 Feb 2025 12:11:14 GMT

Basically, you want to create a cockpit/wizard for Managing Database Users ?

Look at attachment of notes such as 1986645 - Allow only administration users to work on HANA database to get an idea of the SQL statements to be executed.
Insure that SAP HANA Database Administration with SAP HANA Cockpit isn't the solution