HTML5 Basic Tutorial

HTML5 API

HTML5 Media

HTML5 Graphics

HTML5 Application Cache HTML5 Client Storage

HTML 5 Local database

HTML5The Web SQL Database (html5 Locally stored data is indeed very tempting. When you find that you can use query statements similar to those used in MySQL to operate local databases, you will find this thing quite interesting. Today, let's learn about HTML 5The Web SQL Database API: openDatabase, transaction, executeSql.

HTML5 Web SQL Database

WebSQL is an independent module on the front end and is a type of web storage. We often see it during debugging, but it is rarely used.

If you are a Web backend programmer, it should be easy for you to understand SQL operations.

Web SQL Database can work in the latest versions of Safari, Chrome, and Opera browsers.

Core method

The following are the three core methods defined in the specification:

openDatabase: This method uses an existing database or a new database to create a database object.
transaction: This method allows us to control a transaction and perform commit or rollback based on the situation.
executeSql: This method is used to execute actual SQL queries.

Open database

We can use the openDatabase() method to open an existing database. If the database does not exist, a new database will be created. The code is as follows:

var db = openDatabase(#39;testdb39, '1.039, 'Test WebDB39;,　2　*　1024　*　1024);

The five parameters corresponding to the openDatabase() method are as follows:

Database name
Version number
Description text
Database size
Create callback

The fifth parameter, the callback created will be called after the database is created.

Execute query operations

Execute operations using the database.transaction() function:

Example

<!DOCTYPE html>
<html>
<head>　
<meta charset="utf-8">　
<title>Basic Tutorial Website(oldtoolbag.com)</title>　
</head>
<body>
<script>
var db = openDatabase(#39;testdb39, '1.039, 'Test WebDB39;,　2　*　1024　*　1024);
db.transaction(function(tx) {　　
　　　tx.executeSql(#39;CREATE TABLE IF NOT EXISTS LOGS (id unique, log)#39;);
});
</script>
</body>
</html>

After executing the above statement, it will be in the & #39;testdb39; A table named LOGS is created in the database.

Insert data

After executing the above CREATE TABLE statement, we can insert some data:

Example

<!DOCTYPE html>
<html>
<head>　
<meta charset="utf-8">　
<title>Basic Tutorial Website(oldtoolbag.com)</title>　
</head>
<body>
<script>
var db = openDatabase(#39;testdb39, '1.039, 'Test WebDB39;,　2　*　1024　*　1024);
db.transaction(function(tx) {
　　　tx.executeSql(#39;CREATE TABLE IF NOT EXISTS LOGS (id unique, log)#39;);
　　　tx.executeSql(#39;INSERT INTO LOGS (id, log) VALUES (1, "Basic Tutorial Website")');
　　　tx.executeSql(#39;INSERT INTO LOGS (id, log) VALUES (2, "www.oldtoolbag.com")');
});
</script>
</body>
</html>

We can also use dynamic values to insert data:

Example

<!DOCTYPE html>
<html>
<head>　
<meta charset="utf-8">　
<title>Basic Tutorial Website(oldtoolbag.com)</title>　
</head>
<body>
<script>
var db = openDatabase(#39;testdb39, '1.039, 'Test WebDB39;,　2　*　1024　*　1024);
db.transaction(function(tx) {　　
　　tx.executeSql(#39;CREATE TABLE IF NOT EXISTS LOGS (id unique, log)#39;);
　　tx.executeSql(#39;INSERT INTO LOGS (id, log) VALUES (?, ?)#39;[e_id, e_log]);
});
</script>
</body>
</html>

In the example, e_id and e_log are external variables, and executeSql maps each entry in the array parameters to "?".

Read Data

The following example demonstrates how to read existing data in the database:

Example

<!DOCTYPE html>
<html>
<head>　
<meta charset="utf-8">　
<title>Basic Tutorial Website(oldtoolbag.com)</title>　
</head>
<body>
<script>
var db = openDatabase(#39;testdb39, '1.039, 'Test WebDB39;,　2　*　1024　*　1024);
　
db.transaction(function(tx) {
　　　tx.executeSql(#39;CREATE TABLE IF NOT EXISTS LOGS (id unique, log)#39;);
　　　tx.executeSql(#39;INSERT INTO LOGS (id, log) VALUES (1, "Basic Tutorial Website")');
　　　tx.executeSql(#39;INSERT INTO LOGS (id, log) VALUES (2, "www.oldtoolbag.com")');
});
　
db.transaction(function(tx) {
　　　tx.executeSql(#39;SELECT　*　FROM LOGS39, [], function (tx, results) {
　　　　　　var len = results.rows.length, i;
　　　　　　msg = "<p>Number of Query Records: "　+　len　+　"</p>";
　　　　　　document.querySelector('#status#39).innerHTML;　+= msg;
　　　　
　　　　　　for (i = 0; i < len; i++{
　　　　　　　　　alert(results.rows.item(i).log);
　　　　　　}
　　　　
　　　}, null);
});
</script>
</body>
</html>

Complete Example

Example

<!DOCTYPE html>
<html>
<head>　
<meta charset="utf-8">　
<title>Basic Tutorial Website(oldtoolbag.com)</title>　
</head>
<body>
<script>
var db = openDatabase(#39;testdb39, '1.039, 'Test WebDB39;,　2　*　1024　*　1024);
var msg;
　
db.transaction(function(tx) {
　　　　tx.executeSql(#39;CREATE TABLE IF NOT EXISTS LOGS (id unique, log)#39;);
　　　　tx.executeSql(#39;INSERT INTO LOGS (id, log) VALUES (1, "Basic Tutorial Website")');
　　　　tx.executeSql(#39;INSERT INTO LOGS (id, log) VALUES (2, "www.oldtoolbag.com")');
　　　　msg = '<p>Data table has been created and two records have been inserted.</p>';
　　　　document.querySelector('#status#39;).innerHTML = msg;
});
　
db.transaction(function(tx) {
tx.executeSql(#39;SELECT　*　FROM LOGS39, [], function (tx, results) {
　　　　var len = results.rows.length, i;
　　　　msg = "<p>Number of Query Records: "　+　len　+　"</p>";
　　　　document.querySelector('#status#39).innerHTML;　+= msg;
　
　　　　for (i = 0; i < len; i++{
　　　　　　　　msg = "<p><b>"　+　results.rows.item(i).log　+　"</b></p>";
　　　　　　　　document.querySelector('#status#39).innerHTML;　+= msg;
　　　　}
}, null);
});
</script>
<div id="status" name="status">Status Information</div>
</body>
</html>

Test and See ‹/›

The results of the above examples are shown in the figure below:

Delete Record

The format for deleting a record is as follows:

db.transaction(function(tx) {
　　　　tx.executeSql(#39;DELETE FROM LOGS WHERE id=1');
});

Deleting a specific data id can also be dynamic:

db.transaction(function(tx) {
　　　　tx.executeSql(#39;DELETE FROM LOGS WHERE id=?#39;[id]);
});

Update Record

The format for updating a record is as follows:

db.transaction(function(tx) {
　　　　tx.executeSql(#39;UPDATE LOGS SET log=\'www.oldtoolbag.com\'WHERE id=2');
});

Updating a specific data id can also be dynamic:

db.transaction(function(tx) {
　　　　tx.executeSql(#39;UPDATE LOGS SET log=\'www.oldtoolbag.com\'WHERE id=?#39;[id]);
});

Complete Example

Example

<!DOCTYPE html>
<html>
<head>　
<meta charset="utf-8">　
<title>Basic Tutorial Website(oldtoolbag.com)</title>　
</head>
<body>
<script>
var db = openDatabase(#39;testdb39, '1.039, 'Test WebDB39;,　2　*　1024　*　1024);
var msg;
　
　db.transaction(function(tx) {
　　　　tx.executeSql(#39;CREATE TABLE IF NOT EXISTS LOGS (id unique, log)#39;);
　　　　tx.executeSql(#39;INSERT INTO LOGS (id, log) VALUES (1, "Basic Tutorial Website")');
　　　　tx.executeSql(#39;INSERT INTO LOGS (id, log) VALUES (2, "www.oldtoolbag.com")');
　　　　msg = '<p>Data table has been created and two records have been inserted.</p>';
　　　　document.querySelector('#status#39;).innerHTML = msg;
　});
　
　db.transaction(function(tx) {
　　　　　　tx.executeSql(#39;DELETE FROM LOGS WHERE id=1');
　　　　　　msg = '<p>Delete id of　1　of records.</p>';
　　　　　　document.querySelector('#status#39;).innerHTML = msg;
　});
　
　db.transaction(function(tx) {
　　　　　tx.executeSql(#39;UPDATE LOGS SET log=\'www.oldtoolbag.com\'WHERE id=2');
　　　　　　msg = '<p>Update id to　2　of records.</p>';
　　　　　　document.querySelector('#status#39;).innerHTML = msg;
　});
　
　db.transaction(function(tx) {
　　　　tx.executeSql(#39;SELECT　*　FROM LOGS39, [], function (tx, results) {
　　　　　　　var len = results.rows.length, i;
　　　　　　　msg = "<p>Number of Query Records: "　+　len　+　"</p>";
　　　　　　　document.querySelector('#status#39).innerHTML;　+= msg;
　　　　　　　
　　　　　　　for (i = 0; i < len; i++{
　　　　　　　　　　msg = "<p><b>"　+　results.rows.item(i).log　+　"</b></p>";
　　　　　　　　　　document.querySelector('#status#39).innerHTML;　+= msg;
　　　　　　　}
　　　　}, null);
　});
</script>
<div id="status" name="status">Status Information</div>
</body>
</html>

Test and See ‹/›

The results of the above examples are shown in the figure below:

HTML5 Application Cache HTML5 Client Storage