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.