sábado, 29 de diciembre de 2012

SAP HANA goes mobile with PhoneGap (Cordova)


Three days ago, my friends at Packt Publishing send me a free ebook called PhoneGap Beginner's Guide. And while I knew about PhoneGap I had never really use it...so of course, my first thought was...How can I make this work with SAP HANA?

Two days ago, I started to read the book and make the PhoneGap installation...which was a total pain and didn't even work...so I simply put it aside for next year...

Yesterday...my developer spirit could more than me...and since 9 am to 11:30 pm I embarked myself on a crusade to have PhoneGap up and running and of course...to make it work with SAP HANA...here's my story...so you don't have to break your head


With all that...I was almost ready to go...as I was missing the Cordova-2.2.0.jar file...for that...I did the following...

  • Download and copy into the Cordova\libs folder the commons-codec-1.7.jar.
  • I went to Cordova\android\framework\scr\org\apache\cordova and modified the file CordovaWebView.java by commenting out this two lines...
    • if(android.os.Build.VERSION.SDK_INT < android.os.Build.VERSION_CODES.HONEYCOMB)                            settings.setNavDump(true);
  • Using CMD, I went to the Cordova\android\framework directory and execute ==> ant jar.

Now...I was really ready to go...so I create a folder called Projects inside Corbova...and again on CMD I did the following...

  • Inside Corbova\Projects ==> create C:\Cordova\Projects com.BlagTest BlagTest

That created a Blag_Test folder with all the related files from the Cordova project. Then I simply upload it to Eclipse, and made a couple of test to see if everything was working as expected...it did...so the new odyssey for SAP HANA was going to start...

At first...my initial thought was...this should be easy...I have already do it on PowerBuilder, so basically I need to import the ngdbc.jar into my Eclipse Project and that's it...wrong! Didn't work...and after several tries and fails...I finally see the light...I delete the project from Eclipse...copy the ngdbc.jar inside my libs folder of BlagTest...re-imported on Eclipse...and magically...I got a connection to SAP HANA...

Now...make that connection work was another nightmare...for this blog I needed to undust my almost forgotten knowledge of Java and JavaScript...and also...learn new things like PhoneGap and JQueryMobile...

But...I'm going to try to keep the long story short, so you don't get bored...

  • I create a new class called MyClass.java (I was tired...so forget about the silly name)

MyClass.java
package com.BlagTest;

import java.sql.*;
import java.util.ArrayList;

public class MyClass {
 public ArrayList<String> getData(String p_carrid){
     Connection connection = null;
     ArrayList<String> carrid = new ArrayList<String>();
     String data = "";
  try{
   Class.forName("com.sap.db.jdbc.Driver");
   try{
    connection =    
                       DriverManager.getConnection("jdbc:sap://XX.XX.XXX.XXX:30115",
                       "SYSTEM","manager");
   }
   catch (SQLException e){
   }
  } 
  catch (ClassNotFoundException e){
  }    
  if(connection != null){
   try{
    String sqlstring = "select CONNID, FLDATE, PRICE from 
                                SFLIGHT.SFLIGHT where carrid = '" + p_carrid + "'";
    Statement stmt = connection.createStatement();
    ResultSet rs = stmt.executeQuery(sqlstring);
    while(rs.next()){
     data = rs.getString("CONNID") + "/" + 
                                        rs.getString("FLDATE") + "/" +  
                                        rs.getString("PRICE"); 
     carrid.add(data);
    }
   }
   catch(SQLException e){  
   }
  }
  return carrid;
   }
}


In this file, what I'm doing is establishing an JDBC connection to my SAP HANA Server hosted on TK ucloud biz. The I'm selecting the CONNID, FLDATE and PRICE from the SFLIGHT table where the CARRID is going to be a parameters send from the application. As I didn't want to pass a multidimensional array, or an array of arrays, or anything like that...I simply concatenate the values using a "/" to split them later.


  • I modified the already existing BlagTest.java file


BlagTest.java
package com.BlagTest;

import android.app.Activity;
import android.os.Bundle;
import org.apache.cordova.*;

