B4J Question jserver and connection pool

[tufanv]

Hello,

I am playing around with the jserver example. I can successfully reach the server on 127.0.0.1 , I set up a connection pool to mysql server with this :

    pool.Initialize("com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/dbname?autoReconnect=true&useSSL=false", _
        "root", "xxx")
    pool.GetConnection.ExecNonQuery("SELECT * from tblson")

I want to write the results to page with req.write but I can't even log the results. no errors are returned from the execnonquery but how do i print the results ?

Thanks

 

[Erel]

I don't understand your code. Where have you put it?

Why are you executing a SELECT query with ExecNonQuery?

You are not closing the connection. This will cause a connection leak.

 

[tufanv]

I don't understand your code. Where have you put it?

Why are you executing a SELECT query with ExecNonQuery?

You are not closing the connection. This will cause a connection leak.

I am using your example main is :

'Non-UI application (console application)
#Region  Project Attributes
    #CommandLineArgs:
    #MergeLibraries: true
    #AdditionalJar: mysql-connector-java-5.1.43-bin.jar
#End Region

Sub Process_Globals
    Private srvr As Server
    
End Sub

Sub AppStart (Args() As String)
    srvr.Initialize("srvr")
    srvr.Port = 80
    
    srvr.StaticFilesFolder = File.Combine(File.DirApp, "www")
    srvr.LogsFileFolder = File.Combine(File.DirApp, "logs")
    srvr.AddHandler("/hello", "HelloPage", False)
    

    

    srvr.Start
    Log("Server started")
    StartMessageLoop
End Sub

'prints the requests parameters
Public Sub PrintAllParameters (req As ServletRequest) As String
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append("<ul>")
    Dim params As Map = req.ParameterMap
    For Each name As String In params.Keys
        sb.Append("<li>").Append(name).Append(":")
        Dim values() As String = params.Get(name)
        For Each value As String In values
            sb.Append(" ").Append(value)
        Next
        sb.Append("</li>")
    Next
    sb.Append("</ul>")
    Return sb.ToString
End Sub

for the /hello page my code is :

'Class module
Sub Class_Globals
    Private mreq As ServletRequest 'ignore
    Private mresp As ServletResponse 'ignore
    Public pool As ConnectionPool
End Sub

Public Sub Initialize

End Sub

Sub Handle(req As ServletRequest, resp As ServletResponse)
    mreq = req
    mresp = resp
    Log(req.GetParameter("tufan"))
    
    pool.Initialize("com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/foreks?autoReconnect=true&useSSL=false", _
        "root", "xxx")


   =pool.GetConnection.ExecNonQuery("SELECT * from tblson")
    
    
    pool.GetConnection.Close

    resp.Write("Your ip address is: " & req.RemoteAddress )   

End Sub

what I want to do here is when use navigates to 127.0.01/hello , I want to give json response of above query. I am closing the pool because I think I have to close the pool after each user gets the data.

 

[Erel]

There are too many mistakes for me to help you here...

I recommend you to download the example project: https://www.b4x.com/android/forum/threads/webapp-web-apps-overview.39811/
See the code in DB module. Note that it is not a class.

BTW, if you are looking for performance numbers: https://www.b4x.com/android/forum/threads/server-performance-measures.37502/#content

 

[Erel]

The mistakes are:

- The whole idea of a connection pool is that it is created once and all handlers use it. It should never be created inside a handler class.
- Calling pool.GetConnection returns a "new" connection from the pool. Your code leaves the first connection open and then gets another connection and closes it.
- SELECT is a query and should be executed with ExecQuery.
- You are not doing anything with the query result.
- You are not closing the pool (which is good).

 

[tufanv]

Thanks for the list. I will correct them one by one and review that example. Thanks Erel. Performance looks awesome btw. It can help me save myself from 3 dedicated servers and use some cheap vps instead.

 

[Erel]

Your code leaves the first connection open and then gets another connection and closes it.

(not really closes it but rather returns the connection to the pool)

Correct way to work with a connection pool:

Dim connection As SQL = Main.Pool.GetConnection
Try
 'work with connection
Catch

End Try
connection.Close 'return to pool

 

[tufanv]

Works perfect now thanks again. I remember seeing somewhere how to set the maxconnections but I can't find now. Do you have any suggestion on how to set a maximum number according to those performance numbers ?

(not really closes it but rather returns the connection to the pool)

Correct way to work with a connection pool:

Dim connection As SQL = Main.Pool.GetConnection
Try
 'work with connection
Catch

End Try
connection.Close 'return to pool

 

[Erel]

Do you have any suggestion on how to set a maximum number according to those performance numbers ?

It is not possible that each of your threads ends with me asking you to start a new thread for your new question. I will just ignore those questions next time.