PhoneGap Study Notes (v) Local database Web SQL

WEB SQL allows us to create small databases on the client. It itself is an sqlite embedded database

First, open or create a database

var db = Window.opendatabase ("TestDB", "1.0", "TestDB", 100000,function () {});

This method opens a database, creates a data if the database does not exist, and returns the database example, receiving 5 parameters

First: Name of database

Second One: Version

Third: The name or description of the database display

Fourth one: Database size

Fifth: Create a callback (this parameter is not required)

Second, the business

Db.transaction (Function (TX) {},function (Error) {},function () {});

The start transaction contains three parameters, where the first parameter is a mandatory parameter and the second to third parameter is non-mandatory.

First parameter: must, the method that executes the transaction, automatically passes in the transaction object

Second parameter: Callback for a transaction execution failure

Third parameter: A successful callback for a transaction execution

Executes the SQL statement through the transaction's ExecuteSQL (Sql,args,function (tx,resultset) {},function (tx,error) {}), which contains four parameters, where the first parameter is required, and the next three bits are not required

First parameter: SQL statement to execute

The second parameter: the execution parameter of the placeholder in the SQL statement (if the "?" is included in the SQL statement) placeholder, the parameter is a mandatory parameter, and multiple parameters correspond to the array one by one)

Third parameter: Execute a successful callback, which returns two parameters, the first is the transaction object, and the second is the result set (if there is a query that returns a result set, the parameter must)

Fourth parameter: Execution error callback

Iii. about the successful callback function in ExecuteSQL

ExecuteSQL after successful execution, the callback function passes two parameters tx and resultset, where TX is the transaction object and ResultSet contains three attributes, Insertid,rowsaffected,rows, which executes different SQL statements. These three attribute values are also different.

Insertid: This is the ID of the inserted row when the INSERT statement is executed. The remainder of the attempt to read the property throws an exception:

Uncaught invalidaccesserror:failed to read the ' Insertid ' property from ' sqlresultset ': The query didn ' t result in any RO WS being added.

Rowsaffected: The number of rows affected by the SQL statement, if no changes are returned 0

Rows: The query is the result set returned, which is an object that contains two attributes, length and item, traversed as follows (assuming that the result set contains a column named name)

for (Var i=0,len=rows.length;i<len;i++) {

var name=rows.item (i). Name

}

Iv. about ID Self-growth, primary key, and data type

Set a column as the primary key and grow from: "ID INTEGER PRIMARY key autoincrement"

Example: CREATE TABLE if not EXISTS tUser (id INTEGER PRIMARY KEY autoincrement,name VARCHAR)

The SQLite field has no data type, the data type can be ignored when defining the column, regardless of whether it is defined as Integer, varchar, or text, they are stored in a string, but there is a special case, that is, when you set a list from growing, you must

Specifies that the column data type is integer, and other cases are still recommended to write the appropriate data type, although invalid but easy to communicate with other programmers.

Complete Example:

1 <  ID= "msg"></div>

1 varMsg=document.getelementbyid ("MSG");2 init ();3 functioninit () {4   vardb = Window.opendatabase ("TestDB", "1.0", "TestDB", 100000);//Open Database5 initdb (db);6 insertdata (db);7 UpdateData (db);8 finddate (db);9   Ten } One //Create a data table A functionInitdb (db) { -Db.transaction (function(TX) { -         varSql= "CREATE TABLE IF not EXISTS tUser (id INTEGER PRIMARY KEY autoincrement,name VARCHAR)"; theTx.executesql (sql,[],function(){},function(Tx,err) {Console.log (TX); Console.log (err);}); -     });  - } - //Inserting Data + functionInsertData (db) { -Db.transaction (function(TX) { +Tx.executesql ("INSERT into TUser" (name) VALUES (?); ", [' Millet '],function(){},function(Tx,err) {console.log (err);}); ATx.executesql ("INSERT into TUser" (name) VALUES (?); ", [' xiaoming '],function(Tx,resultset) {console.log (resultSet);}); at},function(errormsg) { - Console.log (errormsg);  -     }); - } - //Modifying Data - functionUpdateData (db) { inDb.transaction (function(TX) { -Tx.executesql ("UPDATE tUser SET name=?") WHERE id=? ", [' Xiaoming 2 ', 2]); to     }); + } - //Querying Data the functionfinddate (db) { *Db.transaction (function(TX) {
var sql= "select * from TUser";
           //var sql= "select * from TUser limit 0,3"//with MySQL consistent limit query, limit the second parameter passed in-1 means the query to the end of the record  $Tx.executesql (sql,[],function(tx,resultset) {Panax Notoginseng             varrows =resultset.rows; -             varStr= ""; the              for(vari=0,len=rows.length;i<len;i++){ +Str=str+rows.item (i). id+ ":" +rows.item (i). name+ "<br/>" A             } theMsg.innerhtml=str; +         }); -    });  $}

Results:

This address: http://www.cnblogs.com/wangjiajun/p/4054323.html

PhoneGap Study Notes (v) Local database Web SQL