B4J Question How to reload data from server side ?

[jinyistudio]

Hi
Every time i refresh page,the server will receive a request but just one time(document ready). My application(data logger with mysql) has a key field(datetime type). In front side, when I input a datetime value and i want send it to server and then i could receive data from server. But I don't know how to implement this ?

$('#table1').dataTable( {
             "bFilter":false,
             "bLengthChange":false,
            "aoColumns": [ { "sWidth": "38%" },null,null,null,null,null,null,null,null],
            "bProcessing": true,
            "sAjaxSource": 'tableHelper?method=show'
        } );

 

[Erel]

Are you using websockets?

 

[jinyistudio]

Are you using websockets?

No, Server handler

    $("#datepicker").datepicker({
            onSelect: function(dateText, inst) {
                //raise the event.
                //b4j_raiseEvent(this.id + "_select", {});
                $('#Table1').DataTable( ).fndraw(true);  -->> my idea !? but cann't working
            },
            showButtonPanel: true,
                    dateFormat: "mm/dd/yy"
        });

'Class module
Sub Class_Globals
    Dim sqlOK As Boolean=False
    Dim countries As List
End Sub

Public Sub Initialize
    Dim sq As SQL' = DB.pool.GetConnection 'get a DB connection (change DB to Main)
    sq.Initialize2("com.mysql.jdbc.Driver", "jdbc:mysql://127.0.0.1/data1?characterEncoding=utf8", "root", "25433692")

    Dim rs As ResultSet = sq.ExecQuery("SELECT dt1,va1,va2,va3,va4,va5,va6,va7,va8 FROM log1 ORDER BY dt1 ASC")
    countries.Initialize
    Do While rs.NextRow
        Dim country As List
        country.Initialize
        country.Add(rs.GetString("dt1")) ' datetime
        country.Add(rs.GetString("va1")) ' int
        country.Add(rs.GetString("va2")) ' int
        country.Add(rs.GetString("va3")) ' int
        country.Add(rs.GetString("va4")) ' int
        country.Add(rs.GetString("va5")) ' int
        country.Add(rs.GetString("va6")) ' int
        country.Add(rs.GetString("va7")) ' int
        country.Add(rs.GetString("va8")) ' int
        countries.Add(country)
    Loop
    sq.Close
End Sub

Sub Handle(req As ServletRequest, resp As ServletResponse)
    Select req.GetParameter("method")
        Case "show"
            '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)
        Case "exportCSV"
            ExportCSV(countries, resp)
        Case "exportExcel"
            ExportXLS(countries, resp)
    End Select
End Sub

Private Sub ExportCSV(Exports As List, resp As ServletResponse)
    Dim su As StringUtils
    Dim data As List
    data.Initialize
    'we need to convert each country from a list to an array.
    For Each country As List In countries
        Dim countryArray(country.Size) As String
        For i = 0 To country.Size - 1
            countryArray(i) = country.Get(i)
        Next
        data.Add(countryArray)
    Next
    Dim fileName As String = "csv_" & Main.srvr.CurrentThreadIndex 'need to be sure that each thread gets its own temp file.
    su.SaveCSV2(File.DirTemp, fileName, ",", data, Array As String("Name", "ID", "Population"))
    resp.ContentType = "text/csv"
    resp.SetHeader("Content-Disposition", "attachment;filename=table.csv")
    Dim In As InputStream = File.OpenInput(File.DirTemp, fileName)
    File.Copy2(In, resp.OutputStream)
End Sub

Private Sub ExportXLS(Exports As List, resp As ServletResponse)
    Dim fileName As String = "xls_" & Main.srvr.CurrentThreadIndex
    Dim wb As WritableWorkbook
    wb.Initialize(File.DirTemp, fileName)
    wb.AddSheet("Countries", 0)
    Dim ws As WritableSheet = wb.GetSheet(0)
    ws.SetColumnWidth(0, 23)
    ws.SetColumnWidth(2, 15)
    Dim wf As WritableCellFormat
    wf.Initialize2(wf.FONT_ARIAL, 12, True, False, False, wf.COLOR_BLACK)
    wf.BackgroundColor = wf.COLOR_GREY_25_PERCENT
    wf.SetBorder(wf.BORDER_BOTTOM, wf.BORDER_STYLE_HAIR, wf.COLOR_BLACK)
    Dim wc As WritableCell
    Dim headers() As String = Array As String("Name", "ID", "Population")
    For i = 0 To headers.Length - 1
        wc.InitializeText(i, 1, headers(i))
        wc.SetCellFormat(wf)
        ws.AddCell(wc)
    Next
    For c = 0 To countries.Size - 1
        Dim country As List = countries.Get(c)
        For i = 0 To country.Size - 1
            Dim wc As WritableCell
            If i = 2 Then
                wc.InitializeNumber(i, c + 2, country.Get(i))
            Else
                wc.InitializeText(i, c + 2, country.Get(i))
            End If
            ws.AddCell(wc)
        Next
    Next
    wb.Write
    wb.Close
    resp.ContentType = "application/vnd.ms-excel"
    resp.SetHeader("Content-Disposition", "attachment;filename=table.xls")
    Dim In As InputStream = File.OpenInput(File.DirTemp, fileName)
    File.Copy2(In, resp.OutputStream)
End Sub

 

[Erel]

Why aren't you using websockets for this? It will make it simpler to raise events on the server.

 

[jinyistudio]

Why aren't you using websockets for this? It will make it simpler to raise events on the server.

I can use WebSocket to receive Datepicker's value(b4j_raiseEvent(this.id + "_select", {})) but i don't know how to respone sql result data to the front of datatable because no request from fornt side !?

 

[Erel]

Have you seen the DBUtils example: https://b4x.com:51041/dbutils/index.html

 

[jinyistudio]

Hi
add "bDestroy": true to following block. i could reload new data from server when i select a new date.

$("#datepicker").datepicker({
            onSelect: function(dateText, inst) {
                //raise the event.
                //b4j_raiseEvent(this.id + "_select", {});
                $('#table1').dataTable( {
                     "bProcessing": true,
                     "bDestroy": true,
                    "sAjaxSource": 'tableHelper?method=show'
                } );
            },
            showButtonPanel: true,
                    dateFormat: "mm/dd/yy"
        });