Querying the data using XDE
TweetAs mentioned in previous article, XDE 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 XDE 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 XDE (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.
Again, we will use plain JavaScript to do it, and again we will use the same code used in previous blog with few minor modifications to existing code and adding some new lines:
<body onload="loadXMLDoc();">
<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;loadXMLDoc();">';
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 loadXMLDoc() {
var xhttp = new XMLHttpRequest();
xhttp.onreadystatechange = function() {
if (xhttp.readyState == 4 && xhttp.status == 200) {
procXMLDoc(xhttp);
}
};
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+'"':"");
}
xhttp.open("GET", "https://thyme.dbbee.com/u/SGCEQ5WZFZ/BooksXMLxmlapi.wbsp?dbbee_sr="+startrec+"&dbbee_mr="+maxrec+"&dbbee_sql="+sql, true);
xhttp.send();
}
function procXMLDoc(xml) {
var i;
var xmlDoc = xml.responseXML;
var tHTML="The search returned "+xmlDoc.getElementsByTagName("BooksCount")[0].childNodes[0].nodeValue + " records<br/>";
rc=parseInt(xmlDoc.getElementsByTagName("BooksCount")[0].childNodes[0].nodeValue);
if(rc==0){
document.getElementById("demo").innerHTML = "Search returned no records!";
return;
}
var x = xmlDoc.getElementsByTagName("Book");
for (i = 0; i <x.length; i++) {
tHTML += "<h1>" +
x[i].getElementsByTagName("Title")[0].childNodes[0].nodeValue +
"</h1><h2>" +
x[i].getElementsByTagName("Author")[0].childNodes[0].nodeValue +
"</h2><h2>" +
x[i].getElementsByTagName("Publisher")[0].childNodes[0].nodeValue +
"</h2><h3>" +
x[i].getElementsByTagName("YearPublished")[0].childNodes[0].nodeValue +
"</h3>"+x[i].getElementsByTagName("Image")[0].childNodes[0].nodeValue+"<hr>";
}
document.getElementById("demo").innerHTML = tHTML;
MakeNavigation();
}
//loadXMLDoc();
</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;loadXMLDoc();">
</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:
xhttp.open("GET", "https://thyme.dbbee.com/u/SGCEQ5WZFZ/BooksXMLxmlapi.wbsp?dbbee_sr="+startrec+"&dbbee_mr="+maxrec+"&dbbee_sql="+sql, true);
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 loadXMLDoc() 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 loadXMLDoc() function. The code we added builds vales for dbBee_sql variable based on the values of form elements, and therefore we can not call loadXMLDoc() before form elements are loaded.
Let’s see the JavaScript code we added to the loadXMLDoc() 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.
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 hardcoded and generated by JavaScript function procXMLDoc):
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 procXMLDoc by adding a wrapper div <div class='content-wrap'> and moving the image before other fields. Changed code looks like this (changes are again marked in red color):
function procXMLDoc(xml) {
var i;
var xmlDoc = xml.responseXML;
var tHTML="<strong>The search returned "+xmlDoc.getElementsByTagName("BooksCount")[0].childNodes[0].nodeValue + " records</strong>";;
rc=parseInt(xmlDoc.getElementsByTagName("BooksCount")[0].childNodes[0].nodeValue);
if(rc==0){
document.getElementById("demo").innerHTML = "<strong>Search returned no records!</strong>";
return;
}
var x = xmlDoc.getElementsByTagName("Book");
for (i = 0; i <x.length; i++) {
tHTML +="<div class='content-wrap'>"+x[i].getElementsByTagName("Image")[0].childNodes[0].nodeValue;
tHTML += "<h1>" +
x[i].getElementsByTagName("Title")[0].childNodes[0].nodeValue +
"</h1><h2>" +
x[i].getElementsByTagName("Author")[0].childNodes[0].nodeValue +
"</h2><h2>" +
x[i].getElementsByTagName("Publisher")[0].childNodes[0].nodeValue +
"</h2><h3>" +
x[i].getElementsByTagName("YearPublished")[0].childNodes[0].nodeValue +
"</h3></div>";
}
document.getElementById("demo").innerHTML = tHTML;
MakeNavigation();
}
//loadXMLDoc();
</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">
</div>
<input type="button" value="Search" onclick="startrec=1;loadXMLDoc();">
</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 good, old plain JavaScript.
Related blogs
Navigating the data using XDE
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 (spreading result out over pages to make it easier to view).