B4J Question [RESOLVED] Server Out of Memory Error

Jmu5667

Well-Known Member
Licensed User
Longtime User
Hello

Have a server that is started with a batch file and the following errors have been thrown

B4X:
C:\Isle Systems Java\Aspect Hello>java -version
java version "1.8.0_152"
Java(TM) SE Runtime Environment (build 1.8.0_152-b16)
Java HotSpot(TM) Client VM (build 25.152-b16, mixed mode, sharing)

C:\Isle Systems Java\Aspect Hello>java -XX:+AggressiveHeap -Xms64m -Xmx64m -Xboo
tclasspath/p:alpn-boot-8.1.11.v20170118.jar  -jar ies_svr_aspect_hello.jar
Java HotSpot(TM) Client VM warning: MaxNewSize (268800k) is equal to or greater
than the entire heap (65536k).  A new max generation size of 65280k will be used
.

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler i
n thread "Thread-10"

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler i
n thread "C3P0PooledConnectionPoolManager[identityToken->1hge0w29u1qz6etl1rbsuex
|aa7bc2]-HelperThread-#2"

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler i
n thread "Thread-11"

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler i
n thread "qtp13097502-17"
Unexpected error (113) returned by AddToSystemClassLoaderSearch
Unable to add C:\Program Files (x86)\Java\jre1.8.0_152\lib\management-agent.jar
to system class path - not supported by system class loader or configuration err
or!

Exception: java.lang.OutOfMemoryError thrown from the UncaughtExceptionHandler i
n thread "C3P0PooledConnectionPoolManager[identityToken->1hge0w29u1qz6etl1rbsuex
|aa7bc2]-AdminTaskTimer"

From my log file for the server I have the following:

B4X:
2018-04-20 19:43:45.269:AC7452A84E28EB6DAFA24427D74C5CF4:IES_CLASS:ping:Login_Credentials
2018-04-20 19:43:45.441:AC7452A84E28EB6DAFA24427D74C5CF4:IES_CLASS:ping:WebSocket_Disconnected, Processing Time 328 ms
2018-04-20 19:44:04.019:AC7452A84E28EB6DAFA24427D74C5CF4:IES_CLASS:login:Logoff_Credentials
2018-04-20 19:44:04.300:AC7452A84E28EB6DAFA24427D74C5CF4:IES_CLASS:login:WebSocket_Disconnected, Processing Time 406 ms
Apr 22, 2018 9:06:20 PM com.mchange.v2.async.ThreadPoolAsynchronousRunner
WARNING: An Error forced the closing of Thread[C3P0PooledConnectionPoolManager[identityToken->1hge0w29u1qz6etl1rbsuex|aa7bc2]-HelperThread-#1,5,main]. Will attempt to reconstruct, but this might mean that something bad is happening.
java.lang.OutOfMemoryError: Java heap space
   at sun.security.ssl.InputRecord.<init>(Unknown Source)
   at sun.security.ssl.SSLSocketImpl.waitForClose(Unknown Source)
   at sun.security.ssl.SSLSocketImpl.closeSocket(Unknown Source)
   at sun.security.ssl.SSLSocketImpl.closeInternal(Unknown Source)
   at sun.security.ssl.SSLSocketImpl.close(Unknown Source)
   at com.microsoft.sqlserver.jdbc.TDSChannel.disableSSL(IOBuffer.java:705)
   at com.microsoft.sqlserver.jdbc.TDSChannel.close(IOBuffer.java:2026)
   at com.microsoft.sqlserver.jdbc.SQLServerConnection.close(SQLServerConnection.java:2760)
   at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1559)
   at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:866)
   at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:569)
   at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
   at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
   at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
   at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
   at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
   at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
   at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
   at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
   at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)

