Including data from a SQL database in a Canvas page

Canvas Canvas Tips
1

Canvas is focused on connecting TM1 to the web but it is also flexible enough to include components from other data sources if required. The Canvas server runs in Apache Tomcat which means you can integrate your own logic into the server using servlets and JSP pages.

In the upcoming Canvas release we will include a sample JSP page that connects to a relational database using an ODBC connection. The main block of code in the sample is as follows:

// Get the query parameters
String department = request.getParameter("department");
int skip = Integer.parseInt(request.getParameter("skip"));
int limit = Integer.parseInt(request.getParameter("max"));

// Load ODBC driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

// Open connect to DSN canvas_sample
try(Connection conn=DriverManager.getConnection("jdbc:odbc:canvas_sample","","");){
  
    // Create query
    try(PreparedStatement query = conn.prepareStatement("SELECT * FROM FactFinance WHERE DepartmentGroupKey = ? LIMIT ? OFFSET ?");){
        
        // Set query parameters
        query.setString(1, department);
        query.setInt(2, limit);
        query.setInt(3, skip);
        
        // Execute query
        try(ResultSet result = query.executeQuery();){
            // Convert result to JSON and write to output stream
            out.write(Helper.convertToJson(result));
        }
    }
    
 }

The code connects to an ODBC DSN called canvas_sample and queries the FactFinance table for records that match department number passed to the JSP. To ensure only a small subset of data is returned we also use the LIMIT and OFFSET key words to limit the size of the resultset.

We have included a special helper function Helper.convertToJson(result) that converts a JDBC Resultset to a JSON response. The data returned from the JSP page looks like this:

[
  {
    FinanceKey: 48,
    DateKey: 200907,
    OrganizationKey: 3,
    DepartmentGroupKey: 2,
    ScenarioKey: 1,
    AccountKey: 12,
    Amount: 23197
  },
  {
    FinanceKey: 236,
    DateKey: 200907,
    OrganizationKey: 4,
    DepartmentGroupKey: 2,
    ScenarioKey: 1,
    AccountKey: 12,
    Amount: 34545
  }
]

To call the JSP you need to add some code to your Angular controller:

$http.get("api/samples/sql.jsp?instance=dev&department=" + $scope.page.department + "&skip=0&max=" + $scope.page.max).then(function(result){
  $scope.data = result.data;
});

The above code uses the Angular $http service to execute the HTTP request and return the data to include in the $scope of the page. Now it can be used like any other bit of data:

<table class="table table-striped">
    <thead>
      <tr>
        <th>Index</th>
        <th>Date</th>
        <th>Region</th>
        <th>Department</th>
        <th>Scenario</th>
        <th>Account</th>
        <th>Amount</th>
      </tr>
    </thead>
    <tbody>
      <tr ng-repeat="item in data">
        <td>{{item.FinanceKey}}</td>
        <td>{{item.DateKey}}</td>
        <td>{{item.OrganizationKey}}</td>
        <td>{{item.DepartmentGroupKey}}</td>
        <td>{{item.ScenarioKey}}</td>
        <td>{{item.AccountKey}}</td>
        <td>{{item.Amount | number}}</td>
      </tr>
    </tbody>
  </table>

The full example of the JSP is below, it should be be saved to directory in webapps folder. The folder(s) then determine the URL path. In the example above the sql.jsp is saved to ROOT/api/samples/, which results in a URL of api/samples/sql.jsp.

sql.zip (988 Bytes)

2 Likes