B4J Question [BANano] How to use databases?

[Bladimir Carrillo]

Great new project Alain, CONGRATS and THANK YOU VERY MUCH !!!

ABMaterial saved my life when I needed to make a web app in record time, but now, with BANano I was trying to test the B4J projects included into the downloaded file, but I think definitely need a tutorial .

It consoles me to know that with the help of Alain and his expert fans, I will be using it soon, as happened with ABMaterial . I promise to make my first donation for this project soon!

My first question ...
With B4J and ABMaterial I can connect to my database server by jSQL and jRDC. Is that possible with BANano? If no, what is the better option and how I can do it?

Regards.

 

[alwaysbusy]

Unlike ABMaterial, BANano does not require a server. That said, it can connect with a server (a jServer, php, ...) using ajax calls (BANano.CallAjax and BANano.CallAjaxWait) or by using BANanoSQL but this is not the preferred way, as it exposes everything in the javascript source code. I would only use BANanoSQL in a safe environment, like within an intranet.

So you can in fact run a jServer using jSQL and jRDC and write a rest API in it. You can then let your BANano app talk with it using Ajax.

I've written several small tutorials (do a search for [BANano]), but it is only a couple of months old so if you have a specific question, just like with ABM ask them with the [BANano] prefix in the subject.

 

[alwaysbusy]

@Bladimir Carrillo and thank you for supporting me on Patreon. Much appreciated!

 

[Kiffi]

@Alain:

You forgot BANano.CallInlinePHP() and BANano.CallInlinePHPWait()

 

[Bladimir Carrillo]

So you can in fact run a jServer using jSQL and jRDC and write a rest API in it. You can then let your BANano app talk with it using Ajax.

Please can you write a small example about this?

 

[DonManfred]

Please can you write a small example about this?

See jRDC2 Tutorial

 

[Bladimir Carrillo]

See jRDC2 Tutorial

Thanks @DonManfred I know jRDC2, but I don't know... how BANano app can talk with server using Ajax ?

 

[alwaysbusy]

I never worked with jRDC. You probably know more about it than I

As for the REST API, you need to use a normal jServer Handler class which will talk to your database. I see if I can write something that demonstrates this.

 

[Bladimir Carrillo]

I see if I can write something that demonstrates this.

Thank you @alwaysbusy , I will be waiting

 

[inakigarm]

I never worked with jRDC. You probably know more about it than I

As for the REST API, you need to use a normal jServer Handler class which will talk to your database. I see if I can write something that demonstrates this.

I've worked with jRDC2 a lot and it will be very interesting an example of how configure banano as a jrdc client (not only accessing a server handler via Ajax calls) and I don't know if it's possible because, if I didn't remember badly, it uses B4x serializator.

 

[alwaysbusy]

For the jRDC part I'm not going to be very helpful. The tutorial will just be about the part from BANano -> Ajax -> jServer -> response to BAnano.

 

[Harris]

Here is an example of querying and posting (inserting) data from Android (B4A) with RDC.
It is quite easy actually...


This sub builds up 2 DBCommand (Type) to send a master and detail records to the RDC server - for insertion...

Sub AddLoadRecs
Dim i,j As Int
Dim c, d As Cursor
Dim commands,commandsdet As List
commands.Initialize  
commandsdet.Initialize


If DefCM.Sql1.ExecQuerySingleResult("SELECT count(name) FROM sqlite_master WHERE type='table' AND name ='loadmast'") = 0 Then
 ' do nothing
Else
    Try
    c = DefCM.SQL1.ExecQuery("Select * from loadmast where sent = 0")
   
'    Log(" -----------     ----  Loadmast records: "&c.RowCount)
    For i = 0 To c.RowCount -1
       c.Position = i
       Dim cmd As DBCommand
       cmd.Initialize
       cmd.Name =  "load_mast"
       Dim pkk As Long
       pkk = c.GetLong("pk")
       cmd.Parameters = Array As Object(c.GetLong("pk"),c.GetInt("loadtype"),c.GetInt("acttype"),c.GetLong("recdate"),c.GetInt("drvid"),c.GetString("trkid"),c.GetString("trl1id"),c.GetString("trl2id"),c.GetString("trl3id"),c.GetLong("load_dur"),c.GetLong("wait_dur"),c.GetString("rectype"),c.GetDouble("lat"),c.GetDouble("lon"),c.GetString("place"),c.GetInt("compid"),c.GetInt("sent"),c.GetDouble("odom"),c.GetDouble("fuel"),c.GetString("sourceid"),c.GetString("swver"),c.GetString("comment"))
     
       commands.Add(cmd)
