sábado, 28 de julio de 2012

Bring some Euphoria to SAP HANA


Disclaimer: This blog is based on my own personal experiences and it's not endorsed or supported by SAP. ODBC connection to SAP HANA is neither endorsed or supported by SAP, so it must be used under your own risk, no support will be provided.

As a Technology Evangelist, I'm always trying to generate new content to share with my fellow developers, so the other day I was doing some Node.JS learning and then, all of a sudden I remembered a programming language I used to use about 10 years ago...Euphoria...which is by the way called now OpenEuphoria as it went Open Source.

Why should anybody cared about Euphoria? Well...according to the site..."It's simpler than Basic and more powerful than C++"...I have use it again for this blog, and I gotta say, the haven't loose the magic...Euphoria is a lovely programming language with a huge library to choose from.

So...why do we need to start?

Download OpenEuphoria
Download wxEuphoria

For this blog, I tried to use my Windows 7, but the problem is that the ODBC connector doesn't work on 64bits, so I went to my Windows XP Virtual Machine and started the fun.

I created an ODBC connection to my Amazon Web Services called SAP_HANA and from there, it was just coding and fun.

To make things simple, I used the same approach I used for my blog SAP HANA and Python? Yes Sir! which is basically, connect to SAP HANA via ODBC, show a list of CARRIERS and CITY FROM's and the perform a query to gather and show data.


Euphoria_HANA.exw
include wxeud.e as wxeud
include odbc.e as odbc
include std/sequence.e as seq

global atom hconn, hstmt
global sequence dsn_var, user, auth, msg, carrier_key
global object data

carrier_key = {}

constant
main = create( wxFrame, {0, -1, "Euphoria and SAP HANA", -1, -1, 450, 300}),
win = create( wxPanel, main ),
ldsn = create( wxStaticText, {win, -1, "DSN:",120, 60}),
dsn = create( wxTextCtrl, {win, -1, "", 200, 55} ),
lusername = create( wxStaticText, {win, -1, "Username:",120, 90}),
username = create( wxTextCtrl, {win, -1, "", 200, 85} ),
lpassword = create( wxStaticText, {win, -1, "Password:",120, 120}),
password = create( wxTextCtrl, {win, -1, "", 200, 115, -1, -1, wxTE_PASSWORD} ),
connect = create( wxButton, {win, -1,"Connect", 200, 150})

constant
main2 = create( wxFrame, {0, -1, "Euphoria and SAP HANA", -1, -1, 450, 300}),
win2 = create( wxPanel, main2 ),
lcarrier = create( wxStaticText, {win2, -1, "Carrier:",120, 60}),
carrier = create( wxComboBox, {win2, -1, "", 200, 55, -1, -1, {}}),
lcityfrom = create( wxStaticText, {win2, -1, "City From:",120, 90}),
cityfrom = create( wxComboBox, {win2, -1, "", 200, 85, -1, -1, {}}),
show_query = create( wxButton, {win2, -1,"Show Query", 200, 120})

constant
main3 = create( wxFrame, {0, -1, "Euphoria and SAP HANA", -1, -1, 600, 600}),
win3 = create( wxPanel, main3 ),

goback = create( wxButton, {win3, -1,"Go Back", 260, 1}),
grid = create( wxGrid, {win3, -1, 1, 30, -1, -1, 1, 1, 6} )

function getConnected()
 if initODBC() > 0  then
  abort(0)
 end if
 dsn_var = get_text_value(dsn)
 user = get_text_value(username)
 auth = get_text_value(password)
 hconn = openConnectionODBC( dsn_var, user, auth )
 if not hconn then
  message_box("Connection error","Error",wxOK)
  return 0
 elsif hconn > 0 then
  msg = getErrorODBC( hconn )
  message_box(msg[2],"Error",wxOK)
  return 0
 else
  return 1
 end if
end function

