Category: Palm Development

HTML5 storage Database is generally referred as SQLlite. It is to handle minimal database and Query.

Please refer Basic App Creation in PALM

Step1 :Create Basic Scene “Database”

in Database-Scene.html

<div>Database Tutorial</div>

<div id="textField"></div> <!-- Open field for user to enter values -->
<div id="button-1"></div> <!-- Insert Button, to tigger insert query -->
<div id="button-2"></div> <!-- Retrieve Records from the table -->
<div id="Records"></div> <!-- Display Records -->

Step2 : In Database-Assistant.js

DatabaseAssistant.prototype.setup = function() {
var name = "MainDB";  // required
var version = "1";  // required

var db = openDatabase(name, version); // Open Database
if (!db) {
  Mojo.Log.error("Could not open database"); // If Database is not available , it throws an error message
} else {
  var sql = "CREATE TABLE IF NOT EXISTS 'Table1' (Title TEXT)";
  db.transaction(
  	function (transaction)
  	{
    transaction.executeSql(sql,[], function(transaction, results)
    							   {Mojo.Log.info("Successfully created table")},
    							   function(transaction, error)
    							   {Mojo.Log.error("Could not create table: ")});
  });
}
this.controller.setupWidget('button-1',{},{buttonLabel: 'Insert'}); //Giving the button label as Insert
this.controller.setupWidget('button-2',{},{buttonLabel: 'Retrieve Data'}); // To give the button label as Retrieve
this.controller.setupWidget('textField');
Mojo.Event.listen(this.controller.get('button-1'),Mojo.Event.tap, this.InsertRow) //One Tab of Insert Button, function InsertRow will be called
Mojo.Event.listen(this.controller.get('button-2'),Mojo.Event.tap, this.RetrieveRows) // on Tab of Retrieve Button, function Retrieve Rows will be called
};

Step 3: Include functions below
1. To Insert Row into Table

DatabaseAssistant.prototype.InsertRow = function(event) {
var test = $('textField').innerText;

var db = openDatabase("MainDB", "1"); // this is all that is required to open an existing DB
var sql = "INSERT INTO 'Table1' (Title) VALUES (?)";

db.transaction( function (transaction) {
  transaction.executeSql(sql,  [test],
                         function(transaction, results) {    // success handler
                           Mojo.Log.info("Successfully inserted record");
                         },
                         function(transaction, error) {      // error handler
                           Mojo.Log.error("Could not insert record: " + error.message);
                         }
  );
})
};

2. To Retrieve Rows from table

DatabaseAssistant.prototype.RetrieveRows = function(event) {
var db = openDatabase("MainDB", "1");
var sql = "SELECT * FROM 'Table1'";

db.transaction(function(transaction) {
  transaction.executeSql(sql, [],
                         function(transaction, results) {
                           // results.rows holds the rows returned by the query
                           $("Records").innerHTML=""
                           for(i=0;i<results.rows.length;i++)
                           $("Records").innerHTML += results.rows.item(i).Title +"
"
                         },
                         function(transaction, error) {
                           Mojo.Log.error("Could not read: " + error.message);
                         });
});
};

Important Notes

db.transaction(
function(transaction) {
  transaction.executeSql(sql, [],
                         function(transaction, results) {
                          // this function is a success handler, if the query executed properly, this function will be called
                         },
                         function(transaction, error) {
                         // This function is a failure handler, if any error executing the query.
                         });
});

We can have multiple transaction of queries like below

db.transaction(InsertQuery,[values],SuccessHandler(transaction,results), FailureHandler(transaction,error))
db.transaction(SelectQuery,[],SuccessHandler(transaction,results), FailureHandler(transaction,error))

Output