How to use WebSQL Databases in HTML5
Using HTML5, web pages can store data locally within the user’s browser.
WebSQL database defines an API for storing data in databases that can be queried using a variant of SQL.
There are three core methods:
1. openDatabase
2. transaction
3. executeSql
Creating and Opening Databases(openDatabase)
If you try to open a database that doesn’t exist, the API will create it on the fly for you using openDatabase method.
To create and open a database, use the following code:
|
I’ve passed four arguments to the openDatabase method. These are:
- Database name
- Version number
- Text description
- Estimated size of database
Transactions(transaction)
Once we have opened our database, we can create transactions. The transactions give us the ability to rollback the query transactions.
i.e., if a transaction — which could contain one or more SQL statements — fails (either the SQL or the code in the transaction), the updates to the database are never committed — i.e. it’s as if the transaction never happened.
in single word we can say, transaction obeys, ATOMICITY property of DBMS.
There are also error and success callbacks on the transaction, so you can manage errors, but it’s important to understand that transactions have the ability to rollback changes.
|
|
executeSql
executeSql is used for both read and write statements, includes SQL injection projection, and provides a callback method to process the results of any queries you may have written.
db.transaction(function (query) { |
Sample HTML Code:-
<!doctype html>
<html>
<head>
<script>
//Create DB Connection
var db =openDatabase(‘mydb’,’1.0′,’testdb’,1024);
//Create table under the above DB
db.transaction(function(query){
query.executeSql(‘create table if not exists user(id unique, usesr, passwd)’);
});
//Insert values to the table
db.transaction(function(query){
query.executeSql(‘insert into user values (1,”jai”,”pass”)’);
});
//Get stored values from the table
db.transaction(function(query){
query.executeSql(‘select * from user’,[],function(u,results){
document.write(‘lenght => ‘+results.rows.length)
});
});
</script>
</head>
</html>
Output:-(Executed in Google Chrome)
Nice and simple. Thanks for sharing.
Good 1 🙂 Keep sharing HTML5 for mobile 😉
Thanks for the interesting post.
However, it seems that the WebSQL specification in not in active maintenance anymore ("Beware. This specification is no longer in active maintenance and the Web Applications Working Group does not intend to maintain it further." http://www.w3.org/TR/webdatabase/).
WebSQL is not supported in IE and Firefox. For a list of supported Browsers and Versions see http://caniuse.com/sql-storage.
Hi Thomas,
Thanks for bring it up.
IndexDB in HTML5 would replace this browser compatiblity issue.
I am seeing most interesting stuffs with HTML5.. I will share my experience as i learn.
Thanks.
Bharath. S
Interesting..