Yesterday I had a great pleasure to have a lunch with Artur Górnik, one of HANA “black belts” in SAP MEE region. He did a customer workshop the day before here in Wrocław. And then our lunch turned into 3 hours discussion about all things SAP data managment, interrupted – unfortunately – by his departing train
One of the topics we discussed was using different SQL clients with SAP HANA Express server-only installation. The discussion was triggered by a blog SQL Clients and SAP HANA 2.0 posted by my teammate Craig Cmehil. (As a true black belt) Artur told me that his primary client when working with HANA database is still hdbsql, i.e. the command line interface.
I recently spent some time working with SAP Vora developer edition. And if you worked with it as well, then you might have came across the tool called beeline there. It is a command line shell that uses Apache Hive JDBC to connect to SAP Vora. But the reason why I brought it here now was the note that it was based on another open source project SQLLine. Accordingly to their documentation:
SQLLine is a pure-Java console based utility for connecting to relational databases and executing SQL commands. It is similar to other command-line database access utilities… Since it is pure-Java, it is platform independent, and will run on any platform that can run Java 1.3 or higher.
So, it is one CLI to rule them all as it seems! The author stopped developing it in 2005, but some more search and I found a branch that is still being developed these days: https://github.com/julianhyde/sqlline. Ok, pulling it from git (using WSL on my Windows 10), installing Maven and configuring it to use SAP JVM, running the build process, and here it is: freshly compiled SQLLine.
As described in the Getting Started I copied sqlline.bat and sqlline-VERSION-jar-with-dependencies.jar into one directlry plus copied two JDBC driver files there as well:
* ngdbc.jar for HANA 2.0 SPS 2 Express [called HXE further]
* sajdbc4.jar for SAP SQL Anywhere 17 Developer edition [called SQLA further]
Let me try now. C:Toolssqllineexe>sqlline.bat sqlline version ??? sqlline>
I am not sure why it does not know the version, but at least I am in the shell. Let’s connect to HXE that I activated in SAP CAL. sqlline> !connect jdbc:sap://vhcalhxedb:39015 Enter username for jdbc:sap://vhcalhxedb:39015: SYSTEM Enter password for jdbc:sap://vhcalhxedb:39015: ******** 0: jdbc:sap://vhcalhxedb:39015> select * from dummy; +——-+ | DUMMY | +——-+ | X | +——-+ 1 row selected (0.472 seconds) 0: jdbc:sap://vhcalhxedb:39015>
So far, so good. Now let me connect to SQLA’s standard demo databse I have on my Windows 10 laptop. 0: jdbc:sap://vhcalhxedb:39015> !connect jdbc:sqlanywhere:eng=demo17 Enter username for jdbc:sqlanywhere:eng=demo17: dba Enter password for jdbc:sqlanywhere:eng=demo17: *** 1: jdbc:sqlanywhere:eng=demo17> select * from dummy; +————-+ | dummy_col | +————-+ | 0 | +————-+ 1 row selected (0.004 seconds) 1: jdbc:sqlanywhere:eng=demo17>
Ok, works as well. And how about SQLA that I installed on my Raspberry Pi at home and started the same demo database? 1: jdbc:sqlanywhere:eng=demo17> !connect jdbc:sqlanywhere:Host=192.168.1.224;ServerName=mydemo;DatabaseName=demo Enter username for jdbc:sqlanywhere:Host=192.168.1.224;ServerName=mydemo;DatabaseName=demo: dba Enter password for jdbc:sqlanywhere:Host=192.168.1.224;ServerName=mydemo;DatabaseName=demo: *** 2: jdbc:sqlanywhere:Host=192.168.1.224> select * from dummy; +————-+ | dummy_col | +————-+ | 0 | +————-+ 1 row selected (0.015 seconds) 2: jdbc:sqlanywhere:Host=192.168.1.224>
Now I have my SQLLine client connected to three different database servers (one HANA and two SQL Anywhere; one in the cloud, one on my laptop, and one in my home network). And I can switch between them from within the same command line. 2: jdbc:sqlanywhere:Host=192.168.1.224> !list 3 active connections: #0 open jdbc:sap://vhcalhxedb:39015 #1 open jdbc:sqlanywhere:eng=demo17 #2 open jdbc:sqlanywhere:Host=192.168.1.224;ServerName=mydemo;DatabaseName=demo 2: jdbc:sqlanywhere:Host=192.168.1.224> !go 0 0: jdbc:sap://vhcalhxedb:39015>
But what is cool is that I can submit an SQL statement (assuming the same syntax and db objects, like in this example) to all at once: 0: jdbc:sap://vhcalhxedb:39015> !all select * from dummy; Executing SQL against: jdbc:sap://vhcalhxedb:39015 +——-+ | DUMMY | +——-+ | X | +——-+ 1 row selected (0.45 seconds) Executing SQL against: jdbc:sqlanywhere:eng=demo17 +————-+ | dummy_col | +————-+ | 0 | +————-+ 1 row selected (0.001 seconds) Executing SQL against: jdbc:sqlanywhere:Host=192.168.1.224;ServerName=mydemo;DatabaseName=demo +————-+ | dummy_col | +————-+ | 0 | +————-+ 1 row selected (0.014 seconds) 0: jdbc:sap://vhcalhxedb:39015>
And what is über-cool, is that I can run examples from my Introduction to SAP HANA Spatial engine tutorials on all connections, because both HXE and SQLA support geospatial data. 0: jdbc:sap://vhcalhxedb:39015> !all SELECT NEW ST_Point (1,1).ST_Within(NEW ST_Point(0, 0).ST_Buffer(2)) FROM dummy; Executing SQL against: jdbc:sap://vhcalhxedb:39015 +—————————————————–+ | ST_POINT(1,1).ST_WITHIN(ST_POINT(0,0).ST_BUFFER(2)) | +—————————————————–+ | 1 | +—————————————————–+ 1 row selected (0.27 seconds) Executing SQL against: jdbc:sqlanywhere:eng=demo17 +————————————————————-+ | new ST_Point(1,1).ST_Within(new ST_Point(0,0).ST_Buffer(2)) | +————————————————————-+ | true | +————————————————————-+ 1 row selected (0.012 seconds) Executing SQL against: jdbc:sqlanywhere:Host=192.168.1.224;ServerName=mydemo;DatabaseName=demo +————————————————————-+ | new ST_Point(1,1).ST_Within(new ST_Point(0,0).ST_Buffer(2)) | +————————————————————-+ | true | +————————————————————-+ 1 row selected (0.058 seconds) 0: jdbc:sap://vhcalhxedb:39015>
I really wanted to try it out, so it was quite quick and dirty installation and test. I haven’t tested all the different SQLLine commands and all the different SQL statements and outputs. If you try more, and especially if you break it – please share here in the comments. Much appreciated!
-Vitaliy, aka @Sygyzmundovych http://bit.ly/2gJbgA0 #SAP #SAPCloud #AI