B4J Question Web app with MsSQl database

Bladimir Carrillo

Bladimir Carrillo

Member
Licensed User
Longtime User
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 ...

webapp01.png



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
Click to expand...

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:INFO:oejs.Server:main: jetty-9.3.z-SNAPSHOT2016-09-21 08:04:35.809:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@20398b7c{/,file:///C:/SMAC0434_WEB/www/,AVAILABLE}2016-09-21 08:04:35.809:INFO:oejs.AbstractNCSARequestLog:main: Opened C:\SMAC0434_WEB\logs\b4j-2016_09_21.request.log2016-09-21 08:04:35.903:INFO:oejs.ServerConnector:main: Started ServerConnector@22000e7b{HTTP/1.1,[http/1.1]}{0.0.0.0:40434}2016-09-21 08:04:35.903:INFO:oejs.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:WARN:oejwc.Parser:qtp1395089624-26: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)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)
Click to expand...

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,
Click to expand...

Regards
 
Sort by date Sort by votes
Erel said:
Are you closing all of the SQL objects after you finish using them?
Click to expand...

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
 
Upvote 0
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:

Code: 
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.
 

Attachments

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

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?
 
Upvote 0
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.
Click to expand...
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
 
Upvote 0
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).
Click to expand...

About pool variable declaration ...

Wrong way. pool variable declared as private on each page module, in my case the app have 5 pages
Code: 
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)
Code: 
Sub Process_Globals
   Public pool As ConnectionPool
End Sub


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

Wrong way.
Code: 
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.
Code: 
'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
 
Upvote 0
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.
 
Last edited:
Upvote 0
Harris said:
I shall need to support two way messaging ...
Click to expand...

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

Server side (B4J web app)

Code: 
'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:
Code: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar Threads

aeric
  • Article Article
B4J Library [B4X] [SQL] MiniORMUtils
2 3
Replies
45
Views
21K
aeric
aeric
aeric
  • Article Article
Share My Creation [Project Template] [Web] API Server v2
2 3 4
Replies
68
Views
53K
PABLO2013
PABLO2013
aeric
  • Article Article
Share My Creation [Project Template] [Web] API Server v1
4 5 6
Replies
111
Views
90K
PABLO2013
PABLO2013
Erel
  • Article Article
B4J Code Snippet Opening MS Access databases (mdb)
2
Replies
26
Views
26K
Pesciolina
P
Erel
  • Article Article
B4J Tutorial [WebApp] Concurrent access to SQLite databases
Replies
7
Views
33K
Erel
Erel
Back
Top