AppliBuilder

Visual Mashup Builder

AppliBuilder User Documentation

Using Your Database from Browser Scripts

With AppliBuilder, each application developer gets his/her own database for their application(s). This section describes how to use your database from your browser scripts. You will typically need to setup your database separately, e.g. create tables, using SQL and the Manage Database tools.

Your application browser scripts can directly query and update the application database with SQL. To provide security as needed, there are two forms of queries supported.

SQL Named Queries, which are described below
Ad-hoc SQL queries, which need to be explicitly enabled, and are recommended only during development and testing.

Database Security

To prevent application users from running arbitrary SQL queries on your application database, only Named Queries are allowed by default. Direct SQL access needs to be explicitly turned on for your application. In all other cases, Named Queries need to be used.

A Named Query is an SQL statement or server-side Jython script, which is setup on the server via the AppliBuilder online interface. The browser Javascript invokes the query by name, limiting what queries can be executed by the browser client.

This prevents unauthorized access to your data by browser clients, except for changing any query parameters you have allowed in the Named Query. To further secure the database, support is available for validating the parameter data supplied for named queries.

Using Named Queries

For many applications, Named Queries would be the preferred approach. Although this requires the extra step of setting up the query on the server, this is recommended to prevent the possibility of application users running destructive or disruptive queries on the server.

To use Named Queries do the following:

Setup Named Query on server
Import Named Query Function from Function Library
Use Named Query in your script.

See the Named Query example page for a simple example.

Query Results (resultSet object)

Your Javascript is provided the query results as a resultSet object. The following is an example of using the resultSet object, for a named query called getUsers, where the result has a column named user. (Every application database has a Users table with a user column. So you can setup a named query called getUsers as select * from Users to execute this example.)

function getUserList() {
    var ajaxdb = new Applibase.db.AjaxDb();
    ajaxdb.query("getUsers", null, true, handler);
  
    function handler(resultSet, error) {
        if(resultSet == null) {
            // Get more details of http status from the client
            alert(error + " Response Status: " + ajaxdb.status());
            return;
        }
        var users = [];
        while(resultSet.next()) {
           var user = resultSet.getValue("usrname");
           alert("Found a user: "+user);
        }
     }
}

Note: Access keys (DB or App Keys) are not normally required for your applications, since the server checks the referring URL of your application and provides access to the right application database. Access Keys are typically only required for widgets using common servlets, e.g Data Charts or Rico LiveGrid. Please see the Database Security and Access Keys page for more information

Query Parameters

Query parameters can be used with Named Queries in the same way as with Prepared statements. Use ? in place of a value when the value needs to be passed in as a parameter. Parameters can be passed from the client as an array as follows:

["first", "second", "third", ...]

For the example above, if the getUsers Named Query was parameterized as follows:

    select * from Employees where first_name = ? and last_name = ?

then two query parameters will be passed as:

    ajaxdb.query("getUsers", ["Bill", "Gates"], true, handler);

Display of Results

A convenience function is provided to display the resuls of a query table, i.e. Applibase.db.AjaxDb.renderResultTable(resultSet, container). The container can be any HTML element to which a table can be added, e.g. a HTML Block Element. Don't specify a container to display the results of a query at the end of your page, as follows

new Applibase.db.AjaxDb().renderResultTable(resultSet);

Or use the following to render the table in a widget, e.g. a HTML Block widget named HTML Block1

new Applibase.db.AjaxDb().renderResultTable(resultSet, getWidgetByName('HTML Block1'));

Using SQL Queries

Direct SQL access is useful for development and testing, and possibly a few other situations where data security is not a concern. This access can only be turned on for authenticated users of your applications (cannot be turned on for unauthenticated users).

To turn on direct SQL access for authenticated users, add, or set if already present, the allowsql property to true using the Manage Properties menu item in the Database menu.