HTML 5 on-premises database (Web SQL database) Core method

The Web SQL database API is not actually part of the HTML5 specification, but rather a separate specification. It manipulates the client's database through a set of APIs. Web SQL Database is already supported by mainstream browsers such as Safari, Chrome, Firefox, and opera. HTML5 's web SQL databases is really tempting, and when you find that you can manipulate the local database with query statements like MySQL queries, you'll find it interesting. Today, let's look at the Web SQL Database API for HTML 5.

Here's how to create an open database, create a table, add data, update data, delete data, delete a table.

Introduction of three core methods first

1. OpenDatabase: This method creates a database object using an existing database or creating a new database.

2. Transaction: This method allows us to control the transaction commit or rollback, depending on the situation.

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

First step: Open the connection and create the database

var database = OpenDatabase ("Student", "1.0", "Student table", 1024x768-1024x768, function () {}), and if (!database) {alert ("Database creation failed!") ");} else {alert ("Database creation succeeded! ");}

Explain the OpenDatabase method opens a database that already exists, and if the database does not exist, it can also create a database. Several parameter meanings are:
1, database name.
2, the version number is currently 1.0, regardless of him, write Dead is OK.
3, the description of the database.
4. Set the size of the data.
5, the callback function (can be omitted).
The database is created at the first call, and then the connection is established.
The created database exists locally and the path is as follows:
C:\Users\Administrator\AppData\Local\Google\Chrome\User data\default\databases\http_localhost_*.
Create a Sqllite database, you can open the file with Sqlitespy, you can see the data inside. Sqlitespy is a green software, you can Baidu or Sqlitespy official download: Sqlitespy.

Step Two: Create a data table

This.createtable=function () {database.transaction (tx) {tx.executesql ("CREATE table if not exists Stu (ID REAL U Nique, name TEXT) ", [], function (tx,result) {alert (' Create Stu Table succeeded ');}, function (TX, error) {alert (' Create Stu Table failed: ' + error.message ); });});}

Explain,
The ExecuteSQL function has four parameters, the meanings of which are:
1) A string representing the query, using the SQL language is SQLite 3.6.19. (required)
2) string data inserted into the query where the question mark is located. (optional)
3) The callback function executed upon success. Returns two parameters: TX and the result of execution. (optional)
4) A callback function that executes when a failure occurs. Returns two parameters: TX and failed error message. (optional)

Step three: Perform additions and deletions and change

1) Add Data:

This.insert = function () {Database.transaction (function (TX) {tx.executesql ("INSERT into Stu (ID, name) VALUES (?,?)", [ID , ' Xu Mingxiang '],function () {alert (' Add data Success '),},function (TX, error) {alert (' Add data failed: ' + error.message ');});

2) query data

This.query = function () {Database.transaction (function (TX) {tx.executesql ("select * from Stu", [],function (TX, result) {//execute a successful callback function//Here to result do what you want to do ...},function (TX, error) {alert (' Query failed: ' + error.message ');});}

Special Reminders
The successful callback function in the code above has a parameter of result.

Result: The data set that is queried. Its data type is sqlresultset, just like a DataTable in C #.
The sqlresultset is defined as:

Interface SqlResultSet {readonly attribute long insertid;readonly attribute long rowsaffected;readonly attribute sqlresultsetrowlist rows;};

One of the most important properties of the-sqlresultsetrowlist type is rows for the dataset.
Rows has two properties: Length, item.
Therefore, get the value of the first row of the query result named name: Result.rows.item (0). Name.

3) Update data

This.update = function (ID, name) {database.transaction (function (TX) {tx.executesql ("update stu set name =?") Where id=? ", [Name, Id],function (TX, result) {},function (TX, error) {alert (' Update failed: ' + error.message);});}

4) Delete data

This.del = function (id) {database.transaction (function (TX) {tx.executesql ("delete from Stu where id=?", [Id],function (t X, result) {},function (TX, error) {alert (' Delete failed: ' + error.message);});}

5) Delete Data sheet

this.droptable = function () {Database.transaction (function (TX) {tx.executesql (' drop table Stu ');});

HTML 5 on-premises database (Web SQL database) Core method