'       Log("added Load mast rec: "&i)

       d = DefCM.SQL1.ExecQuery("Select * from loaddet where mastid = "&pkk)
        For j = 0 To d.RowCount -1
           d.Position = j
           Dim cmd As DBCommand
           cmd.Initialize
           cmd.Name =  "load_det"
           cmd.Parameters = Array As Object(d.GetLong("mastid"),d.GetInt("ival1") , d.GetInt("ival2") , d.GetInt("ival3") , d.GetString("sval1") , d.GetString("sval2") , d.GetString("sval3"),d.GetDouble("dval1") ,d.GetDouble("dval2"),d.GetInt("compid") )
           commandsdet.Add(cmd)
'           Log("load detail: "&d.GetLong("mastid"))
        Next
     
       d.Close
    Next

    c.Close

    If commandsdet.Size > 0 Then
       LogCM.reqManager.myloaddet = commandsdet
       LogCM.reqManager.mytaglddet = "load_det"
       LogCM.reqManager.IsConnectedLoaddet  ' (checks to see that there is an internet connection  - then calls ExecuteBatch)
    End If

    If commands.Size > 0 Then
       LogCM.reqManager.myloadmast = commands
       LogCM.reqManager.mytagldmast = "load_mast"
       LogCM.reqManager.IsConnectedLoadmast  ' (checks to see that there is an internet connection  - then calls ExecuteBatch)
    Else
     '   ToastMessageShow(" No Load Records to Send...",False)
    '   AddLogRecs ' send logs if no load records to send....
    End If

    Catch
       Log("Table LoadMast doesn't exist or other error")
       If c.IsInitialized Then
         c.Close
       End If    
'       AddLogRecs ' send logs if no load records to send....

    End Try
End If


End Sub

This sub sets a query to return all "vehicle" data.
With RDC - the cmd.Name is the sql statement to run from the config file on the server... (secure - no possible injection)

in the JobDone - look for "get_vehicles_upd" result - and have at er with the data (update the local SQLite tables... for example).

Sub GetVehiclesupd  ' update the vehicles table....

     Dim cmd As DBCommand
    cmd.Initialize
    cmd.Name = "get_vehicles_upd"
    Dim mdt As Long
    mdt = LogCM.GetLastUPDCheck(cmd.Name)
    cmd.Parameters = Array As Object(mdt, DefCM.Company_id) ' no actual query - just params to god knows what (I and only I do)
'    Log(" veh request: "&mdt&"  "&DateTime.date(DateUtils.UnixTimeToTicks(mdt))&"  "&DateTime.time(DateUtils.UnixTimeToTicks(mdt))&"  "&DefCM.Company_id)
    LogCM.reqManager.ExecuteQuery( cmd, 0, "get_vehicles_upd")
 

End Sub

From the DBRequestManager Class

Public Sub ExecuteQuery(Command As DBCommand, Limit As Int, Tag As Object)
    Dim j As HttpJob
    Dim ms As OutputStream
    Dim out2 As OutputStream = StartJob(j,ms, Tag)
   
    WriteObject(Command.Name, out2)
    WriteInt(Limit, out2)
    WriteList(Command.Parameters, out2)
    out2.Close
    j.PostBytes(link & "?method=query", ms.ToBytesArray)
'    Log(" Length of mem stream: "&ms.ToBytesArray.Length&" Tag: "&Tag)
    'ToastMessageShow("Processing Tag: "&Tag, False)

'    LogCM.SetDataUse(Tag,ms.ToBytesArray.Length,0)
   
End Sub

'Executes a batch of (non-select) commands.
'ListOfCommands - List of the commands that will be executes.
'Tag - An object that will be returned in the result.
Public Sub ExecuteBatch(ListOfCommands As List, Tag As Object)
    Dim j As HttpJob
    Dim ms As OutputStream
    Dim out2 As OutputStream = StartJob(j,ms, Tag)
    WriteInt( ListOfCommands.Size, out2)
    For Each Command As DBCommand In ListOfCommands
        WriteObject( Command.Name, out2)
        WriteList(Command.Parameters, out2)
    Next
   
    out2.Close
    j.PostBytes(link & "?method=batch", ms.ToBytesArray)
    Log(" Length of mem stream: "&ms.ToBytesArray.Length&" Tag: "&Tag)
'    LogCM.SetDataUse(Tag,ms.ToBytesArray.Length,0)

End Sub

Private Sub StartJob(j As HttpJob, MemoryStream As OutputStream, Tag As Object) As OutputStream
    j.Initialize(  "DBRequest", mTarget)
    j.Tag = Tag
    MemoryStream.InitializeToBytesArray(0)
    Dim compress As CompressedStreams
    Dim out As OutputStream = compress.WrapOutputStream(MemoryStream, "gzip")
    WriteObject(VERSION, out)
    Return out
End Sub

Hummmm, who ever thought I "might" be able to teach ab something... Surely - NOT ME!

 

[Bladimir Carrillo]

Thank you @inakigarm and @Harris , but again... I know jRDC2, but I don't know... how BANano app can talk with server using Ajax ?

 

[alwaysbusy]

Actually, the whole loop is already described here: https://www.b4x.com/android/forum/threads/banano-authentication-to-a-b4j-server-rest-api.100269/