SAP HANA 2.0 SPS03 new feature: Ruby driver

[tom_slee]

SAP HANA 2.0 SPS03 saw the introduction of a fully-supported driver for the Ruby programming language. This introduction to using the driver was written by Nehal Gupta, a University of Waterloo student who recently completed a co-op term working with SAP. Thanks Nehal!

There are two Ruby gems in the SAP HANA Client: the Ruby driver for SAP HANA and the ActiveRecord Adapter. This blog post is about the Ruby driver and walks you through the steps for installing, establishing a connection, executing SQL commands and handling result sets. The Ruby driver enables Ruby code to interface with SAP HANA databases. It is written in C++ and Ruby, and is available as a pre-compiled gem.

Installing the HANA Client package

To follow the steps here, you first need to install the SAP HANA Client install package. Instructions for installing it are here, and more officially here. The steps assume you are working on the Linux operating system, that you have rights to run "sudo" commands, that you have Ruby installed, and that you have some familiarity with Ruby.

Verify the prerequisites: HANA Client and Ruby

The default install location for the HANA Client on Linux is /usr/sap/hdbclient. To confirm that you have the HANA Client in your path, open a command prompt and run the following command:

> which hdbsql

If hdbsql cannot be found, edit your login file and add /usr/sap/hdbclient to the PATH and LD_LIBRARY_PATH environment variables. For example, if you are using bash, add these lines to your .bashrc file

export PATH=$PATH:/usr/sap/hdbclient

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/sap/hdbclient

To check the client version, run the following command. The version number should be at least 2.3:

> hdbsql -v

The Ruby driver and the related ActiveRecord provider are found in the ruby subdirectory of the install. Listing the files will also give you the precise version numbers for the gem files, which vary from release to release. Here is the listing from my install:

> ls -lR /usr/sap/hdbclient/ruby/

total 8

dr-xr-xr-x+ 2 root root 4096 Mar 27 17:30 activerecord

dr-xr-xr-x+ 2 root root 4096 Mar 27 17:30 hanaclient



/usr/sap/hdbclient/ruby/activerecord:

total 80

-r--r--r-- 1 root root 79872 Mar 13 18:46 activerecord-hanaclient-adapter-2.3.74.gem

/usr/sap/hdbclient/ruby/hanaclient:
total 21776
-r--r--r-- 1 root root 22298624 Mar 13 18:46 hanaclient-2.3.74-x86_64-linux.gem

Ensure you have Ruby installed. Instructions for installing Ruby can be found at https://www.ruby-lang.org/en/. At a command prompt, run verify Ruby has been installed and to check the version by running the following command:

> ruby -v

Install the HANA driver

Now install the HANA Client gem into your ruby environment. The HANA client version may be different in your case.

> sudo gem install /usr/sap/hdbclient/ruby/hanaclient/hanaclient-2.3.74-x86_64-linux.gem

Check that the gem is properly installed. It should appear in the list of gems:



> gem list



*** LOCAL GEMS ***



...

hanaclient (2.3.74 x86_64-linux)

...

You are now ready to write some Ruby code using the HANA Client interface. The interface is documented here.

Connect to a HANA database

Here is a simple Ruby application that connects to an SAP HANA database, and prints a message to tell you whether or not the connection was successful. You will have to substitute your own HANA information: a HANA server hostname (or IP address), port (the default for HANA 2 is 3XX13, where XX is the System ID), tenant database name (not required if you are running a single-tenant database), and user credentials.

This application then frees the resources that were allocated for the connection.

require 'hanaclient'

include HANACLIENT



# Create and initialize an interface

@api = HANACLIENT::HANACLIENTInterface.new()

HANACLIENT::API.hanaclient_initialize_interface(@api)

@api.hanaclient_init()

 

# Create and set up new connection

@conn = @api.hanaclient_new_connection()

status = @api.hanaclient_connect( @conn,"ServerNode=server:3XX13;DATABASENAME=databasename;UID=USER;Pwd=Password")

 

# Check the status

# Display an error message in case the connection attempt failed, that is status=0

if status == 0

 print "Connection failed: status = #{status}\n"

 msg = @api.hanaclient_error( @conn )

 print "Message=#{msg}\n"

 exit

else

 puts "Connection succeeded: you are connected to the database"

end



# Disconnect from the database and free the connection resources

@api.hanaclient_disconnect(@conn) 

@api.hanaclient_free_connection(@conn) 

@api.hanaclient_fini() 

HANACLIENT::API.hanaclient_finalize_interface(@api)

Execute a SQL statement

You can add the code below to the application after the connection is established. It creates a SQL statement (as a string using the Ruby "heredoc" approach) and simply executes it on the connection.

