Querying the data using JDE

As mentioned in previous article, JDE gateway accepts following variables that can be sent using both GET and POST methods:

dbBee_qs	This variable defines a query string to be searched for. When dbBee JDE receives dbBee_qs it will return all records that contain the word(s) or phrase (if the dbBee_qs value is enclosed in quotation marks), in any part of any non-numeric field in the record source used by JDE (Excel sheet or Access table).
dbBee_sql	This is an SQL WHERE clause, used to extract only those records that fulfill a specified criterion. Since dbBee works directly with Excel and Access files, dbBee_sql must follow Microsoft SQL syntax for WHERE clause.
dbBee_mr	Maximum number of records to be retuned for single request. The default is all records.
dbBee_sr	Number of record to start from. Default value is 1. Combined with dbBee_mr this can be used for pagination.

In this article we’ll show practical use of dbBee_sql variable to filter the records using search criteria.

We will use the same code used in previous blog with few minor modifications to existing code and adding some new lines:


<body onload="loadJSON();">
<script src="https://code.jquery.com/jquery-1.12.2.min.js"></script>
<script>
var maxrec = 10;
var startrec=1;
var rc=0;
function MakeNavigation(){
var pg=1;
var curpg=parseInt(startrec / maxrec)+(parseInt(startrec / maxrec)==startrec / maxrec?0:1);
NavSel='<select id="SelPage" size="1" onchange="startrec=this.options[this.selectedIndex].value;loadJSON();">';
for(i=1;i<=rc;i+=maxrec){
NavSel+='<option value="'+i+'">Go to page '+(pg++)+'</option>';
}
NavSel+="</select>";
document.getElementById("demo").innerHTML += NavSel;
document.getElementById("SelPage").value=(curpg-1)*maxrec+1;
document.getElementById("SelPage").options[document.getElementById("SelPage").selectedIndex].text = "Current page "+curpg; 
}
function loadJSON(){
  var aut=document.getElementById("author").value;
  var pub=document.getElementById("publisher").value;
  if(aut !="" && pub !="") {
    var ao=document.getElementById("ac").checked?" and ":" or ";
    var sql='author = "' + aut +'"'+ ao +'publisher = "' + pub+'"';
  }else{
    var sql=aut != ""?'author = "' + aut+'"':(pub != ""?'publisher = "' + pub+'"':"");
  }
$.getJSON('https://thyme.dbbee.com/u/SGCEQ5WZFZ/BooksJSONjsonapi.wbsp?dbbee_sr='+startrec+'&dbbee_mr='+maxrec+'&dbbee_sql='+sql, function (data) {
    var tHTML="The search returned "+data.BooksCount + " records<br/>";
    rc=parseInt(data.BooksCount);
    for(var i in data.Books){
    tHTML += data.Books[i].Image+"<h1>" +
    data.Books[i].Title +
    "</h1><h2>" +
    data.Books[i].Author +
    "</h2><h2>" + 
    data.Books[i].Publisher +
    "</h2><h3>" +
    data.Books[i]['Year Published'] +
    "</h3><hr>";
    }
    document.getElementById("demo").innerHTML = tHTML;
    MakeNavigation();
    
  });
  }
//loadJSON();
</script>
<form>
Author: <select name="Author" id="author">
<option value="">any</option>
<option value="Andrew Troelsen">Andrew Troelsen</option>
<option value="Betsy Bruce">Betsy Bruce</option>
<option value="Elizabeth Castro">Elizabeth Castro</option>
<option value="Ivor Horton">Ivor Horton</option>
<option value="Jerry Glenwright">Jerry Glenwright</option>
<option value="Matt Keller">Matt Keller</option>
<option value="Olav Martin Kvern">Olav Martin Kvern</option>
<option value="Rod Trent">Rod Trent</option>
<option value="Roger Pring">Roger Pring</option>
<option value="Sandee Cohen">Sandee Cohen</option>
<option value="Scott Kelby">Scott Kelby</option>
<option value="Thomas Sheldon">Thomas Sheldon</option>
<option value="Tom Sheldon">Tom Sheldon</option>
</select><br/>
Publisher: <select name="Publisher" id="publisher">
<option value="">any</option>
<option value="Addison-Wesley Pub Co">Addison-Wesley Pub Co</option>
<option value="Apress">Apress</option>
<option value="McGraw-Hill Professional Publishing">McGraw-Hill Professional Publishing</option>
<option value="NAPP Publishing, Inc.">NAPP Publishing, Inc.</option>
<option value="New Riders Publishing">New Riders Publishing</option>
<option value="Osborne McGraw-Hill">Osborne McGraw-Hill</option>
<option value="Peachpit Press">Peachpit Press</option>
<option value="Sams">Sams</option>
<option value="The Coriolis Group">The Coriolis Group</option>
<option value="Watson-Guptill Pubns">Watson-Guptill Pubns</option>
<option value="Wordware Publishing">Wordware Publishing</option>
<option value="Wrox Press Inc.">Wrox Press Inc.</option>
</select><br/>
Search for both conditions <input type="checkbox" name="ac" id="ac"><br/>
<input type="button" value="Search" onclick="startrec=1;loadJSON();">
</form>
<div id="demo"></div>

