miércoles, 28 de noviembre de 2012

SQLAlchemy and SAP HANA


This Monday, November 26 I participate in Montréal-Python 33: Qualified Quasar a really nice meetup organized by my friend from the Montreal Python Community. I didn't show up only as an attendee, but also as an speaker and I talked about "Python and SAP" (Will blog about it as soon as the videos got ready)...during my presentation, someone asked me about SQLAlchemy and SAP HANA.

Up to that day, I have never used SQLAlchemy and I really didn't knew if something like that existed for SAP HANA, but something...call it premonition...told me that someone was working on that...so I told the guy..."I don't know, but I think someone is working on that...will let you guys know as soon as I got some news".

On Tuesday, I started to check the SQLAlchemy page but didn't catch all the buzz around it...and suddenly, Srdjan Boskovic told me that Martin Stein had been working on...guess what? SQLAlchemy for SAP HANA...he asked me if I could play with it and maybe write a blog about...you could imagine how happy and excited I was...a new technology to learn...hours of head breaking trying to understand it...waking up early and going to bed late just to write a blog on SCN...I was in heaven for sure...but...I was busy already, so I promised both to take a look the next day...

Today, I woke up really early...earlier than usual and started to analyse the code and the examples...read a couple of tutorials and then started to work for the first time ever with SQLAlchemy...with some really nice help from Martin...I finally finished my application...I can say that it took me around 15 hours without brakes...but when you love your work...you just can't stop...

Anyway...this is still on "Beta"...no available yet, so for now...I will show you how to use it and how an application made with it looks like...of course...I return to my favourite Python micro framework...the almighty Bottle.

So, for this project we need Bottle, PyODBC and the SQLAlchemy_HANA libraries. I also used an ODBC Connection to my AWS SAP HANA Server.

Let's see the source code...

Band_Model.py
from sqlalchemy import Column, Integer, String
from meta import Base


class Singer(Base):
    __tablename__ = 'SINGERS'

    singer_id = Column(Integer, primary_key=True)
    first_name = Column(String(20))
    last_name = Column(String(20))
    band = Column(String(20))

    def __init__(self, singer_id, first_name, last_name, band):
        self.singer_id = singer_id
        self.first_name = first_name
        self.last_name = last_name
        self.band = band

    def __repr__(self):
        return "<Single('{first_name}', '{last_name}'\
                ,'{band}')>".format(**self.__dict__)

As I didn't have any idea on what to base my application, I decided to create something to keep track of some of my favorite Punk Rock singers...this first script Band_Model.py will be in charge of create the table SINGERS on SAP HANA.

Band_App.py
from bottle import get, post, request, run, redirect
from meta import Base, engine, Session
from sqlalchemy import *
from sqlalchemy.orm.exc import NoResultFound
from Band_Model import Singer

def connect():
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)

@get('/show')
def show_form():
    output = "<div align='center'>"
    output += "<h1>SQLAlchemy on SAP HANA</h1>"
    output += "<a href='/add_singer'>Add Singer</a>"
    output += "<table border=1>"
    singers = Session.query(Singer).all()
    output += "<tr><th>Id</th><th>First Name</th>"\
              "<th>Last Name</th><th>Band</th>"\
              "<th>Update</th><th>Delete</th></tr>"
    for singer in singers:
        update = "/update_singer?singer_id=" + str(singer.singer_id)
        delete = "/delete_singer?singer_id=" + str(singer.singer_id)
        output += "<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td>"\
                  "<td><a href='%s'>Update</a></td><td>"\
                  "<a href='%s'>Delete</a></td></tr>"\
        % (singer.singer_id, singer.first_name, singer.last_name,
           singer.band, update, delete)
    output += "</table></div>"
    return output

