We can connect to SAP HANA System using windows batch scripts and HANA client HDBSQL.
HDBSQL is a command line tool for executing SQL commands on SAP HANA databases. SAP HANA HDBSQL client plus Windows batch scripts can be used to automate the HANA tasks, like triggering backup or any custom stored procedure/SQL script.
Requirement: We want to connect to SAP HANA system using Windows batch file and HDBSQL client.
Prerequisite: We need SAP HANA Windows client installed on the source windows system. We also need an SAP HANA database user to connect and HANA system connection information.
- Create the script files(Windows Batch and SQL script).
- Run/Schedule the windows batch file.
In our example we will write following basic script file.
SQL Script file: This SQL script file will contain the SQL that needs to be executed on SAP HANA system.
Windows Batch file: This script will have the HANA system connection information including user credentials. We will give the above SQL script file as input to this batch file.
SQL Script file:
SELECT * FROM "SYS"."M_SYSTEM_OVERVIEW";Above sample SQL will list down SAP HANA system detail ,this can be your custom script file containing the SQL that you intended to execute.
Windows Batch file:
We are specifying the HANA system information and the SQL script file(testSql.sql) in the below command.
"C:\Program Files\sap\hdbclient\hdbsql" -n webserver:30015 -i 00 -u SYSTEM -p Ina123 -c ";" -I "C:\Temp\hdbsql\testSQL.sql"
If you do not wish to store credentials in plain text in batch file then you can use hdbuserstore functionality.
Run/Schedule the windows batch file
In the below example we are running the batch file to make sure it connects successfully and produces the desired results.We can use windows scheduler to execute this script periodically.