Preliminary Exploration of HTML 5 Web SQL Database

Perhaps the most useful thing for HTML 5 is its new "Web Storage" API. You can store simple key-value pairs (such as application settings) or simple objects (such as application status). Local and session storage can be used to store these objects, however, in addition to processing trivial relational data, it is far less powerful. This is the application of the "Web SQL Database" API of HTML 5.

First, let me give you a good understanding of JavaScript and object-oriented programming (especially internal functions in an anonymous system) and SQL.

Open Link

To open a connection, run the following code:

Db = openDatabase ("ToDo", "0.1", "A list of to do items.", 200000 );
The code above creates a database object db named Todo with version number 0.1. The db also contains the description and approximate size values. The user agent can use this description to communicate with the user and describe what the database is used. With the size value provided in the Code, the user agent can leave enough storage for the content. If needed, the size can be changed, so there is no need to preassume how much space the user is allowed to use.

To check whether the previously created connection is successful, you can check whether the database object is null:

If (! Db)
Alert ("Failed to connect to database .");
It cannot be assumed that the connection has been successfully established, even if it was successful for a user in the past. There are multiple reasons why a connection fails. Maybe the User Agent rejects your access for security reasons, maybe the device storage is limited. In the face of active and rapidly evolving potential user agents, it is unwise to make assumptions about users' machines, software and capabilities. For example, when a user uses a handheld device, the data they can freely dispose of may only contain a few megabytes.

Execute Query

Run a query. You can use the database. transaction () function. This function has a single parameter that queries the actually executed function.

This function (usually anonymous) has a type of transaction parameter.

Db. transaction (function (tx ){
The transaction has a function: executeSql. This function uses four parameters: the query string, which is inserted into the string data where the question mark is located in the query (similar to the prepared statements in Java ), a function that is successfully executed and a function that fails to be executed.

Tx.exe cuteSql ("select count (*) FROM ToDo", [], function (result) {}, function (tx, error ){});

 

When the query is successful

When the query is successfully executed, the application jumps to a query with a pair of parameters, one is transaction, and the other is the results it collects. This is perfect for actually passing your data to users, such as displaying the ToDo list. I will talk about this topic later.

Query failure

Run the command when the query fails to be executed. Because you pass the transaction object as the first parameter of the function, you can continue the query when an error occurs. For example, if a query cannot run because a table is missing, this is a great time to create a table and execute this statement. From the second parameter of the function, you can obtain information (including descriptions) about the error ).

Example

Suppose we want to use the example above to query a table in the database. If the table does not exist, we will create a table.

In this example, we will call db. transaction () with a function parameter (). In this parameter, we call tx.exe cuteSql (). If this step succeeds, we will not perform any operation (so it is a null parameter ). Or we can pass the transaction together with the failed function and call tx.exe cuteSql () again (). This time, create a query is used.

Db. transaction (function (tx) {tx.exe cuteSql ("select count (*) FROM ToDo ",
[], Null, function (tx, error) {tx.exe cuteSql ("create table ToDo (id real unique, label TEXT, timestamp REAL )",
[], Null, null );});});
 
Using all these internal methods may be a little troublesome, so you may want to create a function that calls db. transaction () externally. For example, we can make the error function self-contained and name it "createToDoTable ()".

Insert

To make the code more concise and secure, the Web SQL Database API allows you to provide string data for the transaction.exe cuteSql () function to represent variables in the called SQL statement. The following code is used for Demonstration:

Db. transaction (function (tx) {tx.exe cuteSql ("insert into ToDo (label, timestamp) values (?, ?) ",
[Label, new Date (). getTime ()], null, null );});
 
In this example, the two question marks in the first parameter are replaced by the corresponding items in the following array. The first one is the TAG set for the task (maybe a variable defined in the Code) and the timestamp generated by calling the function.

Execute this query. The result is similar to the following statement:

Insert into ToDo (label, timestamp) values ("Test", 1265925077487)
Process the result

A successfully executed function contains a set or row for the result object. Each column represents a result. The result contains a group of values assigned to it, indicating the values of each column in the database of the specific result. You can access a row by calling result. rows. item (I), where I is the pointer of the row you want to query. If you want to select a value from a row, you can pass the row to a string pointer in array format, which indicates the column you want to query. For example, if you want the label column, you can call row ['label'].

The following code uses a result object to output a query result:

Db. transaction (function (tx) {tx.exe cuteSql ("SELECT * FROM ToDo", [],
Function (tx, result) {for (var I = 0; I <result. rows. length; I ++)
{Document. write ('<B>' + result. rows. item (I) ['label'] + '</B> <br/>') ;}}, null );});
 
Conclusion

It should be noted that, if not absolutely necessary, do not use Web SQL Database. This is not because they are highly technical, but because they make your code more complex. In most cases, local storage or session storage can complete the corresponding tasks, especially when you can maintain the object state persistence.

As mentioned above, you can obtain many functions through these HTML 5 Web SQL Database API interfaces. I believe that there will be some excellent applications built on these APIs in a few years.