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.
