HTML5 's web SQL Databases (HTML5 Local database) 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 HTML 5 Web SQL Database api:opendatabase, transaction, ExecuteSQL. 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
The code is as follows:
var dataBase = OpenDatabase ("Student", "1.0", "Student table", 1024x768/1024x768, function () {});
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_4987.
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
The code is as follows:
This.createtable=function () {
Database.transaction (Function (TX) {
Tx.executesql (
"CREATE table if not exists Stu (ID REAL UNIQUE, 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.
2) string data inserted into the query where the question mark is located.
3) The callback function executed upon success. Returns two parameters: TX and the result of execution.
4) A callback function that executes when a failure occurs. Returns two parameters: TX and failed error message.
Step three: Perform additions and deletions and change
1) Add Data:
The code is as follows:
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
The code is as follows:
This.query = function () {
Database.transaction (Function (TX) {
Tx.executesql (
"SELECT * from Stu", [],
function (tx, result) {//Execute successful callback function
Do what you want to do with the result here .....
},
Function (TX, error) {
Alert (' Query failed: ' + error.message);
} );
});
}
Explain
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:
The code is as follows:
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 a column for a row of query results: Result.rows[i].item[fieldname].
3) Update data
The code is as follows:
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
The code is as follows:
This.del = function (ID) {
Database.transaction (Function (TX) {
Tx.executesql (
"Delete from Stu where id=?",
[ID],
function (tx, result) {
},
Function (TX, error) {
Alert (' Delete failed: ' + error.message ');
});
});
}
5) Delete Data sheet
The code is as follows:
this.droptable = function () {
Database.transaction (Function (TX) {
Tx.executesql (' drop table Stu ');
});
}
HTML5 Tutorial HTML 5 Local database (Web SQL databases)
