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>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:
*

Related topics

Public API reference
Overview
Legal notice | Copyright © 2018 and Confidential to Pegasystems Inc. All rights reserved
PDN | Feedback
Advanced...