HTML5 Tutorial: html 5 local Database (Web SQL Database)

Comments: HTML5 Web SQL Databases (html5 local database) is really tempting. When you find that you can use the same query statement as mysql to query local Databases, you will find this thing interesting. Today, let's take a look at the Web SQL Database API of HTML 5: openDatabase, transaction, and executeSql. Web SQL database APIs are not actually part of the HTML5 specification, but are separate specifications. It uses a set of APIs to manipulate the client database. Mainstream browsers such as Safari, Chrome, Firefox, and Opera all support Web SQL databases. HTML5 Web SQL Databases is really tempting. When you find that you can use the same query statement as mysql to query local Databases, you will find this interesting thing. Today, let's take a look at the Web SQL Database API of HTML 5.

The following describes how to create an open database, create a table, add data, update data, delete data, and delete a table.

First, we will introduce three core methods.

1. openDatabase: This method uses an existing database or a new database to create database objects.

2. transaction: This method allows us to control transaction commit or rollback as needed.

3. executeSql: This method is used to execute real SQL queries.

Step 1: Open the connection and create a database

The Code is as follows:
Var dataBase = openDatabase ("student", "1.0", "student table", 1024*1024, function (){});
If (! DataBase ){
Alert ("database creation failed! ");
} Else {
Alert ("database created successfully! ");
}


The openDatabase method opens an existing database. If the database does not exist, it can also create a database. The meanings of these parameters are as follows:
1. Database Name.
2. The current version number is 1.0. If it is left empty, it will be OK.
3. Description of the database.
4. Set the data size.
5. Callback Function (can be omitted ).
The database is created when the first call is made, and a connection is established later.
The created database exists locally. The path is as follows:
C: \ Users \ Administrator \ AppData \ Local \ Google \ Chrome \ User Data \ Default \ databases \ http_localhost_4987.
A sqllite database is created. You can use SQLiteSpy to open the file and view the data in it. SQLiteSpy is a green software. You can download it from Baidu or SQLiteSpy: SQLiteSpy.

 
Step 2: create a data table

The Code is as follows:
This. createTable = function (){
DataBase. transaction (function (tx ){
Tx.exe cuteSql (
"Create table if not exists stu (id real unique, name TEXT )",
[],
Function (tx, result) {alert ('created successfully stu table ');},
Function (tx, error) {alert ('failed to create the stu table: '+ error. message );
});
});
}


To explain,
The executeSql function has four parameters, meaning:
1) indicates the query string. The SQL language is SQLite 3.6.19.
2) insert the string data at the question mark in the query.
3) The callback function executed when the call is successful. Two parameters: tx and execution result are returned.
4) A callback function executed when a failure occurs. Two parameters: tx and failed error messages are returned.

 

Step 3: add, delete, modify, and query

1) add data:

The Code is as follows:
This. insert = function (){
DataBase. transaction (function (tx ){
Tx.exe cuteSql (
"Insert into stu (id, name) values (?, ?) ",
[Id, 'xu mingxiang '],
Function () {alert ('data added successfully ');},
Function (tx, error) {alert ('failed to add data: '+ error. message );
});
});


 
2) query data

The Code is as follows:
This. query = function (){
DataBase. transaction (function (tx ){
Tx.exe cuteSql (
"Select * from stu", [],
Function (tx, result) {// callback function for successful execution
// Here, do what you want to do for the result ...........
},
Function (tx, error ){
Alert ('query failed: '+ error. message );
});
});
}


Explain
The callback function successfully executed in the above Code has a parameter result.

Result: The queried dataset. The data type is SQLResultSet, just like the able in C.
SQLResultSet is defined:

The Code is as follows:
Interface SQLResultSet {
Readonly attribute long insertId;
Readonly attribute long rowsAffected;
Readonly attribute SQLResultSetRowList rows;
};

Among them, the most important attribute-SQLResultSetRowList type rows is the "row" of the dataset ".
Rows has two attributes: length and item.
Therefore, obtain the value of a column in a row of the query result: result. rows [I]. item [fieldname].

3) update data


The Code is as follows:
This. update = function (id, name ){
DataBase. transaction (function (tx ){
Tx.exe cuteSql (
"Update stu set name =? Where id =? ",
[Name, id],
Function (tx, result ){
},
Function (tx, error ){
Alert ('Update failed: '+ error. message );
});
});
}


4) delete data


The Code is as follows:
This. del = function (id ){
DataBase. transaction (function (tx ){
Tx.exe cuteSql (
"Delete from stu where id =? ",
[Id],
Function (tx, result ){
},
Function (tx, error ){
Alert ('deletion failed: '+ error. message );
});
});
}



5) delete a data table

The Code is as follows:
This. dropTable = function (){
DataBase. transaction (function (tx ){
Tx.exe cuteSql ('drop table stu ');
});
}


Add, delete, modify, and query demos for web SQL databases. Click here to download them.