public class BlagTest extends DroidGap
{
 
 private MyClass mc;
 
    @Override
    public void onCreate(Bundle savedInstanceState)
    {
     super.onCreate(savedInstanceState);
        super.init(); 
        mc = new MyClass();
        super.appView.getSettings().setJavaScriptEnabled(true);
        super.appView.addJavascriptInterface(mc, "MyCls");
     super.loadUrl("file:///android_asset/www/index.html");        
    }
}


Here, basically we saying that we want to be able to send data from Java to JavaScript by using the setJavaScriptEnabled(true) and then adding the addJavaScriptInterface(mc, "MyCls") we're telling how our class is going to be called...when we call them from JavaScript.


  • Finally...I delete everything from the already generated index.html file and put this code...


index.html
<html>
<head>
<title>SAP HANA from PhoneGap</title>
<meta name="viewport" content="width=device-width, initialscale=1.0"></meta>
<link rel="stylesheet" href="http://code.jquery.com/mobile/1.1.0/jquery.mobile-1.1.0.min.css"/>
<script src="http://code.jquery.com/jquery-1.7.1.min.js"></script>
<script src="http://code.jquery.com/mobile/1.1.0/jquery.mobile-1.1.0.min.js"></script>
<script>

 function getData(){
 var carridField = document.getElementById('carrid');

  var getCarrid = carridField.value;
  var myArrayList = window.MyCls.getData(getCarrid);
  carridField.value = "";
  $("#content").append("<ul id='list' data-role='listview' data-inset='true'</ul>");
  $("#content").trigger("create");
  for(var i = 0; i < myArrayList.size(); i++){
   var array = "" + myArrayList.get(i);
   array = array.split('/');
   var _connid = array[0], _fldate = array[1], _price = array[2];
   var list = "<li><p>CONNID: " + _connid + "</p><p>FLDATE: " + 
                        _fldate + "</p><p>PRICE: " + _price + "</p></li>";
   $("#list").append(list);
  }
  $("#list").listview("refresh");
 }

</script>
</head>
<body>

<div data-role="page">
 <div data-role="content" id="content">
  <div align="center"><h1>SAP HANA from PhoneGap</h1></div>
  Carrid: <input type="text" id="carrid" size="2"/>
  <button id="submitCarrid" onClick="getData()">Submit</button>
 </div>
</div>
</body>
</html>


What I'm doing here...is as following...


  • I have an input text and a button. In the input text, we're going pass an CARRID value and when pressing the button, we're going to call a JavaScript function.
  • The JavaScript function will collect the value from the input text, will call our Java function using window.MyCls.getData() and pass the CARRID parameter. This should return an ArrayList...but instead...it return an Object...so we need to handle it later...
  • Using JQueryMobile we're going to create a ListView which is like an HTML Table on steroids...and the thing I love about JQueryMobile is that we only need to include one "link rel" and two "script src" lines to make it work...as it grabs it from an on-line location.
  • We're going to do a FOR beginning from 0 till the size of our Object, and then will extract it's content using .get() will turning it into an String using "".
  • We simply split the newly created String and assign it to variables.
  • We add the lines to our ListView and update it when we finish.


After that, we can simply go to Project --> Clean to rebuild our project and then right click on our Project Folder and choose Run As --> Android Application.





It took so long time...but the rewards can't be greater...hope you like this -:)

Greetings,

Blag.

lunes, 17 de diciembre de 2012

MySQL, PostreSQL and SAP HANA - A friendly comparison

UPDATE! Thanks to a comment by Alan Suffolk I fixed my script a little bit, just moving the end = time.clock() and time_taken = end - start after the cur.execute(query) because that's exactly when the SAP HANA query ends...you can see the new processing time in the image below...

My good friend Pakdi Decnud gave a great idea while we were having lunch the very same day of the SAP CodeJam Montreal event.

Pakdi told me..."Why don't you make a comparison between SAP HANA and let's say...MongoDB"...I thought that it was of course a great idea...so yesterday I start exploring MongoDB...so you may ask yourselves..."Why then are you talking about MySQL and PostreSQL?"

