Android Question Right way to organize your database functions across separate code modules for sharing across B4xPages.

[beelze69]

Hi,

I have developed a full-fledged application while learning b4A with all my Public Modules in Starter.bas and multiple B4XPages accessing them.

Details are as under:-

1) My application has multiple B4XPages.

2) Each B4Page has variables declared as Private in its Class_Globals for use in various Subroutines written inside that B4XPage, which are Private to that particular B4XPage.

3) However, my Service Module Starter.bas contains all the code related to my Public database functions which I have declared as Public and which is for sharing across ALL B4XPages of my application.

I reference my Public functions in Starter.bas in all my b4xPages as Starter.dbFunction1(), Starter.dbFunction2() etc. and it works perfectly.

4) Now, in order to segregate all my Application specific Database Public functions, I tried to move them by creating a separate class and putting them there.

But I am unable to reference the Public Functions from my B4XPages

by <className>.dbFunction1() .

My question is:

1) Which is the right place to open and close a database connection.

Presently I am doing that in Starter.bas.

2) How should one organize the various functions into classes and reference them with the <className>.<functionName>.

Sorry if I sound too basic.

Thanks

 

[LucaMs]

4) Now, in order to segregate all my Application specific Database Public functions, I tried to move them by creating a separate class and putting them there.

But I am unable to reference the Public Functions from my B4XPages

by <className>.dbFunction1() .

Writing all public methods (the Subs) in a class is the right choice.
To use them, however, you must create an instance of the class, that is create a variable (public, for example gDB) of type clsDB (names of your choice, obviously) and initialize it. Where to do it? The best place is the B4XMainPage.

At that point in any other B4XPage you can write:
B4XPages.MainPage.gDB.SomeDBMethod(...)

In the class write also the methods to initialize and close the DB.

 

[beelze69]

Writing all public methods (the Subs) in a class is the right choice.
To use them, however, you must create an instance of the class, that is create a variable (public, for example gDB) of type clsDB (names of your choice, obviously) and initialize it. Where to do it? The best place is the B4XMainPage.

At that point in any other B4XPage you can write:
B4XPages.MainPage.gDB.SomeDBMethod(...)

In the class write also the methods to initialize and close the DB.

Hi LucaMs,

Thanks...I will try it and revert back if any issues...

 

[beelze69]

Hi LucaMs,

Thanks...I will try it and revert back if any issues...

Hi,

I have done the following changes and the application is working fine.
I have a few more queries. Requesting for guidance.

1) My Service Module Starter.bas has the following code to
connect to the database.

Code in Service Module Starter.bas
----------------------------------

Sub Process_Globals() Public SQL As SQL Public SafeDirectory As String Public RTP As RuntimePermissions Public DBName As String="myDatabaseName.db" End Sub

Sub Service_Create
SafeDirectory = RTP.GetSafeDirDefaultExternal("")
If File.Exists(SafeDirectory, DBName) = False Then
Wait For (File.CopyAsync(File.DirAssets, DBName, SafeDirectory, DBName)) Complete (Success As Boolean) 'File.DirInternal
'    ToastMessageShow($"DB Copied = ${Success}"$, False)
Log("Success: " & Success)
End If
 
ConnectToDatabase
    Wait For SQLite_Ready (Success As Boolean)
End Sub

Public Sub ConnectToDatabase
    SQL.Initialize( SafeDirectory, DBName, False)
End Sub

2) I created a new class file called clsGEN and wrote the following code to

Code in clsGen class file:
-------------------------

Sub Class_Globals
'**********Declarations for the clsGen ************
Public selectedParamValue As String
End Sub

Public Sub isMyConditionMet() As ResumableSub
    'Invoked for clsGen..
    'This function returns true if the SQL Condition is satisfied, false otherwise..
    Dim SenderFilter As Object
    Dim retValue As Boolean
    retValue=False
 
    SenderFilter=Starter.SQL.ExecQueryAsync( "SQL",<select count(fieldname) as countOfRecordsFound from tableName where fieldName=selectedParamValue>,Null)
    Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, RS As ResultSet)
    If Success Then
     
        RS.NextRow 'must be atleast 1 record
        If RS.GetInt("countOfRecordsFound")> 0 Then
         
            retValue=True
        Else
            retValue=False
        End If
     
        RS.Close
 
        Return(retValue)
    Else
        Log(LastException)
        Return(retValue)
    End If
End Sub

3) Finally, in my B4XPage, I have:

Code in my B4XMainPage
----------------------