@get('/add_singer')
def add_form():
    query = ""
    singer_id = 0
    try:
        query = Session.query(Singer.singer_id).\
                    order_by(desc(Singer.singer_id)).first()
        singer_id = int(query[0]) + 1
    except NoResultFound:
        singer_id = 1
    except TypeError:
        singer_id = 1
    output = "<DIV ALIGN='CENTER'><TABLE>"
    output += "<FORM METHOD='POST'><TR><TD>"
    output += "<INPUT TYPE='HIDDEN' NAME='Singer_Id'"\
              "value='%s'></TD></TR>" % (singer_id)
    output += "<TR><TD>First Name</TD><TD>"
    output += "<INPUT TYPE='TEXT' NAME='First_Name'></TD></TR>"
    output += "<TR><TD>Last Name</TD><TD>"
    output += "<INPUT TYPE='TEXT' NAME='Last_Name'></TD></TR>"
    output += "<TR><TD>Band</TD>"
    output += "<TD><INPUT TYPE='TEXT' NAME='Band'></TD></TR>"
    output += "<TR><TD COLSPAN='2' ALIGN='CENTER'>"
    output += "<INPUT TYPE='SUBMIT' value='Add Singer' NAME='Add_Singer'>"
    output += "<INPUT TYPE='RESET' value='Clear'></TD></TR>"
    output += "</FORM><TABLE></DIV>"
    return output

@post('/add_singer')
def create():
    Singer_Id = request.forms.get('Singer_Id')
    Singer_Id = int(Singer_Id)
    First_Name = request.forms.get('First_Name')
    Last_Name = request.forms.get('Last_Name')
    Band = request.forms.get('Band')
    singer = Singer(Singer_Id, First_Name, Last_Name, Band)
    Session.add(singer)
    Session.commit()
    redirect("/show")

@get('/update_singer')
def update_form():
    query = ""
    singer_id = 0
    singer_id = int(request.query.singer_id)
    query = Session.query(Singer.first_name, Singer.last_name, Singer.band).\
                          filter_by(singer_id=singer_id).first()
    first_name = query[0]
    last_name = query[1]
    band = query[2]
    output = "<DIV ALIGN='CENTER'><TABLE>"
    output += "<FORM METHOD='POST'><TR><TD>"
    output += "<INPUT TYPE='HIDDEN' NAME='Singer_Id'"\
              "value='%s'></TD></TR>" % singer_id
    output += "<TR><TD>First Name</TD><TD>"
    output += "<INPUT TYPE='TEXT' NAME='First_Name'"\
              "value='%s'></TD></TR>" % first_name
    output += "<TR><TD>Last Name</TD><TD>"
    output += "<INPUT TYPE='TEXT' NAME='Last_Name'"\
              "value='%s'></TD></TR>" % last_name
    output += "<TR><TD>Band</TD>"
    output += "<TD><INPUT TYPE='TEXT' NAME='Band'"\
              "value='%s'></TD></TR>" % band
    output += "<TR><TD COLSPAN='2' ALIGN='CENTER'>"
    output += "<INPUT TYPE='SUBMIT' value='Update Singer'"\
              "NAME='Update_Singer'>"
    output += "<INPUT TYPE='RESET' value='Clear'></TD></TR>"
    output += "</FORM><TABLE></DIV>"
    return output

@post('/update_singer')
def update():
    Singer_Id = request.forms.get('Singer_Id')
    Singer_Id = int(Singer_Id)
    First_Name = request.forms.get('First_Name')
    Last_Name = request.forms.get('Last_Name')
    Band = request.forms.get('Band')
    Session.query(Singer).filter_by(singer_id=Singer_Id).\
    update({'first_name': First_Name, 'last_name': Last_Name,
           'band': Band})
    Session.commit()
    redirect("/show")

@get('/delete_singer')
def delete():
    singer_id = 0
    singer_id = int(request.query.singer_id)
    Session.query(Singer).filter_by(singer_id=singer_id).delete()
    Session.commit()
    redirect("/show")

connect()
run(host='localhost', port=8080)