Easy answer...and here are my thoughts...
  • I really don't get MongoDB...the whole No-SQL is really alien to me...
  • MongoDB is "Document" based, meaning that you create collections of documents, no databases nor tables...
  • MongoDB doesn't support Inner Joins and aggregates need a framework that it's more weird than MongoDB itself...
  • MongoDB is not meant for enterprise applications

That's why I decide to make a little bit shift and grab the two most used databases by start-ups and developers...MySQL and PostgreSQL.

For this blog, I wanted to have a lot of information...so as always, I grab my beloved Python and create a little script to generate 1 million records for two tables. One script per table.

The structure of the tables is as follows...

DOC_HEADER
Field NameData TypeLength
DOCUMENT_IDVARCHAR8
YEARVARCHAR4
AREAVARCHAR2
NAMEVARCHAR20
LAST_NAMEVARCHAR20

DOC_DETAIL
Field NameData TypeLength
DOCUMENT_IDVARCHAR8
YEARVARCHAR4
AREAVARCHAR2
AMOUNTVARCHAR

And here are the script to generate the 1 million records in a nice .CSV file

Doc_Header_Generator.py
import random
import csv
 
names = ["Anne", "Gigi", "Juergen", "Ingo", "Inga", "Alvaro", "Mario",
"Julien", "Mike", "Michael", "Karin", "Rui", "John", "Rocky", "Sebastian",
"Kai-Yin", "Hester", "Katrin", "Uwe", "Vitaliy"]
last_names = ["Hardy", "Read", "Schmerder", "Sauerzapf", "Bereza", "Tejada",
"Herger", "Vayssiere", "Flynn", "Byczkowski", "Schattka",
"Nogueira", "Mayerhofer", "Ongkowidjojo", "Wieczorek", "Gau", "Hilbrecht",
"Staehr", "Kylau", "Rudnytskiy"]
area = ["001", "002", "003", "004", "005", "006", "007", "008", "009", "010"]
year = ["2000", "2001", "2002", "2003", "2004", "2005", "2006", "2007",
"2008", "2009", "2010", "2011", "2012"]
 
def Generate_File(pSchema, pNumber):
    iNumber = 0
    c = csv.writer(open("Doc_Header.csv", "wb"))
 
    while iNumber < pNumber:
        queries = []
        r_doc_id = random.randrange(1, 999999)
        r_names = random.randrange(0, 20)
        r_lastnames = random.randrange(0, 20)
        r_areas = random.randrange(0, 10)
        r_years = random.randrange(0, 13)
        iNumber += 1
        queries.append(r_doc_id)
        queries.append(year[r_years])
        queries.append(str(area[r_areas]))
        queries.append(names[r_names])
        queries.append(last_names[r_lastnames])
        c.writerow(queries)
 
num_files = input("How many records?: \n")
Generate_File(num_files)

Doc_Detail_Generator.py
import random
import csv
 
area = ["001", "002", "003", "004", "005", "006", "007", "008", "009", "010"]
year = ["2000", "2001", "2002", "2003", "2004", "2005", "2006", "2007",
"2008", "2009", "2010", "2011", "2012"]
 
def Generate_File(pSchema, pNumber):
    iNumber = 0
    c = csv.writer(open("Doc_Detail.csv", "wb"))
 
    while iNumber < pNumber:
        queries = []
        r_doc_id = random.randrange(1, 999999)
        r_areas = random.randrange(0, 10)
        r_years = random.randrange(0, 13)
        r_amount = random.randrange(0, 10000, 1)
        iNumber += 1
        queries.append(r_doc_id)
        queries.append(year[r_years])
        queries.append(str(area[r_areas]))
        queries.append(r_amount)
        c.writerow(queries)
 
num_files = input("How many records?: \n")
Generate_File(num_files)


With the two files ready, I upload them to MySQL, PostgreSQL and SAP HANA.

To measure the speed, I create three Python scripts using...yes...again Bottle...

The basic idea is to join the two tables, select the Document_Id, Year, Area and the sum of Amount.

