SQLLine with SAP HANA. And with SQL Anywhere too…

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 conversation about all things SAP data, 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 the true black belt) Artur told me that his primary tool to work with HANA database is still hdbsql, the command line interface.

On the other side, I recently have 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. The reason why I brought it here – in the context of HANA – is because I noticed that Beeline was based on another open source project called SQLLine. And 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 looked like one CLI to rule them all!! The original author stopped developing SQLLine in 2005, but with some more search I found a branch that was still developed these days: https://github.com/julianhyde/sqlline.

Ok, let’s try it out! First pulling it using git (using WSL on my Windows 10), then installing Maven and configuring it to use SAP JVM, and at the end running the build process. Here it is: freshly compiled SQLLine on my laptop! Time to put it into action!

As described in the Getting Started I copied sqlline.bat and sqlline-VERSION-jar-with-dependencies.jar into one directory. Plus copied two JDBC driver files to that directory 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’s run.

C:\Tools\sqlline\exe>sqlline.bat
sqlline version ???
sqlline>

I am not sure why it does not know the version, but at least I am in the shell.

Now using the command !connect to establish the connection to HXE instance that I have activated in SAP CAL (Cloud Appliance Library). And running simple SELECT to check querying from the database.

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 connecting to SQLA’s standard demo17 database, which I have had 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 have installed on my Raspberry Pi at home? I have started the same demo database as mydemo server on it already.

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>

At this point I have my SQLLine client connected to three different database servers (one HANA and two SQL Anywhere instances; one in the cloud, one on my laptop, and one in my home network). I can list connections using the command !list.

I can switch between these connections from within the same command line using command !go.

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 SQL syntax and identical db objects in connected databases) to all at once using the command !all:

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 [Ok, not all geospatial configurations, like units of measures, are the same, so not all examples will finish successfuly]. Both HANA and SQL Anywhere returning the same distance (uff!) in meters for one degree of longitude at the Earth’s equator:

0: jdbc:sap://vhcalhxedb:39015> !all select new st_point('POINT (0 0)', 4326).st_distance(new st_point('POINT (1 0)', 4326)) as distance from dummy;
Executing SQL against: jdbc:sap://vhcalhxedb:39015
+------------------------+
|        distance        |
+------------------------+
| 111319.4907932264      |
+------------------------+
1 row selected (0.27 seconds)
Executing SQL against: jdbc:sqlanywhere:eng=demo17
+------------------------+
|        distance        |
+------------------------+
| 111319.4907932264      |
+------------------------+
1 row selected (0.012 seconds)
Executing SQL against: jdbc:sqlanywhere:Host=192.168.1.224;ServerName=mydemo;DatabaseName=demo
+------------------------+
|        distance        |
+------------------------+
| 111319.4907932264      |
+------------------------+
1 row selected (0.058 seconds)
0: jdbc:sap://vhcalhxedb:39015>

It was rather 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 them, and especially if you break them – please share here in the comments. Much appreciated!

-Vitaliy, aka @Sygyzmundovych