B4J Question Web app with MsSQl database

Bladimir Carrillo · Sep 22, 2016

Hi all,
Finally I have developed and put in production my first B4J web app following the instructions of the different B4J tutorials .
Some features:
- Use MsSQL database to report work requests daily.
- Use ABMaterial framework
- Login page with initial generic password.
- Reset password page at first login.
- Work request details page including images and GPS location to report.
- Send websocket message to mobile devices (with B4A app) to notify a new request created.
- Requests list with state to edit or show details from that
- Other ...

The app is running for one month receiving almost 100 daily requests with very good speed and performance.

If someone wants to know specific details of how to implement some of the mentioned features , feel free to ask.

Likewise as in any new application there are some errors that require correction. If someone know how to solve them or have any idea, your suggestions are welcome.

Work request screen shot ...

Some errors to solve ....

1. Threads interrupted..
When this error appears, I have to restart the app because the database access is interrupted (the error is shown several times):

WARNING: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@7fc54e6a is interrupting all Threads waiting on a resource to checkout. Will try again in response to new client requests.Sep 21, 2016 7:46:19 AM com.mchange.v2.resourcepool.BasicResourcePool forceKillAcquires

WARNING: Having failed to acquire a resource, com.mchange.v2.resourcepool.BasicResourcePool@7fc54e6a is interrupting all Threads waiting on a resource to checkout. Will try again in response to new client requests.Sep 21, 2016 7:46:20 AM com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask run

WARNING: com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@259c3fd4 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:java.sql.SQLException: Network error IOException: Address already in use: connect at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:436) at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184) at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:146) at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:195) at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:184) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:200) at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1086) at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1073) at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:44) at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1810) at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:648)Caused by: java.net.BindException: Address already in use: connect at java.net.DualStackPlainSocketImpl.connect0(Native Method) at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source) at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source) at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source) at java.net.AbstractPlainSocketImpl.connect(Unknown Source) at java.net.PlainSocketImpl.connect(Unknown Source) at java.net.SocksSocketImpl.connect(Unknown Source) at java.net.Socket.connect(Unknown Source) at net.sourceforge.jtds.jdbc.SharedSocket.createSocketForJDBC3(SharedSocket.java:288) at net.sourceforge.jtds.jdbc.SharedSocket.<init>(SharedSocket.java:251) at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:331) ... 10 moreSep 21, 2016 7:46:20 AM com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask run

2. When restart app...

