SAP HANA 2.0 SPS02 new feature: Go language driver

Introduction

Go is expressive, concise, clean, and efficient. Its concurrency mechanisms make it easy to write programs that get the most out of multicore and networked machines, while its novel type system enables flexible and modular program construction. Go compiles quickly to machine code yet has the convenience of garbage collection and the power of run-time reflection. It’s a fast, statically typed, compiled language that feels like a dynamically typed, interpreted language.


SAP HANA 2.0 SPS02 includes a Go language driver. This blog post describes how to install it and how to execute simple database commands. It will describe the process for Linux, with Windows commands added where needed. I assume a very basic knowledge of the Go language and it assumes you have access to a HANA database on which you can create a table.

Installing the Go language driver

There are two steps to installing the Go driver:

1. Install the SAP HANA Client package on your computer. 
2. Install the Go driver from the HANA Client install into a local Go workspace.
3. The Go driver has two components to it:
• A Go language component
• A C++ shared object (DLL on Windows) that implements the network protocol and provides a low-level interface.

To install the Go driver you need to compile the Go language component (making sure the Go compiler can find the shared object) and then make both these components available to the executable at runtime.

Installing the Go driver into a local Go workspace

You must have Go installed on your local machine. From a command line:

> cd ~/go/src

> export CGO_LDFLAGS=/usr/sap/hdbclient/libdbcapiHDB.so

> cp -r /usr/sap/hdbclient/SAP/ .

> go install SAP/go-hdb/driver

The driver is built in ~/go/pkg/linux_amd64/SAP (the directory identifying the CPU may have a different name if you are running in a different environment.

At runtime, you will also need the HANA client shared object in the LD_LIBRARY_PATH environment variable (or PATH on Windows) or in the same directory as the compiled executable. Here we haveset the LD_LIBRARY_PATH variable, but you can also copy the file:

> export LD_LIBRARY_PATH=:/usr/sap/hdbclient

Connecting to a database

Now create a directory to hold your first Go application:

> mkdir hello

> cd hello

Using a text editor, create a file hello.go that contains this text, replacing the elements that make up connStr with connection parameters for a HANA instance to which you have access.

package main

import (
    "fmt"
    "log"
    "database/sql"
    _ "SAP/go-hdb/driver"
)

func main() {
    // define a connection string
    var connStr string
    connStr = "hdb://<user>:<password>@<host>:3NN13?DATABASENAME=<db>"  
    db, err := sql.Open("hdb", connStr)
    if err != nil {
        log.Fatal(err)
    } else {
        fmt.Println("Connected.")
    }
    defer db.Close()
}

Here are some comments on this code:

package main

This file is the entry point for the application.

import (
        "fmt"
        "log"
        "database/sql"
        _ "SAP/go-hdb/driver"
)

Import some required packages. The “fmt” and “log” packages are for printing and logging output. The “database/sql” package defines the SQL interface for Go, and the final line imports the SAP HANA driver. The final import is an anonymous import (the underbar character) and is done so that none of the exported names are visible to the application code. Instead, all the application code does is interact with the database/sql package.

 var connStr string
 connStr = "hdb://<user>:<password>@<host>:3NN13?DATABASENAME=<db>"

The connection string here is for a multi-tenant database container, and uses the 3NN13 port (NN being your HANA instance number) and tenant DATABASENAME to connect (the DATABASENAME parameter must be upper case). If you are connecting to a single-tenant database you would typically use a port of 3NN15 and no DATABASENAME.

db, err := sql.Open("hdb", connStr)
if err != nil {
    log.Fatal(err)
} else {
    fmt.Println("Connected.")
}
defer db.Close()

the sql.Open method establishes a connection using the “hdb” driver and the supplied connection string. If it is successful it returns db as a sql.DB object that is essentially a connection pool for accessing the database.

If the Open method returns an error, that will be logged; on success the program prints “Connected” to the console.

The defer db.Close() call is standard Go language practice: the defer ensure that as the function completes, the database session will be closed.

Executing a query

To execute a query, add a function and call it from immediately after the “defer db.Close()” line:

   ...
   defer db.Close()
   query(db)
}

Here is the function, which assumes a table T1 that has an integer column ID and a varchar column C2. Modify for your own table.

func query(db *sql.DB) {
    rows, err := db.Query("SELECT ID, C2 FROM T1")
    if err != nil {
        log.Fatal(err)
    } else {
        var id int
        var c2 string
        for rows.Next() {
            err := rows.Scan(&id, &c2)
            if err != nil {
                log.Fatal(err)
            } else {
                fmt.Println(id, ", ", c2)
            }
        }
    }
    defer rows.Close()
}

The query is executed, and so long as there is not an error, the function loops over the rows of the query result (“for rows.Next()”) and prints out the values from each row.

Executing prepared statements

Here I will not go through the whole code, but describe the extra steps you have to make beyond what is in the query example above.

First, add a couple of packages to your import statements:

import (
        "context"
        "database/sql"
        "fmt"
        "log"
        "time"
        _ "SAP/go-hdb/driver"
)

Then, in the query() function, prepare the query using a question mark as a placeholder:

queryStmt, err := db.Prepare("SELECT ID, C2 FROM T1 WHERE ID = ?")

Create a query context, which defines a timeout:

ctx, cancel := context.WithTimeout(context.Background(), 8*time.Second)
defer cancel()
rows, err := queryStmt.QueryContext(ctx, 1)

The value “1” in the QueryContext sets the value of the statement parameter.

You can then loop over the rows of the result set as before.