This script Band_App.py will be in charge of creating the Bottle Application, insert new singers, modify the existing ones and delete them. As you can see...there's no a single SELECT, as SQLAlchemy manages the tables like classes and provide handy methods to manage everything. Now I really get all the buzz around it...it's really impressive...and easy to use...after you spend 15 hours working with it...

Let's see the images...


Of course, the first time we run the application, the Table is going to be empty...we just create it...



We can insert our first record...or maybe more to make it look better...


Oh...it seems that Mr. Ness doesn't have a band...no problem...we can update his record...


We can check if everything is OK or not...


Yes, now everything looks good...however...we need to show that the Delete link works as well...so...sorry for the guys...


Well...that's it...I had a lot of fun working on this...and it's another proof that SAP HANA is really versatile and can be used everywhere. Till next time -:)

Greetings,

Blag.

lunes, 19 de noviembre de 2012

Calling Python from ERP (With PyRFC)


If you read my previous blog Revisiting Python and SAP (With PyRFC) then you will recall that I said that with PyRFC we can not only retrieve data or execute services from the ERP side, but also use PyRFC as a server to be called from the ERP.

In other words, we can create a function in Python that will hosted by PyRFC and then called by an ERP function module.

In this blog we're going to build and XML comparator, that will read two XML files and will return the additions and deletions.

You might have already PyRFC installed, so let's go to the steps we need to follow to make this work...

First, we need to get the information of the Gateway server, so let's go to transaction SMGW and choose Goto --> Parameters --> Display. At the end of the table, you will find the needed information.


Then, we need to create an TCP/IP connection, so we can call our PyRFC Server. Go to SM59 and create the following.


Now, we need to create a file that will contain our connection information, for both the ERP and the Gateway Server.



In my testing, I used the gwhost that comes from the SMGW but somehow it didn't work properly...I used the value of ashost...so try with one first and if it doesn't work, do it like me.

Now, log into your ERP and create a simple structure called ZXML_RESPONSE.


Create a Table Type using this structure and call it ZXML_RESPONSE_TT.

Now, go to transaction SE37 and create a function module called ZXML_DIFF. This FM will be empty as the work will be done on the Python side.


For now...we're done with the ERP side...let's move to the Python side...

We need two libraries to make this work ListComparator and ElementTree, you can install both using easy_install.

The code is simple, Python will receive two path's, one for each XML, read it's contents, compare the differences and return what has been added and what has been deleted.



PyRFC_XML_Diff.py
from sapnwrfc2 import Server, Connection
from ConfigParser import ConfigParser
from elementtree import ElementTree as ET
from listcomparator.comparator import Comparator

config = ConfigParser()
config.read('sapnwrfc.cfg')


def xml_diff(request_context, XML_1="", XML_2="", ROOT="",
                    ADDITIONS=[], DELETIONS=[]):
    add_list = {}
    del_list = {}
    length = 0
    lower_root = ROOT.encode('utf-8')
    root_old = ET.parse(XML_1).getroot()
    root_new = ET.parse(XML_2).getroot()
    objects_old = root_old.findall(lower_root.lower())
    objects_new = root_new.findall(lower_root.lower())
    objects_old = [ET.tostring(o) for o in objects_old]
    objects_new = [ET.tostring(o) for o in objects_new]
    my_comp = Comparator(objects_old, objects_new)
    my_comp.check()

    for e in my_comp.additions:
        line = e.split("\n")
        length = len(line)
        for i in range(0, length):
            add_list = {}
            add_list.update({"LINE": line[i]})
            ADDITIONS.append(add_list)

    for e in my_comp.deletions:
        line = e.split("\n")
        length = len(line)
        for i in range(0, length):
            del_list = {}
            del_list.update({"LINE": line[i]})
            DELETIONS.append(del_list)

    return {
        'ADDITIONS': ADDITIONS,
        'DELETIONS': DELETIONS
    }