We marked all the new stuff with red, so it is easier to track. First let’s check what we have modified in code we used in previous article:


$.getJSON('https://thyme.dbbee.com/u/SGCEQ5WZFZ/BooksJSONjsonapi.wbsp?dbbee_sr='+startrec+'&dbbee_mr='+maxrec+'&dbbee_sql='+sql

As you can see, we added dbBee variable dbbee_sql to the URL, so dbBee will filter the records and send us only those that meet the conditions. We also removed (commented) call to loadJSON() function from the script and moved it to the onload event hooked to the body tag. We did this because in this case we need the entire document to be loaded before we send ajax request to the dbBee. You’ll notice that we added a search form with two selection boxes and added some code to the loadJSON() function. The code we added builds vales for dbBee_sql variable based on the values of form elements, and therefore we can not call loadJSON() beforeform elements are loaded.

Let’s see the code we added to the loadJSON() function:


  var aut=document.getElementById("author").value;
  var pub=document.getElementById("publisher").value;
  if(aut !="" && pub !="") {
    var ao=document.getElementById("ac").checked?" and ":" or ";
    var sql='author = "' + aut +'"'+ ao +'publisher = "' + pub+'"';
  }else{
    var sql=aut != ""?'author = "' + aut+'"':(pub != ""?'publisher = "' + pub+'"':"");
  }

What we do here is this – we assign selected values of form elements (selection boxes) to variables. Then we check if both variables have value (both selection boxes have a selected value). If that is the case we combine two condition using either SQL keyword AND or SQL keyword OR depending on state of checkbox labeled "Search for both conditions”. In the other case (when one or both variables have empty value) we check which one has the value and build dbBee_sql based on it, or leave dbBee_sql value empty in case that none of them has selected value.

Function MakeNavigation() remained unchanged, since pagination works same way regardless of total number of records. For live demo please click here (http://dbbee.com/demo/demojsonnavsrc.htm)

Now, our application is fully functional, but still looks pretty basic (if not ugly). To fix the appearance we only need few CSS rules added to our page and few minor changes in our HTML (both hard-coded and generated by JavaScript function loadJSON):

CSS:


<style type="text/css">
body{
     font-family:Arial, Helvetica, sans-serif;
     font-size:14px;
}
select, form input{
     box-sizing: border-box;
     -webkit-box-sizing: border-box;
     -moz-box-sizing: border-box;
     border:1px solid #BEBEBE;
     padding: 7px;
     margin:0px;
     -webkit-transition: all 0.30s ease-in-out;
     -moz-transition: all 0.30s ease-in-out;
     -ms-transition: all 0.30s ease-in-out;
     -o-transition: all 0.30s ease-in-out;
     outline: none; 
     margin-bottom:12px;
}
select{
     width:227px;
}
select:focus{
     -moz-box-shadow: 0 0 8px #88D5E9;
     -webkit-box-shadow: 0 0 8px #88D5E9;
     box-shadow: 0 0 8px #88D5E9;
     border: 1px solid #88D5E9;
}
form input[type=button]{
     background: #4B99AD;
     padding: 8px 15px 8px 15px;
     border: none;
     color: #fff;
     margin-left:34px;
     cursor:pointer;
}
form input[type=button]:hover{
     background: #4691A4;
     box-shadow:none;
     -moz-box-shadow:none;
     -webkit-box-shadow:none;
}
.form-title{
     float:left;
     width:75px;
     margin-top:9px;
}
#allConditions{
     float:left;
     margin-top:9px;
     width:192px;
}
form input[type=checkbox]{
     float:right;
     margin-top:3px;
}
h1, h2, h3 {
     padding:0;
     margin:0 0 15px 0;
}
h1{
     font-size:24px;
}
h2{
     font-size:18px;
     color:#7e7e7e;
}
h3{
     font-size:14px;
}
img{
     float:left;
     margin-right:10px;
     margin-top:7px;
}
.content-wrap{
     float:left;
     margin-bottom:10px;
     width:100%;
}
div.content-wrap:first-of-type{
     margin-top:30px;
}
div.content-wrap:last-of-type{
     margin-bottom:30px;
}
#SelPage{
     float:left;
}
  </style>

