Online example: http://basic4ppc.com:51042/reports/index.html
In this example we are using a JQuery open source plugin named DataTables: https://datatables.net/release-datatables/examples/data_sources/ajax.html
DataTables makes it very simple to add powerful tables with features such as: pagination, search, sorting and others.
The data is loaded from the server. The server response is a JSON object.
This is the server code:
B4X:
Dim sq As SQL = DB.pool.GetConnection 'get a DB connection (change DB to Main)
Dim rs As ResultSet = sq.ExecQuery("SELECT Name, ID, Population FROM countries ORDER BY name ASC")
Dim countries As List
countries.Initialize
Do While rs.NextRow
Dim country As List
country.Initialize
country.Add(rs.GetString("Name"))
country.Add(rs.GetString("ID"))
country.Add(rs.GetString("Population"))
countries.Add(country)
Loop
sq.Close
'create a JSON response
Dim m As Map
m.Initialize
m.Put("aaData", countries)
Dim jg As JSONGenerator
jg.Initialize(m)
resp.ContentType = "application/json"
resp.Write(jg.ToString)The html code:
B4X:
<div id="tableDiv">
<table id="table1">
<thead>
<tr>
<th>Name</th>
<th>ID</th>
<th>Population</th>
</tr>
</thead>
</table>
</div>
<script>
$(document).ready(function() {
$('#table1').dataTable( {
"bProcessing": true,
"sAjaxSource": 'tableHelper?method=show' //this is the server handler
} );
} );
</script>The user can also export the data as a CSV file or XLS (Microsoft Excel) file. The CSV file is created with StringUtils.SaveCSV.
The XLS file is created with jExcel library.
Note that in both cases we need to create a temporary file. You should remember that the code can be executed by several threads at the same time (if there are several concurrent requests). So each thread must work with its own temporary file.
We use the current thread index for that:
B4X:
Dim fileName As String = "xls_" & Main.srvr.CurrentThreadIndexThe handler code and the static files are included in the zip file.