In this post we will see some informations about Web SQL. I know you all are familiar with SQL, If not I strongly recommend you to read some basic informations here . As the name implies, Web SQL has so many similarities with SQL. So if you are good in SQL, you will love Web SQL too. Web SQL is an API which helps the developers to do some database operations in client side, like creating database, open the transaction, creating tables, inserting values to tables, deleting values, reading the data. If you need any other way to save some data in client side, you can use storage mechanisms introduced in HTML5.
Now we will look some of the operations a developer can do with Web SQL. I hope you will like this.
Using the code
As you all know, to work with SQL queries, you must create a database. So the first step we are going to do, is creating the database.
Create/Open Web SQL Database
To create a Web SQL database, we can use a function called openDatabase which has four parameters as follows.
The creation callback gets fired while the database is being created.
Now shall we open a Web SQL database with the above mentioned parameters? We can do that by running a query as follows.
[sql]
var myDBInstance = openDatabase(‘dbsibeeshpassion’, ‘1.0’, ‘This is a client side database’, 2 * 1024 * 1024);
[/sql]
Here I have given the size of my database as 2*1024*1024. In most browsers the size is flexible, but few maintains a limit of 5 MB. As from the above query we have created a Web SQL database. Now we will check whether the DB is creatd successfully or not.
[js]
//check whether the database is created or not.
if (!myDBInstance) {
alert(‘Oops, your database was not created’);
}
else {
var version = myDBInstance.version;
}
[/js]
Here, you will get an alert if the database is not created. Or you will be able to fetch the version details from the database instance.
Getting_version_details_from_database_instance
Once the database is created, we can start using the transaction as we uses in SQL.
Creating transaction
To create a transaction we can use the following syntax. We can use transaction method from our database instance.
[js]
myDBInstance.transaction(function (tran) {
});
[/js]
Here myDBInstance is our database instance. And tran is our transaction object which we are going to use for our upcoming operations. Why we uses transaction is, as you all know transaction can be roll backed. For example, if any of the operation throws any error, the transaction will be roll backed so there won’t be any kind of mismatching data happening. And off course, we can easily manage error logs with the help of transaction. Shall we write queries needed for our operations?
First of all, we will create a table in our database. To execute any queries in Web SQL, you must use the method executesql.
[sql]
tran.executeSql(‘CREATE TABLE IF NOT EXISTS Users (id unique, Name, MailID)’);
[/sql]
As you can see we are creating the table Users if it does not exists in the database. As in SQL we are assigning id as a unique key.
Next thing is we need to insert some rows to our table.
[sql]
tran.executeSql(‘insert into Users (id, Name, MailID) values (1, "Sibi","sibikv4u@gmail.com")’);
tran.executeSql(‘insert into Users (id, Name, MailID) values (2, "Aji","ajaybhasy@gmail.com")’);
tran.executeSql(‘insert into Users (id, Name, MailID) values (3, "Ansu","ansary.ans21@gmail.com")’);
[/sql]
If you want to assign name, mailid, id values to insert query, you are welcomed to create those variables and assign to the query as shown below.
[js]
var name = "Sibi";
var id = "1";
var MailID = "sibikv4u@gmail.com";
tran.executeSql(‘insert into Users (id, Name, MailID) values (?,?,?)’,[id,name,MailID]);
[/js]
So we have inserted some values too. Now we need to read the data we have inserted to our table right? To do that we can use we need to create a new transaction and another executeSql command.
[js]
tran.executeSql(‘SELECT * FROM Users’, [], function (tran, data) {
});
[/js]
Here we will get the output in data. As you can see I have given a call back function along with the command. This can be used to loop through our data and shows the same in our page. So we can modify our reading transaction block as follows.
[js]
myDBInstance.transaction(function (tran) {
var html = ‘<table><thead><th>Mail ID </th><th>ID</th><th>Name </th></thead><tbody>’;
tran.executeSql(‘SELECT * FROM Users’, [], function (tran, data) {
for (i = 0; i < data.rows.length; i++) {
html += ‘<tr><td>’
+ ‘<a ‘ + ‘href="mailto:’ + data.rows[i].MailID + ‘">’ + data.rows[0].MailID + ‘</a>’ +
‘</td><td>’ + data.rows[i].id + ‘</td><td>’ + data.rows[i].Name + ‘</td></tr>’;
};
html += ‘</tbody></table>’;
$(‘#myTab’).html(html);
});
});
[/js]
Before that,
You can add a CSS for the table we are creating dynamically as follows.
[css]
<style>
table,tr,td,th {
border:1px solid #ccc;
border-radius:5px;
padding:10px;
margin:10px;
}
</style>
[/css]
Complete code
Complete code for the implementation is given below.
[html]
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Introduction to Web SQL</title>
<script src="Scripts/jquery-1.11.1.min.js"></script>
<script type="text/javascript">
var myDBInstance = openDatabase(‘dbsibeeshpassion’, ‘1.0’, ‘This is a client side database’, 3 * 1024 * 1024);
//check whether the database is created or not.
if (!myDBInstance) {
alert(‘Oops, your database was not created’);
}
else {
var version = myDBInstance.version;
//var name = "Sibi";
//var id = "1";
//var MailID = "sibikv4u@gmail.com";
myDBInstance.transaction(function (tran) {
tran.executeSql(‘CREATE TABLE IF NOT EXISTS Users (id unique, Name, MailID)’);
//tran.executeSql(‘insert into Users (id, Name, MailID) values (?,?,?)’, [id, name, MailID]);
tran.executeSql(‘insert into Users (id, Name, MailID) values (1, "Sibi","sibikv4u@gmail.com")’);
tran.executeSql(‘insert into Users (id, Name, MailID) values (2, "Aji","ajaybhasy@gmail.com")’);
tran.executeSql(‘insert into Users (id, Name, MailID) values (3, "Ansu","ansary.ans21@gmail.com")’);
});
myDBInstance.transaction(function (tran) {
var html = ‘<table><thead><th>Mail ID </th><th>ID</th><th>Name </th></thead><tbody>’;
tran.executeSql(‘SELECT * FROM Users’, [], function (tran, data) {
for (i = 0; i < data.rows.length; i++) {
html += ‘<tr><td>’
+ ‘<a ‘ + ‘href="mailto:’ + data.rows[i].MailID + ‘">’ + data.rows[0].MailID + ‘</a>’ +
‘</td><td>’ + data.rows[i].id + ‘</td><td>’ + data.rows[i].Name + ‘</td></tr>’;
};
html += ‘</tbody></table>’;
$(‘#myTab’).html(html);
});
});
}
</script>
<style>
table,tr,td,th {
border:1px solid #ccc;
border-radius:5px;
padding:10px;
margin:10px;
}
</style>
</head>
<body>
<div id="myTab"></div>
</body>
</html>
[/html]
Output
Web_SQL_Output
That is all. We did it. Have a happy coding.
Conclusion
Did I miss anything that you may think which is needed? Did you try Web SQL yet? Have you ever wanted to do this requirement? Could you find this post as useful? I hope you liked this article. Please share me your valuable suggestions and feedback.
Your turn. What do you think?
A blog isn’t a blog without comments, but do try to stay on topic. If you have a question unrelated to this post, you’re better off posting it on C# Corner, Code Project, Stack Overflow, Asp.Net Forum instead of commenting here. Tweet or email me a link to your question there and I’ll definitely try to help if I can.
Kindest Regards
Sibeesh Venu