HTML5 Web SQL Database

HTML5 Web SQL Database with Examples: Web SQL is quite an interesting specification that is not a part of HTML 5 but it is very helpful to develop web applications. The main purpose of web SQL is to manipulate the client-side database which is not safe for user data because it doesn’t store at the server-side. So don’t store sensitive data in the server within it.

HTML5 Web SQL Database with Examples

NOTE: HTML5 Web SQL Database works in the latest versions of web browsers such as Google Chrome, Safari, Opera only.

HTML5 Web SQL Methods

There are 3 core methods of HTML5 Web SQL that are described one by one in the following.



  • Open database
  • Transaction
  • Execute SQL

HTML5 Open database

To create an object for the database you can use an open database method. In case if the database is not present then it will create a database and also creates an object to the database. Hence no need to consider to close the connection of the database.

HTML5 Open database Syntax

var dbObj = OpenDatabase('[Database_Name]', '[Version_Number]', '[Text_Description]', '[size]', '[Creation_Callback]')

Open database  Example

1. In your HTML 5, first, create a button as follows.

<!DOCTYPE html>
<html>
<head>
<title>Open Database</title>
</head>
<body>
<button id="btnCreateDB">Create Database</button>
</body>
</html>

2. Create a Javascript function that helps to create a database.

function CreateDB() {
var Database_Name = 'MyDatabase';
var Version = 1.0;
var Text_Description = 'My First Web-SQL Example';
var Database_Size = 2 * 1024 * 1024;
var dbObj = openDatabase(Database_Name, Version, Text_Description,
Database_Size, OnSuccessCreate());
}
function OnSuccessCreate() {
alert('Database Created Sucessfully');
}

3. You have to bind this Javascript function with the onclick event of the btnCreateDB button and the code for that is as follows.

<!DOCTYPE html>
<html>
<head>
<title>Open DataBase</title>
<script>
function CreateDB() {
var Database_Name = 'MyDatabase';
var Version = 1.0;
var Text_Description = 'My First Web-SQL Example';
var Database_Size = 2 * 1024 * 1024;
var dbObj = openDatabase(Database_Name, Version, Text_Description,
Database_Size, OnSuccessCreate());
}
function OnSuccessCreate() {
alert('Database Created Sucessfully');
}
</script>
</head>
<body>
<button id="btn1" onclick="CreateDB()">Create Database</button>
</body>
</html>

4. Now you can open the file here we are opening like Google Chrome but default the output be like as shown below.HTML 5 SQL 15. Here you have to press the F12 function key & now click on the Google Chrome developer tool and go-to the resource tab which leads to the Web SQL database.

HTML 5 SQL 2

6. Select the Create Database button you will the output as given below.

HTML 5 SQL 3

And within the developer tool, you can get access to the database.

HTML 5 SQL 4

Now we have learned how to create and open a database in Web SQL and there are 5 parameters in the openDatabase function.

1. Database name-The Database Name is the mandatory argument that defines the name of the database, if not it specifies like the exception.

2. Version Number-The The version number defines the database version like version 2.0 or version 3.0 etc and it is required to open a particular database.

3. Text Description-Text Description defines information about the database.

4. The size of the database-To specifies the size of the database.

5. Creation callback- The creation callback is optional because if you didn’t specify any value in the database and want to perform some action then the creation callback is used. Once the database is created then this work is done.

Transactions

After completion of the opening database, you can create transactions that provide the commit and rollbacks. So, the transaction allows more than one query and in these transactions, any of them fails or query error occurs then with help of rollback we can get queries if success then all queries are committed.

Transactions Example

A transaction is also a function that defines more than one query.

function CreateDB() {
var Database_Name = 'MyDatabase';
var Version = 1.0;
var Text_Description = 'My First Web-SQL Example';
var Database_Size = 2 * 1024 * 1024;
var dbObj = openDatabase(Database_Name, Version, Text_Description,
Database_Size);
dbObj.transaction(function (tx) {
//Code of the transaction
//will goes here
});
}

HTML5 Execute SQL

The execute SQL function is important in the Web SQL database because it is used to execute read and write statements within SQL injection projection and defines the callbacks. Hence when we have a transaction object then we call execute SQL function.

Execute SQL Example

