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.
Steps:
- 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.