function getQuery(sequence sql)
 hstmt = prepareSQL( hconn, sql )
 data = executeSQL( hstmt )
 if hstmt > 0 then
  message_box("Query failed","Error",wxOK)
 end if
 if data > 0  then
  message_box("No data found","Error",wxOK)
  return 0
 else
  data = {getColumnHeaders( hstmt )} & odbc:getData( hstmt )
  return data
 end if
end function

function fillParameters(object data, atom pos, atom combo, atom key)
 sequence value, seq
 seq = {}
 if sequence( data ) then
  for i = 2 to length( data ) do
   value = remove_all(0,data[i][pos])
   seq = append(seq, value)
   if key = 1 then
    value = remove_all(0,data[i][1])
    carrier_key = append(carrier_key, value)
   end if
     end for
     wxeud:add_item(combo, seq)
 end if
 return 1
end function

function fillCombos()
 sequence sql
 sql = "SELECT CARRID,CARRNAME FROM SFLIGHT.SCARR WHERE MANDT = 300"
 data = getQuery(sql)
 fillParameters(data, 2, carrier, 1)
 sql = "SELECT DISTINCT CITYFROM FROM SFLIGHT.SPFLI WHERE MANDT = 300"
 data = getQuery(sql)
 fillParameters(data, 1, cityfrom, 0)
 return 1
end function

function initializeGrid()
 set_col_label(grid,0,"Carrier")
 set_col_label(grid,1,"Connection")
 set_col_label(grid,2,"Flight Date")
 set_col_label(grid,3,"Passenger Name")
 set_col_label(grid,4,"City From")
 set_col_label(grid,5,"City To")
 atom carrier_sel, len, row, col
 sequence sql, value
 carrier_sel = get_selection(carrier) + 1
 sql = "SELECT SBOOK.CARRID,SBOOK.CONNID,FLDATE, " &
          "PASSNAME,CITYFROM,CITYTO" &
          " FROM SFLIGHT.SBOOK INNER JOIN SFLIGHT.SPFLI" &
          " ON SBOOK.CONNID = SPFLI.CONNID" &
          " WHERE SBOOK.CARRID = '" & carrier_key[carrier_sel] & "'" &
          " AND CITYFROM = '" & get_string_selection(cityfrom) & "'" &
          " AND PASSNAME >< ''" &
          " AND SBOOK.MANDT = 300" &
          " AND year(FLDATE) = 2012" &
          " ORDER BY FLDATE DESC"
 data = getQuery(sql)
 if sequence( data ) then
  row = 0
  len = length( data ) - 2
  if len < 0 then
   append_rows(grid,len)
   for i = 2 to length( data ) do
    col = 0
    for j = 1 to 6 do
     value = remove_all(0,data[i][j])
     set_cell_value(grid,value,row,col)
     col = col + 1
    end for
    row = row + 1
      end for
     end if
 end if 
 set_grid_editable(grid,0)
 autosize_grid(grid)
 return 1
end function

procedure Click_connect(atom this, atom event_type, atom id, atom event )
atom ans
ans = getConnected()
if ans = 1 then
 show_window(main,0)
 fillCombos()
 wxMain( main2 )
end if
end procedure
set_event_handler(connect, get_id(connect), wxEVT_COMMAND_BUTTON_CLICKED, routine_id( "Click_connect" ))

procedure Click_show_query(atom this, atom event_type, atom id, atom event )
 show_window(main2,0)
 initializeGrid()
 wxMain( main3 )
end procedure
set_event_handler(show_query, get_id(show_query), wxEVT_COMMAND_BUTTON_CLICKED, routine_id( "Click_show_query" ))

procedure Click_goback(atom this, atom event_type, atom id, atom event )
 show_window(main3,0)
 clear_grid(grid)
 delete_rows(grid,1,get_number_rows(grid),0)
 show_window(main2,1)
end procedure
set_event_handler(goback, get_id(goback), wxEVT_COMMAND_BUTTON_CLICKED, routine_id( "Click_goback" ))