<!DOCTYPE html>
<html>
<head>
<title>Open DataBase</title>
<script>
function CreateDB() {
var Database_Name = 'MyDatabase';
var Version = 1.0;
var Text_Description = 'My First Web-SQL Example';
var Database_Size = 2 * 1024 * 1024;
var dbObj = openDatabase(Database_Name, Version, Text_Description,
Database_Size);
dbObj.transaction(function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS Employee_Table (id
unique, Name, Location)');
});
}
</script>
</head>
<body>
<button id="Create_DB_n_Table" onclick="CreateDB()">Create Database &
Table</button>
</body>
</html>

Execute SQL Example Output

1.When the page gets loadedHTML 5 SQL 52. In case you open the developer tools of Google Chrome then you will see the
web page like.

HTML 5 SQL 6

3. After clicking the button

HTML 5 SQL 7

Insert data into a Web SQL

Insert data into a Web SQL Example

<!DOCTYPE html>
<html>
<head>
<title>Open DataBase</title>
<script>
var Database_Name = 'MyDatabase';
var Version = 1.0;
var Text_Description = 'My First Web-SQL Example';
var Database_Size = 2 * 1024 * 1024;
var dbObj = openDatabase(Database_Name, Version, Text_Description,
Database_Size);
dbObj.transaction(function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS Employee_Table (id
unique, Name, Location)');
});
function Insert() {
var id = document.getElementById("tbID").value;
var name = document.getElementById("tbName").value;
var location = document.getElementById("tbLocation").value;
dbObj.transaction(function (tx) {
tx.executeSql('insert into Employee_Table(id, Name, Location) values(' +
id + ',"' + name + '","' + location + '")');
});
}
</script>
</head>
<body>
<form id="frm1">
<table>
<tr>
<td>ID:</td>
<td><input type="text" id="tbID" /></td>
</tr>
<tr>
<td>Name:</td>
<td><input type="text" id="tbName" /></td>
</tr>
<tr>
<td>Location:</td>
<td><input type="text" id="tbLocation" /></td>
</tr>
<tr>
<td><button id="btnInsert" onclick="Insert()">Insert</button></td>
</tr>
</table>
</form>
</body>
</html>

Here we created a form with three text boxes and with one button to submit the value.

1. When the page gets loaded you will see the following output

HTML 5 SQL 8

2. If you open the developer’s tool then you will see the output like this:

HTML 5 SQL 9

3.After filling data in text boxes and submit it.

HTML 5 SQL 10

Read data from the HTML5 web SQL

Read data from the web SQL Example

<!DOCTYPE html>
<html>
<head>
<title>Open DataBase</title>
<script>
var Database_Name = 'MyDatabase';
var Version = 1.0;
var Text_Description = 'My First Web-SQL Example';
var Database_Size = 2 * 1024 * 1024;
var dbObj = openDatabase(Database_Name, Version, Text_Description,
Database_Size);
dbObj.transaction(function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS Employee_Table (id
unique, Name, Location)');
});
function Insert() {
var id = document.getElementById("tbID").value;
var name = document.getElementById("tbName").value;
var location = document.getElementById("tbLocation").value;
dbObj.transaction(function (tx) {
tx.executeSql('insert into Employee_Table(id, Name, Location) values(' +
id + ',"' + name + '","' + location + '")');
});
}
dbObj.transaction(function (tx) {
tx.executeSql('SELECT * FROM Employee_Table', [], function (tx, results)
{
var len = results.rows.length, i;
var str = '';
for (i = 0; i < len; i++) {
str += "<tr>";
str += "<td>" + results.rows.item(i).id + "</td>";
str += "<td>" + results.rows.item(i).Name + "</td>";
str += "<td>" + results.rows.item(i).Location + "</td>";
str += "</tr>";
document.getElementById("tblGrid").innerHTML += str;
str = '';
}
}, null);
});
</script>
</head>
<body>
<p id="hh"></p>
<form id="frm1">
<table>
<tr>
<td>ID:</td>
<td><input type="text" id="tbID" /></td>
</tr>
<tr>
<td>Name:</td>
<td><input type="text" id="tbName" /></td>
</tr>
<tr>
<td>Location:</td>
<td><input type="text" id="tbLocation" /></td>
</tr>
<tr>
<td><button id="btnInsert" onclick="Insert()">Insert</button></td>
</tr>
</table>
</form>
<table id="tblGrid" cellpadding="10px" cellspacing="0" border="1">
<tr style="background-color:black;color:white;font-size:18px;">
<td>
ID
</td>
<td>
Name
</td>
<td>
Location
</td>
</tr>
</table>
</body>
</html>

With the help of a select query, you can read data from Web SQL.

Read data from the HTML5 web SQL Example Output

HTML 5 SQL 11