Navigating the data using XDE

Tweet

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_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).

Again, we will use plain JavaScript to do it. We will use the same code used in previous blog with few minor modifications to existing code and adding some new lines:

<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);
    }
  };
  xhttp.open("GET", "https://thyme.dbbee.com/u/SGCEQ5WZFZ/BooksXMLxmlapi.wbsp?dbbee_sr="+startrec+"&dbbee_mr="+maxrec, 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);
  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>
<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, true);

As you can see, we added dbBee variables dbbee_sr and dbbee_mr to the URL, so dbBee will send us only partial response, starting with record number defined in dbbee_sr and containing maximum of records defined in dbbee_mr.

Now about the new stuff - you’ll notice that we added three global variables: startrec, maxrec and rc and we added one new function MakeNavigation(). We set startrec value to 1, so initial display starts with first record. We set maxrec to 10, so our single page will contain maximum of 10 records. Variable rc is actual record counter and it has to be global because its value is modified by function procXMLDoc() and used by function MakeNavigation().

Function MakeNavigation() builds drop down select element to be used for navigating through pages.

Line var curpg=parseInt(startrec / maxrec)+(parseInt(startrec / maxrec)==startrec / maxrec?0:1); calculates the number of current page (so we can set dropdown selection box to proper value and change the option text).

Next block uses for loop to create selection box and the code below that sets its value to reflect the current page.

Please note that selection box has hooked onchange event onchange="startrec=this.options[this.selectedIndex].value;loadXMLDoc();" that changes startrec variable to selected value of selection box and executes loadXML() function to resend the request with new dbBee_sr value.
For live demo please click here.

Now, when we have solved the pagination we only need some sort of search form to have fully featured database search application without a single line of backend programming (that part is taken care of by dbBee).
Creating the search form is covered in our next article.