Deliver dynamic search of SAP data into a website using RESTful services

[former_member182638]

A Google revelation 

Quite a while ago I was intrigued with the ability of www.google.com to provide text suggestions for search strings AS I WAS TYPING the string ...

 

When I traced this interaction, I noticed that on each keystroke the site was in the background firing an event which eventuated in a RESTful call to a server using AJAX.  Each call would return a limited number of text suggestions to the webpage, such as in the example above.  The below trace illustrates the interaction when typing the 3 letters 'S' 'A' 'P' in succession ...

I wondered whether it would be possible to implement a similar form of interaction where the source data was an SAP system.  This could be used, for instance, to search for employees, customers, cost centres etc. by name.  And I really like this interaction pattern, because for the user it is simple and seamless (and anyone who has used Google or other sites that implement a similar approach should be familiar with it).

At the same time, I have always believed that there is an untapped opportunity to combine the capabilities of the SAP ICM (Internet Communication Manager) and custom HTTP handlers, with the broader web community and technologies that have evolved in that realm.  To put it more bluntly, often SAP departments are not inclined to work with corporate web or intranet teams, choosing instead to focus on the SAP supplied UI platforms of (primarily) SAPGUI or SAP Portal with which they are familiar.  Similarly, corporate web and intranet teams are often not inclined to collaborate with their SAP counterparts, believing that they live on a different planet which lies somewhere in the Gamma quadrant.  It seems there is a lost opportunity for these two groups to collaborate and build new ways to extract value from SAP data.   

Proof of concept

So I set about building a scenario on my personal environment which would include the following:

• Simple dummy website which might serve as a corporate intranet
• Dynamic search in the website triggering AJAX calls to a local miniSAP ABAP system
• The scenario here is a search for transaction codes in an SAP system (not a great scenario but bear in mind this demonstration was built on a miniSAP system) Custom HTTP handler on the ABAP system which returns results in JSON format
• Website processes the return and displays the results, similar to Google  

Here is a YouTube video demonstration below ....  

If you cannot see the YouTube video, a Flash version is available here ... http://www.johnmoy.com/demos/ztransactioncodes/ztransactioncodes.swf  

I should also point out that this scenario is NOT to be confused with SAP NetWeaver Enterprise Search.   That is a different thing altogether. 

You might wonder why I simply wouldn't code something using WebDynpro ABAP or BSP?  Well, in the case of WebDynpro ABAP that framework currently does not support stateless scenarios (I am told that is in the roadmap).  I certainly need this to be stateless, because the service could simply sit on the webpage untouched for an indefinite period.  BSP is a viable option, but I really wanted here to look at a scenario where your corporate intranet resides ON A DIFFERENT PLATFORM.   

What's the point? 

Why might this be a useful architecture to consider?  After all, it really is a non-standard approach when compared with standard SAP UI solutions. Well, I certainly wouldn't consider this for every UI scenario.  Rather, I think it is useful in certain edge cases where quick and simple access to SAP data might be needed (eg. integrating customer lookup into your corporate intranet homepage).

Here are some benefits of this approach ...

• You are no longer tied to SAP's browser support matrixes (which you are if you are using technologies such as WebDynpro etc.).  Of course, since your web developers are coding the client-side, they will take responsibility for browser compatibility based on what they need to support in your organization.
• You can implement this using a stateless approach, which can really scale.  So for instance you could deploy this service to the corporate intranet homepage.  The SAP server is only load-effected if someone starts typing in the search field.
• You could RE-USE this service with other clients, such as iPhone, Blackberry or Android apps in your organisation.    

Let's build it! 

As always, where possible I like to share my sample code so that you can try this yourself.  You can build this in 15 minutes. For THIS scenario, I will implement a simple SAP transaction code lookup service.  Of course, you could think of much more valuable scenarios for your own system (customer, employee lookups etc.), but since I am running this on a personal miniSAP system there is limited data available.  The concept however can be readily applied to other scenarios.

PART A: Enable RESTful service