def create_table()

    # Create a table

    str = <<-EOC

    CREATE TABLE "Product"

        ("ID" integer NOT NULL,

        "Name" varchar(15) NOT NULL,

        "Description" varchar(30) NOT NULL,

        "Size" varchar(18) NOT NULL,

        "Color" varchar(18) NOT NULL,

        "Quantity" integer NOT NULL,

        PRIMARY KEY("ID")

    )

    EOC

    @api.hanaclient_execute_immediate(@conn, str)

    puts "Created table 'Product'"

end



create_table()

The next example uses the same approach to insert a row and commit the change to the database.

def insert_fixed_row()

    @api.hanaclient_execute_immediate(@conn,<<-end_sql)

    INSERT INTO "Product" VALUES

    311,

    'Jacket',

    'Winter',

    'Small',

    'Red',

    54

    )

    end_sql



    # Commit the changes

    @api.hanaclient_commit(@conn)

    puts "Row inserted"

end

Prepare and execute a statement

Of course most of the time you do not want to hard-code values in your application. In addition, preparing a statement before execution permits query routing in multi-node systems, and better plan cache reuse. Here is an illustration of how to declare and bind parameters so you can pass values to the SQL statements.

def update_row(size, id)

     sql = <<-end_sql

     UPDATE "Product"

     SET "Size" = ?

     WHERE "ID" = ?

     end_sql

     stmt = @api.hanaclient_prepare(@conn, sql)

     # Describe the parameters

     rc, param_size = @api.hanaclient_describe_bind_param(stmt, 0)

     rc, param_id = @api.hanaclient_describe_bind_param(stmt, 1)

     # Set the values

     param_size.set_value(size)

     param_id.set_value(id)

     # Bind the parameters

     rc = @api.hanaclient_bind_param(stmt, 0, param_size )

     rc = @api.hanaclient_bind_param(stmt, 1, param_id )

     rc = @api.hanaclient_execute(stmt)

     # Commit the changes

     @api.hanaclient_commit(@conn)

     puts "Updated the row"

end

The steps you need to take are:

1. Prepare the statement


2. Describe bind parameters


3. Set parameter values


4. Bind the parameters to the statement


5. Execute the statement


6. Commit the change or changes when you are ready to complete the transaction

In this case, the return code is ignored for simplicity, but in a real application you may want to check the value of the return codes to monitor the success of the database operations.

Querying results and handling result sets

Here is one way to execute a query and loop over the rows of its result set. After getting the number of rows in the result set, it loops over each and uses hanaclient_get_column to get the value of each iterm.

def query_products()

    sql = <<-end_sql

    SELECT "Name", "Size" FROM "Product"

    end_sql

    stmt = @api.hanaclient_execute_direct(@conn, sql)

    num_rows = @api.hanaclient_num_rows(stmt)

    for counter in 1..num_rows

        @api.hanaclient_fetch_next(stmt)

        rc, name = @api.hanaclient_get_column(stmt, 0)

        rc, size = @api.hanaclient_get_column(stmt, 1)

        print "The item retrieved is a ", name, " size ", size, "\n"

    end

end

Calling stored procedures

Here is a function that creates a stored procedure, which takes a product ID as a parameter. The procedure decrements the quantity of the product in stock.

def create_stockentry_proc()

    sql = <<-end_sql

    CREATE OR REPLACE PROCEDURE STOCKENTRY(IN product_id INT)

    LANGUAGE SQLSCRIPT AS

    BEGIN

       UPDATE "Product"

       SET "Product"."Quantity" = "Product"."Quantity" - 1

       WHERE "Product"."ID" = product_id ;

    END

    end_sql

    @api.hanaclient_execute_immediate(@conn, sql)

end

And here is a function that calls the procedure. Parameters are treated the same as any other SQL statement: hanaclient_execute is used in such cases and a fetch statement is used if results are to be retrieved.

def call_stockentry_proc(product_id)

    stmt = @api.hanaclient_prepare(@conn, "CALL STOCKENTRY(?)")

    # Describe the parameter

    rc, product_id_param = @api.hanaclient_describe_bind_param(stmt, 0)

    # Set the values

    product_id_param.set_value(product_id)

    # Bind the parameters

    rc = @api.hanaclient_bind_param(stmt, 0, product_id_param)

    # Call the procedure

    rc = @api.hanaclient_execute(stmt)

    # Commit the changes

    @api.hanaclient_commit(@conn)

    # Free statement resources

    @api.hanaclient_free_stmt(stmt)

end

For more information on working with the Ruby driver, refer to the SAP HANA Client Interface Programming Reference here.