java.lang.OutOfMemoryError: Java heap space
   at sun.security.ssl.InputRecord.<init>(Unknown Source)
   at sun.security.ssl.SSLSocketImpl.waitForClose(Unknown Source)
   at sun.security.ssl.SSLSocketImpl.closeSocket(Unknown Source)
   at sun.security.ssl.SSLSocketImpl.closeInternal(Unknown Source)
   at sun.security.ssl.SSLSocketImpl.close(Unknown Source)
   at com.microsoft.sqlserver.jdbc.TDSChannel.disableSSL(IOBuffer.java:705)
   at com.microsoft.sqlserver.jdbc.TDSChannel.close(IOBuffer.java:2026)
   at com.microsoft.sqlserver.jdbc.SQLServerConnection.close(SQLServerConnection.java:2760)
   at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1559)
   at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:866)
   at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:569)
   at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
   at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
   at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
   at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
   at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
   at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
   at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
   at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
   at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)

java.lang.reflect.InvocationTargetException
   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.b4j.object.JServlet.createInstance(JServlet.java:62)
   at anywheresoftware.b4j.object.BackgroundWorkersManager$1.run(BackgroundWorkersManager.java:21)
   at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.RuntimeException: java.lang.OutOfMemoryError: Java heap space
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:119)
   at anywheresoftware.b4a.objects.Timer$TickTack$1.run(Timer.java:118)
   at anywheresoftware.b4a.keywords.SimpleMessageLoop.runMessageLoop(SimpleMessageLoop.java:30)
   at anywheresoftware.b4a.StandardBA.startMessageLoop(StandardBA.java:26)
   at anywheresoftware.b4a.keywords.Common.StartMessageLoop(Common.java:148)
   at ies.svr.aspect.hello.hello_helper._initialize(hello_helper.java:77)
   ... 7 more
Caused by: java.lang.OutOfMemoryError: Java heap space
   at java.util.jar.Manifest$FastInputStream.<init>(Unknown Source)
   at java.util.jar.Manifest$FastInputStream.<init>(Unknown Source)
   at java.util.jar.Manifest.read(Unknown Source)
   at java.util.jar.Manifest.<init>(Unknown Source)
   at java.util.jar.JarFile.getManifestFromReference(Unknown Source)
   at java.util.jar.JarFile.getManifest(Unknown Source)
   at sun.misc.URLClassPath$JarLoader$2.getManifest(Unknown Source)
   at java.net.URLClassLoader.defineClass(Unknown Source)
   at java.net.URLClassLoader.access$100(Unknown Source)
   at java.net.URLClassLoader$1.run(Unknown Source)
   at java.net.URLClassLoader$1.run(Unknown Source)
   at java.security.AccessController.doPrivileged(Native Method)
   at java.net.URLClassLoader.findClass(Unknown Source)
   at java.lang.ClassLoader.loadClass(Unknown Source)
   at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