params_connection = config._sections['connection']
conn = Connection(**params_connection)
func_xml_diff = conn.get_function_description("ZXML_DIFF")

params_gateway = config._sections['gateway']
server = Server(**params_gateway)
server.install_function(func_xml_diff, xml_diff)
print "--- Server registration and serving ---"
server.serve(100)

Now that we have out Python Server ready...let's define a couple of XML to test this.




We can clearly see that both XML files are different...and that Blag individual has received a suspicious raise in his quantity...let's analyse this...

Go back to transaction SE37 and run the function. It's very important to fill the RFC Target sys parameter with the name we used to named our RFC destination.


The ROOT parameter will tell our function which one is the parent function of the XML files.

Now, run the PyRFC_XML_Diff.py application and you will see this, that will indicate us that the server is up and running.



Now, run the function and we will get our response from Python.


Now, we can analyse both the ADDITIONS and DELETIONS tables...


We can see that something happened with the account number 0001, which wasn't added, but it's value was modified. Also, in the second XML, Kiara's account was added.


So now, everything fall in line...the account for Blag used to have a quantity of 100 and now it has a quantity of 10,000.

So as you can see, we only use an empty shell from ERP to call all the functionality from Python.

Greetings,

Blag.

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.

martes, 13 de noviembre de 2012

SAP CodeJam Montreal

Thanks to an initiative of Krista Elkin, Jonathan Druker and myself ( with a lot of support from Craig Cmehil and Helena Losada ), SAP CodeJam Montreal is going live on Thursday, December 13, 2012 from 3 to 9 pm in the SAP Labs Montreal offices.


This is your chance to learn more about SAP HANA (Tables, Views, Atrtibute Views, Calculation Views, SQLScript and more), network with people from different companies and background and most important, have fun in this 6 hours event.

This is an event for developers, so might need to have some programming background. But don't worry, previous knowledge of SAP HANA is not needed.

Soon, I will update this blog with the link where you can sign up for the event. And remember, we have limited space so make to sure to sign up quick. (Already updated!)

Upon registration, we will send you an email with details of what you need to bring in order to get the most out of this awesome event.


Also, don't forget to press "Like" on the CodeJam's Facebook Page.

SAP Labs Montreal
111 Rue Duke
Suite 9000
Montreal, Quebec
H3C 2M1
Canada

See you there!

Greetings,

Blag.

viernes, 9 de noviembre de 2012

Consuming R from SAP Mobile Platform


Early this year, in March, I was visiting my team mates in SAP Labs Palo Alto, and my good friend a team mate Rui Nogueira asked to participate in his most excellent Technology Innovation Podcast show where we spoke about R and SAP HANA. By the end of the interview I said Rui that I was going to try to connect R and SUP (Which is now called SMP)...but actually...never did because my lack of time and specially and most important...because I didn't have a clue on how to do it...

So...yesterday, while I was reading the Steve Jobs book on my Kindle, I head a voice inside my head saying..."Dude! What the SAP? Where's R and SMP?"...at that moment...I knew I had to do something about it...

Again...I didn't have a clue on how to do it or how to really start working about it...but as I have already used Rook (R WebServer) in my blog RSAP, Rook and ERP and also Sinatra (Ruby WebServer) in my blog PowerBuilder and Gateway - The Sinatra style I knew, that was the way to go.

I knew that I needed to develop a Rook application to expose the data as JSON and pass it to SMP. Well...that failed quickly, because as far as I know and also my tests failed, SMP doesn't support JSON yet.

My next thought was to make the Rook application to expose the data as XML, which of course worked fine, but without using the standard XML library from R because the response is an C object that really looks bad when converted to a string.

First thing, was to think about a good example for this blog...but as they say, you have to teach a man how to fish...so I came up with a fairly simple example. Let's say you're a professor and my wife Milly, my daughter Kiara and myself are students. You have an Excel file where you will put the names and the grades and you want a mobile application that will read the file, calculate the means and provide the final score by simply passing the name of the student. I know...Excel? Why not SAP HANA? Well...you're an old fashion teacher...SAP HANA is so fast that you cannot even see it...so you stick to the most basic tools...