c:\SMAC0434_WEB>java -jar STs.jarGZipping HTML, JavaScript and CSS files...Optimizing new and updated PNG files...2016-09-21 08:04:34.731:INFO::main: Logging initialized @29219ms2016-09-21 08:04:35.059:INFOejs.Server:main: jetty-9.3.z-SNAPSHOT2016-09-21 08:04:35.809:INFOejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@20398b7c{/,file:///C:/SMAC0434_WEB/www/,AVAILABLE}2016-09-21 08:04:35.809:INFOejs.AbstractNCSARequestLog:main: Opened C:\SMAC0434_WEB\logs\b4j-2016_09_21.request.log2016-09-21 08:04:35.903:INFOejs.ServerConnector:main: Started ServerConnector@22000e7b{HTTP/1.1,[http/1.1]}{0.0.0.0:40434}2016-09-21 08:04:35.903:INFOejs.Server:main: Started @30386msjava.lang.RuntimeException: org.eclipse.jetty.websocket.api.WebSocketException:RemoteEndpoint unavailable, current state [CLOSED], expecting [OPEN or CONNECTED] at anywheresoftware.b4j.object.WebSocket.setEvents(WebSocket.java:378) at anywheresoftware.b4j.object.WebSocketModule$Adapter$ThreadHandler.run(WebSocketModule.java:190) at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) at java.util.concurrent.FutureTask.run(Unknown Source) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source)Caused by: org.eclipse.jetty.websocket.api.WebSocketException: RemoteEndpoint unavailable, current state [CLOSED], expecting [OPEN or CONNECTED] at org.eclipse.jetty.websocket.common.WebSocketSession.getRemote(WebSocketSession.java:299) at anywheresoftware.b4j.object.WebSocket.sendText(WebSocket.java:107) at anywheresoftware.b4j.object.WebSocket.setEvents(WebSocket.java:375) ... 6 more2016-09-21 08:04:39.689:WARNejwc.Parser:qtp1395089624-26rg.eclipse.jetty.websocket.api.ProtocolException: Unknown opcode: 7 at org.eclipse.jetty.websocket.common.Parser.parseFrame(Parser.java:325) at org.eclipse.jetty.websocket.common.Parser.parse(Parser.java:252) at org.eclipse.jetty.websocket.common.io.AbstractWebSocketConnection.readParse(AbstractWebSocketConnection.java:663) at org.eclipse.jetty.websocket.common.io.AbstractWebSocketConnection.onFillable(AbstractWebSocketConnection.java:493) at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:261) at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95) at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:75) at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:213) at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:147) at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654) at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572) at java.lang.Thread.run(Unknown Source)org.eclipse.jetty.websocket.api.ProtocolException: Unknown opcode: 7 at org.eclipse.jetty.websocket.common.Parser.parseFrame(Parser.java:325) at org.eclipse.jetty.websocket.common.Parser.parse(Parser.java:252) at org.eclipse.jetty.websocket.common.io.AbstractWebSocketConnection.readParse(AbstractWebSocketConnection.java:663) at org.eclipse.jetty.websocket.common.io.AbstractWebSocketConnection.onFillable(AbstractWebSocketConnection.java:493) at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:261) at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95) at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:75) at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:213) at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:147) at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654) at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572) at java.lang.Thread.run(Unknown Source)

3. Related to mobile websockets module...
The following error are showing from I has implemented a module to send messages to B4A android mobile app using websockets.
(The error appears ay time when I am not sending remote mesagges to device):

java.lang.RuntimeException: java.lang.RuntimeException: java.lang.RuntimeExcepti
on: java.lang.NullPointerException
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:114)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
at anywheresoftware.b4j.object.WebSocketModule$Adapter$1.run(WebSocketMo
dule.java:126)
at anywheresoftware.b4a.keywords.SimpleMessageLoop.runMessageLoop(Simple
MessageLoop.java:30)
at anywheresoftware.b4a.StandardBA.startMessageLoop(StandardBA.java:26)
at anywheresoftware.b4j.object.WebSocketModule$Adapter$ThreadHandler.run
(WebSocketModule.java:195)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.RuntimeException: java.lang.RuntimeException: java.lang.Nul
lPointerException
at anywheresoftware.b4a.keywords.Common.CallSub4(Common.java:471)
at anywheresoftware.b4a.keywords.Common.CallSubNew(Common.java:409)
at sts.sismac.net.st_edit._page_parseevent(st_edit.java:1625)
at sun.reflect.GeneratedMethodAccessor97.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
... 10 more
Caused by: java.lang.RuntimeException: java.lang.NullPointerException
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:114)
at anywheresoftware.b4a.keywords.Common.CallSub4(Common.java:462)
... 16 more
Caused by: java.lang.NullPointerException
at sts.sismac.net.abmshared._connectnavigationbar(abmshared.java:610)
at sts.sismac.net.st_edit._connectpage(st_edit.java:660)
at sts.sismac.net.st_edit._page_ready(st_edit.java:1659)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
... 17 moreThank you in advance,

Regards

Erel · Sep 22, 2016

Errors can happen from time to time. Lets start with the first one. Are you closing all of the SQL objects after you finish using them?

Bladimir Carrillo · Sep 28, 2016

Erel said:
Are you closing all of the SQL objects after you finish using them?

Thanks Erel for your suggestion and sorry for the delay in responding.
I have made a full verification of all SQL objects and adjusted all statements to make sure all after being used are closed, but the error continues showing in circumstances and times where the application is not being used (in late at night and very early morning).

Any other suggestions?

Thank you in advance

Erel · Sep 29, 2016

