Named Query

Named Queries can be:

• A Prepared statement.
• Prepared statements each one separated with semicolon.
• Jython script, which will be runned with Security manager and all the output (through print statement) will be captured as output and will be sent back.

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);
    } else {
        var cols = resultSet.columns();        
        while(resultSet.next()) { 
            for(var index = 0; index < cols.length; ++index) {
               var colval = resultSet.getValue(col);
            }
        }
    }
}

Handler for Jython NamedQuery

function handler(resultSet, error) { 
    if(resultSet == null) {
        alert(error);
    } else {
        if(resultSet.next()) { 
            alert("RESULT: " + resultSet.getValue());
        }
    }
}

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 + " 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 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