Skip to Content

This article describes how to create a simple Web application running at SAP HANA Cloud and consuming HANA scripted calculation view. With a few steps we could create calculation view via HANA Studio SQL Editor and consume it in a standard servlet. The described scenario provides the opportunity to create on demand web application which consumes HANA database artifacts in secured and isolated manner.

Prerequisites


  1. You have an account on SAP HANA Cloud trial landscape
  2. You have downloaded and installed the SAP HANA Cloud Platform tools for Eclipse IDE in accordance with Installing SAP Development Tools for Eclipse.
  3. You have downloaded the latest version of SAP HANA Cloud SDK (Java Web) in accordance with the Installing Cloud SDK Guide.

Procedure

Step 1: Create a Trial Instance

Open the SAP HANA Cloud Cockpit and enter the HANA Instances section. Choose New HANA Instance. Enter the required name and save changes.

/wp-content/uploads/2013/07/1_689355.jpg

Step 2: Create and Consume a Scripted Calculation View in a Web Application

In a Web application, we’ll create a database table called Managers, and a relevant scripted calculation view. The scripted calculated view contains three major artifacts – user-defined type, stored procedure and column view. Script procedure could be defined via SQL or CE as described in SAP HANA SQLScript Reference.

     Using cloud persistence service by plain JDBC, we’ll consume the calculation view in a servlet.
     All this is implemented in the source code of the HANA Scripted View Demo application in GitHub. We’ll use it as an example.

To run the HANA Scripted View Demo application:
     1. From GitHub, clone the https://github.com/SAP/cloud-hana-scriptview-demo repository and import the relevant project.
     2. Using the context menu of the project in the Project Explorer view, choose Run As -> Run on server.
     3. Choose the Trial landscape and deploy. For more details, see Deploying on the Cloud from Eclipse IDE.
     4. In the Cockpit, go to Databases & Schemas. Check the DB schema ID associated with your scriptview application. It should be something like this:      <your trial user>trial.scriptview.web.

/wp-content/uploads/2013/07/2_689448.png

5. In your Eclipse IDE, open the SAP HANA Development perspective.

6. In Systems, choose Add Cloud System.

/wp-content/uploads/2013/07/3_689459.jpg

7. Connect to the Trial landscape using your Trial account information. Choose the DB schema associated with the scriptview application.

/wp-content/uploads/2013/07/5_689460.png

8. Get the ID of the DB schema

/wp-content/uploads/2013/07/6_689461.png

9. Run a search & replace operation over the scriptview project to replace all occurrences of NEO_<YOUR_SCHEMA_ID> with the actual DB schema ID that you got in the previous step.

10. Republish the scriptview application and observe the table data visualized.

NOTE: ScriptViewDemoServlet is using persistence service via JDBC to consume scripted calculation view created via database tunneled HANA studio.

NOTE: Application web.xml is containing the additional configurations needed for the plain JDBC.

The application contains the following SQL scripts:

  • cloud-hana-scriptview-demo\src\main\resources\sql\create_procedure.sql– the procedure that will be called when using the scripted view
  • cloud-hana-scriptview-demo\src\main\resources\sql\create_tables.sql – creates
    the type used by the procedure
  • cloud-hana-scriptview-demo\src\main\resources\sql\create_view.sql –  creates the scripted view


public class ScriptViewDemoServlet extends HttpServlet {
    .....
    @Override
    public void init() throws ServletException {
        try {
            DBUtil dbUtil = new DBUtil((DataSource) new InitialContext().lookup("java:comp/env/jdbc/DefaultDB"));
            dbManager = new DBManager(dbUtil);
            dbManager.initDB();
        } catch (NamingException e) {
            throw new RuntimeException("Failed to lookup default datasource", e);
        } catch (IOException ioe) {
            throw new RuntimeException("Failed to load resource", ioe);
        }
    }
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        PrintWriter writer = response.getWriter();
        writer.print(getFormattedString(dbManager.getManagers()));
        writer.flush();
    }
    ....
}
public class DBManager {
    ....
    private static final String SELECT_ALL_MANAGERS_SCRIPTED_VIEW =
          "SELECT * FROM \"NEO<YOUR_SCHEMA_ID>\".\"manager/SCRIPTVIEW\"";
    public DBManager(DBUtil dbAccess) {
        this.dbAccess = dbAccess;
    }
    public List<ManagerEntity> getManagers() throws IOException {
        Connection connection = dbAccess.getConnection();
        List<ManagerEntity> managerList = new ArrayList<ManagerEntity>();
             ResultSet result = connection.prepareStatement(SELECT_ALL_MANAGERS_SCRIPTED_VIEW).executeQuery();
                while (result.next()) {
                    ManagerEntity manager = new ManagerEntity();
                    manager.setId(result.getInt("ID"));
                    manager.setName(result.getString("NAME"));
                    manager.setRegion(result.getString("REGION"));
                    manager.setBudget(result.getDouble("BUDGET"));
                    manager.setBudgetPercentageShare(result.getDouble("BUDGET_PERCENTAGE_SHARE"));
                    managerList.add(manager);
                }
        } catch (SQLException e) {
            throw new RuntimeException("Error while reading customer data", e);
        } finally {
            dbAccess.closeConnection(connection);
        }
        return managerList;
    }
    ....
}












