Using Bootstrap and Datatables on IBM i with ajax auto-refresh
Do you have a requirement to display a table with data that needs to be refreshed regularly and automaticlly on the IBM i? In the following blog we will highlight example code showing how to populate a Bootstrap Datatables table automatically using an ajax call to an IBM i SQLRPGLE program. This is part of a multipart article outlining a complete example web app on the IBM i.
In this third part of the blog we will demonstrate, with code, an example of how to populate a table with a call to an ajax program that updates only the rows of the table. This makes the table refresh seamless without the jerky flicker that happens with a full html page output.
The code examples are supplied purely as a guide on how to implement a call to an ajax program in order to update a Bootstrap Datatables table on the IBM i platform. The example application that we will outline later is not meant to be a finished product but is an aide which may help you develop the framework and direction necessary to drive a web based project forward. The entire demonstration app will be downloadable and will setup all the required configuration descriptions and settings using the supplied commands. We are using Zend Community PHP where the Command Line Interface (CLI) program runs from /QOpenSys/pkgs/bin/php. The code was written using IBM i (i5/OS) v7.2
When refreshing data on a webpage it would not be unusual for the application to output the entire html of the page in order to achieve the update. When automatically refreshing data on a page using this method would cause the screen to flicker and take on a clunky appearance.
A better way of updating targeted areas of the page is to invoke a call to an ajax program which replaces only the required data in an imperceptible manor.
The framework for this project is based on Bootstrap 4 which can be found at Bootstrap and the table is built using Datatables
Below is an example of an SQLRPGLE program that is called using ajax from the javascript routine.
//---------------------------------------------------------------------------------------------// // All source code included in this document are provided on an "AS IS" basis without warranty // // of any kind. NOVAGEM WILL NOT BE LIABLE FOR ANY ACTUAL, DIRECT, SPECIAL, INCIDENTAL, OR // // INDIRECT DAMAGES OR FOR ANY ECONOMIC CONSEQUENTIAL DAMAGES INCLUDING LOST PROFITS OR // // SAVINGS. // //---------------------------------------------------------------------------------------------//
**free
// Example IBM i Web App
// Display entry and exit data
// Novagem Ltd 2020
ctl-opt dftactgrp(*NO) bnddir('DWABINDIR') option(*NOSHOWCPY:*NODEBUGIO);
dcl-ds DWAUSR# extname('DWAUSR') prefix('#') end-ds;
dcl-ds DWATRN# extname('DWATRN') prefix('#') end-ds;
dcl-ds SETTINGSDS extname('SETTINGS') end-ds;
/copy CGIDEV2/qrpglesrc,prototypeb
/copy CGIDEV2/qrpglesrc,usec
dcl-c keyVldLstNam 'DWAKEYVL';
dcl-c NL x'15';
dcl-s #r int(10);
dcl-s #t int(10);
dcl-s #DWATRNUSR int(10);
dcl-s ajaxLrg char(1999999);
dcl-s encryptionKey char(32);
dcl-s epoch timestamp inz(z'1970-01-01-00.00.00');
dcl-s INTIME char(5);
dcl-s IODATE char(30);
dcl-s OUTTIME char(5);
dcl-s vldLstNam char(10);
dcl-s vldLstLib char(10);
dcl-s entId char(100);
dcl-s timeptr pointer;
dcl-s timeptrchr char(26) based(timeptr);
dcl-pr ctime pointer extproc('ctime');
time int(10);
end-pr;
dcl-pr GetVldLst char(100);
vldLstNam char(10) const;
vldLstLib char(10) const;
entId char(100) const;
end-pr;
dcl-pr WriteWebData extproc('QtmhWrStout');
writeData like(ajaxLrg) const;
length int(10) const;
error like(qusec);
end-pr;
dcl-pr CHKUSER extpgm('CHKUSER');
action char(30) const;
USBIZNAM char(60);
USZONE char(40);
end-pr;
dcl-ds DWATRNUSRDS occurs(5000);
TRINTIME like(#TRINTIME);
TROUTTIME like(#TROUTTIME);
USBIZNAM like(#USBIZNAM);
USZONE like(#USZONE);
USFSTNAM like(#USFSTNAM);
USLSTNAM like(#USLSTNAM);
USADR1 like(#USADR1);
USPSTCD like(#USPSTCD);
USTEL like(#USTEL);
USEMAIL like(#USEMAIL);
end-ds DWATRNUSRDS;
%occur(DWATRNUSRDS)=1;
exec sql SELECT * INTO :SETTINGSDS FROM SETTINGS WITH NC; // Get app settings
CHKUSER('RETURN_WITH_LOGIN_PAGE':USBIZNAM:USZONE); // Check user is logged in with valid cookie
wrtsection('*fini'); // Output HTML buffer containg headers ready for AJAX repsonse to be sent
// Get and set data encryption key
encryptionKey=GetVldLst(keyVldLstNam:APPLIB:'DATA');
exec sql SET ENCRYPTION PASSWORD = :encryptionKey;
// Get entry and exit transactions
exec sql DECLARE C1 CURSOR FOR
SELECT T1.TRINTIME, T1.TROUTTIME, T2.USBIZNAM, T2.USZONE, DECRYPT_BINARY(T2.USFSTNAM), DECRYPT_BINARY(T2.USLSTNAM),
DECRYPT_BINARY(T2.USADR1), DECRYPT_BINARY(T2.USPSTCD), DECRYPT_BINARY(T2.USTEL), T2.USEMAIL
FROM DWATRN T1 LEFT JOIN DWAUSR T2 ON T1.TRBIZNAM = T2.USBIZNAM AND T1.TRZONE = T2.USZONE AND T1.TREMAIL = T2.USEMAIL
WHERE T1.TRBIZNAM = :USBIZNAM AND T1.TRZONE = :USZONE
ORDER BY T1.TRINTIME DESC, T1.TROUTTIME DESC WITH NC;
exec sql OPEN C1;
exec sql FETCH NEXT FROM C1 FOR 5000 ROWS INTO :DWATRNUSRDS;
#DWATRNUSR=SQLERRD(3)-1;
exec sql CLOSE C1;
// Write data back to AJAX caller
if #DWATRNUSR>=0;
exsr writeJSON;
else;
ajaxLrg='{"data":[]}';
WriteWebData(%trim(ajaxLrg):%len(%trim(ajaxLrg)):qusec);
endif;
// Exit program
*INLR=*ON;
return;
// *************
begsr writeJSON;
// *************
// Output AJAX header
ajaxLrg='{"data":[';
// Build AJAX rows
for #r=1 to #DWATRNUSR;
%occur(DWATRNUSRDS)=#r;
exsr formatTime;
ajaxLrg=%trim(ajaxLrg)+'['+
'"'+%trim(USLSTNAM)+', '+%trim(USFSTNAM)+'",'+
'"'+%trim(USADR1)+'",'+
'"'+%trim(USPSTCD)+'",'+
'"'+%trim(USTEL)+'",'+
'"'+%trim(USEMAIL)+'",'+
'"<span>'+%char(TRINTIME:*ISO0)+'</span>'+%trim(IODATE)+'",'+
'"'+%trim(INTIME)+'",'+
'"'+%trim(OUTTIME)+'"],';
endfor;
// Apend last AJAX row
%occur(DWATRNUSRDS)=#r;
exsr formatTime;
ajaxLrg=%trim(ajaxLrg)+'['+
'"'+%trim(USLSTNAM)+', '+%trim(USFSTNAM)+'",'+
'"'+%trim(USADR1)+'",'+
'"'+%trim(USPSTCD)+'",'+
'"'+%trim(USTEL)+'",'+
'"'+%trim(USEMAIL)+'",'+
'"<span>'+%char(TRINTIME:*ISO0)+'</span>'+%trim(IODATE)+'",'+
'"'+%trim(INTIME)+'",'+
'"'+%trim(OUTTIME)+'"]]}';
// Return AJAX back to client handler
WriteWebData(%trim(ajaxLrg):%len(%trim(ajaxLrg)):qusec);
// ***
endsr;
// ***
// **************
begsr formatTime;
// **************
#t=%diff(TRINTIME:epoch:*S);
timeptr=ctime(#t);
IODATE=%subst(timeptrchr:1:10)+' '+%subst(timeptrchr:21:4);
INTIME=%subst(%char(%time(TRINTIME)):1:5);
if TROUTTIME>z'0001-01-01-00.00.00.000000';
OUTTIME=%subst(%char(%time(TROUTTIME)):1:5);
else;
OUTTIME='--------';
endif;
// ***
endsr;
// ***
// *********
begsr *PSSR;
// *********
*INLR=*ON;
return;
// ***
endsr;
// ***
Below is the Bootstrap html containing the jquery which calls the Datatables functions to setup and invoke the ajax.
<!DOCTYPE html>
<html>
<head>
<title>Access Enquiry</title>
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.6.3/css/font-awesome.min.css">
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.21/css/dataTables.bootstrap4.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js" crossorigin="anonymous"></script>
<script src="https://cdn.datatables.net/1.10.21/js/dataTables.bootstrap4.min.js" crossorigin="anonymous"></script>
<script src="https://cdn.datatables.net/buttons/1.6.2/js/dataTables.buttons.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js" integrity="sha384-OgVRvuATP1z7JjHLkuOU7Xw704+h835Lr+6QL9UvYjZE3Ipu6Tp75j7Bh/kR0JKI" crossorigin="anonymous"></script>
<script type="text/javascript">
/* Table initialisation */
var interval = 9999999;
$(document).ready(function() {
var table = $('#example').DataTable({
"ajax": '/cgi-bin/inoutenq.pgm', // Load table transaction data via ajax call
"order": [[ 5, "desc" ]], // initial order of table is column 5 descending
// Setup button and interval timer function for toggling auto refresh with dynamic hourglass icon in centre of datatables toolbar row
// l=page length controls, B=Buttons, f=search filter box, tr=table data rows, i=row stats information, p=pagination controls
dom:
"<'row'<'col-sm-3'l><'col-sm-6 text-center'B><'col-sm-3'f>>" +
"<'row'<'col-sm-12'tr>>" +
"<'row'<'col-sm-5'i><'col-sm-7'p>>",
buttons: [
{
text: 'Toggle Refresh <i class="fa fa-hourglass-o"></i>', // appearance of auto refresh toggle buttom
className: 'btn btn-primary btn-sm btn-rounded',
action: function ( e, dt, node, config ) { // Actions to be processed on click of autorefresh button
if (interval == 9999999) {
$(".dt-button.btn.btn-primary.btn-sm.btn-rounded").html('Toggle Refresh <i class="fa fa-hourglass-start"></i>');
interval = 3000; // Autorefresh interval is 3 seconds
timerinterval = setInterval( function () {
$('#example').DataTable().ajax.reload();
}, interval );
}
else { // Stop autorefresh
$(".dt-button.btn.btn-primary.btn-sm.btn-rounded").html('Toggle Refresh <i class="fa fa-hourglass-o"></i>');
interval = 9999999; //
clearInterval(timerinterval);
$('#example').DataTable().ajax.reload();
}
}
}
]
});
});
</script>
<style>
#example tr td span {
display:none;
}
</style>
</head>
<body style="padding-top: 4.5rem;">
<!--%navbarhtml%-->
<main role="main" class="container-fluid">
<div class="card-header bg-danger text-white">
<h4 class="text-uppercase text-center">Access Enquiry</h4>
</div>
<br />
<div class="container-fluid">
<table id="example" class="table table-bordered table-striped table-sm">
<thead>
<tr>
<th width="17%">Name</th>
<th width="20%">Address 1</th>
<th width="10%">P/Code</th>
<th width="9%">Telephpone</th>
<th width="23%">Email</th>
<th width="12%">Date</th>
<th width="5%">Entry</th>
<th width="5%">Exit</th>
</tr>
</thead>
</table>
<footer>
<p class="text-right" style="margin-top: 5px">By <a href="#" onclick="return false;">Novagem Ltd</a> © 2020</p>
<br />
</footer>
</div>
</main>
</body>
</html>