sábado, 17 de noviembre de 2012

Ruby joins the SAP HANA party


Believe it or not...I totally forgot to write a blog on how integrate Ruby and SAP HANA...shame on me for sure...and while this is not rocket science, I'm sure there people out there wondering how to do this or struggling to make it work. For all of them...here's how to do it.

First things first, let's define what are we going to do...as I feel bad about forgetting Ruby, I think this should be a really cool blog...so...we're going to use Sinatra (an old time friend of mine) to develop a web application that will connect via ODBC and will present us all the tables contained in the SFLIGHT schema in a dropdown box...after selecting a table, we will have the full content of the table "a la SE16"...

So...we need to install Sinatra...open an CDM session and type the following

Install Sinatra on Ruby
gem install sinatra


Then, we need to install something to read our ODBC connection...where're going to use RDBI, but as it is a native library, we need to do something else before we can actually install it...otherwise we're going to receive an error...

We need to download the Development Kit and extract it, then open an CMD session, go to the folder where you extracted the DevKit and type the following

Install DevKit for Ruby
ruby dk.rb init
ruby dk.rb install

With that, we have all the compilers that we need to make this work. Go to the CMD and type the following

Install RDBI
gem install rdbi-driver-odbc

We should be ready by now...there's one small detail...we might have an ODBC connection for our SAP HANA Server...but...as Ruby works on 32bits, we're going to have a message saying that the driver and the architecture mismatch...to solve it...follow this steps...

Creating an 32bit ODBC
Go to C: --> Windows --> SysWOW64 --> odbcad32.exe


When you create your ODBC connection, make sure to choose the HDBODBC32 driver.

Now...we're more than ready...let's see the Ruby code...

Ruby_SAP_HANA.rb
require 'sinatra'
require 'rdbi-driver-odbc'
 
get '/' do
  body do
    <<-eos
    <div align='center'>
    <h1>Ruby/Sinatra and SAP HANA - Table Browser</h1>
    <form action='/login' method='post'>
      <label for='dsn'>DSN</label><br/>
      <input type='text' name='dsn' /><br />   
      <label for='user'>User</label><br />
      <input type='text' name='user' /><br />
      <label for='password'>Password</label><br />
      <input type='password' name='password' /><br />
      <input type='submit' name='submit' value='Login' />
    </form>
    </div>
    eos
  end
end
 
get '/login_view' do
  $output = "<div align='center'><form action='/table_view' method='post'>"
  $output += "Choose Table <SELECT NAME='tab'>"
  for i in 0...$Tables_Len
    $output += "<option value=#{$Tables[i]}>#{$Tables[i]}</option>"
  end 
  $output += "</option>"
  $output += "<input type='submit' name='submit' value='Show Table' />"
  $output += "</form></div>"
  body $output
end
 
get '/show_table' do
  $output = "<div align='center'><table border='1'><tr>"
  for i in 0...$Fields_Len
    $Fields_Fields = $Fields[i].to_s
    $output += "<th> #{$Fields_Fields} </th>"
  end
  $output += "</tr>"
  for i in 0...$Data_Len
    $output += "<tr>"
    for j in 0...$Fields_Len
      $output += "<td> #{$Data[i][j].to_s} </td>"
    end
    $output += "</tr>"
  end
  $output += "</table></div>"
body $output
end
 
post '/login' do
  $dsn,$user,$password = params[:dsn],params[:user],params[:password]
  "#{do_login}"
  "#{get_tables}"                    
  redirect '/login_view'
end
 
post '/table_view' do
   $tab = params[:tab]
   "#{get_data}"
   redirect '/show_table'
end
 
helpers do
  def do_login
    $dbh = RDBI.connect :ODBC, :db => $dsn, :user => $user,:password => $password
  end
 
  def get_tables
    $rs = $dbh.execute "SELECT table_name from SYS.CS_TABLES_ where schema_name = 'SFLIGHT'"
    $ary = $rs.as(:Array).fetch(:all)
    $Tables = Array.new
 
    for i in 0...$ary.length
      $Tables.push($ary[i][0])
    end
    $Tables_Len = $Tables.length 
  end
 
  def get_data
    $query = "SELECT COLUMN_NAME FROM SYS.CS_COLUMNS_ AS A INNER JOIN SYS.CS_TABLES_ AS B "
    $query += "ON A.TABLE_REF = B.REF_ID WHERE TABLE_NAME = '"
    $query += $tab
    $query += "' AND ABAP_TYPE_ID > 0"
    $rs = $dbh.execute $query
    $ary = $rs.as(:Array).fetch(:all)
    $Fields = Array.new
    $Data = Array.new
 
    for i in 0...$ary.length
      $Fields.push($ary[i][0])
    end
    $Fields_Len = $Fields.length
   
    $query = "SELECT * FROM SFLIGHT." + $tab
    $rs = $dbh.execute $query
    $ary = $rs.as(:Array).fetch(:all)
   
    for i in 0...$ary.length
      $Data.push($ary[i])
    end
    $Data_Len = $Data.length
  end 
end

Let's see some pictures, so you can have a better idea of how this works...


We choose our DSN, pass the Username and Password.

The dropdown box will show us the tables included in the SFLIGHT schema.


We show all the fields with their corresponding data in a nice HTML table.

What do you think? Have I atoned my sins for not writing about Ruby and SAP HANA before?

Greetings,

Blag.