Conclusion

This article demonstrates how HANA Cloud users could combine on-demand with calculation engine features. It is not intended to cover all scripted view, SQL/CE features and don’t cover HANA Modeler. It is very basic example which provides overview of HANA Studio/HANA Cloud integration and usage of database artifacts from standard cloud web application.

Contributor:

Dobrinka Stefanova

Reference

8 Easy Steps to Develop an XS application on the SAP HANA Cloud Platform

Using HANA Modeler in the SAP HANA Cloud

To report this post you need to login first.

18 Comments

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

  1. Gregory Misiorek

    Hi Nikolay,

    i would like to try it on an AWS instance once i get it back up and running. just out curiosity, what is the business case for one table with 20 records in it? how can i convince a spreadsheet user that HANA is more suited than their desktop application to use it in their work?

    thanks for sharing,

    greg

    (0) 
    1. Matthias Steiner

      Hi Greg,

      I think there’s some confusion here. The example provided does not relate to HANA One (our offering hosted on AWS), but demonstrates how-to leverage SAP HANA native capabilities on the SAP HANA Cloud Platform. If you want to try it out, you can register for your account here: http://scn.sap.com/docs/DOC-28197

      just out curiosity, what is the business case for one table with 20 records in it?

      Common, that was just an example to get a dataset rather quickly. Feel free to create millions of test data (or real data) 😉

      Cheers,

      Matthias

      (0) 
      1. dinesh somani

        Hi Matthias,

        Looks good. Just a clarity question. My understanding was that NATIVE applications do not use JDBC. Instead they work directly with the HANA platform using OData or JS. So, is this a new nomenclature?

        Will appreciate your inputs.

        Regards

        Dinesh

        (0) 
        1. Dimitar Tenev

          Hi Dinesh,

          There is no “new nomenclature“, because the blog talks about “HANA native scripted calculation view” and its usage from web application in SAP HANA Cloud. May be you are confused with HANA XS applications, which are not target of the blog.

          Best regards,

          Dimitar

          (0) 
    1. Nikolay Stoichkov Post author

      Hi Maksim,

      It seems that “SYS” is set for your current schema and this schema access is restricted. You could set back your current schema to your default via the SQL Editor –

      SET SCHEMA “NEO_ + <your default schema id>”

      Please let me know if this helped, you could always contact me if you have any troubles – nikolay.stoichkov@sap.com or call me directly.

      Best regards,

      Nikolay

      (0) 
  2. Manoj Aswani

    I tried the above tutorial. But got stuck at one place i.e. while testing the remote servlet access. I have also authorized my user i.e. – p1678012516trial with the role that I specified in web.xml of application. And when I am trying to login with this user while accessing the servlet it is not authenticating. The password that I am using is same as that of I use to login in SAP HANA Cloud Service. Repeated trial ended up in following error. Please help in getting out of this problem. I used FORM based authentication also in place of BASIC and the result was same.

    HTTP Status 403 – Access to the requested resource has been denied


    type Status report

    message Access to the requested resource has been denied

    description Access to the specified resource has been forbidden.


    SAP

    (0) 
    1. Dimitar Tenev

      Hi Manoj,

      p1678012516trial” is yout account id. Do assign the role “jdbc_remote_access_admin” to your user id “p1678012516“.

      Best regards,

      Dimitar

      (0) 
  3. Ashok Kumar M

    Hi Nikolay,

    Is it possible to connect to HANA XS Engine from HANA Cloud?

    If yes, could you please share some information?

    Best Regards,

    Ashok.

    (0) 
  4. SUGANTHI SELVARAJ

    Hi Nikolay,

    Thanks for the information!

    I would like to consume this calculation view (more precisely the HANA DB in HANA Cloud) in an SAPUI5 application using oData. Is there any possibility for this? Please suggest!

    Regards,

    Suganthi.

    (0) 
  5. Christoph Rühle

    Hi guys, like mentioned above, this is just a regular scripted view. I want to script a real calculation view located in a package, but I cant find any reference how to do this. any suggestions how to script a calculation view via sql editor? thanks in advance, Christoph.

    (0) 
  6. Christoph Rühle

    Hi Nikolay.. the problem is that I have to create a Calc View via HANA Studio (Content…). I want to create it via procedure thou. Is that generally possible or do I hav to do it always manually?? Cheers, Christoph.

    (0) 
  7. Prasanna Prabhu

    Hi Nikolay Stoichkov,

    I had a similer usecase of creating an attribute view and consuming that in the JAVA  application.

    I tried the same steps and it fails in executing the select statement on the HANA view and throws error. But if i execute the same query in the SQL console it works.

    Am i missing something here? What else can be possible issue?

    Regards,

    Prasanna.

    (0) 

Leave a Reply