Let's start with the MySQL Script...

MySQL_Bottle_Documents.py
from bottle import get, run
import mysql.connector
import time


@get('/show_query')
def show_form():
    counter = 0
    start = time.clock()
    conn = mysql.connector.Connect(host='localhost', user='root',
                        password='root', database='P075400')
    cur = conn.cursor()

    query = '''SELECT A.DOCUMENT_ID, A.YEAR, A.AREA, SUM(AMOUNT)
                  FROM DOC_HEADER AS A INNER JOIN DOC_DETAIL AS B
                  WHERE A.DOCUMENT_ID = B.DOCUMENT_ID
                      AND A.YEAR = B.YEAR
                      AND A.AREA = B.AREA
                  GROUP BY DOCUMENT_ID, YEAR, AREA'''
    cur.execute(query)

    output = "<DIV ALIGN='CENTER'><TABLE BORDER='1'>"
    output += "<TR BGCOLOR='#B9C9FE'>"
    output += "<TH>Document</TH><TH>Year</TH>"
    output += "<TH>Area</TH><TH>Amount</TH>"
    output += "</TR>"
    for row in cur:
        counter += 1
        document_id = str(row[0])
        year = str(row[1])
        area = str(row[2])
        amount = str(row[3])
        output += "<TR BGCOLOR='#E8EDFF'>"
        output += '''<TD>%s</TD><TD>%s</TD>
                         <TD>%s</TD><TD>%s</TD>''' %     
                   (document_id, year,area, amount)
        output += "</TR>"
    output += "</TABLE>"
    end = time.clock()
    time_taken = end - start
    output += "<H1>SAP HANA - %s records in %s seconds</H1></DIV>"\
    % (counter, time_taken)
    return output

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

I let the script run...and after more than one hour...I simply got bored and interrupt the process...

So, I continue with PostgreSQL...

PostgreSQL_Bottle_Documents.py
from bottle import get, run
import psycopg2
import time


@get('/show_query')
def show_form():
    counter = 0
    start = time.clock()
    conn = psycopg2.connect("dbname=P075400 user=postgres password=root")
    cur = conn.cursor()

    query = '''SELECT "DOC_HEADER"."DOCUMENT_ID", "DOC_HEADER"."YEAR",
                "DOC_HEADER"."AREA", SUM("DOC_DETAIL"."AMOUNT") FROM
                public."DOC_HEADER", public."DOC_DETAIL" WHERE
                "DOC_HEADER"."DOCUMENT_ID" = "DOC_DETAIL"."DOCUMENT_ID"
                AND "DOC_HEADER"."YEAR" = "DOC_DETAIL"."YEAR"
                AND "DOC_HEADER"."AREA" = "DOC_DETAIL"."AREA"
                GROUP BY "DOC_HEADER"."DOCUMENT_ID", "DOC_HEADER"."YEAR",
                "DOC_HEADER"."AREA"'''
    cur.execute(query)

    output = "<DIV ALIGN='CENTER'><TABLE BORDER='1'>"
    output += "<TR BGCOLOR='#B9C9FE'>"
    output += "<TH>Document</TH><TH>Year</TH>"
    output += "<TH>Area</TH><TH>Amount</TH>"
    output += "</TR>"
    for row in cur:
        counter += 1
        document_id = str(row[0])
        year = str(row[1])
        area = str(row[2])
        amount = str(row[3])
        output += "<TR BGCOLOR='#E8EDFF'>"
        output += '''<TD>%s</TD><TD>%s</TD>
                         <TD>%s</TD><TD>%s</TD>''' % 
                     (document_id, year, area, amount)
        output += "</TR>"
    output += "</TABLE>"
    end = time.clock()
    time_taken = end - start
    output += "<H1>PostgreSQL - %s records in %s seconds</H1></DIV>"\
    % (counter, time_taken)
    return output

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

This time...I was lucky...


Out of 2 millions records, PostgreSQL managed to aggregate the amount field and generate 7669 records in 36 seconds...not bad at all...