Apr 22, 2018 9:13:48 PM com.mchange.v2.async.ThreadPoolAsynchronousRunner
WARNING: An Error forced the closing of Thread[C3P0PooledConnectionPoolManager[identityToken->1hge0w29u1qz6etl1rbsuex|aa7bc2]-HelperThread-#2,5,main]. Will attempt to reconstruct, but this might mean that something bad is happening.
java.lang.OutOfMemoryError: Java heap space

The version of java is "1.8.0_152" the server appStart code is:

B4X:
Sub AppStart (Args() As String)
  
   Dim res As Int
      
   Try
       appPath = mod_settings.get_app_path
       logPath = mod_settings.get_log_path
      
       mod_settings.load_settings
      
       RedirectOutput(logPath, "ies_svr_aspect_hello.log")
       writelog("AppStart")
      
       ' // setup the SQL server
       res = ConfigureSQL
       If res > 0 Then
           ExitApplication2(res)
       End If
      
      
       ' // configure websocket      
       srvr.Initialize("")
       srvr.AddWebSocket("/login", "login")
       srvr.AddWebSocket("/ping", "ping")
       srvr.AddWebSocket("/core", "core")
       srvr.AddWebSocket("/addressbook", "addressbook")
       srvr.AddBackgroundWorker("hello_helper")
       res = ConfigureSSL(SettingsMap.Get("ssl_port"))
       If res > 0 Then
           ExitApplication2(res)
       End If      
       srvr.Http2Enabled = True
       srvr.Start  
      
       ' // configure console port
       svrConsole.Initialize(SettingsMap.Get("console_port"),"svrConsole")
       If Not(console_listen) Then
           ExitApplication2(3)
       End If
      
       ' // start the message loop
       StartMessageLoop
      
   Catch
       writelog("Error " & LastException.Message)
       ExitApplication2(999)
   End Try  
  
  
  
End Sub

The app has been running for over 67 hours and is still running but non contactable from the B4A client and no activity is shown on the server when a connection request from the client is made, the client times out correctly. Tthese are the settings for the server:

heartbeat=1
sql_pool=100
console_port=5001
ttl=20
sql_host=//127.0.0.1\\VM_PPS_TEST
sql_usr=mlEFnB+TGY6JgC0BhBGVl/DU9yJJQXncpQxvJm5DhgcVhBFBBa8SMg\=\=
keystore=C\:\\Isle Systems Data\\Certs\\jetty.keystore
sql_pwd=yVn0gVUBZkMuutsmwN4BZI4DKIw5DrVHL+bIGVwF6LzrUVNLyDvN8Q\=\=
ssl_port=5000

Any advice or guidelines on settings memory etc would be appreciated, also trapping these kind of errors. Here is the ping class, all class's are pretty much the sames format, I have included this in case I am doing something incorrect with the thread pool:

B4X:
'WebSocket class
Sub Class_Globals
  
   Private sql                   As SQL
   Private ws                    As WebSocket  
   Private session_id_client   As String
   Private startTime            As Long = DateTime.Now
   Private tmrKill               As Timer
  
  
End Sub

Public Sub Initialize
  
   DateTime.DateFormat = "yyyy-MM-dd"
   DateTime.TimeFormat = "HH:mm:ss.SSS"
   tmrKill.Initialize("tmrKill",30000)
  
End Sub

Private Sub WebSocket_Connected (WebSocket1 As WebSocket)
  
   Try
       ws = WebSocket1
       ws.RunFunction("ServerReady", Array As Object("READY"))
       ws.Flush
       tmrKill.Enabled = True
   Catch
       writelog("WebSocket_Connected, error - " & LastException)
       close_socket
   End Try

End Sub

Sub tmrKill_Tick
  
   writelog("tmrKill_Tick, Killing Connection")
   tmrKill.Enabled = False
   close_socket
  
End Sub

Sub getSqlConnection
  
  
   Try
       ' // get a connection to the system db
       sql = Main.DB_SQL.GetConnection      
   Catch
       writelog("getSqlConnection, error - " & LastException)
       close_socket
   End Try
  
  
End Sub

Sub closeSqlConnection
  
   Try
       ' // get a connection to the system db
       sql.Close      
   Catch
       writelog("closeSqlConnection, error - " & LastException)
       close_socket
   End Try
  
  
End Sub

Sub close_socket
  
   closeSqlConnection
   ws.Close
  
End Sub


Sub update_ttl
  
      
   Dim rs As ResultSet, sSQL As String
   Dim valid As Boolean
  
   valid = True
   ' // get a SQL connection
   getSqlConnection
   ' // do we have a connection
   If sql.IsInitialized Then
       Try
           ' // check f this user is online
           rs = sql.ExecQuery("SELECT public_id_device FROM [IS_PRESENCE].dbo.presence_hello " & _
                               "WHERE session_id = '" & session_id_client & "'")
          
           If rs.NextRow Then
               ' // update the TTL for this device
               Dim ttl As Long
               ' // TTL =  heatbeat freq * 3 + now
               ttl = DateTime.Now + ((Main.SettingsMap.Get("heartbeat") * DateTime.TicksPerMinute)*3) + DateTime.TicksPerMinute
               sql.BeginTransaction
               sSQL = "UPDATE [IS_PRESENCE].dbo.presence_hello " & _
                   "SET ttl = '" & DateTime.Date(ttl) & " " & DateTime.time(ttl) & "' " & _
                   "WHERE session_id = '" & session_id_client & "'"
                  
               sql.ExecNonQuery(sSQL)
               sql.TransactionSuccessful
               valid= True
           Else
               writelog("update_ttl() session is no longer valid " & session_id_client)
               valid= False
           End If
           rs.close
          
       Catch
           valid= False
           writelog("update_ttl() error = " & LastException.Message)
       End Try

   Else
       writelog("update_ttl() sql.IsInitialized = " & sql.IsInitialized)
       valid= False
   End If
  
   ' // close connection to sql
   closeSqlConnection
  
   If Not(valid) Then
       ' // send
       ws.RunFunction("ServerPing", Array As Object("FAILED"))
   Else
       ' // send
       ws.RunFunction("ServerPing", Array As Object("PINGOK"))
   End If
   ws.Flush

  
End Sub


Sub Login_Credentials(Params As Map)
  
   session_id_client = Params.Get("session_id")
   writelog("Login_Credentials")
   If session_id_client.Length > 0  Then
       CallSubDelayed(Me,"update_ttl")
   Else
       close_socket
   End If
  
  
End Sub

Private Sub WebSocket_Disconnected
  
   tmrKill.Enabled = False
   writelog("WebSocket_Disconnected, Processing Time " & (DateTime.Now - startTime) & " ms")
  
End Sub


Sub writelog(pData As String)
  
   DateTime.DateFormat = "yyyy-MM-dd"
   DateTime.TimeFormat = "HH:mm:ss.SSS"
  
   Log(DateTime.date(DateTime.Now) & " " & DateTime.Time(DateTime.Now) & ":" & session_id_client & ":IES_CLASS:ping:" & pData)
  
End Sub

Thanks in advance

John.
 

Jmu5667

Well-Known Member
Licensed User
Longtime User
Why are you limiting the heap to 64mb? It is quite low.

Hi Erel

I am testing the app with a single user connection which is making a connection to the server every 10 seconds and receiving a response. The reason being is that I want to check if I am getting memory leaks. There a 2 main tasks that can be be run:

1) Ping connection, occurs every minute - This seems to have a memory leak as task manager is showing a steady increment is memory usage (small as it is) (B4A app)
2) Retrieve Address Book data - in test mode this occurs every 10 seconds (B4A app)