1. Check again that you are closing all SQL objects in all cases (including exceptions).
2. Try to decrease the pool size: https://www.b4x.com/android/forum/t...ith-connectionpool-library.42647/#post-261085

Mashiane · Sep 29, 2016

Wow, looking nice. I'm new to the ABMaterial framework and still getting into grips with it. This is fairly nice. For starters, I need to master the grid, i'm curious about the websockets you mentioned to send notifications. Do you mind sharing your experiences?

Bladimir Carrillo · Oct 5, 2016

Mashiane said:
... Do you mind sharing your experiences?

Yes of course. Although the expert on ABMaterial is the author Alain Bailleul (alwaysbusy member), gladly I try to answer your questions.

Bladimir Carrillo · Oct 5, 2016

Erel said:
1. Check again that you are closing all SQL objects in all cases (including exceptions).
2. Try to decrease the pool size: https://www.b4x.com/android/forum/t...ith-connectionpool-library.42647/#post-261085

Thanks Erel, I have reviewed and adjusted everything as suggested, but the problem still occurs at least every 1 or 2 days.
Any other suggestions please?

Erel · Oct 5, 2016

The online server example (www.b4x.com:51042) is running for several months without any database issue. I can only guess that somewhere in your code you are not releasing the SQL connections.

alwaysbusy · Oct 6, 2016

I agree with Erel you must've missed some SQL close somewhere. However, I've written a Cron library in the past that could be useful here as a last resort.

ABCron is a more advanced timer library that can schedule more intelligently tasks using Cron expressions. You can also set a start and end datetime.

e.g.
0 0/5 14,18 * * ? = Fire every 5 minutes starting at 2:00 PM and ending at 2:55 PM, AND fire every 5 minutes starting at 6:00 PM and ending at 6:55 PM, every day
0 15 10 ? * MON-FRI = Fire at 10:15 AM every Monday, Tuesday, Wednesday, Thursday and Friday
0 15 10 ? * 6#3 = Fire at 10:15 AM on the third Friday of every month

I've added a method RestartApplicationNONUI() that should restart you .jar file when the Cron expression is met.

Usage example:

B4X:

Sub Process_Globals
   Dim cron As ABCron
   Dim RestartCounter As Int
End Sub

Sub AppStart (Args() As String)
   ' load info saved in a file previously by the cron_tick() method
   If File.Exists(File.DirApp, "cronsave.txt") Then
     Dim reader As TextReader
     reader.Initialize(File.OpenInput(File.DirApp, "cronsave.txt"))
     RestartCounter = reader.ReadLine
     reader.Close
   End If
   ' "0 0/1 * 1/1 * ? *" means run cron_tick every minute
   ' in the real world, use a more intelligent one like:
   ' "0 0 3 1/1 * ? *" means run cron tick every night at 3am
   ' more info on how to build a Cron Expression: https://docs.oracle.com/cd/E12058_01/doc/doc.1014/e12030/cron_expressions.htm
   cron.Initialize(Me, "cron", 0, 0, "", "0 0/1 * 1/1 * ? *")
   cron.Enabled = True
   StartMessageLoop
End Sub

Sub cron_Tick()
   RestartCounter = RestartCounter + 1
   Log("Ticked")
   ' do some cleanup stuff, or save info in a file
   Dim writer As TextWriter
   writer.Initialize(File.OpenOutput(File.DirApp, "cronsave.txt",False))
   writer.WriteLine(RestartCounter)
   writer.Close
   #if release
     cron.RestartApplicationNONUI
   #end if
End Sub

Sub cron_Finished()
   Log("finished")
End Sub

You will need to kill the .jar file if you want to actually stop it: on windows with the task manager, on linux probably kill -9 <PID>. I have only been able to test the library on Windows.

Bladimir Carrillo · Oct 7, 2016

alwaysbusy said:
I agree with Erel you must've missed some SQL close somewhere. However, I've written a Cron library in the past that could be useful here as a last resort.

This library will be very useful while solving errors. Tthank you very much Alain.