Of course, in R, we prefer to work with .CSV files, so being a good teacher, you create the file and give to us, so we can play with it.

To make thing simple for myself, I used my CloudShare.com account to start the work...I create my SMP application, called the Rook WebPage and test it on my BlackBerry emulator...everything worked like a charm...but...there's always one...the real thing came when I decided to move everything to AWS...

Thing is...and I didn't realize it in time...in CloudShare.com everything worked because both the SMP Server and the Rook Server are in the same place...the same localhost environment...in AWS, things change because the SMP Server is in the cloud while my Rook Server is in my localhost (laptop)...big problem...

I stayed yesterday working until 10:00 pm trying to figure out how to solve this big problem...maybe that's why I came up with a really solution...I said...Ok...let's move the Rook Server to the cloud as well! So I logged into my SMP Server, installed R and everything and run the Rook Server...back in my laptop...of course it failed miserably...the new Rook server was localhost but for my AWS server...so no way my Android emulator was going to be able to see it...

I said...Ok...don't panic...I have an R Server on AWS...let's do it there...another fail (getting used to it)...the R Server in AWS is headless, so no browser is allowed to work...also...Rook is always localhost, so there was no way to make the call...

I started to panic...so I went to sleep...at least for a while...

Today, I woke up at 5:30 am and start browsing hoping to see the light at the end of the tunnel...and I did...thank God...I did...

An amazing fella called Noah Lorang managed to make Rook work on Heroku...everything explain on his Github account...so my life was saved (not for long, sadly)...