I posted the ping class so you(others also) can see what I am doing.

Regards

John.
 
Upvote 0

Jmu5667

Well-Known Member
Licensed User
Longtime User
The correct way to work with a pooled SQL connection is:
B4X:
Dim sql As SQL = Main.DB_SQL.GetConnection
Try
'...
Catch
'...make sure that the code here will not cause a crash.
End Try
sql.Close

I recommend you to remove the two methods that open and close the SQL object. They just make things more complicated.

Ok Erel, I will implement your recommendations and update the post accordingly
 
Upvote 0

Jmu5667

Well-Known Member
Licensed User
Longtime User
The correct way to work with a pooled SQL connection is:
B4X:
Dim sql As SQL = Main.DB_SQL.GetConnection
Try
'...
Catch
'...make sure that the code here will not cause a crash.
End Try
sql.Close

I recommend you to remove the two methods that open and close the SQL object. They just make things more complicated.
Hi Erel

I have implemented your recommendations as it's seem to be better mem using in task manager, at the moment it's just the ping occuring every minute. I also had a back ground task running, can you see if there is anything wrong with what I have done.

B4X:
Sub Class_Globals
   
   Private tmrHook               As Timer   
   
End Sub

Public Sub Initialize
   
   writelog("Background Thread Started, TTL Frequency Check Every " &  Main.SettingsMap.Get("heartbeat") & " Minutes")
   tmrHook.Initialize("tmrHook", Main.SettingsMap.Get("heartbeat") * DateTime.TicksPerMinute)
   tmrHook.Enabled = True
   StartMessageLoop '<- don't forget!
