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.

1. SQL Datasources, which are described below
2. 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 Datasources (which are implemented by Named Queries internally) 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 Datasource 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 Datasource. To further secure the database, support is available for validating the parameter data supplied for named queries.

Using Datasources

For many applications, Datasources 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 Datasource on server
• Import Datasource Function from Function Library
• Use Datasource in your script.

See the Datasource 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 Datasource 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 Datasource 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);
        }
     }
}

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", ...]

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

    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);

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

Using Direct 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 Data menu.

Javascript Client

Every developer will be associated with a database and APPKEY on the server. The database name and APPKEY forms the important parameters to the AjaxDb^? client. Its used to identify developer information before executing the Named Query.

Using the javascript client:

var ajaxdb = new Applibase.db.AjaxDb(); 

var ENCODED_APPKEY = "YOUR_ENCODED_APPKEY";
var ajaxdb = new Applibase.db.AjaxDb();
ajaxdb.setKey(ENCODED_APPKEY); 

var DBNAME = "database", APPKEY = "MY_APPKEY";
var ajaxdb = new Applibase.db.AjaxDb(DBNAME, APPKEY);

Execute Named Query.

// getUsers = select * from myUsers;
ajaxdb.query("getUsers", null, true, handler);

Execute Named Query by passing parameters.

// greetUser = insert into myUsers(name) values (?);
ajaxdb.query("addUser", ["prasad"], true, handler);

Execute Named Query by passing parameters to second query but nothing for first query.

// countAndSelect = 
//       select count(*) from myUsers;
//       select * from myUsers where user = ?;
ajaxdb.query("countAndSelect", [null, [ "prasad"] ], true, handler);

Execute Named Query (Jython Script) by passing variable values.

// name_role: 

// for name in names:
//    print name
// for role in roles:
//    print roles

var params = { "names" : ["me", "you", "others"], "roles" : ["admin", "friend", "unknown"] };
ajaxdb.query("name_role", params, true, handler);

Executing Sql Query (You should have ajaxdc with database name, appkey or encoded appkey)

ajaxdb.sqlquery("select * from myUsers", true, handler);

Basic structure of callback handler is as follows:

Handler for NamedQuery^? (not Jython) or SqlQuery^?

function handler(resultSet, error) { 
    if(resultSet == null) {
        alert(error["message"]);
    } else {
        var cols = resultSet.columns();        
        while(resultSet.next()) { 
            for(var index = 0; index < cols.length; ++index) {
               var colval = resultSet.getValue(col);
            }
        }
    }
}

Note: The handler function should be global to the function which calls the "ajaxdb.query()" function. If the query call is in an 'if block', the handler function should be outside the 'if block'

Handler for Jython NamedQuery^?

function handler(resultSet, error) { 
    if(resultSet == null) {
        alert(error["message"]);
    } else {
        if(resultSet.type() == "JYTHON") {
            alert("RESULT: " + resultSet.getResult());
        } else if(resultSet.type() == "SQL") {
           // Handle it as sql result
        }
    }
}

EXAMPLE:

function getAjaxDb() {
    var ajaxdb_instance = new Applibase.db.AjaxDb();
    return ajaxdb_instance;
}

function getUserList() {
    var ajaxdb = getAjaxDb();
    ajaxdb.query("getUsers", null, true, handler);
  
    function handler(resultSet, error) {
        if(resultSet == null) {
            // Get more details of http status from the client
            alert(error["message"] + " Response Status: " + ajaxdc.status());
            return;
        }
        var users = [];
        while(resultSet.next()) {
           var user = resultSet.getValue("user");
           users.push(user);
        }
        // Use "users" and do some thing with it.
     }
}

Execution model at Server

• If DBNAME and APPKEY is sent by client it will be used.
• If ENCODED_APPKEY is sent by client, it will be used overriding the above information.
• If the page is restricted to use, then you will have to signin to view the page, in such cases the login information is used, that is accessible to the application.
• If nothing is specified, the default APPKEY available to application will be used with anonymous access flag turned on. This will not allow sql query execution directly.

Passing parameters to named query (Client to Server)

Here is the general format of parameters that gets sent to server

When list is being sent: Each value of the list is converted to this form, (& is used to separate multiple items)

p[statementIndex].[parameterIndex]=value When the value is not null

n[statementIndex].[parameterIndex]= When the value is null

When map is being sent: Each of the key/value pair is converted to this form, (& is used to separate multiple items)

$nameKey=value

NOTE: value should be properly encoded, use encodeURIComponent of javascript.

Passing single parameter to single query.

getUsersByName = select * from myUsers where username = ?

var params = ["somename"];
ajaxdb.query("getUsersByName", params, true, handler);

Parameter string sent to server:

p0.1=somename

Passing multiple parameters to single query.

getUsersByNameAndId = select * from myUsers where username = ? and userid = ?

var params = ["somename", 10];
ajaxdb.query("getUsersByName", params, true, handler);

Parameter string sent to server:

p0.1=somename&p0.2=10

Passing parameters to multiple query.

getUsersByNameLaterById
  select * from myUsers where username = ?
  select * from myUsers where userid = ?

var firstParams = ["someone"];
var secondParams = [10];
var params = [firstParams, secondParams];
ajaxdb.query("getUsersByName", params, true, handler);

Parameter string sent to server:

p0.1=someone&p1.1=10

Passing parameters as map to named query.

usersAndRoles:
  for user in users:
    for role in roles:
        print user + "-" + role

var paramsMap = { users: ['a','b','c'], roles:['1','2','3'] }
ajaxdb.query("usersAndRoles", paramsMap, true, handler);

Parameter string sent to server:

$users=%5B%22a%22%2C%22b%22%2C%22c%22%5D&$roles=%5B%221%22%2C%222%22%2C%223%22%5D

You can send null values for any the parameter var params = ["notNullValue", null, 10]

Parameter string sent to server:

p0.1=notNullValue&n0.2=&p0.3=10