B4J Question DB Advice

[Squiffy]

Would appreciate some advice ...

In my server app I have a DB class that creates an odbc connection pool like this :

public Sub init
   Dim JdbcUrl As String = Main.settings.Get("JdbcUrl")
   Dim driverClass As String = Main.settings.Get("DriverClass")
   Dim dbuser As String = Main.settings.Get("DBUser")
   Dim dbpassword As String = Main.settings.Get("DBPassword")
   pool.Initialize(driverClass, JdbcUrl, dbuser, dbpassword)
End Sub

I call this init before calling srvr.Start. For each DB action, I do the following (for example) :

Public Sub MyDBAction
    Dim dbcon As SQL=pool.GetConnection
    ....
    dbcon.Close
End

My app is an API server so there are many connections firing in all the time, from both users' browsers and other servers.

It all seems to work, but I wanted to know if I am doing it the "right" way. In PHP world I would make a new connection to the DB each time (sending username/password/etc.) for each user request and destroy it afterwards. But here I only "log in" to the DB server once and then use connection pooling.

Does anyone have any advice?

Thanks.

 

[Erel]

Your code is correct. You might want to add a Try Catch block:

Sub MyDBAction
Dim dbcon As SQL= pool.GetConnection
Try
 ...
Catch
 Log(LastException)
End Try
dbcon.Close
End Sub

The user name and password will be internally used by the pool whenever it needs to create a new connection.

 

[Squiffy]

Thanks Erel.
So just to be clear in my head, in the scenario ...
1. b4j server starts and does pool.Initialize
2. the remote DB server subsequenty reboots.
3. incoming user request arrives at b4j server after DB server has come back online and does a pool.GetConnection

... would the pool.GetConnection establish the log in connection to the DB? I just need to be sure what to do in the event the DB server resets, as in my head I was thinking I needed to do another pool.Initialize.

Thanks again.

 

[Erel]

Database recovery is explained here: http://www.mchange.com/projects/c3p0/#configuring_recovery

The bottom line is that pool.GetConnection will throw an exception if the database is offline. It will start working when the DB server is online again.