End Sub

Sub tmrHook_Tick
   
   process_ttl
   
End Sub

Sub writelog(pData As String)
   
   DateTime.DateFormat = "yyyy-MM-dd"
   DateTime.TimeFormat = "HH:mm:ss.SSS"
   
   Log(DateTime.date(DateTime.Now) & " " & DateTime.Time(DateTime.Now) & ":IES_CLASS:hello_helper:" & pData)
   
End Sub

Sub process_ttl
   
   
   Dim sSQL As String, devID As String, pubID As String
   Dim rs As ResultSet, doDelete As Boolean
   
   ' // get a SQL connection
   Dim sql   As SQL = Main.DB_SQL.GetConnection
   
   Try
       ' // do we have a connection
       If sql.IsInitialized Then

           rs = sql.ExecQuery("SELECT public_id_device, public_id FROM [IS_PRESENCE].dbo.presence_hello " & _
                       "WHERE ttl <= '" & DateTime.Date(DateTime.Now) & " " & DateTime.time(DateTime.Now) & "' " )
           
           ' // reset all the devices that have timeout out
           Do While rs.NextRow
               
               doDelete = True
               devID = rs.GetString("public_id_device")
               pubID = rs.GetString("public_id")
               
               ' // update the device with the human id, and ste the exit code ot ttl.
               sql.BeginTransaction           
               sSQL = "UPDATE [IS_SYSTEM].dbo.devices_hello " & _
                       "SET " & _
                       "active_user = '', " & _
                       "active = 0, " & _
                       "last_user = '" & pubID & "', " & _
                       "last_user_exitcode = 2 " & _
                       "WHERE device_id = '" & devID & "'"

               sql.ExecNonQuery(sSQL)
               sql.TransactionSuccessful
               
               ' // update the users record with non-active state
               sql.BeginTransaction
               sSQL = "UPDATE [IS_SYSTEM].dbo.c_users " & _
                       "SET [IS_SYSTEM].dbo.c_users.hello_active = 0 " & _                   
                       "WHERE [IS_SYSTEM].dbo.c_users.public_id = '" & pubID & "'"
               sql.ExecNonQuery(sSQL)
               sql.TransactionSuccessful
               
           Loop
           rs.close
           
           If doDelete Then
               ' // now remove them from the presence
               sql.BeginTransaction
               sSQL = "DELETE FROM [IS_PRESENCE].dbo.presence_hello " & _
                           "WHERE ttl <= '" & DateTime.Date(DateTime.Now) & " " & DateTime.time(DateTime.Now) & "' "
               sql.ExecNonQuery(sSQL)
               sql.TransactionSuccessful
           End If       
       End If   
   Catch
       writelog("process_ttl() Error - " & LastException.Message )
   End Try
   
   ' // close connection
   sql.close
   
End Sub

It gets executed every minute with the current settings, 'heartbeat' set to 1 (minute).

Regards

John.
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
are you suggesting non batch query method instead of the current method I use ? If so, why ?
you have a look and in this loop you are updating a lot of items. For each query you start a transaction.
Put them all in a batch and call the batch after the loop. Only one transaction is needed.
 
Upvote 0

OliverA

Expert
Licensed User
Warning: This is a nit-pick and may not affect memory/performance off the app. I just like doing as little as possible in a loop (including variable declarations), since I've been raised on a "loops are expensive" mindset.

