Encrypted SQL example

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>

Encrypted SQL

Encrypted SQL example

Related topics

API reference (29)

Index