JDBC to MaxDB on the Linux commandline

ODBC is Microsofts effort into creating a unified way of talking to databases. Sure, you don’t get all of the bells and whistles that that particular database offers, but it does give you the option of talking to a database and getting the basics like reading and writing data. Since this was a reasonable success, Sun Microsystems re-created the same for Java, resulting in a number of connectors for various databases being available.

So that leaves us with finding the right JDBC connector to talk to our database. In my case I have a MaxDB embedded into a commercial product that I want to get some information out of. After some research it turns out that the company that made MaxDB was bought by SAP. After some time SAP bought a company called Sybase which also made a database. They stopped developing MaxDB it in favor of the new data server called SAP ASE. So note that those two are not the same.

This means that in search of drivers, you’ll find a lot of old software but luckily the JDBC driver still works. To get it you’ll have to go to https://developers.sap.com/trials-downloads.html and download either the Windows or Linux package for ‘SAP MaxDB‘. I got the Windows version ‘maxdb_all_win_64bit_x86_64_7_9_10_05.exe‘ and installed it. You can then find the JDBC driver here: C:\Program Files\sdb\MaxDB1\runtime\jar\sapdbc.jar

From everything that you installed, this is the only file you need; it’s self-contained. You can also use that JAR file on Windows or Linux, it doesn’t matter. I tested the driver with DBeaver (community edition). You can read on how to install the JDBC driver here: https://github.com/dbeaver/dbeaver/wiki/Database-drivers

Now that we’re sure that we can connect, it’s time to create a script so we can pull out data in an automated way. First off, copy the sapdbc.jar file to your Linux machine. I’m on a Ubuntu 20.04 machine so install our client I type:

apt install sqlline                                # Installs version 1.0.2

This installs a rather old but functional version of sqlline. Now we have to tell it to use only our JDBC driver:

cp sapdbc.jar /usr/share/java                      # Copy it where sqlline can find it
cp -a /usr/bin/sqlline /usr/bin/sqlline.ori        # Backup the calling script
nano /usr/bin/sqlline                              # Change to: find_jars jline sqlline sapdbc

Now we can start sqlline and connect to the database:

sqlline -d com.sap.dbtech.jdbc.DriverSapDB
!connect jdbc:sapdb://my_maxdb_server:7210/my_database my_user my_password
!list
!tables
!quit

While this works, it should be noted that the latest version of sqlline has JSON output support and the ability to run SQL scripts (which we want to do). So let’s upgrade our version. We’ll have to install a Java package so we have ‘javadoc’ available during compilation. Compilation is done with a version of Apache Maven, a Java building tool.

apt install openjdk-11-jdk-headless
git clone git://github.com/julianhyde/sqlline.git
cd sqlline
./mvnw package

# Compilation is now done. Let's replace the old sqlline

mv /usr/share/java/sqlline.jar /usr/share/java/sqlline.jar.old
cp target/sqlline-1.13.0-SNAPSHOT-jar-with-dependencies.jar /usr/share/java/sqlline.jar
rm -f ~/.sqlline/history        # The new version of sqlline can't read this

Now let’s run it again but with the new version:

cat << 'EOF' > run.sql
!outputformat json
SELECT * FROM ARCHIVE;
EOF
sqlline -u 'jdbc:sapdb://my_maxdb_server:7210/my_database' -n my_user -p my_password \
  --run=run.sql --silent=true 2>/dev/null > result.json
cat result.json | jq .

There we go. Now we can pull data from the database and use it in scripts. You can find a complete manual to sqlline here: https://julianhyde.github.io/sqlline/manual.html