Previous | Next

4 Database support HOWTO

4.1 Initialization

      const alias = "urn:aliases:sqltest";
      const complete = Components.interfaces.mozISqlRequest.STATUS_COMPLETE;

      var connection;
      var result;

      function init() {
        var sqlService = Components.classes["@mozilla.org/sql/service;1"]
                         .getService(Components.interfaces.mozISqlService);

        if (!service.hasAlias(alias)) {
          alert("The alias for the sqltest is not defined.");
          return;
        }

        try {
          connection = sqlService.getConnection(alias);
        }
        catch (ex) {
          alert(service.errorMessage);
          return;
        }

        var query = "select code, name from states";
        var request = connection.asyncExecuteQuery(query, null, startupObserver);
      }

      var startupObserver = {
        onStartRequest: function(request, ctxt) {
        },
        onStopRequest: function(request, ctxt) {
          if (request.status == complete) {
            result = request.result;
            var ds = result.QueryInterface(Components.interfaces.nsIRDFDataSource);

            var menulist = document.getElementById("statesMenulist");
            menulist.database.AddDataSource(ds);
            menulist.builder.rebuild();
            menulist.selectedIndex = 0;

            var tree = document.getElementById("statesTree");
            tree.database.AddDataSource(ds);
            tree.builder.rebuild();
          }
          else {
            alert(request.errorMessage);
          }
        }
     };
    

4.2 Integration with XUL

       <menulist id="statesMenulist"
                 datasources="rdf:null" ref="SQL:ResultRoot">
         <template>
           <menupopup>
             <menuitem uri="..."
                       value="rdf:http://www.mozilla.org/SQL-rdf#code"
                       label="rdf:http://www.mozilla.org/SQL-rdf#name"/>
           </menupopup>
         </template>
        </menulist>

        <tree id="statesTree" flex="1"
              context="editContextMenu"
              seltype="single" enableColumnDrag="true"
              datasources="rdf:null" ref="SQL:ResultRoot" flags="dont-build-content">
          <treecols>
            <treecol id="codeCol"
                     label="State code"
                     sort="rdf:http://www.mozilla.org/SQL-rdf#code"
                     sortActive="true" sortDirection="ascending"/>
            <splitter class="tree-splitter"/>
            <treecol id="nameCol" flex="1"
                     label="State name"
                     sort="rdf:http://www.mozilla.org/SQL-rdf#name"/>
          </treecols>
          <template>
            <treechildren>
              <treeitem uri="rdf:*">
                <treerow>
                  <treecell label="rdf:http://www.mozilla.org/SQL-rdf#code"/>
                  <treecell label="rdf:http://www.mozilla.org/SQL-rdf#name"/>
                </treerow>
              </treeitem>
            </treechildren>
          </template>
        </tree>
    

4.3 Realtime queries

      <vbox>
        <textbox> id="syncStateCode" size="2" maxlength="2"
                  onkeyup="if (event.keyCode == 13) syncFindState()"/>
        <textbox id="syncStateName"/>
      </vbox>

      function syncFindState() {
        var code = document.getElementById("syncStateCode").value
        var query = "select name from states where code = '" + code + "'";
        try {
          var result = connection.executeQuery(query);
          var element = document.getElementById("syncStateName");
          if (result.rowCount) {
            var enumerator = result.enumerate();
            enumerator.first();
            element.value = enumerator.getVariant(0);
          }
          else {
            element.value = "Not found";
          }
        }
        catch (ex) {
          alert(connection.errorMessage);
        }
      }
    

4.4 Updateable results

      <popupset>
        <popup id="editContextMenu">
          <menuitem label="Insert" oncommand="doInsert()"/>
          <menuitem label="Update" oncommand="doUpdate()"/>
          <menuitem label="Delete" oncommand="doDelete()"/>
       </popup>
      </popupset>

      function getSelectedRowIndex() {
        var tree = document.getElementById("statesTree");
        var currentIndex = tree.currentIndex;
        var resource = tree.builderView.getResourceAtIndex(currentIndex);
        var datasource = result.QueryInterface(Components.interfaces.mozISqlDataSource);
        return datasource.getIndexOfResource(resource);
      }

      function doInsert() {
        window.openDialog("testDialog.xul", "testDialog", "chrome,modal=yes,resizable=no", result);
      }

      function doUpdate() {
        var rowIndex = getSelectedRowIndex();
        window.openDialog("testDialog.xul", "testDialog", "chrome,modal=yes,resizable=no", result, rowIndex);
      }

      function doDelete() {
        var rowIndex = this.getSelectedRowIndex();
        var enumerator = result.enumerate();
        enumerator.absolute(rowIndex);
        try {
          enumerator.deleteRow();
        }
        catch(ex) {
          alert(enumerator.errorMessage);
        }
      }


      var result;
      var enumerator;

      function init() {
        result = window.arguments[0];
        enumerator = result.enumerate();
        if (window.arguments.length == 2) {
          enumerator.absolute(window.arguments[1]);
          var columnCount = result.columnCount;
          for(var i = 0; i < columnCount; i++) {
            if (!enumerator.isNull(i)) {
              var element = document.getElementById(result.getColumnName(i));
              element.value = enumerator.getVariant(i);
            }
          }
        }
      }

      function onAccept() {
        var columnCount = result.columnCount;
        for (var i = 0; i < columnCount; i++) {
          var element = document.getElementById(result.getColumnName(i));
          if (element.value)
            enumerator.setVariant(i, element.value);
          else
            enumerator.setNull(i);
        }

        try {
          if (window.arguments.length == 2)
            enumerator.updateRow();
          else
            enumerator.insertRow();
        }
        catch(ex) {
          alert(enumerator.errorMessage);
          return false;
        }

        return true;
      }

    

4.5 Screenshot

Screenshot

Previous | Next