Saturday, November 20, 2010

Sample example on Web Database API

Hi,

Here iam going to briefly explain (with sample) on web database API.

Web Database API or Client-side Database is one of the HTML 5 feature.
Modern Browsers are providing client-side database, as of now, opera 10.6 and chrome 4+ (may be safari too) are supporting this feature. IE9, FF4 may support this feature in their final release.

Here iam providing sample html page, to get start with web database API, download the sample at the end of the post.
Most web browsers are likely to implement this embedded database feature using "sqlite". check this site for proper usage or supported SQL statements: www.sqlite.org


In this sample example, iam creating a table called "MyTable" with 3 columns: id, first name, last name.
The UI provides, CreateTable, DropTable, AddRecords, ShowTable buttons and status text and result in Table.

Example Code:

<!DOCTYPE html>
<html>
<head>
<meta name="author" content="Raju Dasa"/>
<script type="text/javascript">
//global vars
var fname,lname;
var db = null;

window.onunload=function(){ db = null; } //close connection
function CreateTransaction(func)
{
if (window.openDatabase)
{
if(db == null) db = window.openDatabase("app", "", "my database", 1024*1024);
db.transaction(func);
}
else
{
GetErr("", {'message':"Your Browser doesn't supports HTML5 Local Database!"});
}
}
function createTable(tx)
{
tx.executeSql('CREATE TABLE MyTable(id integer primary key,fname char(20),lname char(20))',[],GetResult,GetErr);
}
function showTable(tx)
{
tx.executeSql('SELECT * FROM MyTable', [], GetResult,GetErr);
//var name='raju'; //tx.executeSql('SELECT * FROM MyTable WHERE name = ?', [ name ], GetResult,GetErr);
}
function GetResult(tx, rs)
{
if(rs.rows && rs.rows.length>0)
{
$("output").innerHTML = "";
for(var i=0;i<rs.rows.length;i++)
{
var str = "<tr><td>"+rs.rows.item(i).id+
"</td><td>"+rs.rows.item(i).fname+"</td><td>"+
rs.rows.item(i).lname+"</td></tr>";
$("output").innerHTML += str;
//alert(rs.rows.item(1).fname); //rows[0].name ->support in opera.
}
}
$("status").innerHTML = 'Rows Affected: ' + ((rs.rowsAffected==0 || rs.rowsAffected<rs.rows.length)? rs.rows.length : rs.rowsAffected);
}
function GetErr(tx, err)
{
$("status").innerHTML = err.message || "";
$("output").innerHTML = "";
}
function DropTable(tx)
{
tx.executeSql('DROP TABLE MyTable',[],
function(txs,rs){ $("status").innerHTML="Done!"; },GetErr);
}
//--------------
function AddRecord(tx)
{
fname = $("ftxt").value;
lname = $("ltxt").value;
if(fname!="" || lname !="")
{
AppendRecord(tx);
}
}
function AppendRecordResult(tx,rs)
{
var uid = rs.rows.item(0).uid || 100;
if( uid > 0 )
{
var str = (1+uid) + ",'"+fname+"','"+lname+"'";
tx.executeSql("INSERT INTO MyTable VALUES("+str+")",[],GetResult,GetErr);
$("ftxt").value=""; $("ltxt").value="";
}
}
function AppendRecord(tx)
{
tx.executeSql("SELECT MAX(id) AS uid FROM MyTable", [], AppendRecordResult,
function(txs, err)
{
$("status").innerHTML="Error! "+err.message;
} );
}
function $(id){ return document.getElementById(id); }
</script>
<style type="text/css">
BUTTON{ margin:4px; border:1px solid red; width:85px; }
</style>
</head>
<body>
<br/>
<center><b>HTML5 Web Database API</b></center>
<br/>
<button onclick="CreateTransaction(createTable)">createTable</button>
<br/>
firstName:<input type="text" id="ftxt"/> lastName:<input type="text" id="ltxt"/>
<button onclick="CreateTransaction(AddRecord)">Add record</button>
<br/>
<button onclick="CreateTransaction(showTable)">ShowTable</button>
<br/>
<button onclick="CreateTransaction(DropTable)">DropTable</button>
<br/><br/>
<div>
<table border="1" id="output" style="table-layout:fixed;">
</table>
</div>
<br/>
<b>status</b>:&nbsp;&nbsp;<span id="status"></span>

</body>
</html>

If you have Chrome web browser, check chrome developer tools (storage tab), one can check, query(run SQL) the data for a site.


Below is the screenshot from Chrome.


screenshot from Opera.


For support in older browsers, you need to install GoogleGears plugin.
(most of the features of GoogleGears are now included in HTML5).


Points to note:

  • If you are dealing with data, always write your logic (javascript code) in the callback functions, since it works in async manner.

  • Don't open multiple instances of database connections, it's allowed, but it's better to create one and make it globally accessable.


Download the sample here.



References:sites:
  • http://www.infoq.com/news/2010/02/Web-SQL-Database

  • www.sqlite.org

  • http://www.webreference.com/authoring/languages/html/HTML5-Client-Side/

  • http://www.w3.org/TR/offline-webapps/

No comments:

Post a Comment