Transcript Introducing HTML and XHTML

Phonegap Bridge – File System
CIS 136 Building Mobile Apps
1
Storage Options
Storage API
2
Methods
1. First instruction will be to openDatabase() –



Creates a new SQLLite database or opens one that has already been
created
database is in persistent storage
This method returns a database object that allows manipulation of the
data
var appDB = window.openDatabase(“myDB”,”1.0”,”notes”, 1024*1024);
2. Next instruction will be the transaction()–
 executes SQL statements using executeSql() method
 The first executeSql() method is to create/open a table
appDB.transaction(txnSQLFunc,onTxnError,onTxnSuccess);
function txnSQLFunc()
{
}
3
Process so far…
var appDB = window.openDatabase(“myDB”,”1.0”,”notes”, 1024*1024);
appDB.transaction(CreateTable,onTxError,onTxSuccess);
function CreateTable(tx)
{
var sqlString = ‘CREATE TABLE IF NOT EXISTS STUDENTS
(StudentID TEXT PRIMARY KEY NOT NULL,
FirstName TEXT,
LastName TEXT,
Email TEXT)’;
tx.executeSql(sqlString, [], onSqlSuccess(), onSqlError();
}
function onTxError(tx,err)
{
console.log(“transaction failed: Code: “ + err.code);
}
fuction onTxSuccess()
{
console.log(“Transaction successful”);
}
4
Understanding executeSql()


Executes sql statements
Four parameters





SQL Statement – to execute against the database object the transaction
isassociated with
Value – array of values passed to SQL statement
Success function – Function that will execute if the SQL statement is sucessfully
processed
Error function –function that will execute if the SQL statement fails
The success function is passed two parameters:



a transaction object (which is used to execute additional SQL statements
against the table)
A results object which contains the results of the SQL operation
The results object exposes the following properties:



5
insertID:If the SQL statement was to insert a record, the ROW ID
rowAffected: If the SQL statement selected, inserted, updated, or deleted records,
the number of records affected
Rows: An object containing the records returned by the SQ statement
example sqlSuccess function
function onSqlSuccess(tx,res)
{
If (res) // make sure a results object was returned
{
console.log(“Insert ID: “ + res.insertID);
console.log(“Rows affected: “ + res.rowAffected;
if (res.rows) // if records were returned
{
var numRecs = res.rows.length;
if numRecs > 0
{
for (var i=0; i< numrecs; i++)
{
// do something with each record
}
}
}
}
6
Select Statement
Retrieves records from a table
Syntax:


SELECT * FROM table_name;
Syntax for sorting records

SELECT *
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;
 Syntax for
filtering records
SELECT *
FROM table_name
WHERE column_name operator value
[AND column_name operator value]
[OR column_name operator value];
7
Insert Into Statement
Adds a record to the table
Syntax


INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
Example

function insertRecord(tx)
{
var str= “INSERT INTO Customers (CustomerName,Country)
VALUES ('Tom Smith', 'Norway')”;
tx.executeSql(str,[],onInsertSuccess,onInsertError);
}
8
Insert Into Statement
Example 2

function insertRecord(tx)
{
var strName = document.getElementById(“custName”).value;
var strCtry = document.getElementById(“custCtry”).value;
var str= “INSERT INTO Customers (CustomerName,Country)
VALUES (?,?)”;
tx.executeSql(str,[strName,strCtry],onInsertSuccess,onInse
rtError);
}
9
Update Statement


Updates a record that is in the table
Syntax
UPDATE table_name
SET column1=value1,column2=value2,...
[WHERE some_column=some_value];
Example
function updateRecord(tx)
{
var strName = document.getElementById(“custName”).value;
var strCtry = document.getElementById(“custCtry”).value;
var str= “UPDATE Customers
SET CustomerName = strName,Country=strCtry)
WHERE Country=‘Norway’
tx.executeSql(str,[],onUpdateSuccess,onUpdateError);
}
10
Delete Statement


Removes a record from the table
Syntax
DELETE FROM table_name
[WHERE some_column=some_value];
Example
function deleteRecord(tx)
{
var str= “DELETE FROM Customers
WHERE Country=‘Norway’;
tx.executeSql(str,[],onDeleteSuccess,onDeleteError);
}
11