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>
Encrypted SQL API usage example
</title>
<link rel="x-antenna-managed-webapp-descriptor"
href="webapp-descriptor.xml"/>
<script type="text/javascript">
var db;
function openDB() {
if (db == null) {
try {
var onCreated = function() {
printText('db Created/opened');
}
printText("Opening database...");
//
var radios = document.getElementsByName('sharing');
var sharing;
for (var i = 0, length = radios.length; i < length; i++) {
if (radios[i].checked) {
sharing = radios[i].value;
break;
}
}
//
db = openDatabase('testdb', '1.0',
'my test database', 2 * 1024 * 1024, sharing);
}
catch (e) {
printText("Error occured in openDB: " + e);
}
}
}
function createDBandPopulateSampleData() {
try {
openDB();
dbObj = db;
sharing = null;
// create schema (using object callbacks)
printText("Creating schema...");
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: " + JSON.stringify(err.message));
},
function () {
printText("Inserting data transaction succeeded");
}
);
printText("Data inserted.");
} catch (e) {
printText("Error occured in createDBandPopulateSampleData: " + e);
}
}
function createDBandPopulateSampleDataWithInnerTransaction() {
try {
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 < 49999; i++) {
var t = new Date().getTime();
}
console.log("finished long loop");
},
function(err) {
printText("Something went wrong when inserting data: " + JSON.stringify(err.message));
},
function() {
printText("Inserting data transaction succeeded");
}
);
printText("Data inserted.");
} catch(e) {
printText("Error occured in createDBandPopulateSampleData: " + e);
}
}
function selectAndPrintAllDataWithCallbackFunction() {
try {
openDB();
dbObj = db;
printText("Selecting all data (with 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 + ": id=[" +
results.rows.item(i).id + "] text=[" +
results.rows.item(i).text + "]");
}
},
function(tx, result) { printText('Reading data failed: ' +
result.message)}
);
},
function (err) {
printText(
"Something went wrong when selecting all data (with callback function): " +
err);
},
function () {
printText("Selecting data transaction succeeded");
}
);
} catch (e) {
printText("Error occured in selectAndPrintAllData: " + e);
}
}
function selectAndPrintAllDataWithCallbackObject() {
try {
openDB();
dbObj = db;
printText("Selecting all data (with 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 + ": id=[" +
results.rows.item(i).id + "] text=[" +
results.rows.item(i).text + "]");
}
}
});
}
},
{
handleEvent:
function (err) {
printText(
"Something went wrong when selecting all data (with callback object): " +
err);
}
});
} catch (e) {
printText("Error occured in selectAndPrintAllData: " + e);
}
}
function deleteDataWithCallbackFunction() {
try {
openDB();
dbObj = db;
printText("Deleting all data (with 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 callback function): " +
err);
},
function () {
printText("Deleting data transaction succeeded");
}
);
} catch (e) {
printText("Error occured in selectAndPrintAllData: " + e);
}
}
function deleteAllDataWithCallbackFunction() {
try {
openDB();
dbObj = db;
printText("Deleting all data (with 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 callback function): " +
err);
},
function () {
printText("Deleting data transaction succeeded");
});
} catch (e) {
printText("Error occured in selectAndPrintAllData: " + e);
}
}
function selectAndPrintDataWithNullText() {
try {
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 + ": id=[" + results.rows.item(i).id +
"], text=[" + results.rows.item(i).text +
"], text === null: " +
(results.rows.item(i).text === null));
}
}
);
},
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", "1.1", function (tx) {
tx.executeSql(
'INSERT INTO testtable (id, text) VALUES (11, "apple")');
});
}
</script>
</head>
<body>
<header>
<h3>
<span>
Encrypted SQL API Usage Example
</span>
</h3>
</header>
<div style="padding: 10px;">
<div class="info">
This Application demonstrates the use of the SQL JavaScript API.
</div>
</div>
<div>
<form action="#n" name="sharingForm">
<label for="gender">
Sharing:
</label>
<input type="radio" name="sharing" value="" checked="">
None
</input>
<br>
<br>
<input type="radio" name="sharing" value="window.launchbox.SQLStorage.SHARED_AMONG_APPS" >
Among apps
</input>
<br>
<br>
<input type="radio" name="sharing" value="window.launchbox.SQLStorage.SHARED_AMONG_USERS" >
Among users
</input>
<br>
<br>
</form>
</div>
<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="Create db, 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 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: *
The webapp-descriptor.xml file for this application is defined in the
following way:
<?xml version="1.0" encoding="UTF-8"?> <webapp-descriptor xmlns="http://www.pega.com/application-hosting/web-app-descriptor/2.0"> <id>com.pega.sample.encryptedsql</id> <version>1.0.0</version> <name>Encrypted SQL API usage example</name> </webapp-descriptor>