B4J Question B4J Connection Pool Examples?

[AKJammer]

Hey All,
I've been scouring the forums trying to find some examples of using a Connection Pool for a B4J UI application, but while I've found references to "you should be using it", I haven't found anything that tells how. I've downloaded the library files, but there wasn't an example program to show what to do with it.
I've mostly finished my program and am going through cleaning stuff up. 18 months of learning and the first few modules can use some tweaking. One of the things that's always been annoying was that if I let the program sit overnight, as soon as it tries to hit the database the next morning it blows up. Apparently using a connection pool should stop that. Does anyone have an example B4J program that shows what should be set up first and what the steps are to access the pool, use the connector, then release the pool.

Thanks,
Jim

 

[EnriqueGonzalez]

the most common example of connectionPooling is JRDC.

you need to declare the global, lets say i have this in main globals.

dim pool as ConnectionPool

Then in the server handlers everytime someone invokes your http server you will do.

Sub Handle(req As ServletRequest, resp As ServletResponse)
Dim sq As SQL = Main.Pool.GetConnection

You MUST always close that connection. IF you dont close it, server will keep it alive and it will happen what you mention, suddenly it will not be working anymore.

sql.close

The pooling usually comes with 5 concurrent connections, that is to few when dealing with real use. so it is good to add this lines after you initialize it.

'MAIN
Dim jo As JavaObject = pool
jo.RunMethod("setMaxPoolSize", Array(20)) 'number of concurrent connections

it is important to not add to many, if you do it and you have a missing sql.close, your server ram and cpu will spike.

 

[AKJammer]

Hmmm, Ok, I'm not using a web server for this part of the project, it's just a thick client talking to a database, but let me see if I've got this correct.

In the main menu start of my program I need to have a

public pool as connectionpool

Then initialize it. Q: Can it be initialized with either mySql or Sqlite?

Once initialized, and set with a higher pool limit, I create the sql object with

dim sq as sql = main.pool.getconnection

at that point, the SQ is just like my current main.SQL object I'm using now. I can create/end transactions, and do whatever I need to do with it in the short span I need it, then close it with SQ.Close to return it to the pool. Q: Should it be kept open for just that transaction set, or as long as the form is open? is there a standard?

So if I go to request a connection from the pool and the application has lost it's connection to the database, it'll reconnect automagically? Or is it something I have to check and do a reconnect in my code?

Thanks,
Jim

 

[EnriqueGonzalez]

Q: Can it be initialized with either mySql or Sqlite?

any database, correct.

I create the sql object with

thats correct

at that point, the SQ is just like my current main.SQL object I'm using now. I

thats correct too

Q: Should it be kept open for just that transaction set, or as long as the form is open?

It depends on each task and person, for me, i like to open and close per function, sometimes i pass the sql as a parameter to other functions and close it in the parent.

is there a standard?

No. just close it as soon as you can.

it'll reconnect automagically?

The pool saves you time with the handshake between your app and the DB, it does not persist the connection. It will request a connection everytime you create an sql instance. and AFAIK but i am not sure, it could handle the reconnection.

 

[Erel]

The pool is responsible for keeping several active connections all the time and giving you a live connection when you need one (if possible of course). It will not handle the not very likely case, where the connection that you are working with, breaks. This will cause an error.

 

[AKJammer]

Currently I'm opening a single connection to the database at program start and using that connection whenever I need one. If the program sits idle overnight, it bombs as soon as it tries to hit the database because the connection is gone. I'm ok with changing things around to make improvements, but not just to do things differently. How is using a connection pool a better option? hmmm, answering my own question, I guess it would allow me to do something like this? Would something like this work or should I be looking into mySql and seeing why it's timing out?

sub getSqlConnector as SQL
   Try
       Dim sq As SQL = pool.GetConnection
       return sq
   Catch
        Try
               'reconnect to the database first then send an SQL
               pool.initialize(.......)
               Dim sq as SQL = pool.GetConnection
               return sq
        Catch
              'Fail out, Database not available
        End Try
    End Try
end Sub

 

[EnriqueGonzalez]

. It will not handle the not very likely case, where the connection that you are working with, breaks.

Just to be clear on this statement, if during execution the connection breaks, it will indeed throw an error, that doesnt mean the connection pool will not try to recover the connection.

HikariCP - High Performance Connection Pool

