B4J Question Open/Close Connection?

Pendrush said:

If you have concurrent connections you should open/close connection(s).

Click to expand...

ilan said:

so my questions is should i open/close the connection every time a client (browser, mobile app, etc...) is making a request (via websocket or server handler) to the webapp or can i leave the connection open since ONLY the webapp is making a connection to the DB?

Click to expand...

Pendrush said:

What if two users try to post or one try get other try post in exact same time?

Click to expand...

    Public sql1 As SQL

'...
Wait For(connectToSql(dblocation,mysql_dbname,mysql_user,mysql_pass)) Complete (sql As SQL)
        sql1 = sql
        connectedToSql = sql <> Null
        Log("is connected to mysql: " & connectedToSql)  

'...

 
Public Sub connectToSql(dbloc As String, dbn As String, user As String, pass As String) As ResumableSub
    Try
        Dim userPool As ConnectionPool
        userPool.Initialize(mysql_driver, $"${mysql_jdbc1}${dbloc}/${dbn}${mysql_jdbc2}"$,user,pass)
        userPool.GetConnectionAsync("sql1")
        Wait For sql1_ConnectionReady (Success As Boolean, sql As SQL)
        If Success Then Return sql Else Return Null
    Catch
        Log(LastException)
        Return Null
    End Try
End Sub

ilan said:

is that bad?

Click to expand...

EnriqueGonzalez said:

Yes, you may as well not use the connection pool at all, in this scenario you don'tneed it

Click to expand...

Public Sub OpenConnection (pool As ConnectionPool) As ConnectionPool
    Try
        pool.Initialize(Conn.DriverClass, Conn.JdbcUrl, Conn.User, Conn.Password)
        Dim jo As JavaObject = pool
        jo.RunMethod("setMaxPoolSize", Array(Conn.MaxPoolSize))
    Catch
        LogError(LastException)
    End Try
    Return pool
End Sub

Public Sub OpenSQLiteDB As SQL
    Dim DB As SQL
    DB.InitializeSQLite(File.DirApp, Conn.DbName, False)
    Return DB
End Sub

'...'
Dim con As SQL = OpenDB
'...'

Sub OpenDB As SQL
    If Main.Conn.DbType.EqualsIgnoreCase("mysql") Then
        pool = Main.OpenConnection(pool)
        Return pool.GetConnection       
    End If
    Return Null
End Sub

Public Sub Initialize
    Dim config As Map = LoadConfigMap
    pool.Initialize(config.Get("DriverClass"), config.Get("JdbcUrl"), config.Get("User"), _
        config.Get("Password"))
#if DEBUG
    DebugQueries = True
#else
    DebugQueries = False
#end if
    serverPort = config.Get("ServerPort")
    LoadSQLCommands(config)
End Sub

Public Sub GetConnection As SQL
    If DebugQueries Then LoadSQLCommands(LoadConfigMap)
    Return pool.GetConnection
End Sub

ilan said:

he is initializing the pool object each time he makes a query

Click to expand...

OliverA said:

Follow the jRDC2 code. Use Connected/disconnected to get/close a SQL connection. Pooling will keep connections alive and re-use them
That's one of the goals of pooling, to minimize opening/closing the actual connections to the DB, since these are resource/time expensive tasks. Pooling allows to re-use these open connections.

Click to expand...

SHOW SESSION VARIABLES LIKE 'wait_timeout';

SET session wait_timeout=600;

ilan said:

after googling a little i found exact your answer

so this is what i did. i also saw that i can change the time the DB will hold my connections on sleep status

SQL:

SHOW SESSION VARIABLES LIKE 'wait_timeout';

default is 28800 (8 hours)

i can change it to 600 (10 min) like this

SQL:

SET session wait_timeout=600;

Click to expand...

aeric said:

Changing this variable will let MySQL engine to kill the sleeping connection?

Click to expand...