Bladimir Carrillo · Oct 7, 2016

Erel said:
I can only guess that somewhere in your code you are not releasing the SQL connections.

Apparently I solved the problem number 1.
I had been using a private pool variable in each module instead of using a single public pool variable; I guess this caused in each session are accumulated connections and this generated the error.
Could this be possible?

Erel · Oct 7, 2016

Bladimir Carrillo said:
Could this be possible?

Yes. You should have provided this information in the first post.

Please start a new thread for a new question.

Bladimir Carrillo · Oct 7, 2016

Erel said:
Yes. You should have provided this information in the first post.

You're right, I'm so sorry, I did not take into account that possibility related with pool variable declaration from first post.

Erel said:
Please start a new thread for a new question..

I'll create a new posts specifically related to new errors for resolution.
Thank you very much.

Harris · Oct 7, 2016

Bladimir Carrillo said:
You're right, I'm so sorry, I did not take into account that possibility related with pool variable declaration from first post.

I'll create a new posts specifically related to new errors for resolution.
Thank you very much.

Could you post 'the wrong way' and 'the right way' so I and others do fall into this trap? I kinda understand but would like to see it (even thou I have not experienced it).

I shall need to support two way messaging and want to get off the Google messaging service - since they now want to charge for it.
Can you share your code module - or entire project if possible? Great to learn from others who have 'banged head' getting things to work.
I can share whatever you desire from my project.

Thanks

Bladimir Carrillo · Oct 8, 2016

Harris said:
Could you post 'the wrong way' and 'the right way' so I and others do fall into this trap? I kinda understand but would like to see it (even thou I have not experienced it).

About pool variable declaration ...

Wrong way. pool variable declared as private on each page module, in my case the app have 5 pages

B4X:

Sub Class_Globals
    Private pool As ConnectionPool
End Sub

Right way. pool variable declared as public only one time on a code module (may be Main or other code module. In my case use one named DB)

B4X:

Sub Process_Globals
   Public pool As ConnectionPool
End Sub

About pool variable usage (explanation as comments) ...

Wrong way.

B4X:

Sub btn1_Clicked(Target As String)
     DB.init(pool)      'private pool variable initialized on each page module several times (on each crud or data population process)
     .....
     DBUtils.UpdateRecord2(pool.GetConnection, "Users", Fields, Where)     'pool.GetConnection used instead of a SQL variable
End Sub

Right way.

B4X:

'Main module
Sub AppStart (Args() As String)
   DB.init  'public pool variable initialized only one time
End Sub

'DB code module
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")

   Try
     pool.Initialize(driverClass, JdbcUrl, dbuser, dbpassword)
     
     Dim jo As JavaObject = pool
     jo.RunMethod("setMaxPoolSize", Array(100))   'Set the max pool size to avoid app crash
   Catch
     Log(LastException)
   End Try
End Sub

'Page code (passwod change example)
Sub btn1_Clicked(Target As String)
   Dim pwdSQL As SQL = DB.pool.GetConnection  'a SQL variable it must be declared

   pwdSQL.BeginTransaction     'Transaction start

   Try
       Dim Fields As Map
       Fields.Initialize
       Fields.Put("UsrPw", usrPw)

       Dim Where As Map
       Where.Initialize
       Where.Put("UsrId", usrId)
         
       DBUtils.UpdateRecord2(pwdSQL, "Users", Fields, Where)   'SQL variable as parameter

       pwdSQL.TransactionSuccessful    'Transaction commited ok

     Else
       Log(LastException)
     End If

   Catch
     pwdSQL.Rollback    'Transaction canceled
     Log(LastException)
   End Try

   pwdSQL.Close     'SQL variable closed after finished process 
   
End Sub

Regards

Harris · Oct 9, 2016

https://www.b4x.com/android/forum/threads/solved-connectionpool-maxpoolsize-issue.61096/

I had issue with pool early on. See link above.

Your example is GREAT! Thanks so much. Funny that what seems quite logical to us is, at times... all wrong...

Also....

