B4J Tutorial Hint about ("remote") DB queries

Discussion in 'B4J Tutorials' started by LucaMs, Aug 12, 2018.

  1. LucaMs

    LucaMs Expert Licensed User

    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:
    Code:
    ' 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:
    Code:
    Sub GetUserID(UserName As String, PW As StringAs Int
        
    Dim UserId As Int = - 1 ' = not found.
        ' query execution
        Return UserID
    End Sub
    but now:
    Code:
    Sub GetUserID(UserName As String, PW As StringAs 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:
    Code:
    Dim UserID = modDB.GetUserID("Erel""NamesOfMyFourQueens"' this is his real password  ^__^
    Now:
    Code:
    ' 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).



    [P.S.] It would also be useful if tDBResult also contained the error message; still better a constant (enum :rolleyes:) that indicates the error type.
     
    Last edited: Aug 12, 2018
    jimmyF likes this.
  2. OliverA

    OliverA Well-Known Member Licensed User

    Why?
    Code:
    Sub GetUserID(UserName As String, PW As StringAs 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 StringAs 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 likes this.
  3. LucaMs

    LucaMs Expert Licensed User

    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 :)
     
  4. LucaMs

    LucaMs Expert Licensed User

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

    OliverA Well-Known Member Licensed User

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

    LucaMs Expert Licensed User

     
  7. OliverA

    OliverA Well-Known Member Licensed User

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

    LucaMs Expert Licensed User

    Yes.
     
  9. OliverA

    OliverA Well-Known Member Licensed User

    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 likes this.
  10. LucaMs

    LucaMs Expert Licensed User

    I think that you're right.

    I wanted to avoid a little typing :D 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". :D


    Thank you, Oliver.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice