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.