Sub Class_Globals
Private myClass As clsGen
End Sub

Public Sub Initialize
'    B4XPages.GetManager.LogEvents = True
myClass.Initialize
End Sub

Sub myCallingSubroutine()

    'Passing the parameter to the variable declared in the class file
        myClass.selectedParamValue="someValue"
     
     
        Dim  theFunction As ResumableSub=myClass.isMyConditionMet()
        Log("checking for the condition)
        Wait For(theFunction) Complete (myRetValue As Boolean)
        If myRetValue==True Then
            'The match is found
            Wait For (ShowToast("conditon matched",  2000,"Normal")) Complete(Unused As Boolean)

                Else
                    'The match is not found
        Wait For (ShowToast("Condition did not match...",  2000,"ERROR")) Complete(Unused As Boolean)
        End If
End Sub

'Note: ShowToast is my User-Defined-function that just displays a coloured message depending on the parameter string I pass to it viz. Normal or ERROR etc..

4) Things that I would like to clarify:

4.1) Whether I have declared and initialized my class module the right way in Point 3)

4.2) Is there a better way to pass the parameter in Point 3 ) than the present way of

myClass.selectedParamValue="someValue"


4.3) The Service Module Starter.bas contains the code for handling the database.

Also please note that I have closed my recordset in my class file in Point 2 but not the database.

In this regard, please refer this post

https://www.b4x.com/android/forum/threads/sql-close.109065/

Here, to a Comment:'Closing the db intentionally is a good practice'

Erel replies as follows:

"Erel: I consider it a bad suggestion.Android apps are different than desktop apps.
They don't have a clear or well defined closing event.
You can make your app more complex than it should be by closing the database
in Activity_Pause (of each activity) and then open it again.
Nothing good will come out of this."

Further, to a query in the same discussion,

Q: 'Leave SQL "open" and wait for OS to close it?'

"Erel:Yes. Unless your app has an explicit exit point. Most apps don't."

Hence, I am unsure of how I should handle proper closure of the database in B4a.

I request you to throw more light on it.

I personally, am from an environment where we keep db connections open for minimal
amount of time - opening just before invoking a function and immediately
close the connection. Honestly, it gives me the creeps to leave database connections unattended.

5) Lastly, in the above referred post

Erel seems to mean that if your application has an explicit exit point you can close
the connection.

In view of the above, assuming that my B4XMainPage is the exit point of my application,

how should database closure be properly implemented in my B4xMainPage ?

Thanks.

 

[LucaMs]

4.1) Whether I have declared and initialized my class module the right way in Point 3)

Yes. [Just bad names ]

4.2) Is there a better way to pass the parameter in Point 3 ) than the present way of

myClass.selectedParamValue="someValue"

Sure; your Sub isMyContiditionMet uses that parameter, so write it... as parameter of that Sub:

Public Sub isMyContiditionMet(selectedParamValue As String) As ResumableSub

Dim theFunction As ResumableSub=myClass.isMyConditionMet("SomeValue") <---

and remove:

Public selectedParamValue As String

how should database closure be properly implemented in my B4xMainPage ?

You can never be 100% certain that the system won't kill the process (the app); I think Erel was referring to this.

In the class, create a CloseDB Public Sub.

I think you should put a close app button and call CloseDB from its Click event.

You could also close the DB in the B4XPage_Disappear event and reopen it in B4XPage_Appear, if you really want to be "sure" that it is closed; initialization (not copying) should take very little time, acceptable.
Additionally, if your app is Android-only, you can also call CloseDB from the Starter service's Service_TaskRemoved and Service_Destroy events, in addition to what has already been written above, not as an alternative.


[I assume you know: there is no need to copy the database file in asynchronous mode and call with Wait For, unless you want to display an animation, progress bar or animated splash screen at the same time]

 

[beelze69]

Hi,

Thanks a lot for your valuable inputs.

I have made the following changes. And I have a few more queries.
Requesting for guidance.

Additionally, if your app is Android-only, you can also call CloseDB from the Starter service's Service_TaskRemoved and Service_Destroy events, in addition to what has already been written above, not as an alternative.

Yes, mine is only Android..

As per your suggestion, I have invoked Database closure in Service_TaskRemoved and Service_Disappear events in my Service Module Starter.bas as follows:

Public Sub CloseDB
    SQL.Close
End Sub

Sub Service_TaskRemoved
'This event will be raised when the user removes the app from the recent apps list.
    CloseDB
End Sub

Sub Service_Destroy
 
    CloseDB
End Sub