For SAP HANA, I decided to take fully advantage of the Calculation Views, so I create the following...



I joined both tables, used a projection, applied the aggregation and specified the result...then I wrote this Python script...

SAPHANA_Bottle_Documents.py
from bottle import get, run
import dbapi
import time


@get('/show_query')
def show_form():
    counter = 0
    start = time.clock()
    conn = dbapi.connect('hanasvr-02', 30015, 'P075400', '5pA5kb6i')
    cur = conn.cursor()

    try:
        ret = cur.execute("drop type test_out")
    except dbapi.Error:
        pass

    try:
        ret = cur.execute("drop procedure pyTest")
    except dbapi.Error:
        pass

    queries = ['''create type test_out as table (DOCUMENT_ID NVARCHAR(8),
               YEAR VARCHAR(4), AREA VARCHAR(2), AMOUNT BIGINT)''',
               '''create procedure pyTest (OUT out_test TEST_OUT)\
               language sqlscript reads sql data with result view ProcView as\
               begin\
               out_test = CE_CALC_VIEW("_SYS_BIC"."blag/CV_DOCUMENTS",
               ["DOCUMENT_ID","YEAR","AREA","AMOUNT"]);\
               end''']

    for query in queries:
        cur.execute(query)
        conn.commit()

    query = '''select DOCUMENT_ID, YEAR, AREA, AMOUNT from ProcView'''

    cur.execute(query)
    ret = cur.fetchall()

    output = "<DIV ALIGN='CENTER'><TABLE BORDER='1'>"
    output += "<TR BGCOLOR='#B9C9FE'>"
    output += "<TH>Document</TH><TH>Year</TH>"
    output += "<TH>Area</TH><TH>Amount</TH>"
    output += "</TR>"
    for row in ret:
        counter += 1
        document_id = str(row["DOCUMENT_ID"])
        year = str(row["YEAR"])
        area = str(row["AREA"])
        amount = str(row["AMOUNT"])
        output += "<TR BGCOLOR='#E8EDFF'>"
        output += '''<TD>%s</TD><TD>%s</TD>
                     <TD>%s</TD><TD>%s</TD>''' % 
                     (document_id, year, area, amount)
        output += "</TR>"
    output += "</TABLE>"
    end = time.clock()
    time_taken = end - start
    output += "<H1>SAP HANA - %s records in %s seconds</H1></DIV>"\
    % (counter, time_taken)
    return output

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

After the execution...I couldn't be happier...here's the result...



SAP HANA managed the same 2 million records...generate the same 7669 aggregated records in only 18 seconds...that's 50% faster than PostgreSQL and...well...let's only say...way faster than MySQL...

Now...tell me that SAP HANA is not the coolest and fastest Database around...I dare you -:)

By doing that little fix on my Python Script for SAP HANA...the new processing time, without generating the Bottle table is...


Greetings,

Blag.

sábado, 15 de diciembre de 2012

A small tribute to Innovation

I believe myself to be an innovator...a great developer...someone that goes beyond to make the impossible...but...there's always someone else making things that to me...seem impossible...

This is a small tribute to those people who had motivate me and always will...in no particular order...some of my all time heroes...

Ivan Femia - abap2xlsx - Generate your professional Excel spreadsheet from ABAP

I remember, a couple of years ago that I need to developer a really complex ALV report...that seems impossible to build...so my first thought was to use OLE and build it as an Excel file...I had done that before, but it was slow and buggy...somehow...like send from heaven...abap2xlsx came down to me and hit me in the face...it was totally awesome! How this guy Ivan was? How could he build something like this? It was just what I needed! I quickly install it, make a short report and show it to the functional...she love it as well...and I start working on the real report...after some hard work (and I'm proud to say that I even made my small contribution to the project) the report was finished and really shining...the customer love it and asked for more...really awesome...

Gregor Wolf - Community Project ZGEOCODE

It's really hard and even maybe a blasphemy to name Mr. Wolf and show only one blog...Gregor has contribute (and he's still doing it) to so many community projects...that it's think it's fair to make this analogy..."Gregor Wolf is to the community, what the flight tables are to every ERP installation". He's just an amazing guy with the biggest heart...and ZGEOCODE is just an example of what he's capable of...pure magic...

