B4J Question [BANano] How to use databases?

Bladimir Carrillo · Jan 9, 2019

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 · Jan 9, 2019

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 · Jan 9, 2019

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

Kiffi · Jan 9, 2019

@Alain:

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

Bladimir Carrillo · Jan 10, 2019

alwaysbusy said:
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 · Jan 10, 2019

Bladimir Carrillo said:
Please can you write a small example about this?

See jRDC2 Tutorial

Bladimir Carrillo · Jan 10, 2019

DonManfred said:
See jRDC2 Tutorial

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

alwaysbusy · Jan 10, 2019

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 · Jan 10, 2019

alwaysbusy said:
I see if I can write something that demonstrates this.

Thank you @alwaysbusy , I will be waiting

inakigarm · Jan 10, 2019

alwaysbusy said:
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 · Jan 10, 2019

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 · Jan 11, 2019

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

B4X:

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

B4X:

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

B4X:

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 · Jan 11, 2019

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 · Jan 11, 2019

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

B4J Question [BANano] How to use databases?

Bladimir Carrillo

Member

alwaysbusy

Expert

alwaysbusy

Expert

Kiffi

Well-Known Member

Bladimir Carrillo

Member

DonManfred

Expert

Bladimir Carrillo

Member

alwaysbusy

Expert

Bladimir Carrillo

Member

inakigarm

Well-Known Member

alwaysbusy

Expert

Harris

Expert

Bladimir Carrillo

Member

alwaysbusy

Expert

Similar Threads

B4J Question [BANano] How to use databases?

Member

Expert

Expert

Well-Known Member

Member

Expert

Member

Expert

Member

Well-Known Member

Expert

Expert

Member

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency