B4J Question [SOLVED] - Best Programming Practices for Remote Database Access

[jroriz]

I researched several tutorials and posts here on the forum, but I still have questions about best practices for accessing a remote database.

I have in mind an UI application that will be open for hours, sending intermittent reads and writes.

My doubts:

1. Use GetConnection and ClosePool only once?
2. GetConnection once in AppStart and ClosePool once in CloseRequest?
3. Can the WriteData and ReadData procedures be improved?

Here is the code for discussion:

#Region Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 600
   
    #AdditionalJar: mysql-connector-java-5.1.48.jar
   
#End Region

Sub Process_Globals
    Private fx As JFX
    Private MainForm As Form
    Dim pool As ConnectionPool

#Region Database Location
    Private DBLocation As String = "999.999.999.99:3306"
    Private DBname As String = "dbname"
    Private DBUsername As String = "dbusername"
    Private DBPassword As String = "dbpassword"
#End Region

    Private sq As SQL

    Private t As Timer, op As Int

End Sub

Sub AppStart (Form1 As Form, Args() As String)
   
    MainForm.Initialize("Form", 300,300)
    'MainForm = Form1
    'MainForm.RootPane.LoadLayout("Layout1") 'Load the layout file.
    MainForm.Show
   
    pool.Initialize("com.mysql.jdbc.Driver", $"jdbc:mysql://${DBLocation}/${DBname}?characterEncoding=utf8"$, DBUsername, DBPassword)

    t.Initialize("t", Rnd(1000, 6 * 1000))
    t.Enabled = True
End Sub

Sub t_tick
    LogError("tick " & DateTime.Time(DateTime.Now))
    t.Enabled = False

    If op = 0 Then
        op = 1
        ReadData
       
    Else
        WriteData
        op = 0
    End If
   
    't.Interval = Rnd(1000, 60 * 1000)
    t.Interval = Rnd(1000, 6 * 1000)
   
    t.Enabled = True
End Sub

Sub WriteData

    sq = pool.GetConnection

    sq.BeginTransaction
   
    sq.ExecNonQuery("UPDATE config_cfg SET cfg_value = 'SP' WHERE cfg_id = 1006")
   
    sq.TransactionSuccessful
   
    sq.Close
   
    Log("WRITE OK " & DateTime.Time(DateTime.Now))

End Sub

Sub ReadData

    sq = pool.GetConnection
   
    Dim Cursor As ResultSet
    Cursor = sq.ExecQuery("SELECT cfg_id, cfg_name FROM config_cfg LIMIT 2")
    Do While Cursor.NextRow
        Log(Cursor.GetString("cfg_id"))
        Log(Cursor.Getstring("cfg_name"))
    Loop
   
    Cursor.Close
    sq.Close
   
    Log("READ OK " & DateTime.Time(DateTime.Now))
   
End Sub

Private Sub Form_Closed()
    Log("Leaving...")
    pool.ClosePool
End Sub

'Return true to allow the default exceptions handler to handle the uncaught exception.
Sub Application_Error (Error As Exception, StackTrace As String) As Boolean
    Return True
End Sub

 

[Harris]

A quick example... Just a few snippets...
ABMaterial has an excellent DBM (Database Management Module) that works well.
I never close the pool (right or wrongly)...

Main app start...

Sub AppStart (Args() As String)
   
    DBM.InitializeMySQL()
    '.....

End Sub

' The DBM module

'Static code module
private Sub Process_Globals
    Private pool As ConnectionPool
End Sub


#Region Init

Sub InitializeMySQL() 'ignore
    Log("init mysql")
    Try
        pool.Initialize( Consts.jDBDRV, Consts.jDBCUrl, Consts.jDBCUsr, Consts.jDBCPass)
    Catch
        Log("Last Pool Init Except: "&LastException.Message)
    End Try
    ' change pool size...
    Dim jo As JavaObject = pool
    jo.RunMethod("setMaxPoolSize", Array(100))
End Sub

Sub GetSQL() As SQL 'ignore
    Return pool.GetConnection
End Sub

Sub CloseSQL(mySQL As SQL) 'ignore
    mySQL.Close
End Sub

#End Region


' Using SQL objects