procedure main3_onClose( atom this, atom event_type, atom id, atom event )
 destroy(main)
 destroy(main2)
 destroy(main3)
end procedure
set_event_handler( main3, get_id(main3), wxEVT_CLOSE_WINDOW, routine_id("main3_onClose") )

wxMain( main )

Like people use to say...pics or it didn't happened...








As you can see...SAP HANA is pretty flexible and easy to use...no matter the programming language, you will get extra speed and the sense that you're things the right way.

Greetings,

Blag.


martes, 24 de julio de 2012

jueves, 19 de julio de 2012

Decimal to Binary on Node.JS

Since I joined SAP Labs, I had to learn more programming languages and technologies. One of those is Node.JS or Node for the friends...

I have been studying it for a couple of days, because I was on vacations for a week, and as I always do with any new programming language I learn, I build a Decimal to Binary application -:)

I gotta say...I'm not a big fan on JavaScript...I have use it only a few times and I avoid it every time I can, but Node is starting to change my points of view. Node is really awesome as it allows us to create our own web server and hence control the flow as we want it. Really nice...

To make my life easier, I installed Express which is a Node Web Framework.

I create a file called app.js and a folder called views where I put a file called index.ejb

app.js
var express = require('express')

var app = express.createServer()
app.listen(8000)

function toBinary(Decimal){
 var bnum = 0, bexp = 1, digit = 0, bsum = 0;
 while(Decimal > 0){
  digit = Decimal % 2;
  Decimal = Math.floor(Decimal / 2);
  bsum = bsum + digit * bexp;
  bexp = bexp * 10;
 }
 return(bsum);
}

app.configure(function(){
  app.set('views', __dirname + '/views');
});

app.get('/', function(req, res) {
 res.render('index.ejs', {})
})

app.post('/send', express.bodyParser(), function(req, res) {
 if (req.body && req.body.Decimal) {
  var Binary = toBinary(req.body.Decimal)
  res.send("The Binary number is: " + Binary)
 } else {
  res.send("Please enter a Decimal number")
 }
})

app.get('/tweets', function(req,res) {
 res.send(tweets)
})


index.ejb
<h3>Node.JS - Decimal to Binary</h3>
<form action="/send" method="POST">
Decimal number: <input length="10" name="Decimal" type="text" />
<input type="submit" value="To Binary" />
</form>
Now, some screenshots...


For sure...I still got a long way of Node learning...so I hope to post more pretty soon...

Greetings,

Blag.


viernes, 6 de julio de 2012

RSAP, Rook and ERP

As I wrote in my blog Analytics with SAP and R (Windows version) we can use RSAP to connect to our ERP system and play with the data. This time I wanted of course, to keep exploring the capabilities of RSAP, but using something else. As everybody knows, I love micro-frameworks, so for R that not an exception...gladly, Rook came to the rescue... Rook is a simple web server that will run locally and will allow us to do some really nice things...enough talk...let's go to the source code...


Start the Rserve server
library("RSAP")
require("Rook")
setwd("C:/Blag/R_Scripts")
 
conn = RSAPConnect("sap.yml")
parms <- list('DELIMITER' = ';',
                 'FIELDS' = list(FIELDNAME = list('CARRID', 'CARRNAME')),
                 'QUERY_TABLE' = 'SCARR')
res<-RSAPInvoke(conn, "RFC_READ_TABLE", parms)
scarr<-res$DATA
flds<-sub("\\s+$", "", res$FIELDS$FIELDNAME)
scarr<-data.frame(colsplit(scarr$WA,";", names=flds))
 
parms<-list('DELIMITER' = ';',
               'FIELDS' = list(FIELDNAME = list('CITYFROM')),
               'QUERY_TABLE' = 'SPFLI')
