AppliBuilder
Visual Mashup Builder

AppliBuilder User Documentation

Database Application Tutorial

Let's build a simple task manager application, to provide a detailed step-by-step guilde to creating a database application.  Before you begin, try the Task Manager application in your Sample project, to see what the completed application will look like.

Overview

The task manager application is built around a single Task table in your application database, that serves as the persistent store, and allows us to manipulate the data with SQL statements.  We'll define a set of Named Queries to add, remove, modify and query the data in the table.

Our application will have three pages, a main Task Manager page, an Add Task Page, and a Modify Task page.  We'll build each page in the builder, and use the popupPage library function to popup the Add Task and Modify Task pages on the main page when needed.

We will write a set of page functions for each page to implement the application logic.  And we'll add some page styles to each page to improve the presentation of each page.

You will need to decide where to place your application pages, i.e. pick an application (or create a new one), to which you will add the Task Manager pages.  

Database Table and Named Queries

To start, use the Query Database menu item in the Database menu to create the Task Table.  Insert the following create table SQL statement and click Run Query.
create table Task (
id INTEGER_IDENTITY,
name VARCHAR(100),
task_type VARCHAR(50),
owner VARCHAR(60),
due_date DATE,
completed DATE,
notes TEXT,
primary key (id)
);
The Task table is now in the database.  Next, we need to setup the SQL statements we need to add, modify, delete and query the data.  As you build your application, you will add Named Queries to match your database access requirements.  In our task manager application, we have a set of Named Queries we will use as follows.

Define a set of Named Queries using the Add Named Query menu item in the Database menu.  For each Named Query fill in the Name and Query fields as shown below and click Add.
Name: getTasks
Query: select * from Task;

Name: getTasksByType
Query: select * from Task where task_type = ?;

Name: addTask
Query: insert into Task values (1,?,?,?,?,null,?);

Name: getTaskById
Query: select * from Task where id = ?;

Name: modifyTask
Query: update Task set name=?,task_type=?,owner=?,due_date=?,completed=?,notes=? where id=?;

Name: deleteTask
Query: delete from Task where id=?;
Now the required Named Queries for each of the operations we will perform on the Task table have been added.

Task Manager Main Page

Now create an application page called taskmgr, using the Create item in the Page menu.  In the form, enter taskmgr in the name field, enter a title, set the number of columns to 3, and click Create. 
Task Manager Page
In the new page, we will add widgets as you see in  the snapshot from the builder window.  Add and setup the widgets on the page as follows (with some notes on some of  their properties). 
You're free to reorganize the page to make it look better. You will need to set the names of the widgets as noted to ensure the page functions below work.

Be sure to opee the function library and import the popupPage and named query functions.  Set the following button events using the event tab of the button property form.
Next add the following functions to the taskmgr page using the Create item in the Function menu.

Name: displayAllTasks
Type: Header Function
Source:
var selval = getInputValue('task_type');

var nquery = 'getTasks';
var params = null;
if (selval && selval[0] != 'all') {
   nquery = 'getTasksByType';
   params = selval;
}
var ajaxdb = new Applibase.db.AjaxDb();
ajaxdb.query(nquery, params, 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;
    }
    new Applibase.db.AjaxDb().renderResultTable(resultSet);
    closeUrlInPopup();
}

Name: deleteTask
Type: Header Function
Source:
var taskid = getInputValue('taskid');
if (taskid == '' || isNaN(taskid)) {
    alert("Please specify ID of the task to be deleted");
    return;
}
if (!confirm("Are you sure you want to delete task with id: "+taskid)) {
    return;
}

var ajaxdb = new Applibase.db.AjaxDb();
ajaxdb.query("deleteTask", new Array(taskid), 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;
    }
    while(resultSet.next()) {
       //resultSet.getValue("name");
    }
    displayAllTasks();
}

To run the displayAllTasks() function when the page is loaded, create the following function
Name: setupdisplay (used only for storage in database)
Type: Body Block
Source:
onloadFunctions.push(displayAllTasks);

This causes displayAllTasks tobe run when the page is loaded and display the current tasks.

Add Task Page

Now create an application page called addtask, using the Create item in the Page menu.  In the form, enter addtask in the name field, and click Create.
Add Task Page
In the new page, we will add widgets as you see in  the snapshot from the builder window.  Add and setup the widgets on the page as follows (see notes on some of  their properties). 
You will need to set the names of the widgets as noted to ensure the page functions below work.

Be sure to open the function library and import the named query function.  Set the following button events using the event tab of the button property form.
Next add the following function to the taskmgr page using the Create item in the Function menu.

Name: addTask
Type: Header Function
Source:
var qparams = new Array();
qparams[0] = getInputValue('name');

if (qparams[0] == '') {
    alert('Please specify a task name');
    return;
}

qparams[1] = getInputValue('task_type')[0]; //type
qparams[2] = getInputValue('owner'); // owner
qparams[3] = getInputValue('duedate'); // target_date
qparams[4] = getInputValue('notes'); // Notes

alert("Params: "+qparams);

/* Get Ajax Client. */
var ajaxdb = new Applibase.db.AjaxDb();

/* Do a sql query, asynchronous with callback handler. */
ajaxdb.query('addTask', qparams, true, callback);

/* Callback handler will be passed with resultSet and error.
 * If resultSet is null you will have to look at the error message.
 */
function callback(resultSet, error) {
   if(resultSet == null) {
      alert(error + " Response Status:  " + ajaxdb.status());
   }
   parent.displayAllTasks();
}


Modify Task Page

Now create an application page called modtask, using the Create item in the Page menu.  In the form, enter modtask in the name field, and click Create. 
Modify Task Page
In the new page, we will add widgets as you see in  the snapshot from the builder window.  Add and setup the widgets on the page as follows (see notes on some of  their properties). 
You will need to set the names of the widgets as noted to ensure the page functions below work.

Be sure to open the function library and import the named query function.  Set the following button events using the event tab of the button property form.
Next add the following function to the taskmgr page using the Create item in the Function menu.

Name: updateTask
Type: Header Function
Source:
var qparams = new Array();
qparams[0] = getInputValue('name');

if (!currentTaskID || currentTaskID == '') {
    alert("Error, task ID not set. ");
    return;
}
if (qparams[0] == '') {
    alert('Task name cannot be blank');
    return;
}

qparams[1] = getInputValue('task_type')[0]; //type
qparams[2] = getInputValue('owner'); // owner
qparams[3] = getInputValue('due_date'); // target_date
qparams[4] = getInputValue('completed'); // target_date
qparams[5] = getInputValue('notes'); // Notes
qparams[6] = currentTaskID;

/* Get Ajax Client. */
var ajaxdb = new Applibase.db.AjaxDb();

/* Do a sql query, asynchronous with callback handler. */
ajaxdb.query('modifyTask', qparams, true, callback);

/* Callback handler will be passed with resultSet and error.
 * If resultSet is null you will have to look at the error message.
 */
function callback(resultSet, error) {
   if(resultSet == null) {
      alert(error + " Response Status:  " + ajaxdb.status());
   }
   parent.displayAllTasks();
}


Page Styles

Finally, we'll add some page styles to center content and otherwise improve the content of the page.





 © 2006 Applibase, Inc.