Also use parameterized queries (which you may already have, if you switched to batching). Every time you do this
B4X:
               sSQL = "UPDATE [IS_SYSTEM].dbo.devices_hello " & _
                       "SET " & _
                       "active_user = '', " & _
                       "active = 0, " & _
                       "last_user = '" & pubID & "', " & _
                       "last_user_exitcode = 2 " & _
                       "WHERE device_id = '" & devID & "'"
you are creating a new string. For small loops, this may be a non-issue. But since you seem to worry about memory constraints, this can add up (the garbage collector will take care of all those strings, but then you are putting more work on the garbage collector, which could impact performance). Please note this is a nit-pick and I'm only pointing it out since you seem to worry about memory usage.
 
Upvote 0

Jmu5667

Well-Known Member
Licensed User
Longtime User
Warning: This is a nit-pick and may not affect memory/performance off the app. I just like doing as little as possible in a loop (including variable declarations), since I've been raised on a "loops are expensive" mindset.

Also use parameterized queries (which you may already have, if you switched to batching). Every time you do this
B4X:
               sSQL = "UPDATE [IS_SYSTEM].dbo.devices_hello " & _
                       "SET " & _
                       "active_user = '', " & _
                       "active = 0, " & _
                       "last_user = '" & pubID & "', " & _
                       "last_user_exitcode = 2 " & _
                       "WHERE device_id = '" & devID & "'"
you are creating a new string. For small loops, this may be a non-issue. But since you seem to worry about memory constraints, this can add up (the garbage collector will take care of all those strings, but then you are putting more work on the garbage collector, which could impact performance). Please note this is a nit-pick and I'm only pointing it out since you seem to worry about memory usage.


Nah, it's cool, I guess I could just not use the string and add the update bit, and I too was raised in an austere 640kb environment ;)
 
Upvote 0

Jmu5667

Well-Known Member
Licensed User
Longtime User
Warning: This is a nit-pick and may not affect memory/performance off the app. I just like doing as little as possible in a loop (including variable declarations), since I've been raised on a "loops are expensive" mindset.

Also use parameterized queries (which you may already have, if you switched to batching). Every time you do this
B4X:
               sSQL = "UPDATE [IS_SYSTEM].dbo.devices_hello " & _
                       "SET " & _
                       "active_user = '', " & _
                       "active = 0, " & _
                       "last_user = '" & pubID & "', " & _
                       "last_user_exitcode = 2 " & _
                       "WHERE device_id = '" & devID & "'"
you are creating a new string. For small loops, this may be a non-issue. But since you seem to worry about memory constraints, this can add up (the garbage collector will take care of all those strings, but then you are putting more work on the garbage collector, which could impact performance). Please note this is a nit-pick and I'm only pointing it out since you seem to worry about memory usage.

B4X:
sql.AddNonQueryToBatch("UPDATE [IS_SYSTEM].dbo.devices_hello " & _
                       "SET " & _
                       "active_user = '', " & _
                       "active = 0, " & _
                       "last_user = '" & rs.GetString("public_id") & "', " & _
                       "last_user_exitcode = 2 " & _
                       "WHERE device_id = '" & rs.GetString("public_id_device") & "'",Null)
 
Upvote 0

OliverA

Expert
Licensed User
Close...
B4X:
   ' // 1st parameterized statement
   sSQL1 = "UPDATE [IS_SYSTEM].dbo.devices_hello " & _
                   "SET " & _
                   "active_user = '', " & _
                   "active = 0, " & _
                   "last_user = ?, " & _
                   "last_user_exitcode = 2 " & _
                   "WHERE device_id = ?"
                 
   ' // 2nd parameterized statement
   sSQL2 = "UPDATE [IS_SYSTEM].dbo.c_users " & _
                   "SET [IS_SYSTEM].dbo.c_users.hello_active = 0 " & _                   
                   "WHERE [IS_SYSTEM].dbo.c_users.public_id = ?"
   
   ' // reset all the devices that have timeout out
   Do While rs.NextRow
       doDelete = True
       devID = rs.GetString("public_id_device")
       pubID = rs.GetString("public_id")
               
       ' // update the device with the human id, and ste the exit code ot ttl.
       sql.AddNonQueryToBatch(sSQL1, Array(pubID, devID))
       ' // update the users record with non-active state
       sql.AddNonQueryToBatch(sSQL2, Array(pubID))
   Loop
   rs.close
   Dim SenderFilter As Object = sql.ExecNonQueryBatch("SQL")
   Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
   If (Success) Then
       If doDelete Then
           ' // now remove them from the presence
           ... ' Existing code here
       End If
   Else
       Log("Something went wrong with the batch update")
   End If
 