I have never used Heroku before...and I gotta admit...is not for newbies...it really took me a long time to make it work...but I finally did it (Obviously...otherwise I wouldn't be boring you with all my senseless ranting)...

So...here's what I did...

  • I create myself a Heroku account and installed the Heroku Tool Belt.
  • I create myself a public key.
  • Inside the Git Bash application installed by the Heroku Tool Belt I log myself in.


Clone and create application in Heroku
git clone git://github.com/noahhl/rookonheroku.git blagcodes 
#(This will clone Noah's Github and create a folder called blagcodes 
#to store the codes)
heroku create blagrook 
#(I create an application for my Rook script)
git push heroku master 
#(This allow me to pass everything from my blagcodes folder 
#to my Github account)

With that, I was almost ready to rock...but I needed to do something else first...pass my own Rook script...

Summarize.R
library(Rook)
 
newapp<-function(env){
  req<-Rook::Request$new(env)
  res<-Rook::Response$new()
 
  name_param = req$params()$name
 
  Grades_Source = read.csv(file="Grades.csv",header=TRUE)
  Name<-Grades_Source$Name
  Grades<-Grades_Source$Grades
  Mean<-aggregate(Grades~Name,data=Grades_Source,FUN=mean)
  Mean_Result<-c(subset(Mean,Name == name_param))
 
  res$write("<root>")
  res$write("<Name>")
  res$write(Mean_Result$Name)
  res$write("</Name>")
  res$write("<Final_Grade>")
  res$write(as.character(Mean_Result$Grade))
  res$write("</Final_Grade>")
  res$write("</root>")
 
  res$finish()
}
 
 
server = Rhttpd$new()
server$add(app = newapp, name = "summarize")
server$start(listen="0.0.0.0", port=as.numeric(Sys.getenv("PORT")))
 
 
while(T) {
  Sys.sleep(10000)
}


Despise the name, I actually made an easier thing a just grabbed the file called demo.R and replace it with my own source code. Also, I copied the Grades.csv file to my blagcodes folder (which is located in my laptop).

The code is simple, we create a Rook application called "summarize" that will read the Grades.csv file, aggregate it using the mean function and print a basic XML structure passing the Name and Grade of the person we're passing a parameter. We need to pass the listen="0.0.0.0" and the port=as.numeric(Sys.getenv("PORT")) so Heroku knows how to call the page.

Back into the Git Bash I did the following to pass my changes back to Heroku...

Passing back to Heroku
git add .
git commit -am "message"
git push heroku

With this, everything was set-up and ready...so let's see how it looks...


(I'm using IE just because I wanted to show that the response from the Rook Application might look different than an XML response, but by looking at the source code you can actually see that's is an XML...also, because IE doesn't try to melt the tabs when putting them together as Chrome does).

When I start developing the SMP application I realized that calling a WebService wasn't an option...as this is of course not a WebService...so instead I used a REST Web Service...but it was asking me for XSD structures...so after another long time...I find a nice on-line tool to do that...

I simply pass an XML structure and let the tool work for me...



Of course...I have never worked with XSD before...so I didn't knew what to expect...thing is...if you use this structure...is going to fail...first because of the Final_Grade being xs:byte and second because when I was loading the parameters, Final_Grade was showing as well...and I didn't want it...so I made a copy of the file, change a bit here and there and came with Request.txt and Response.txt as you can see here...



So...as I was telling you...I create a REST WebService...


After this...came another tricky part as I wasn't sure how to make the parameter worked for me...gladly...I manage to make it work...


I load up my Request.txt and get the Root element...


Then I repeat the same for Response.txt and ended up with this...


After I create my Mobile Application, I create a Personalization Key to keep track of the parameter.


This is the look and feel of the application. Something very simple, you are requested a name, you press Get Grades and the result will be shown in a list.


Now...we're ready to test the application...



I guess...it didn't went very well for me in the exams...let's see how my daughter went...



As expected! My daughter is both smarter and beautiful than me...

Well...that's all folks...it took around 24 hours (summing up yesterday and today) to get this thing working...but I can assure Mr. Nogueira that Blag always keep his promises...I promised to have R working on SMP...and here it is...

Greetings,

Blag.

sábado, 3 de noviembre de 2012

Revisiting Python and SAP (With PyRFC)


A couple of days back Srdjan Boskovic wrote a blog called Python / ABAP Stack. This for sure, bring me back memories as I already played a lot with sapnwrfc from Piers Harding.

After some email exchanges with Srdjan and Jonas Kunze I was able to set up and start working with it.

You may ask...why another Python to SAP connector? Simply, while Pier's one is awesome, it lacked some features that SAP needed for internal development, so this guys take the hard work of making from the scratch exactly what they needed...one of the beauties of being a developer...you can grab an idea...a turn it into a full blown application or RFC connector in this case...

As a way to introduce PyRFC, the best example is always showing something with the flights tables...which are always present in every ERP installation and as I have it used before on SAP HANA and Python? Yes Sir!, Bottle seemed to be a good option...because you don't want to see more command line screen, don't you?

I will show the code first and the I will tell you about some of the most significant changes that I can see from using PyRFC (And by the way...I fight myself to make my example better and have some error handling, which is always good, even for humble blogs like this one).


Bottle_PyRFC.py
from bottle import get, post, request, run, redirect
from sapnwrfc2 import Connection, ABAPApplicationError, LogonError
from ConfigParser import ConfigParser
 
conn = ""
 
@get('/login')
def login_form():
    return '''<DIV ALIGN='CENTER'><BR><BR><BR><BR>
                <H1>Python (Bottle) & SAP - using PyRFC</H1>
                <BR><TABLE BORDER='1' BORDERCOLOR='BLUE'
                     BGCOLOR='WHITE'>
                <FORM METHOD='POST'>
                <TR><TD>User</TD><TD>
                <INPUT TYPE='TEXT' NAME='User'></TD></TR>
                <TR><TD>Password</TD>
                <TD><INPUT TYPE='PASSWORD' NAME='Passwd'></TD></TR>
                <TR><TD COLSPAN='2' ALIGN='CENTER'>
                <INPUT TYPE='SUBMIT' value='Log In' NAME='LOG_IN'>
                <INPUT TYPE='RESET' value='Clear'></TD></TR>
                </FORM>
                <TABLE>
              </DIV>'''
 
@post('/login')
def login_submit():
    global conn
    try:
        user = request.forms.get('User')
        passwd = request.forms.get('Passwd')
        config = ConfigParser()
        config.read('sapnwrfc.cfg')
        params_connection = config._sections['connection']
        params_connection["user"] = user
        params_connection["passwd"] = passwd
        conn = Connection(**params_connection)
        redirect("/choose")
    except LogonError:
        redirect("/error")
 
@get('/choose')
def choose_table():
    return '''<CENTER>
                <FORM METHOD='POST'>
                <INPUT TYPE='TEXT' NAME='Table'><BR>
                <INPUT TYPE='SUBMIT' value='Show Table'
                 NAME='Show_Table'>
                </FORM>
              </CENTER>'''
 
@get('/error')
def error():
    output = "<div align='center'><h1>Invalid username or password</h1></div>"
    return output
 
@post('/choose')
def show_table():
    global conn
    fields = []
    fields_name = []
    counter = 0
    table = request.forms.get('Table')
    try:
        tables = conn.call("RFC_READ_TABLE", QUERY_TABLE=table, DELIMITER='|')
        data_fields = tables["DATA"]
        data_names = tables["FIELDS"]
        long_fields = len(data_fields)
        long_names = len(data_names)
 
        for line in range(0, long_fields):
            fields.append(data_fields[line]["WA"].strip())
        for line in range(0, long_names):
            fields_name.append(data_names[line]["FIELDNAME"].strip())
 
         output = "<div align='center'><h1>%s</h1></center>" % table
 
        output += "<table border='1'><tr>"
        for line in range(0, long_names):
            field_name = fields_name[line]
            output += "<th bgcolor='#B8D5F5'> %s </th>" % field_name
        output += "</tr>"
        for line in range(0, long_fields):
            counter += 1
            if(counter % 2 == 0):
                output += "<tr bgcolor='#DCE1E5'>"
            else:
                output += "<tr>"
            data_split = fields[line].split("|")
            for line in range(0, long_names):
                output += "<td> %s </td>" % data_split[line]
            output += "</tr>"
        output += "</table>"
 
     except ABAPApplicationError:
        output = "<div align='center'><h1>Table %s was not found</h1></div>" % table
        return output
 
     return output
    conn.close()
 
run(host='localhost', port=8080)


So, for me PyRFC has some mayor benefits, like the option to catch ABAPApplicationError and LoginError (I assume that Pier's version have it as well, but I never worried to look for it...shame on me), also the way to call the Function Module is very clean, a simple Python function that will receive as parameters the FM name and the parameters, taking from us the need to define each parameter as an attribute of the object. Also, it's really fast and it can be used on the Server side...but we will talk about that later...in other blog...when I got the chance to actually work with it...

Let's run this program and see how it looks...when running it from Python you might need to go to your browser a pass the following link (as we're executing a Bottle application):

http://localhost:8080/login


As always, was very enjoyable to work with Python, and of course, when mixing it my PyRFC the fun exceed my expectations as right now it's almost 8:00 pm on Saturday night...and I'm posting a blog of what I worked on almost all afternoon...programming is fun...don't forget it...

Greetings,

Blag.

SAP HANA and R (The way of the widget)


A real developer never stops learning that's a quote I always love to repeat...because it applies to my life...you can know a lot of things but there's always something new to learn, or to re-learn. That's why a couple of days ago I start reading wxPython in Action, a really nice book that show us how to use wxWidgets in Python. While I was reading the book...a thought came into my mind...a lot of programming languages have implementation of wxWidgets like Ruby, Python, Euphoria and Perl, to name a few...but what about R? Does R have something like that? Of course...it is named gWidgets.

After that, I started to read the documentation, search for examples in the web and suddenly I knew that I wanted to build an small application and integrate it with SAP HANA.

For those who doesn't know yet, my good friend and team mate Juergen Schmerder posted on Twitter one of the biggest news (at least for me)...




This means that I can use ODBC in my blogs or in my personal project without worrying about SAP not supporting it...really big news...you can read more here
SAP HANA Opens SQL Interfaces for Custom Developed Apps.

So, here's the app I came with (Keep in mind that you will need the latest version of R (2.15.2) available from CRAN.
You will need the following libraries as well RODBC, gWidgets, cairoDevice and gWidgetsRGTk2.



gWidgets_SAP_HANA.R
library("RODBC")
require ( gWidgets )
options ( guiToolkit="RGtk2" )
require( cairoDevice )

ch<-odbcConnect("HANA",uid="SYSTEM",pwd="manager")
airline_values<-c()
result<-sqlQuery(ch,"SELECT DISTINCT CARRID FROM SFLIGHT.SPFLI")
for(i in 1:nrow(result)){
 airline_values<-append(airline_values, as.character(result$CARRID[i]))
}

selected_airline<-""
distances<-data.frame(CONNID="", DISTANCE="", stringsAsFactors=FALSE)
g_distance<-c()
g_connid<-c()

window<-gwindow("gWidgets and SAP HANA", visible=FALSE)
group<-ggroup(cont=window, expand=TRUE, horiz=FALSE)
lyt<-glayout(cont=group, spacing=2)
lyt[1, 1:15, anchor=c(-1,0)]<-(search<-glabel("Carrier", cont=lyt))
lyt[1, 18:34, anchor=c(-1,0)]<-(airlines<-gcombobox(c("", airline_values), cont=lyt))
lyt[1, 38:42, anchor=c(-1,0)]<-(button<-gbutton("Submit", cont=lyt))
lyt[3:200, 1:200, anchor=c(-1,0)]<-(notebook<-gnotebook(cont=lyt))

group1<-ggroup(cont=notebook, label="Table")
group2<-ggroup(cont=notebook, label="Graphic")
plot_device<-ggraphics(cont=group2)
table<-gtable(distances,cont=group1,expand=TRUE)

addHandlerClicked(button, handler=function(h, ...){
  value<-""
  query<-"SELECT CONNID, SUM(DISTANCE) AS DISTANCE FROM SFLIGHT.SPFLI WHERE CARRID ="
  value<-paste(value,"'",svalue(airlines),"'", sep="")
  query<-paste(query, value , sep=" ")
  query<-paste(query, "GROUP BY CONNID", sep=" ")
  distances<-sqlQuery(ch, query)
  table[]<-distances
  barplot(distances$DISTANCE, names.arg=distances$CONNID)
})

visible(window)<-TRUE


Basically, what we are doing is to create a window container, that it's going hold up a group, which is going to hold up a layout, which is going to hold up a lable, a combobox, a button and a notebook (tabbed panel). The notebook will hold up two groups which are going to contain a table and a graphic output.
When we start the application, we're going to load the CARRID values from SPFLI into the combobox, then after choosing a value and pressing the button, we're going to build a query that will return all the CONNIDs along with a sum of the DISTANCE field. That information will be shown in both the table and the graphic output (using a Barplot).

Let's see how it looks when running the app.


For some reason, the last tab is always shown first and I haven't found a way to get rid of that behaviour.


As we're just running the app, we show the table with an empty structure.


The combobox is filled with the CARRID values.


We choose "AA" and show the values on the table. Now we can switch tabs and the generated graphic.


Now, just to demonstrate that this really works, we're going to choose another carrier, let's say "LH".


Let's see the graphic now.


It works just fine.

So, what do you think? For the user perspective dealing with a gWidgets Application is was much better than dealing with command line Application, no?

Greetings,

Blag.