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