• Via transaction SE24, create a public class ZCL_TRANSACTION_CODES
• Assign interface IF_HTTP_EXTENSION to this class.  This should introduce an instance method 'HANDLE_REQUEST' to our class.
• For our implementation of HANDLE_REQUEST, paste the following code* and activate the class ... 

method IF_HTTP_EXTENSION~HANDLE_REQUEST.

* John Moy, April 2011

* An SAP Community Contribution

*

* RESTful service to deliver transaction code details in JSON format

* for a given search string.

*

* This code is has been simplified for illustrative purposes only.

* For productive use, you should seek to implement a RESTful framework,

* implement a JSON converter, and refactor to achieve appropriate

* separation of concerns.

*

* Data definition

*

  data:

    path_info     type string,

    verb          type string,

    action        type string,

    attribute     type string,

    rows          type integer,

    json_string   type string.

*

* Process request

*

  path_info = server->request->get_header_field( name = '~path_info' ).

  verb = server->request->get_header_field( name = '~request_method' ).

*

* Determine if method is get.

*

  if verb ne 'GET'.

    call method server->response->set_header_field( name = 'Allow' value = 'GET' ).

    call method server->response->set_status( code = '405' reason = 'Method not allowed' ).

    exit.

  endif.

*

* Determine the action and attribute

*

  SHIFT path_info LEFT BY 1 PLACES.

  SPLIT path_info AT '/' INTO action attribute.

* Application logic.

* (in reality this would be refactored into a separate class)

  data: lt_tcodes type table of tstct,

        lv_search_string type string,

        lv_search_string_upper type string,

        lv_search_string_upperlower type string,

        lv_search_firstchar(1) type c,

        exc_ref type ref to cx_sy_native_sql_error.

  field-symbols: <tcoderow> type tstct.

* Version of search string that directly matches case

  concatenate '%' attribute '%' into lv_search_string.

* Version of search string that is entirely upper case

  move lv_search_string to lv_search_string_upper.

  translate lv_search_string_upper to upper case.

* Version of search string with first character in upper case

  move attribute(1) to lv_search_firstchar.

  translate lv_search_firstchar to upper case.

  move attribute to lv_search_string_upperlower.

  shift lv_search_string_upperlower by 1 places.

  concatenate '%' lv_search_firstchar lv_search_string_upperlower '%'

    into lv_search_string_upperlower.

* Persistence access logic.

* (in reality this would be refactored into a separate layer)

* Note also inefficiences of this 'select' statement can and should

* be addressed in real life implementations.

  select * from tstct into table lt_tcodes

    where sprsl eq sy-langu

      and ( tcode like lv_search_string_upper or

            ttext like lv_search_string_upper or

            ttext like lv_search_string_upperlower or

            ttext like lv_search_string ).

* If error detected then abort

  if sy-subrc ne 0.

    call method server->response->set_status( code = '404' reason = 'ERROR' ).

    call method server->response->set_cdata( data = json_string ).

    exit.

  endif.

  rows = lines( lt_tcodes ).

* Now populate JSON string with the appropriate fields we need

* (in reality it would be appropriate to implement and call a JSON converter)

  move '[ ' to json_string.

  loop at lt_tcodes assigning <tcoderow>.

    concatenate json_string '{ ' '"key": "' <tcoderow>-tcode '", ' '"desc": "' <tcoderow>-ttext '" }' into json_string.

    if ( sy-tabix < rows ).

      concatenate json_string ', ' into json_string.

    endif.

* If we have reached 15 rows, then terminate the loop

    if ( sy-tabix eq 15 ).

      concatenate json_string '{ "key": "", "desc": "... and more" }' into json_string.

      exit.

    endif.

  endloop.

  concatenate json_string ' ]' into json_string.

*

* Set the content type

*

  server->response->set_header_field( name = 'Content-Type' value = 'application/json; charset=utf-8' ).

*

* Enable this service to be available to other sites

*

  server->response->set_header_field( name = 'Access-Control-Allow-Origin' value = '*' ).

*

* Return the results in a JSON string

*

  call method server->response->set_cdata( data = json_string ).

endmethod.

