B4J Question Open/Close Connection?

[ilan]

hi

i am building a Rest Api using B4j WebApp running on a LAMP Server.

My Questions is:

the web app is the only client that is connecting to the MySql DB. Every other client can make get/post requests to the b4j webapp => b4j webapp read/write data to the mysql db and returns back to the client.

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?

thanx

 

[Pendrush]

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

 

[ilan]

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

so in my case i dont need to open/close?

because i only make a connection from 1 web app to the db.

 

[Pendrush]

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

 

[EnriqueGonzalez]

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?

Yes! do, or even better, as it is a MySQL database, just use a pool like the official connectionPool library or hickari. if you leave it open and expect to have it open in any point in time you will not be able to catch errors properly

Connections can be closed for a lot of factors, MySQL could have a parameter to close it, B4J could have some others and even Java/driver can have their owns.

 

[ilan]

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

will it be a problem? i am using maridadb (mariadb-java-client-2.7.3)

i have a public sql object in main class.

    Public sql1 As SQL

i initialize it like this:

'...
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

now from everywhere i just call main.sql1....

is that bad?

 

[EnriqueGonzalez]

is that bad?

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

 

[ilan]

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

thank you for your answer.

so my question now is, i see in @aeric api example that he is initializing the pool object each time he makes a query but in erels jrdc example it is different. erel is creating a public pool object and he just get a connection from the pool object. he is not initializing each time he makes a query he is just getting a connection from the pool object.
so what is the right way ??

@aeric example

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

and he is calling the openconnection sub before each query like this:

'...'
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

but erel is not initializing the pool object he just get a connection from the pool object

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

and get the connection before making a query like this:

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

and another question i would like to ask. if i am using a websocket can i get a connection in the WebSocket_Connected event and close it in the WebSocket_Disconnected event or should i do it every time before and after making a query?

and also even if i close the pool connection in sql workbench i still see the connections open, why?

 

[OliverA]

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.

 

[aeric]

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

I think I was too rush when creating the SQLite compatibility and ignored the MySQL part. I try to revisit my code again. I agree that it is a bad way to initialize the connection if it is not required.

 

[ilan]

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.

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

SHOW SESSION VARIABLES LIKE 'wait_timeout';

default is 28800 (8 hours)

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

SET session wait_timeout=600;

 

[aeric]

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

SHOW SESSION VARIABLES LIKE 'wait_timeout';

default is 28800 (8 hours)

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

SET session wait_timeout=600;

Changing this variable will let MySQL engine to kill the sleeping connection?
Or we should use a timer to kill the connection periodically like the following code?

sql.SQLException: "Too many connections" with ConnectionPool library

Hi, Following this thread : http://www.b4x.com/android/forum/threads/sql-error-operation-not-allowed-after-resultset-closed.42516/ I have changed all my SQL queries and SQL connection to the Pool from ConnectionPool method. I now run into this problem: Jul 07, 2014 11:03:19 AM...

www.b4x.com

 

[ilan]

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

Yes that is what it should do.
But dont set it too low because u will loose the purpose of pooling. The whole idea is to reuse the connection and dont open!close a new connection

 

[aeric]

Now, I see what I was doing is to ensure every connection is open only when a request is initiated and it is close immediately. This approach is not making use of Pooling. As mentioned by @OliverA, this is resource/time expensive or in other words consume more memory and would slow down the performance. However, with this approach, I don't ever need to worry about MaxConnectionPool or need to set the size. It is unnoticeable in local development machine but I am not sure what is the performance in production environment. I never do any benchmark.

Web API version 1.10

Web API version 1.11 (with MySQL pooling)