[I assume you know: there is no need to copy the database file in asynchronous mode and call with Wait For, unless you want to display an animation, progress bar or animated splash screen at the same time]

I think you are referring to my code

Sub Service_Create
    'This is the program entry point.
    'This is a good place to load resources that are not specific to a single activity.
    SafeDirectory = RTP.GetSafeDirDefaultExternal("")
 
    If File.Exists(SafeDirectory, DBName) = False Then
        Wait For (File.CopyAsync(File.DirAssets, DBName, SafeDirectory, DBName)) Complete (Success As Boolean) 'File.DirInternal
        '    ToastMessageShow($"DB Copied = ${Success}"$, False)
    
'        Log("Success: " & Success)
    End If
 
    ConnectToDatabase
    Wait For SQLite_Ready (Success As Boolean)
End Sub

Actually, I have a splash screen being displayed when my app starts.. so I think I will leave it like that..

You could also close the DB in the B4XPage_Disappear event and reopen it in B4XPage_Appear, if you really want to be "sure" that it is closed;

I am personally not sure as to how it will function since my application has 'multiple B4XPages' and since 'B4XPages always remain in memory' , I am not too sure as to how will Android function especially if I invoke DBClose for every pages's B4xPage_Disappear[invoked whenever a Page disappears] and open the database in every page's B4xPage_Appear [invoked when a Page appears] event - as an end-user will keep 'sifting through the pages at will' while the app is running.

Requesting for further guidance on this.

 

[beelze69]

initialization (not copying) should take very little time, acceptable.

1) Requesting you to give an example of how do I initialize the database in a B4xPage..


And lastly,

2) How do I release the class instance in b4XPage?

In my example, this code does not work !

Private Sub B4XPage_CloseRequest As ResumableSub
'You can see the list of page related events in the B4XPagesManager object. The event name is B4XPage.
'Called when the Page is closed

Set myClass=nothing
End Sub

Thanks.

 

[LucaMs]

The above means that you have declared the SQL database (much better name: DB) in the Starter. You also wrote CloseDB in that service.

Instead, everything related to the DB, including declaration, initialization, opening, closing and queries, should be written in the class that manages the DB.

In the Starter service you should only write:

Sub Service_TaskRemoved
    B4XPages.MainPage.YourDBManagerPublicVariable.CloseDB
End Sub

Sub Service_Destroy
    B4XPages.MainPage.YourDBManagerPublicVariable.CloseDB
End Sub

YourDBManagerPublicVariable is what you named MyClass; better name: DBManager.

 

[LucaMs]

1) Requesting you to give an example of how do I initialize the database in a B4xPage..

SQL.Initialize( SafeDirectory, DBName, False)

 

[LucaMs]

2) How do I release the class instance in b4XPage?

B4XPages.MainPage.DBManager = Null

 

[LucaMs]

I am personally not sure as to how it will function since my application has 'multiple B4XPages' and since 'B4XPages always remain in memory' , I am not too sure as to how will Android function especially if I invoke DBClose for every pages's B4xPage_Disappear[invoked whenever a Page disappears] and open the database in every page's B4xPage_Appear [invoked when a Page appears] event - as an end-user will keep 'sifting through the pages at will' while the app is running.

Requesting for further guidance on this.

In fact I should have suggested the B4XPage_Background and B4XPage_Foreground events, but also these written in each of your pages. They will not be triggered at every page change but only when the app goes into the background / becomes visible again.

(The pages remain in memory, but this does not mean that they are visible, only one of them will be).

 

[beelze69]

View attachment 151123

The above means that you have declared the SQL database (much better name: DB) in the Starter. You also wrote CloseDB in that service.

Instead, everything related to the DB, including declaration, initialization, opening, closing and queries, should be written in the class that manages the DB.

In the Starter service you should only write:

Sub Service_TaskRemoved
    B4XPages.MainPage.YourDBManagerPublicVariable.CloseDB
End Sub

Sub Service_Destroy
    B4XPages.MainPage.YourDBManagerPublicVariable.CloseDB
End Sub

YourDBManagerPublicVariable is what you named MyClass; better name: DBManager.

Hi,

Thanks.. I will try this..

 

[beelze69]

Hi,

Thanks.. I will try this.

 

[beelze69]

B4XPages.MainPage.DBManager = Null

Hi,

Thanks.. I will try this.

Presently, as per my example I have written

Private Sub B4xPage_CloseRequest(Root1 As B4XView) 
myClass=Null
End Sub

And it has not given me any error so far..