- HTML5 provides database storage to store data on client's machine using a Structured Query Language (SQL) database.
- It uses a temporary database to store data for a specified period of time.
- In order to use this feature we need to open database connection, then we can execute SQL queries on database using two basic functions i.e. transaction() and executeSql().
Opening an connection
var db = openDatabase('HTML5DB', '1.0', 'Client Side DB', 50 * 1024 * 1024);
//With Callback Function
var db = openDatabase('HTML5DB', '1.0', 'Client Side DB', 50 * 1024 * 1024, function () {
alert("DB Created");
});
The preceding code snippet creates a database object, db, with the title HTML5DB, a version number of 1.0, along with a description and approximate size and callback function in later one.We need to pass basic four arguments to the openDatabase method and callback function if needed.
- Database name
- Version number
- Text Description
- Size (Approx)
- Callback
The callback function is called when the database is being created. The return value from the openDatabase method contains the transaction methods needed to perform SQL operations (queries) on database.
If you try to open a database that doesn’t exist, the API will create it on the fly for you. You also don’t have to worry about closing databases.
Size
The default database size is 5MB for borwsers. The Safari browser shows prompt if user tries to create database exceeding the default database size.
The version number is required argument to openDatabase.
You can change or update version of database using changeVersion method.
Using this method we can know which version of database user is using and then we can upgrade. The changeVersion method is supported only in chrome and opera.
Transaction
The ability to rollback if some error occurs is why we use transaction for executing sql queries. There are also error ans success callbacks on the transaction, so you can manage errors.
//A simple transaction
db.transaction(function (tx) {
//using tx object we can execute multiple sql queries.
tx.executeSql("CREATE TABLE IF NOT EXISTS EMPLOYEE (id unique,name Text)");
});
In the preceding code snippet we have used transaction and used executeSql method inside transaction. The above SQL query creates a table EMPLOYEE.executeSql method
The executeSql method is used to execute a SQL query on database.
The executeSql method takes four arguments:
1. a string query.
2. an array of strings to insert the values for place holders in string query.
3. success callback function.
4. failure calback function.
Its a good practice to use SQL quesries or executeSql method inside transaction.
Query to Create Table
$("#CreateTable").click(function () {
db.transaction(function (tx) {
tx.executeSql("CREATE TABLE IF NOT EXISTS EMPLOYEE (id unique,name Text)", [], function () {
alert("Table Created");
}, function () {
alert("Error");
});
});
});
The preceding code snippet creates a table EMPLOYEE with id and name as parameter. We have also defined callback functions for success and failure of executeSql method.
You can check chrome's developer tool to verify the Database and Table creation. You can verify this under Resources tab.
We have created EMPLOYEE table under HTML5DB database. We can verify the table created under Web SQL.
Insert Record
$("#InsertRecord").click(function () {
db.transaction(function (tx) {
tx.executeSql("INSERT INTO EMPLOYEE (id,name) VALUES (1,'Jack Wilshere')", [], function () {
alert("Record Inserted");
}, function () {
alert("Error");
});
});
});
Suppose we want to capture the table data to insert from external source, then we can use the second parameter i.e. inserting the values in the table supplying values to the placeholder defined in the query.
var id = "2";
var name = "Thierry Henry";
$("#InsertRecord").click(function () {
db.transaction(function (tx) {
tx.executeSql("INSERT INTO EMPLOYEE (id,name) VALUES (?,?)", [id, name], function () {
alert("Record Inserted");
}, function () {
alert("Error");
});
});
});
In the preceding code snippet we have defined placeholders in the query. We are then passing values to the placeholder to insert data into the table. The executeSql method's second argument maps the field data to the query.id and name are external variables, and executeSql maps each item in the array argument to the “?”s.
Display Records
$("#SelectRecord").click(function () {
db.transaction(function (tx) {
tx.executeSql('SELECT * FROM EMPLOYEE', [], function (tx, results) {
var len = results.rows.length, i;
var ulEle = $("<ul/>");
for (i = 0; i < len; i++) {
ulEle.append("<li>" + results.rows.item(i).name + "</li>");
}
$("#targetDiv").append(ulEle);
});
});
});
In the preceding code snippet we have used select query to select all the records from the table and displayed on UI. We have created a UL element and then appened LI elements to UL element.We need to use the column name after the item object to access the value of that column.
For Example
In order to get name column's value we should use it like below:
results.rows.item(i).name
Deleting a Record
$("#DeleteRecord").click(function () {
db.transaction(function (tx) {
tx.executeSql("DELETE FROM EMPLOYEE WHERE id=1", [], function () {
alert("Record Deleted");
}, function () {
alert("Error");
});
});
});
In the above code snippet we have used Delete statement to delete record with id =1 from EMPLOYEE table.Dropping a Table
$("#DropTable").click(function () {
db.transaction(function (tx) {
tx.executeSql("DROP TABLE EMPLOYEE", [], function () {
alert("Table Deleted");
}, function () {
alert("Error");
});
});
});
In the preceding code snippet we have delete EMPLOYEE table from the HTML5DB database.This is all about Database storage in HTML5, enough to start on Database storage.
No comments:
Post a Comment