I shall need to support two way messaging and want to get off the Google messaging service - since they now want to charge for it.
Can you share your code module - or entire project if possible? Great to learn from others who have 'banged head' getting things to work.
I can share whatever you desire from my project.

Bladimir Carrillo · Oct 9, 2016

Harris said:
I shall need to support two way messaging ...

About messaging ...
The app send a message to a B4A app.

Server side (B4J web app)

B4X:

'WsMsg (Server WebSocket module) code:
'====================
'WebSocket class
Sub Class_Globals
    Private ws As WebSocket
End Sub

Public Sub Initialize
 
End Sub

Private Sub WebSocket_Connected (WebSocket1 As WebSocket)
    ws = WebSocket1
    FnMsg.DevWsAdd(ws)  'Add last ws to a List each time a client (B4A app) connects
End Sub

Sub Device_Message(Params As Map)
    Log("DEVICE MESSAGE: " & Params.Get("message"))  'Show message from B4A app when that arrives.
End Sub

Private Sub WebSocket_Disconnected

End Sub
'====================

'FnMsg (code module) code:
'====================
Sub Process_Globals
   Dim wsDev As List
End Sub

Sub DevWsAdd(ws As WebSocket)
   wsDev.Add(ws)
End Sub

Sub DevMsg(Msg As String)   'SEND MESSAGE to each connected devices (client B4A app). Can be used when needed
   Dim j As Int = wsDev.Size-1
   For i=j To 0 Step -1
     Dim ws As WebSocket
     ws =  wsDev.Get(i)
     If ws.Open Then
       Try
         ws.RunFunction("ServerMsg", Array As Object(Msg))   '"ServerMsg" is referenced function name on B4A app
         ws.Flush
       Catch
         Log(LastException)
       End Try
     Else
       wsDev.RemoveAt(i)    'Remove items form list for closed connections
     End If
   Next
 
End Sub
'====================

Client side (B4A app)
*Needs WebSocketHandler class module and WebSocket library

Main module code:

B4X:

Sub Process_Globals
   Private wsh As WebSocketHandler
End Sub

Sub Activity_Create(FirstTime As Boolean)
   ...
   wshConnect
End Sub

Sub wshConnect As Boolean   'Connect to Server web app
   Dim link As String 'Server IP/Url
   wsh.Connect("ws://" & link & "/ws/Dev1")
End Sub

Sub wshMsgSend(Msg As String)  'SEND MESSAGE to server (B4J web app)
   Try
     Dim data As Map
     data.Initialize
     data.Put("message", Msg)
     wsh.SendEventToServer("Device_Message", data)
   Catch
     Log(LastException)
   End Try
End Sub

Sub wsh_Connected
   Log("Connected")
End Sub

Sub wsh_Closed (Reason As String)
   Log("Closed by " & Reason)
   'At this point you can try to reconect
End Sub

Sub wsh_ServerMsg(Params As List)  '"ServerMsg" related from Server web app
   Log(Params.Get(0))  'Show message when arrive from B4A app
End Sub

Sub wsState as String  'Can be used to verify connection state
   If wsh.ws.Connected Then
      Return "Connected"
   Else If
      Return "Disconnected"
   End If
End Sub

Other threads related to websockets:
https://www.b4x.com/android/forum/threads/websocket-client-library.40221/
https://www.b4x.com/android/forum/threads/webapp-chatroom-threads-sessions-and-server-events.39969/
https://www.b4x.com/android/forum/threads/simple-multi-user-chat-demo.35996/

Regards

Bladimir Carrillo · Oct 10, 2016

Erel said:
Errors can happen from time to time. Lets start with the first one...

First error has been solved!, thanks again.

Any idea about errors 2 and 3 please?

Erel · Oct 10, 2016

Please start a new thread for any other question.

B4J Question Web app with MsSQl database

Member

B4X founder

Member

B4X founder

Expert

Member

Member

B4X founder

Expert

Attachments

Member

Member

B4X founder

Member

Expert

Member

Expert

Member

Member

B4X founder

Similar Threads