public Sub LoginCheck(Login As String, Pass As String) As List
    Log(" starting check")
    Dim sql As SQL = GetSQL
    Dim q As String =    "SELECT * FROM users WHERE mail = '" & Login & "' AND password = '" & Pass & "'"
    Dim dat As List
    dat.Initialize2(SQLSelect(sql, q, Null))
    CloseSQL(sql)
    Return dat
End Sub

Sub SQLSelect(SQL As SQL, Query As String, Args As List) As List 'ignore
    Dim l As List
    l.Initialize
    Dim cur As ResultSet
    Try
        cur = SQL.ExecQuery2(Query,  Args)
    Catch
        Log(LastException)
        Return l
    End Try
    Do While cur.NextRow
        Dim res As Map
        res.Initialize
        For i = 0 To cur.ColumnCount - 1
            res.Put(cur.GetColumnName(i).ToLowerCase, cur.GetString2(i))
        Next
        l.Add(res)
    Loop
    cur.Close
    Return l
End Sub

constants module...

'Static code module  Consts

private Sub Process_Globals
'
    Public const jDBDRV As String = "com.mysql.jdbc.Driver"
    Public const jDBCUrl As String = "jdbc:mysql:server/dbname?characterEncoding=utf8&useSSL=true"
    Public const jDBCUsr As String = "username"
    Public const jDBCPass As String = "password"

End Sub

 

[jroriz]

A quick example... Just a few snippets...
ABMaterial has an excellent DBM (Database Management Module) that works well.
I never close the pool (right or wrongly)...

Main app start...

Sub AppStart (Args() As String)
  
    DBM.InitializeMySQL()
    '.....

End Sub

' The DBM module

'Static code module
private Sub Process_Globals
    Private pool As ConnectionPool
End Sub


#Region Init

Sub InitializeMySQL() 'ignore
    Log("init mysql")
    Try
        pool.Initialize( Consts.jDBDRV, Consts.jDBCUrl, Consts.jDBCUsr, Consts.jDBCPass)
    Catch
        Log("Last Pool Init Except: "&LastException.Message)
    End Try
    ' change pool size...
    Dim jo As JavaObject = pool
    jo.RunMethod("setMaxPoolSize", Array(100))
End Sub

Sub GetSQL() As SQL 'ignore
    Return pool.GetConnection
End Sub

Sub CloseSQL(mySQL As SQL) 'ignore
    mySQL.Close
End Sub

#End Region


' Using SQL objects

public Sub LoginCheck(Login As String, Pass As String) As List
    Log(" starting check")
    Dim sql As SQL = GetSQL
    Dim q As String =    "SELECT * FROM users WHERE mail = '" & Login & "' AND password = '" & Pass & "'"
    Dim dat As List
    dat.Initialize2(SQLSelect(sql, q, Null))
    CloseSQL(sql)
    Return dat
End Sub

Sub SQLSelect(SQL As SQL, Query As String, Args As List) As List 'ignore
    Dim l As List
    l.Initialize
    Dim cur As ResultSet
    Try
        cur = SQL.ExecQuery2(Query,  Args)
    Catch
        Log(LastException)
        Return l
    End Try
    Do While cur.NextRow
        Dim res As Map
        res.Initialize
        For i = 0 To cur.ColumnCount - 1
            res.Put(cur.GetColumnName(i).ToLowerCase, cur.GetString2(i))
        Next
        l.Add(res)
    Loop
    cur.Close
    Return l
End Sub

constants module...

'Static code module  Consts

private Sub Process_Globals
'
    Public const jDBDRV As String = "com.mysql.jdbc.Driver"
    Public const jDBCUrl As String = "jdbc:mysql:server/dbname?characterEncoding=utf8&useSSL=true"
    Public const jDBCUsr As String = "username"
    Public const jDBCPass As String = "password"

End Sub

Considering the LoginCheck procedure, would it be the same principle for write operations (getconnection and then sql.close)?

 

[Harris]

Absolutely... open and close the sql - or you will run out of pool handles... (or worse).

 

[jroriz]

Considering the LoginCheck procedure, would it be the same principle for write operations (getconnection and then sql.close)?

I tweaked the code of the first post according to your advice.
See if you agree.

 

[Harris]

Looks fine as a very basic example - open and close where applicable.