B4J Tutorial Hint about ("remote") DB queries

After (unfortunately) having write many functions (in a code module in my b4j server project) which access a MySql, I remembered a fundamental rule: use Try-Catch when accessing external resources.

So I thought to change all the functions that access the DB (and unfortunately all the calls to these functions, throughout the project) by imitating what we do in the Jobdone (http query): verify first of all the "Success" of the query; but how?

To do so, I create a Type in my DB queries module:

' modDB - code module

Sub Process_Globals
    Type tDBResult(Success As Boolean, ResultData As Object)
    Private mDBResult As tDBResult
    ' ...
End Sub


Sub Init ' to be called once, as if this were a class module.
    mDBResult.Initialize
End Sub

Now, all my DB functions will return mDBResult.
No more:

Sub GetUserID(UserName As String, PW As String) As Int
    Dim UserId As Int = - 1 ' = not found.
    ' query execution
    Return UserID
End Sub

but now:

Sub GetUserID(UserName As String, PW As String) As tDBResult
    mDBResult.Success = True

    Dim UserId As Int = - 1 ' not found.
    Try
        ' query execution
        UserId = ...
        mDBResult.ResultaData = UserID
    Catch
        mDBResult.Success = False
    End Try

    Return mDBResult
End Sub

Obviously now calling a function like this requires writing more lines.
Before:

    Dim UserID = modDB.GetUserID("Erel", "NamesOfMyFourQueens") ' this is his real password  ^__^

Now:

    ' Module variable.
    Private mDBResult As tDBResult

    ' In Activity_Create of where you need it.
    mDBResult.Initialize

    ' In your routine.
    Dim UserID
    mDBResult = modDB.GetUserID("Erel", "NamesOfMyFourQueens")
    If mDBResult.Success Then
        UserID = mDBResult.ResultData
    Else
        ' handle the error.
    End If

but whis way you can handle db access errors and avoid app (server!) crashes!




I think I will try to implement other: run the query(s) a second time, before returning Success = False (this requires to use recursive functions; I do not know if I want to work so much ).



[P.S.] It would also be useful if tDBResult also contained the error message; still better a constant (enum ) that indicates the error type.

 

[OliverA]

this requires to use recursive functions;

Why?

Sub GetUserID(UserName As String, PW As String) As tDBResult
   Dim xDBResult As tDBResult
   For x = 1 to 2
   'Could also do something like this
   'For x = 1 to retryCounts
   'where retryCounts variable could be set to whatever suits one
      xDBResult = GetUserID_(UserName, PW)
      If xDBResult.Success = True Then Exit
   Next
   Return xDBResult
End Sub

Sub GetUserID_(UserName As String, PW As String) As tDBResult
    mDBResult.Success = True

    Dim UserId As Int = - 1 ' not found.
    Try
        ' query execution
        UserId = ...
        mDBResult.ResultaData = UserID
    Catch
        mDBResult.Success = False
    End Try

    Return mDBResult
End Sub

 

[LucaMs]

Why?

Because at that moment I was not thinking of implementing this thing so I was just imagining a recursive routine; indeed, the solution you have published is simple and functional

 

[LucaMs]

In the same way, I am not really thinking to this other "feature", just a vague idea in my mind; I'm thinking if it would be useful to use Resumable routines and if these could simplify the (mainly calling) code.

 

[OliverA]

After (unfortunately) having write many functions (in a code module in my b4j server project) which access a MySql

How are you achieving this? jRDC2? Plain SQL via SQL object? DBUtils? Combo?

 

[LucaMs]

Plain SQL via SQL object

 

[OliverA]

b4j server project

Are you using Jetty? Is GetUserID called in a handler (accessing the modDB code module)?

 

[LucaMs]

Are you using Jetty? Is GetUserID called in a handler (accessing the modDB code module)?

Yes.

 

[OliverA]

The reason for the questions is that I have a hint of an idea brewing in my head.

Unrelated, I don't think you should create a Private mDBResults in Process_Globals of your code module. You should Dim mDBResults/mDBResults.Initialize in each function that uses it. Even though the code module becomes part of the main thread, each handler can call your code module (since each handler is its own thread) and then one function call may set your Global mDBResults to True, while another just set it to False before returning the value. Plus, when you return a Global mDBResults to a handler, your just returning a "pointer" and then both mDBResults.Success and mDBResults.ResultData could be changed by another call to a module function by another handler. My code then is wrong too for GetUserID_ (the GetUserID of my code seems to do it correctly).

 

[LucaMs]

I think that you're right.

I wanted to avoid a little typing but mainly to have to declare the local variable.

Instead I think that what you say is correct, just a reference to that private module variable is returned to the calling handlers, therefore with a high probability of concurrency problems.

I must admit that I had suspected it but then, out of laziness, I thought: "I will come back on this".


Thank you, Oliver.