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