The index.html file containing JavaScript that makes use of the
Encrypted SQL API functionality is listed below.
<!DOCTYPE html>
<html manifest="manifest.appcache">
<head>
<title>WebSQL api sample</title>
<link rel="stylesheet" href="../css/style.css" type="text/css" media="all" />
<script type="text/javascript">
var db, dbS;
function openDB() {
if (db == null) {
try {
var onCreated = function() {
printText('db Created/opened');
}
printText("Opening database...");
db = openDatabase('testdb', '1.0', 'my test database', 2 * 1024 * 1024, onCreated);
} catch (e) {
printText("Error occured in openDB: " + e);
}
}
}
function openSharedDB() {
if (dbS == null) {
try {
var onCreated = function() {
printText('db Created/opened');
}
printText("Opening database...");
dbS = openSharedDatabase('testdb', '2.0', 'my test database', 2 * 1024 * 1024, onCreated);
} catch (e) {
printText("Error occured in openDB: " + e);
}
}
}
function createDBandPopulateSampleData(isShared) {
try {
if(isShared) {
openSharedDB();
dbObj = dbS;
} else {
openDB();
dbObj = db;
}
// create schema (using object callbacks)
printText("Creating schema...");
dbObj.transaction(
function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS testtable (id unique, text)');
},
function (err) {
printText("Something went wrong when creating schema: " + err);
}
);
printText("Schema created");
// populate date (using function callbacks)
printText("Inserting data...");
dbObj.transaction(
function (tx) {
tx.executeSql('INSERT INTO testtable (id, text) VALUES (1, "apple")');
tx.executeSql('INSERT INTO testtable (id, text) VALUES (?, ?)', [2, 'banana']);
tx.executeSql('INSERT INTO testtable (id, text) VALUES (?, ?)', [3, 'kiwi'],
function(tx, result) { printText('kiwi record insert succeeded.')},
function(tx, error) { printText('kiwi record insert failed.' + error.message)});
tx.executeSql('INSERT INTO testtable (id, text) VALUES (?, ?)', [4, 'orange'],
null,
function(tx, error) { printText('orange record insert failed.')});
tx.executeSql('INSERT INTO testtable (id, text) VALUES (?, ?)', [5, 'plum'],
function(tx, result) { printText('plum record insert succeeded.')},
null);
tx.executeSql('INSERT INTO testtable (id, text) VALUES (?, ?)', [6, "an'a'nas"],
null,
null);
tx.executeSql('INSERT INTO testtable (id, text) VALUES (?, ?)', [7, 'gr"a"pe'],
undefined,
undefined);
tx.executeSql('INSERT INTO testtable (id, text) VALUES (?, ?)', [8, "man'go"],
function(tx, result) { printText('mango record insert succeeded.')},
undefined);
tx.executeSql('INSERT INTO testtable (id, text) VALUES (?, null)', [9]);
tx.executeSql('INSERT INTO testtable (id, text) VALUES (?, ?)', [10, null]);
},
function (err) {
printText("Something went wrong when inserting data: " + err);
},
function () {
printText("Inserting data transaction succeeded");
}
);
printText("Data inserted.");
} catch (e) {
printText("Error occured in createDBandPopulateSampleData: " + e);
}
}
function createDBandPopulateSampleDataWithInnerTransaction(isShared) {
try {
if (isShared) {
openSharedDB();
dbObj = dbS;
} else {
openDB();
dbObj = db;
}
// create schema (using object callbacks)
printText("Creating schema...");
dbObj.transaction(
function(tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS testtable (id unique, text)');
},
function(err) {
printText("Something went wrong when creating schema: " + err);
}
);
printText("Schema created");
// populate date (using function callbacks)
printText("Inserting data...");
dbObj.transaction(
function(tx) {
tx.executeSql('INSERT INTO testtable (id, text) VALUES (1, "apple")');
tx.executeSql('INSERT INTO testtable (id, text) VALUES (?, ?)', [2, 'banana'],
function(parentTx, results) {
console.log("inside banana executeSql callback, parentTx: " + JSON.stringify(parentTx) + ", results: " + results);
parentTx.executeSql('INSERT INTO testtable (id, text) VALUES (?, ?)', [11, 'bananaInner11']);
parentTx.executeSql('INSERT INTO testtable (id, text) VALUES (?, ?)', [12, 'bananaInner12']);
},
function(err) {
console.log("outer banana tx error: " + err);
}
);
tx.executeSql('INSERT INTO testtable (id, text) VALUES (?, ?)', [3, 'kiwi'],
function() { printText('kiwi record insert succeeded.'); },
function(parentTx, error) { printText('kiwi record insert failed.' + error.message); });
tx.executeSql('INSERT INTO testtable (id, text) VALUES (?, ?)', [4, 'orange'],
null,
function() { printText('orange record insert failed.'); });
console.log("started long loop");
for (var i = 0; i < 4999999; i++) {
var t = new Date().getTime();
}
console.log("finished long loop");
},
function(err) {
printText("Something went wrong when inserting data: " + err);
},
function() {
printText("Inserting data transaction succeeded");
}
);
printText("Data inserted.");
} catch(e) {
printText("Error occured in createDBandPopulateSampleData: " + e);
}
}
function selectAndPrintAllDataWithCallbackFunction(isShared) {
try {
if(isShared) {
openSharedDB();
dbObj = dbS;
} else {
openDB();
dbObj = db;
}
printText("Selecting all data (with a callback function)...");
dbObj.transaction(
function (tx) {
tx.executeSql('SELECT * FROM testtable', [],
function (tx, results) {
console.log(results);
var len = results.rows.length;
for (var i = 0; len > i; i++) {
printText("row " + i + ": " + JSON.stringify(results.rows.item(i)));
}
},
function(tx, result) { printText('Reading data failed: ' + result.message)}
);
},
function (err) {
printText("Something went wrong when selecting all data (with a callback function): " + err);
},
function () {
printText("Selecting data transaction succeeded");
}
);
} catch (e) {
printText("Error occured in selectAndPrintAllData: " + e);
}
}
function selectAndPrintAllDataWithCallbackObject(isShared) {
try {
if(isShared) {
openSharedDB();
dbObj = dbS;
} else {
openDB();
dbObj = db;
}
printText("Selecting all data (with a callback object)...");
dbObj.transaction(
{
handleEvent:
function (tx) {
tx.executeSql('SELECT * FROM testtable', [],
{
handleEvent:
function (tx, results) {
var len = results.rows.length, i;
for (i = 0; len > i; i++) {
printText("row " + i + ": " + JSON.stringify(results.rows.item(i)));
}
}
}
);
}
},
{
handleEvent:
function (err) {
printText("Something went wrong when selecting all data (with a callback object): " + err);
}
}
);
} catch (e) {
printText("Error occured in selectAndPrintAllData: " + e);
}
}
function deleteDataWithCallbackFunction(isShared) {
try {
if(isShared) {
openSharedDB();
dbObj = dbS;
} else {
openDB();
dbObj = db;
}
printText("Deleting all data (with a callback function)...");
dbObj.transaction(function (tx) {
printText("Deleting in sequence...");
tx.executeSql("DELETE FROM testtable WHERE id=1");
tx.executeSql("DELETE FROM testtable WHERE id=?", [2]);
tx.executeSql("DELETE FROM testtable WHERE id=?", [3], null);
tx.executeSql("DELETE FROM testtable WHERE id=?", [4], null, null);
tx.executeSql("DELETE FROM testtable WHERE id=?", [5], undefined);
tx.executeSql("DELETE FROM testtable WHERE id=?", [6], undefined, undefined);
tx.executeSql("DELETE FROM testtable WHERE id=?", [7], function(tx, result) { printText('Record with id=7 deleted.')});
tx.executeSql("DELETE FROM testtable WHERE id=?", [8], null, function(tx, error) { printText('Got an error when deleting record with id=8.')});
},
function (err) {
printText("Something went wrong while deleting all data (with a callback function): " + err);
},
function () {
printText("Deleting data transaction succeeded");
}
);
} catch (e) {
printText("Error occured in selectAndPrintAllData: " + e);
}
}
function deleteAllDataWithCallbackFunction(isShared) {
try {
if(isShared) {
openSharedDB();
dbObj = dbS;
} else {
openDB();
dbObj = db;
}
printText("Deleting all data (with a callback function)...");
dbObj.transaction(function (tx) {
printText("Deleting all data...");
tx.executeSql("DELETE FROM testtable", [],
function(tx, result) { printText('Deleted data.')},
function(tx, error) { printText('Error occured when deleting data.')}
);
},
function (err) {
printText("Something went wrong while deleting all data (with a callback function): " + err);
},
function () {
printText("Deleting data transaction succeeded");
}
);
} catch (e) {
printText("Error occured in selectAndPrintAllData: " + e);
}
}
function selectAndPrintDataWithNullText(isShared) {
try {
if(isShared) {
openSharedDB();
dbObj = dbS;
} else {
openDB();
dbObj = db;
}
printText("Selecting data with null text...");
dbObj.transaction(
function (tx) {
tx.executeSql('SELECT * FROM testtable WHERE text IS NULL', [],
function (tx, results) {
var len = results.rows.length, i;
for (i = 0; len > i; i++) {
printText("row " + i + ": " + JSON.stringify(results.rows.item(i)));
}
}
);
},
function (err) {
printText("Something went wrong when selecting data with null text): " + err);
}
);
} catch (e) {
printText("Error occured in selectAndPrintDataWithNullText: " + e);
}
}
function printText(str) {
var d = document.getElementById('results');
d.innerHTML += "<br/>" + str;
d.scrollTop = d.scrollHeight;
console.log(str);
}
function clearText() {
var d = document.getElementById('results');
d.innerHTML = "";
}
function changeDBVersion() {
openDB();
dbObj = db;
dbObj.changeVersion("1.0", "2.0", function (tx) {
tx.executeSql('INSERT INTO testtable (id, text) VALUES (11, "apple")');
});
}
</script>
</head>
<body>
<header>
<h3>SQL API example</h3>
</header>
<div style="padding: 10px;">
<div class="info">This Application demonstrates usage of SQL JavaScript API provided by Pega.</div>
</div>
<table width="100%">
<tr>
<td width="50%" style="border-right: 1px solid;" align="center">
<div id="simple-activate" onclick="openTabButtons();" style="font-weight: bold;">SIMPLE DATABASE</div>
</td>
<td width="50%" align="center">
<div id="shared-activate" onclick="openTabButtons(true);">SHARED DATABASE</div>
</td>
</tr>
</table>
<div id="simple-buttons" style="display: block;padding: 10px;">
<input type="button" onclick="createDBandPopulateSampleData();" value="Create DB and populate sample data"/>
<input type="button" onclick="createDBandPopulateSampleDataWithInnerTransaction();" value="Select and print all data with inner transaction"/>
<input type="button" onclick="selectAndPrintAllDataWithCallbackFunction();" value="Select and print all data (callback function)"/>
<input type="button" onclick="selectAndPrintAllDataWithCallbackObject();" value="Select and print all data (callback object)"/>
<input type="button" onclick="selectAndPrintDataWithNullText();" value="Print data with null text"/>
<input type="button" onclick="deleteAllDataWithCallbackFunction();" value="Delete all data"/>
<input type="button" onclick="changeDBVersion();" value="Change DB version"/>
</div>
<div id="shared-buttons" style="display: none;padding: 10px;">
<div class="info">Shared database works only in Pega Mobile Client.</div>
<input type="button" onclick="createDBandPopulateSampleData(true);" value="Create DB and populate sample data"/>
<input type="button" onclick="createDBandPopulateSampleDataWithInnerTransaction(true);" value="Select and print all data with inner transaction"/>
<input type="button" onclick="selectAndPrintAllDataWithCallbackFunction(true);" value="Select and print all data (callback function)"/>
<input type="button" onclick="selectAndPrintAllDataWithCallbackObject(true);" value="Select and print all data (callback object)"/>
<input type="button" onclick="selectAndPrintDataWithNullText(true);" value="Select and print data with null text"/>
<input type="button" onclick="deleteAllDataWithCallbackFunction(true);" value="Delete all data"/>
</div>
<div style="padding: 10px;margin-top:-20px;">
<input type="button" onclick="clearText();" value="Clear all text"/>
</div>
<div id="results"></div>
<script type="text/javascript">
function openTabButtons(openShared) {
var toOpen = document.getElementById("simple-buttons");
var toClose = document.getElementById("shared-buttons");
var toBold = document.getElementById("simple-activate");
var toUnbold = document.getElementById("shared-activate");
if(openShared) {
toOpen = document.getElementById("shared-buttons");
toClose = document.getElementById("simple-buttons");
toBold = document.getElementById("shared-activate");
toUnbold = document.getElementById("simple-activate");
}
toOpen.style.display = "block";
toClose.style.display = "none";
toBold.style.fontWeight = "bold";
toUnbold.style.fontWeight = "normal";
}
</script>
</body>
</html>
The contents of the cache manifest file called manifest.appcache for
this application are listed below:
CACHE MANIFEST CACHE: index.html NETWORK: *