Juergen Schmerder - New from SAP Research: Blue Ruby - a Ruby VM in ABAP

Juergen doesn't blog as much as I would like him to do...but he do...you gotta hold on your chair...Blue Ruby was to me...a revelation...almost made me cry when I read about it...I couldn't believe that something like that could happen in the ABAP world...and while it didn't lasted too long...Blue Ruby will be always in my heart...

Thomas Jung - New ABAP Editor Backport!

Talk about Thomas, is talk about raw programming energy...this guy is so good, you can't believe he's a regular human person...when he wrote about porting the new ABAP Editor to older releases was like being blind and open your eyes for the first time...or waking up from the Matrix...whatever you like...Thomas has been the king of ABAP, BSP, WebDynpro and now SAP HANA...talking about SAP and not mention Thomas should be a crime...because this guy has contribute so much, that I could never speak talking about him...just amazing!

Piers Harding - Scripting - Whats your fancy - Perl, Python or Ruby?

Piers is and always will be my totally and complete hero...I mean...he created connectors RFC connector for Perl, Ruby and Python...how crazy is that?! You know why I learned Ruby and Python? Because of Piers! Sure I did...it was because his RFC connectors that I said to myself..."Dude...you like Scripting Languages...you like PHP...but this guy is offering a whole new world with Ruby and Python...go get them!"...but the lovely story doesn't end there...you know that I have dedicated myself to promote the integration of SAP HANA and R in many blogs...and because my current title, I haven't done any ABAP in more than a year...well...Piers created an RFC connector for R too! I couldn't believe it when I saw it...and one of my biggest achievements and something that will make proud forever, is that I worked very closed to him to create the Windows version of the R RFC connector...it was one of my "Wayne's World" moments...

Daniel McWeeney and Ed Herrmann - SAPlink One Week, One New Version -- UPDATE

I remember...that a long time ago...in my first years in the ABAP world...my team needed to make a backup of an SAP system...so we had to copy and paste tables and programs in notepads to reproduce them on a newer system (Looking back...I could say...WTF?!)...anyway...at that time I decided to create a couple of applications to automated the process...ABAP applications that would read all the Z tables and programs and create notepad files that could be easily ported into a new system...some years later...SAPLink saw the light...it was an amazing program...not a couple...but a single program that did what my program did...only...100 times better...in single XML file you could have a complete application (The main program, includes, tables, structures, etc)...these guys, that sadly are not involved with the community any more (We miss you guys), create the tool that every abaper in the world was looking for...a simple application that in my humble opinion...changed the world of ABAP consulting...

To finish this blog, I just would like to say..."Never stop dreaming...never stop creating...never stop being innovative...because your passion, it's what makes our world go round"...

Greetings,

Blag.

viernes, 14 de diciembre de 2012

SAP Code Jam Montreal - The Experience

Yesterday, December, Thursday 13, 2012 we held the first SAP CodeJam Montreal in the EBC of SAP Labs Montreal.

Special thanks goes to Krista Elkin and Jonathan Druker, with a great support from Helena Losada

The event started at 3:00pm and lasted until 7:00pm. We had about 30 people (SAP and non-SAP).

I was the host for the event, where I teach them about SAP HANA and creation of Tables, Views, Attribute, Analytic and Calculation Views, connection to Microsoft Excel and SQLScript.


I tried to make the event as casual as possible, so people had the right to interrupt me at any time and make questions...and even help me out when I make a mistake -:)  So kudos to Pierre Dominique for his great and valuable help...

People were really engaged following the Workbook that I compiled for the event, building all the examples and exercises.






Around 6:00pm we had a little break for "Pizza Time" and networking.




After that, we continue...




At 7:00pm we finished the event and people start leaving and according to their on-site feedback, they had a great time and really enjoyed the event.




For me it was really a great experience and I'm looking forward to repeat it next year -:)

Greetings,

Blag.

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.