Upvote 0

Jmu5667

Well-Known Member
Licensed User
Longtime User
Close...
B4X:
   ' // 1st parameterized statement
   sSQL1 = "UPDATE [IS_SYSTEM].dbo.devices_hello " & _
                   "SET " & _
                   "active_user = '', " & _
                   "active = 0, " & _
                   "last_user = ?, " & _
                   "last_user_exitcode = 2 " & _
                   "WHERE device_id = ?"
                
   ' // 2nd parameterized statement
   sSQL2 = "UPDATE [IS_SYSTEM].dbo.c_users " & _
                   "SET [IS_SYSTEM].dbo.c_users.hello_active = 0 " & _                  
                   "WHERE [IS_SYSTEM].dbo.c_users.public_id = ?"
  
   ' // reset all the devices that have timeout out
   Do While rs.NextRow
       doDelete = True
       devID = rs.GetString("public_id_device")
       pubID = rs.GetString("public_id")
              
       ' // update the device with the human id, and ste the exit code ot ttl.
       sql.AddNonQueryToBatch(sSQL1, Array(pubID, devID))
       ' // update the users record with non-active state
       sql.AddNonQueryToBatch(sSQL2, Array(pubID))
   Loop
   rs.close
   Dim SenderFilter As Object = sql.ExecNonQueryBatch("SQL")
   Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
   If (Success) Then
       If doDelete Then
           ' // now remove them from the presence
           ... ' Existing code here
       End If
   Else
       Log("Something went wrong with the batch update")
   End If


Hi Oliver

What is the difference between using the parameter array and the way I have done it ?

Regards

John.
 
Upvote 0

OliverA

Expert
Licensed User
Back to the nit-pick. Mine uses one string (well two, sSQL1, sSQL2) your's actually creates a new string for each call to AddNonQueryBatch (since it is a "dynamic" string). BTW, you just gave me an idea for a Wish for the SQL library.
 
Upvote 0

Jmu5667

Well-Known Member
Licensed User
Longtime User
Back to the nit-pick. Mine uses one string (well two, sSQL1, sSQL2) your's actually creates a new string for each call to AddNonQueryBatch (since it is a "dynamic" string). BTW, you just gave me an idea for a Wish for the SQL library.

But each AddNonQueryBatch creates a string to be stored, does it not ? Since I am dynamically doing it I am not creating a local string within the sub. Am I wrong or it this a Java thing ? BTW, what the wish ?
 
Upvote 0

OliverA

Expert
Licensed User
Since I am dynamically doing it I am not creating a local string within the sub
Yes, and my wish is addressing this (I'll post a link here). Technically, AddNonQueryBatch will create different references to different strings for your code and different references to the same string(s) in my code. I hope that makes sense (especially since it is a nit-pick).
 
Upvote 0

Jmu5667

Well-Known Member
Licensed User
Longtime User
Yes, and my wish is addressing this (I'll post a link here). Technically, AddNonQueryBatch will create different references to different strings for your code and different references to the same string(s) in my code. I hope that makes sense (especially since it is a nit-pick).

Ah man, the string values change so a reference to the same string is not good ? Or am I missing something :confused:
 
Upvote 0
Top