Skip to Content

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?

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.ewx

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…

Euphoria_HANA_001.png

Euphoria_HANA_002.png

Euphoria_HANA_003.png

Euphoria_HANA_004.png

Euphoria_HANA_005.png

Euphoria_HANA_006.png

Euphoria_HANA_007.png

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.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Abhijit Gaikwad

    Any suggestion/s on the ETL part?

    1. Data uploads directly to HANA excusing SAP entirely, ODBC may surface out here.
    2. Data uploads in a conventional way, RFC and SAP API’s.

    -Abhijit

    (0) 
    1. Alvaro Tejada Galindo Post author

      Well…you can always use the INSERT INTO method…which would for sure take a lot of time…or you could use JDBC to script something and get the generated records into SAP HANA directly…  Greetings, Blag.

      (0) 

Leave a Reply