Changes in HTML:

In hardcoded HTML we replaced label Author: with <span class="form-title">Author:</span> and label Publisher: with <span class="form-title">Publisher:</span>, added <strong> attribute in few places and added wrapper div <div id="allConditions"> to all conditions label and checkbox. Also, we changed HTML generated by JavaScript function loadJSON by adding a wrapper div <div class='content-wrap'>. Changed code looks like this (changes are again marked in red color):


function loadJSON(){
  var aut=document.getElementById("author").value;
  var pub=document.getElementById("publisher").value;
  if(aut !="" && pub !="") {
    var ao=document.getElementById("ac").checked?" and ":" or ";
    var sql='author = "' + aut +'"'+ ao +'publisher = "' + pub+'"';
  }else{
    var sql=aut != ""?'author = "' + aut+'"':(pub != ""?'publisher = "' + pub+'"':"");
  }
$.getJSON('https://thyme.dbbee.com/u/SGCEQ5WZFZ/BooksJSONjsonapi.wbsp?dbbee_sr='+startrec+'&dbbee_mr='+maxrec+'&dbbee_sql='+sql, function (data) {
    var tHTML="The search returned "+data.BooksCount + " records<br/>";
    rc=parseInt(data.BooksCount);
    for(var i in data.Books){
    tHTML +="<div class='content-wrap'>" + data.Books[i].Image+"<h1>" +
    data.Books[i].Title +
    "</h1><h2>" +
    data.Books[i].Author +
    "</h2><h2>" + 
    data.Books[i].Publisher +
    "</h2><h3>" +
    data.Books[i]['Year Published'] +
    "</h3></div>";
    }
    document.getElementById("demo").innerHTML = tHTML;
    MakeNavigation();
    
  });
  }
//loadJSON();
</script>
<form>
<span class="form-title">Author:</span> <select name="Author" id="author">
<option value="">any</option>
<option value="Andrew Troelsen">Andrew Troelsen</option>
<option value="Betsy Bruce">Betsy Bruce</option>
<option value="Elizabeth Castro">Elizabeth Castro</option>
<option value="Ivor Horton">Ivor Horton</option>
<option value="Jerry Glenwright">Jerry Glenwright</option>
<option value="Matt Keller">Matt Keller</option>
<option value="Olav Martin Kvern">Olav Martin Kvern</option>
<option value="Rod Trent">Rod Trent</option>
<option value="Roger Pring">Roger Pring</option>
<option value="Sandee Cohen">Sandee Cohen</option>
<option value="Scott Kelby">Scott Kelby</option>
<option value="Thomas Sheldon">Thomas Sheldon</option>
<option value="Tom Sheldon">Tom Sheldon</option>
</select><br/>
<span class="form-title">Publisher:</span> <select name="Publisher" id="publisher">
<option value="">any</option>
<option value="Addison-Wesley Pub Co">Addison-Wesley Pub Co</option>
<option value="Apress">Apress</option>
<option value="McGraw-Hill Professional Publishing">McGraw-Hill Professional Publishing</option>
<option value="NAPP Publishing, Inc.">NAPP Publishing, Inc.</option>
<option value="New Riders Publishing">New Riders Publishing</option>
<option value="Osborne McGraw-Hill">Osborne McGraw-Hill</option>
<option value="Peachpit Press">Peachpit Press</option>
<option value="Sams">Sams</option>
<option value="The Coriolis Group">The Coriolis Group</option>
<option value="Watson-Guptill Pubns">Watson-Guptill Pubns</option>
<option value="Wordware Publishing">Wordware Publishing</option>
<option value="Wrox Press Inc.">Wrox Press Inc.</option>
</select><br/>
<div id="allConditions">
Search for both conditions <input type="checkbox" name="ac" id="ac"><br/>
</div>
<input type="button" value="Search" onclick="startrec=1;loadJSON();">
</form>
<div id="demo"></div>

Now, with these few simple modifications completely done in front-end our search looks much more attractive and professional. To see live demo please click here.

As you can see from these few articles, it is not only possible, but also really simple and easy to create fully customized, real world application that queries and browses the database records without single line of database programming (or any other kind of server-side programming), using only dbBee Wizard and jQuery/JavaScript.

Related blogs

Navigating the data using JDE

In this article we’ll show practical use of dbBee_mr and dbBee_sr variables to limit the number of records per request and to paginate the records...

Connecting dbBee JDE with host page using jQuery

In this article we’ll give you basic instructions on how to connect JDE with your host page.

Building JSON data exchange project using JDE wizard

JDE project creates a gateway for serving data stored in your Excel Sheet or Access Table as a JSON document that can easily be fetched using AJAX with JavaScript or jQuery.