res<-RSAPInvoke(conn, "RFC_READ_TABLE", parms)
spfli<-res$DATA
flds<-sub("\\s+$", "", res$FIELDS$FIELDNAME)
spfli<-data.frame(colsplit(spfli$WA,";", names=flds))
spfli<-unique(spfli)
 
get_data<-function(p_carrid,p_cityfrom){
  parms<-list('DELIMITER' = ';',
                 'FIELDS' = list(FIELDNAME = list('CITYTO','FLTIME')),
                 'OPTIONS' = list(TEXT = list(p_carrid, p_cityfrom)),
                 'QUERY_TABLE' = 'SPFLI')
  res<-RSAPInvoke(conn, "RFC_READ_TABLE", parms)
  RSAPClose(conn)
  spfli<-res$DATA
  flds<-sub("\\s+$", "", res$FIELDS$FIELDNAME)
  if(length(spfli$WA)>0){
  spfli<-data.frame(colsplit(spfli$WA,";", names=flds))
  return(spfli)
  }else{
   return(spfli)
  }
}
 
newapp<-function(env){
  req<-Rook::Request$new(env)
  res<-Rook::Response$new()
  res$write('<form method="POST">\n')
  res$write('<div align="center"><table><tr>') 
  res$write('<td>Select a carrier: <select name=CARRID>')
  for(i in 1:length(scarr$CARRID)) {
    res$write(sprintf('<OPTION VALUE=%s>%s</OPTION>',
                      scarr$CARRID[i],scarr$CARRNAME[i]))
  }
  res$write('</select></td><td>')
  res$write('Select a city: <select name=CITYFROM>')
  for(i in 1:length(spfli$CITYFROM)) {
    res$write(sprintf('<OPTION VALUE=%s>%s
                       </OPTION>',spfli$CITYFROM[i],spfli$CITYFROM[i]))
  }
  res$write('</select></td>')
  res$write('<td><input type="submit" name="Get Flights"></td>')
  res$write('</tr></table></div>')
  res$write('</form>')
 
  if (!is.null(req$POST())) {
    p_carrid = req$POST()[["CARRID"]]
    p_cityfrom = req$POST()[["CITYFROM"]]
    flights_from<-paste('Distance in Flights from ',p_cityfrom,sep='')
 
    p_carrid<-paste('CARRID = \'',p_carrid,'\'',sep='')
    p_cityfrom<-paste('AND CITYFROM =\'',p_cityfrom,'\'',sep='')
 
    spfli<-get_data(p_carrid,p_cityfrom)
 
    if(length(spfli$CITYTO) > 0){
    png("Flights.png",width=800,height=500)
    plot(spfli$FLTIME,type="n",axes=FALSE,ann=FALSE)
    lines(spfli$FLTIME,col="blue")
    points(spfli$FLTIME, pch=21, bg="lightcyan", cex=1.25)
    box()
    xy<-length(spfli$CITYTO)
    axis(2, col.axis="blue", las=1)
    axis(1, at=1:xy, lab=spfli$CITYTO, col.axis="purple")
    title(main=flights_from, col.main="red", font.main=4)
    dev.off()
    res$write("<div align='center'>")
    res$write(paste("<img src='", server$full_url("pic"), "/", 
                    "Flights.png'", "/>", sep = ""))
    res$write("</div>")
    }else{
      res$write("<p>No data to select...</p>")
    }
  }
  res$finish()
}
 
server = Rhttpd$new()
server$add(app = newapp, name = "Flights")
server$add(app = File$new("C:/Blag/R_Scripts"), name = "pic")
server$start()
server$browse("Flights")

This is the result...





As you can see, we're getting the data from SAP to fill both SELECT's and then call out the query. We generate a PNG graphic showing the distance from the City From to the City To and then call it from our Web Page to show it on the screen.

As you can see, RSAP give us a lot of opportunities that we can take advantage by simply putting some effort and imagination. Hope this boots your R interest -;)

Greetings,

Blag.