This is a wrapper for: https://github.com/brettwooldridge/HikariCP It can be used the same way as the ConnectionPool from the jServer library. Below you will find my implementation of using it with MariaDB/MySQL Dim cp As HikariCP cp.Initialize("org.mariadb.jdbc.Driver"...

www.b4x.com

I dont know for c3p0 library but for hikariCP that is a pooling just like former. the reconnection process goes like this:

In nutshell, every request goes to HikariCP to obtain a connection from a pool in order to run a query. HikariCP picks up the random connection from its available pool (they call it as a Concurrent Connection Bag) and checks if the picked up connection is still alive by firing the sample query on the database (we can also explicitly provide sample query).In case if a database is not responding (i.e connection is no more valid) then HikariCP closes this connection and removes it from the pool.

Refilling the pool after closing and removing of dead connection is an important step taken by HikariCP when pool state is normal (i.e it’s not shut down or suspended).In the process of refilling, HikariCP tries to create new connections asynchronously which might end up getting connection attempt failed if the database is still not up. The whole process repeats till timeout set in HikariCP’s configuration is not over.

A connection timeout is a contract between application and the pool; your application should get a connection within the specified time or get an exception. In the end, HikariCP throws connection not available if it times out.

Extracted from:

To my understanding, then. @AKJammer your solution may not work, The pool is already trying to refill its connections, if you re initialize it then you are not fully using the pool capabilities.

Also, it requieres a simple breeze or a high ping to take down an SQL connection, thats why it is signficantly better to use pooling as it will always try to refill itself of alive connections.

 

[AKJammer]

Ok, So it sounds like I just need to try it out and see if it works. I guess Erel's comment may have been for a truly broken connection as opposed to a timeout. I'll put stuff in place today, then let it sit idle overnight. Long testing time, but <shrug>...

Thanks.
Checks in the mail...

 

[EnriqueGonzalez]

Ok, So it sounds like I just need to try it out and see if it works.

Thats possibly the best route here.

Checks in the mail...

Thanks! always a pleasure!

 

[AKJammer]

Ok, So currently what I'm doing is:

dim sql as SQL

try
  sql = open a mySQL database connection
catch
      'no connection available, ask if they want a local database to practice in.
      try
           sql = open a local SQLite database connection w/create = false
      catch
              'no SQLite database exists, do you want to create a new one?
               sql = open a local SQLite database connection w/create = true
               create the database structure that supports the application
      end try
end try

The issue at this point is that I can currently open sql as either mySQL or Sqlite and use it. For the pool the intialization appears the same as a sql.initialize2.

Common.sql.Initialize2(Common.driverclass,Common.host,Common.user,Common.pass)

Common.pool.Initialize(Common.driverclass,Common.host,Common.user,Common.pass)

But SQLite initialization is

Common.sql.InitializeSQLite(dbDirectory,Common.SqlDbName, False)

How do you initialize a SQLite database into the pool?
Hmmm. Just did some checking... You can't. https://www.b4x.com/android/forum/t...ent-access-to-sqlite-databases.39904/#content

So I'll have to access the database differently now whether I was able to get a pool or am going local as a training database.

I guess I could do this:

'in Common: 
dim sqlite as SQL

'do my pool startups and set flag if MariaDB or SQLite

sub getSqlConnector as SQL
   if MariaDB
       dim sql as pool.getConnection
       return sql
   else 
        return sqlite
end sub

 

[EnriqueGonzalez]

Hmmm. Just did some checking... You can't.

i dont know exactly why Erel suggest you should not pool Sqlite, i guess that with the proper configurations and some java you could, but being a local db with no network interface it is very likely to never go off. if it actually goes offline you probably has some greater issues to attend.

I guess I could do this:

this is a possible solution. you just need to remember not to close sqlite on every use.

 

[Erel]

i dont know exactly why Erel suggest you should not pool Sqlite

As there is no server in SQLite, handling of concurrent access requires more care. Specifically you need to enable WAL mode and to use a single SQL connection. The connection pool will create multiple connections.

 

[AKJammer]

Just wanted to do a final follow-up. It took a couple days to get all the sql statements converted over, there are several hundred in the application. I finished yesterday afternoon, then started the program and let it sit overnight. This morning I went in and started looking at screens. No Blowups!! So the pool maintained my overall connection and was able to issue new sql's as needed. Thanks again for the assist.

Jim

 

[EnriqueGonzalez]

?That's very good to hear!