* Note: As with prior blogs, I should mention that I cannibalised some code from this blog  (Android and RESTFul web service instead of SOAP) by Michael Hardenbol to develop this service.  I have also collapsed several layers of code into the one method simply for ease of cutting and pasting this exercise, however in reality you would refactor to build in a separation of concerns.  You would probably want to implement a proper RESTful framework in your ICF if you intend to deploy a number of services (or wait for SAP's Gateway product).  SAP Mentor DJ Adams provides some ideas to accomplish this in his blog  (A new REST handler / dispatcher for the ICF).  Also, the code returns a result in JSON  (http://en.wikipedia.org/wiki/JSON) format.  Typically you would call a re-usable JSON converter (there are several available in the SCN community) but for the purposes of this exercise I have simply constructed the result with a crude CONCATENATE statement. 

• Via transaction SICF, create a new service 'ztransactions' under the path /default_host/sap
• For the service, add a description
• Go to the 'Logon Data' tab and enter service user credentials here (username / password) to make this an anonymous service (note that we could make this an authenticated service with some extra work)
• Go to the 'Handler List' tab and add the following class into the handler list .... ZCL_TRANSACTION_CODES

• Activate the service (from the SICF tree, right-click on the service and select 'Activate Service')

PART B: Enable Web Application

• This is actually the part you would expect your web team to accomplish
• For simplicity however, I have provided some sample code that you can download here and implement on a web server, or alternatively you can simply utilize a hosted version here.  DISCLOSURE: Under terms of use for the original website template, I created the hosted dummy site based on a sample provided by Free Website Templates.  I incorporated my own images, themes, an open source accordian control and some custom javascript into the amended site.
• In either case, to get this to work you will need to overtype the default fully qualified domain name for your own server that appears immediately above the 'Transaction Codes' accordian button.
• The most important work that occurs here is in the javascript file 'sapsearch.js' which I replicate below ...

//

//  Sample dynamic search javascript

//  by John Moy

//  An SAP Community Contribution

//  March 2011

//

//

//  Function to initiate dynamic search of SAP data

//

function showSAPSearchResult(sapserver, resource, queryString, targetElement)

{

    if (queryString.length<=1)

    {

        document.getElementById(targetElement).innerHTML="";

        return;

    }

   

    var url= "http://" + sapserver.value + "/sap/" + resource + "/search/" + queryString;

    // Initiate request

    var httpRequest = createCORSRequest("get", url);

   

    if (httpRequest) {

        // Register handler for processing successful search

        httpRequest.onload = function() {

            var result = JSON.parse(httpRequest.responseText);

            var htmlResponse = "";

            for (var i = 0; i < result.length; i++) {

                var key = result[i]["key"];

                var description = result[i]["desc"];

                htmlResponse += "<ul>" + key + " - " + description + "</ul>";

            }

            document.getElementById(targetElement).innerHTML="<ul>" + htmlResponse + "</ul>";   

        }

       

        // Register handler for processing failed search

        httpRequest.onerror = function() {          

            document.getElementById(targetElement).innerHTML="<ul>No results returned</ul>";             

        }

       

        // Initiate query

        httpRequest.send();

    }

    else {

        alert('Sorry, your browser does not support cross origin resource sharing');

    }

}

//

//  Function to create Cross Origin Resource Sharing (CORS) request

//  for all modern browser types

//  Based on: http://bit.ly/9iT2Na

//

function createCORSRequest(method, url){

    var xhr;

    // Checking for XDomainRequest determines if browser

    // implements IE-based proprietary solution

    if (typeof XDomainRequest != "undefined"){

        xhr = new XDomainRequest();

        xhr.open(method, url);

    }

    else {

        xhr = new XMLHttpRequest();

   

        // Checking for 'withCredentials' property determines if browser supports CORS   

        if ("withCredentials" in xhr){

            xhr.open(method, url, true);

        }

        // Browser supports neither CORS or XDomainRequest

        else {

            xhr = null;

        }

    }

   

    return xhr;

}

• You can try this out.  The easiest way is to launch the version I have hosted for your convenience.
• Note that this is currently coded to work with IE8+ and recent versions of Firefox, Chrome and Safari.  It is possible to code for earlier versions of IE (such as IE6) however the code for cross origin resource sharing (discussed below) would need to be changed.

How does it Work?

The interaction here is as follows ...

• User types into the input field for Transaction Codes
• Website detects an 'onKeyup' event for theinput field and fires a request to the javascript function 'showSAPSearchResult', passing various parameters including the SAP server and the value entered.
• The javascript function 'showSAPSearchResult' ignores any inputs that are only one character in length and exits in these situations.
• The javascript function 'showSAPSearchResult' constructs a RESTful url call which looks something like this example (if typing 'sql') ...

  http://local.nsp.system:8000/sap/ztransactions/search/sql

• At this point I should point out that it is arguable whether this form of url request fully complies with 'REST' principles.  Originally I thought not to include the verb 'search' in the url but when I saw that both Google and Yahoo APIs incorporated this, I decided to include it.
• The javascript function 'showSAPSearchResult' then calls a function 'createCORSRequest' which creates an HTTP GET call to the SAP service we created earlier, passing the constructed url.
• The ABAP service processes the request, and then returns a very lean JSON-formatted response that looks something like this ...

[{"key": "ADA_SQLDBC","desc": "SQLDBC_CONS" },{"key": "DB6CST","desc": "DB6: Analyze Cumulative SQL Trace" },{"key": "DB6CST_LST","desc": "DB6: Analyze Cumulative SQL Trace" },{"key": "DB6EXPLAIN","desc": "DB6:  Explain SQL Statement" },{"key": "DB6SQC","desc": "DB6: Analyze SQL Cache" },{"key": "SDBE","desc": "Explain an SQL statement" },{"key": "SQLR","desc": "SQL Trace Interpreter" },{"key": "ST04RFC","desc": "SAP Remote DB Monitor for SQL Server" },{"key": "ST04_MSS","desc": "Monitoring SQL Server (remote/local)" } ]

• A local callback javascript function 'showSAPSearchResult' receives the response from the ABAP server.  This function parses the JSON result, transforms it into HTML and places it dynamically into the HTML Document Object Model so that it appears instantly for the user to see.

A word about Cross Origin Resource Sharing (CORS)

The solution here has been architected here such that the calling website can reside in a different domain to the SAP server.  There might be instances where this may be useful (eg. mashups).  It is also useful here because it means you can trial it using the hosted site I uploaded to www.johnmoy.com/demos/ztransactioncodes .  It is however not essential if the consuming website has the same domain as the RESTful service.

I would like to thank Chris Paine for bringing this to my attention.  You can read more about it here.

A word about Performance

For these situations, you should consider the impact on your server and whether the request / response cycle will occur quickly enough. Also in some cases the data you are looking up might involve onerous volumes that will not be viable for use with this solution. To be sure, the simple scenario I have provided here has NOT been performance optimized. Ideally, the table you lookup is memory resident (eg. fully buffered) and / or searches utilize database indexes.  In my example I added some crude code to tackle case insensitive searches for text .... in a full ERP system for some tables you can leverage MATCHCODE columns which store data in full uppercase to avoid this issue.

You can see why HANA would be a great accompaniment to this type of solution.

Extending the Solution

Whilst I don't address it in this blog, you can very easily extend the solution to then permit you to click on a result item, invoking another RESTful service to receive a details display on your site.  In the case of our transaction codes example, we could invoke the transaction via ITS or even launching SAPGUI (if we leverage a Portal service).

Licensing

A word about licensing .... Whilst the approach I have outlined here seems technically feasible, you may need to discuss any licensing implications with your friendly SAP account rep.  I'm no licensing expert, so I won't even begin to speculate about what the implications might be from a licensing perspective.

Final Words

In some respects, the vision for SAP's new Gateway product is along the same lines as this blog post. That is, consumption of lean RESTful services by heterogeneous clients (eg. websites, mobile devices, etc).  What Gateway provides is a formal framework to expose services.  Gateway or not, if you take nothing else from this post, perhaps you should consider having a friendly chat with your neighbourhood web team?  You might just accomplish some great things together.