B4J Library Modded jRDC2 w/SQLite support and more

Attached you'll find a modified version of jRDC2 (source https://www.b4x.com/android/forum/t...ation-of-rdc-remote-database-connector.61801/) that supports SQLite as the datastore out of the box. Support for V1 has been removed and therefore this is not a drop in replacement for jRDC2. This is more meant for people starting new with jRDC2. Comments are welcome (pro and con). Other modifications are listed in the main file and are relisted here:

Note: The stored procedures add-on is technically unnecessary. Out of the box, jRDC2 can handle simple stored procedures (that just return a result set) via ExecuteQuery. If anyone is interested in fleshing this out, PM me. I would rely on you to be knowledgeable in stored procedures and have your own DB available to test the jRDC2 server against. Finally, you have to have patience with me, I'm slow.

'2020/05/28 Removed show-stopping bug in /test handler. Cannot close DB if it is SQLite.
'
'2020/04/24 Changes:
'
'Updated source to match updates/features of jRDC2 2.22
'
'Added ability to use "simple" stored procedures. Modified DBRequestManager to allow usage of
'new feature. This was done with the help of @Chris Guanzon.
'In config properties, set up SQL statement to call stored procedure
'
B4X:
sql.sLogin=CALL sLogin(?,?)
'
'Usage sample on client side (using updated DBRequestManager):
'
'
B4X:
Dim req As DBRequestManager = CreateRequest
Dim cmd As DBCommand = CreateCommand("sLogin", Array(EditText1.Text, EditText2.Text))
Wait For (req.ExecuteCall(cmd, 0, Null)) JobDone (j As HttpJob)
If j.Success Then
    req.HandleJobAsync(j, "req")
    Wait For (req) req_Result (res As DBResult)
    'work with result
    req.PrintTable(res)
Else
    Log("ERROR: " & j.ErrorMessage)
End If
j.Release
'

'******
'2017/10/30 Update
'******
'Main module changes:
'-------------------
'Added the logging of one of the IP addresses that the server is bound to. The method.
' to retrieve the IP address is similar to ServerSocket's GetMyIP method. It was adapted
' from https://issues.apache.org/jira/browse/JCS-40.
'Added ability to assign server to a specific IP address.

'RDCConnector changes:
'--------------------
'Added IPAddress configuration option. Allows to set the jRDC server's IP address. An un-bindable
' address will crash the server.
'Added HasIPAddress and GetIPAddress to access IPAddress configuration.
'Added support for SQLite. If the DriverClass contains SQLite (case insensitive), then jRDC
' is configured for SQLite usage. SQLite does not use pools. Pool/non-pool handling gleaned
' from DBM.bas module of ABMaterial.
'Added CreateFile configuration option. This option is used for the SQLite backend
' (if used). If set to True or set to 1, then the SQLite database will be created in
' case it does not exist yet. Any other settings are interpreted as False.
'Added PoolSize configuration option. This is the size of the pool that should be used
' for pooled JDBC databases. This option was gleaned from the DBM.bas module of ABMaterial.
'Modified GetCommand to return empty string ("") when no command found.
'Modified GetConnection to handle non-pool SQL connections (SQLite).

'RDCHandler changes:
'-------------------
'Modified Handler to not close the databas connection in case a pool is not used, else SQLite
' will not function properly
'Modified ExecuteQuery2 and ExecuteBatch2 to check cmd.Parameters for Null. This removed a
' Null pointer exception error message in case cmd.Parameters was Null. Also call
' ExecQuery/ExecNonQuery when no cmd.Parameters are given.
'Modified ExecuteQuery2 and ExecuteBatch2 to check for valid command. If no valid command found,
' return 500 error with proper response. This takes care of a syntax exception error for the DB.
' in case of MySQL it looks something like this:
' com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax;
' check the manual that corresponds to your MySQL server version for the right syntax to use near 'null' at line 1
'Modified Try/Catch block in ExecuteBatch2. Moved the code below the End Try that dealt with
' converting the DBResult object and sending it to the client into end of the Try block. This
' took care of an java.lang.IllegalStateException: WRITER exception caused by OutputStream when
' called after the Catch's SendError.
'Added Try/Catch block to ExecuteQuery2. This should prevent the following on the B4X client
' side: java.io.EOFException: Unexpected end of ZLIB input stream
'Removed all jRDC v1 functionality.

'Miscellaneous notes:
'-------------------
'The config.properties contains sql. directives that pertain to a jRDC version of the DBUtils
' demo.
'Per usual, please ensure you have the proper #AdditionalJar set for the database backend
' of your choice (even for SQLite).
 

Attachments

  • jRDCv2_mod_release_20200528.zip
    9.9 KB · Views: 287
  • DBRequestManager.bas
    4.7 KB · Views: 240
Last edited:

inakigarm

Well-Known Member
Licensed User
Thanks Oliver; I'm working this days on a project that uses jRDC2 in a client -server scheme (Sqlite on local and Sqlite on server, synchronising the data on server with clients data)
One mod I've done on jRDC2 is retrieving the sql commands from a sqlite db also (configProperties.db) because:
- It's easier to modify/add sql commands from an helper application than the config.Properties file ( and as I'm not a very good programmer always I'very to modify the projected application in one way or another
- Gets the project 'tidy' and more understandable
- It'easier to apply the new added commands without the need of resetting the JRDC server application

If you want, I can send you the code (obviously, it's not a complex mod)
 

OliverA

Expert
Licensed User
Specifically for SQLite to work correctly in a concurrent environment you must:
- Set the journal mode to wal.
- Use a single SQL connection initialized with InitializeSQLite.
Both have been accounted for.
 

OliverA

Expert
Licensed User
and as I'm not a very good programmer always I'very to modify the projected application in one way or another
Same here. Both my "contributions" are based on already posted projects (Universal DBUtils and now this).
One mod I've done on jRDC2 is retrieving the sql commands from a sqlite db
Ha, I was going to take a stab at this next (a means of updating the SQL statements without restarting server).
If you want, I can send you the code
That would be great!
 

Diceman

Active Member
Licensed User
Oliver, I may have discovered a bug, or maybe I'm missing something. Can you shed some light on it?

1) In HandleJobAsync the CallSubDelayed2() gets executed but the sub "req_result" is never executed. I have a breakpoint inside of "req_result" and a log statement and they never execute. I thought maybe it can't find the sub so I added an "if statement" to make sure the sub "result_req" exists before the CallSubDelated2() statement is executed.

2) I had to put the "req_result" sub in jRDC2Utils because that is where mTarget is pointing to. The debugger shows mTarget.Name = "b4j.example.jrdc2utils". I would have thought req_result would belong in Main. I have one in there too, and it can't find it there either.

Do you have any idea why req_result() sub is never executed? BTW, req_result() sub is also called in jRDC2Utils.ExecuteQuery but the code in req_result() never executes there either. I tried both Debug and Release modes.

TIA

B4X:
'This is the code from DBRequestManager

Public Sub HandleJobAsync(Job As HttpJob, EventName As String)
    Dim ser As B4XSerializator
    Dim data() As Byte = Bit.InputStreamToBytes(Job.GetInputStream)
    ser.ConvertBytesToObjectAsync(data, "ser")
    Wait For (ser) ser_BytesToObject (Success As Boolean, NewObject As Object)
    If Success = False Then
        Log("Error reading response: " & LastException)
        Return
    End If
    Dim res As DBResult = NewObject
    res.Tag = Job.Tag
    If SubExists(mTarget, EventName & "_result") Then
        Log("Calling " & EventName & "_result")     '<-- This line gets executed and is shown in log  
        CallSubDelayed2(mTarget, EventName & "_result", res)    '<-- Line gets executed but sub is never executed (log stmt or breakpoint in "req_results" never executed)
    Else
        Log("Sub Does Not Exist: " & EventName & "_result")
    End If
End Sub

B4X:
'This is the sub in jRDC2Utils that never executes.

public Sub req_result(aDbResult As DBResult)
    Log("[jRDC2Utils.req_result] EXECUTED")
End Sub
 

OliverA

Expert
Licensed User
I'm going to need more code from you to see what you are doing code wise (how you use DBRequestManager and/or JRDC2Utils) before I can give you an answer. As is, JRDC2Utils wraps DBRequestManager and handles the interception of the _result callback via Wait For(s).
 

Diceman

Active Member
Licensed User
I'm going to need more code from you to see what you are doing code wise (how you use DBRequestManager and/or JRDC2Utils) before I can give you an answer. As is, JRDC2Utils wraps DBRequestManager and handles the interception of the _result callback via Wait For(s).

So where should the user's actual "req_result" sub be defined? I haven't been able to get any req_result() sub to be executed whether it is defined in Main or jRDC2Utils. Is it operator error (me?) or a problem with the code? I define events all the time for the classes I define, but I never had to use "Wait For..." when calling an event.

I have added tags "##:" throughout the client that explains the problem.
There is No rush at looking at it. It is NOT holding me up. Whenever you get around to it. Thanks.
 

Attachments

  • jRDCv2_Client_EventProblem_B4J.zip
    10 KB · Views: 337

OliverA

Expert
Licensed User
So where should the user's actual "req_result" sub be defined?
When you use the jRDC2Utils module, you do not have to define, nor worry about "req_result". It is taken care of by the ExecuteQuery method of the jRDC2Utils module and this method returns the DBResult object that is returned by DBRequestManager's HandleJobAsync method. If you want to take care of the results yourself that are returned by HandeJobAsync, then
1) Use ExecuteQuery2 of the jRDC2Utils module, which allows you to provide a caller object and method name to execute after retrieving the results (technically it's just a wrapper of ExecuteQuery)
or
2) Don't use jRDC2Utils, but just follow the examples given by @Erel's post on jRDC2 client usage (https://www.b4x.com/android/forum/t...ation-of-rdc-remote-database-connector.61801/).

Answer to comments on the code you supplied:
You're going to hate me for mentioning this but, :)
How is this going to work if calling rtn Main, is accessing more than one server? (rdcLink will have the value of the last Initialize rdcUrl parameter).
It is common to use more than one server. A server that interacts with the user (client) and this server would get the data from another server. Or backup the data on the server to another server.
One solution (preferred) would be to make jRDC2Utils a class (jRDC2Mgr?). This would solve the Event problem and allow you to add properties if needed at a later date.
The 2nd solution would be to pass the rdcLink as a parameter in each of your SQL calls as well as the event name (see "##:?" below).
Nope, no hate from me. Yes, as it stands, jRDC2Utils works with one server and you provided two means it could be changed to handle more. My only comment here is that no event name is necessary, since jRDC2Utils either 1) takes care of that or 2) allows you to specify caller/method in the ExecuteQuery2 method.
' Dim selectedRowValue As Object = TableView1.SelectedRowValues(0) '##:BUG
'##:Fix 2018-07-14 If no row is selected, selectedRowValue is NULL
Dim selectedRowValue As Object=Null 'Fix
If selectedRow >= 0 Then 'Fix
selectedRowValue = TableView1.SelectedRowValues(0) 'Fix
End If 'Fix
Strange. At what point is ValueChanged method called without having a row selected? Please note since my code is pretty much a straight rip of the DBUtils example, that bug would also be manifest there.

Note: Technically all these discussions should be happening at the jRDC2 client example that uses this code (https://www.b4x.com/android/forum/threads/jrdc2-client-example-using-modded-jrdc2.85581/).
 

Diceman

Active Member
Licensed User
OliverA, thanks for your reply. I will try your suggestions and get back to you with the results.
 

Diceman

Active Member
Licensed User
When you use the jRDC2Utils module, you do not have to define, nor worry about "req_result". It is taken care of by the ExecuteQuery method of the jRDC2Utils module and this method returns the DBResult object that is returned by DBRequestManager's HandleJobAsync method. If you want to take care of the results yourself that are returned by HandeJobAsync, then
1) Use ExecuteQuery2 of the jRDC2Utils module, which allows you to provide a caller object and method name to execute after retrieving the results (technically it's just a wrapper of ExecuteQuery)
or
2) Don't use jRDC2Utils, but just follow the examples given by @Erel's post on jRDC2 client usage (https://www.b4x.com/android/forum/t...ation-of-rdc-remote-database-connector.61801/).

Ok, I don't know how I missed seeing the sub ExecuteQuery2 that has an event parameter. Thanks.

Answer to comments on the code you supplied:

Nope, no hate from me. Yes, as it stands, jRDC2Utils works with one server and you provided two means it could be changed to handle more. My only comment here is that no event name is necessary, since jRDC2Utils either 1) takes care of that or 2) allows you to specify caller/method in the ExecuteQuery2 method.

I will do some rudimentary benchmarking to see if changing jRDCUtils2 into a class causes any speed or memory problems if used on the server. I'm thinking of having one server call one or more other servers to get more data. It may be simpler just to pass the parameters to the jRDC2 code module so garbage collection is easier.

Strange. At what point is ValueChanged method called without having a row selected? Please note since my code is pretty much a straight rip of the DBUtils example, that bug would also be manifest there.

I have included a couple of screenshots that may help. It is a trivial fix.

Note: Technically all these discussions should be happening at the jRDC2 client example that uses this code (https://www.b4x.com/android/forum/threads/jrdc2-client-example-using-modded-jrdc2.85581/).

Correct. I redownloaded these 2 files this morning and retested.

Thanks again for your help. I'm up and running again. :)

ClientInstructions.png ClientException.png
 

OliverA

Expert
Licensed User
See post#1 for details. Short recap:
1) Removed show-stopping bug
2) Updated to match v2.22 of jRDC2
3) Started to implement ability to call stored procedures
 
Last edited:

rgarnett1955

Active Member
Licensed User
Hi All,

I am using Oliver's revised jRDC.

I made a small mod to it so I can start multiple instances with a different port using a command line argument for the port:

jRDCV2 Command Line Arg For Port:
Sub AppStart (Args() As String)
    Try
        portArgStr = Args(0)
    Catch
        portArgStr = "17175"
    End Try
    
    If IsNumber(portArgStr) Then
        portNumGlobal = portArgStr
        If portNumGlobal < 0 Or portNumGlobal > 65535 Then
            Return
        End If
    End If...

I should probably include code to check that the port requested is not in use and return an error if it is.

I start each instance with the following code from the Windows Task Scheduler at system boot with a 2 to 5 minute delay on start to allow the network time to connect.


Startup Command String:
java -jar jRDC_sqLite.jar 17178

of course I set the start directory to the directory of the jRDC_sqLite.jar.

The xml export of the task is shown below
<Task version="1.4">
<RegistrationInfo>
<Date>2020-06-01T14:36:41.5892505</Date>
<Author>ROBSDESKTOP02\Admin</Author>
<Description>
Provides Remote Data Access to psmdb_winSrv.db Port: 17178 Uses predefined queries in config file.
</Description>
<URI>\PowerMonitorServers\Java RDC Server Port 17178</URI>
</RegistrationInfo>
<Triggers>
<BootTrigger>
<Enabled>true</Enabled>
<Delay>PT10M</Delay>
</BootTrigger>
</Triggers>
<Principals>
<Principal id="Author">
<UserId>S-1-5-21-3571400722-621192633-461336673-1002</UserId>
<LogonType>Password</LogonType>
<RunLevel>LeastPrivilege</RunLevel>
</Principal>
</Principals>
<Settings>
<MultipleInstancesPolicy>Parallel</MultipleInstancesPolicy>
<DisallowStartIfOnBatteries>false</DisallowStartIfOnBatteries>
<StopIfGoingOnBatteries>true</StopIfGoingOnBatteries>
<AllowHardTerminate>true</AllowHardTerminate>
<StartWhenAvailable>false</StartWhenAvailable>
<RunOnlyIfNetworkAvailable>false</RunOnlyIfNetworkAvailable>
<IdleSettings>
<StopOnIdleEnd>true</StopOnIdleEnd>
<RestartOnIdle>false</RestartOnIdle>
</IdleSettings>
<AllowStartOnDemand>true</AllowStartOnDemand>
<Enabled>true</Enabled>
<Hidden>false</Hidden>
<RunOnlyIfIdle>false</RunOnlyIfIdle>
<DisallowStartOnRemoteAppSession>false</DisallowStartOnRemoteAppSession>
<UseUnifiedSchedulingEngine>true</UseUnifiedSchedulingEngine>
<WakeToRun>false</WakeToRun>
<ExecutionTimeLimit>PT0S</ExecutionTimeLimit>
<Priority>7</Priority>
</Settings>
<Actions Context="Author">
<Exec>
<Command>java</Command>
<Arguments>-jar jRDC_sqLite.jar 17178</Arguments>
<WorkingDirectory>
W:\MyProgramFiles_W\PowerMonProgs\pmonDB_uServerJRDC\jRDC_sqLite\jRDCv2_sqLite\Objects\
</WorkingDirectory>
</Exec>
</Actions>
</Task>

Doing this makes each server independent and able to run in a separate windows task. Of course you should only allow a single instance to make database updates, deletes, updates and inserts, but queries that only read data will work OK.

Can anyone see why this idea might be a bad one and/or there might be better methods?

Can someone send me a copy of the "on jRDC2 is retrieving the sql commands from a sqlite db" alluded to above by inakigarm?

Best regards

Rob
 

OliverA

Expert
Licensed User
queries that only read data will work OK.
If that is your sole intent, then i think multiple instances are not needed. Each request to jRDC2 starts a new thread. That, combined with pooling, is the pretty much the same as running multiple instances. Plus you are not using up additional ports on the machine. And you don't have to worry about if your request need to be only SELECT queries or if you cans sent data modifying commands.
 

Diceman

Active Member
Licensed User
I should probably include code to check that the port requested is not in use and return an error if it is.
I start each instance with the following code from the Windows Task Scheduler at system boot with a 2 to 5 minute delay on start to allow the network time to connect.

Rob,

I'm not sure why you are going to all this trouble of having more than one jRDC2 server running on the same computer.

1) Are you doing this so you can have different jRDC2 servers running on the same machine and each server would handle requests for a different application (like one server for PoS and the other server for displaying stock quotes)?

2) Or is the code in each jRDC2 server identical except for the port, so both servers are handling requests from the same type of application?

If #1 then it would allow you to save money and put 2 servers on the same machine to start with. Then if it gets too slow later on just move one of the servers to another computer.

If #2 then there is no benefit because jRDC2 is multi-threaded and can handle dozens (hundreds?) of requests simultaneously.

Doing this makes each server independent and able to run in a separate windows task. Of course you should only allow a single instance to make database updates, deletes, updates and inserts, but queries that only read data will work OK.

This is true if both servers are accessing the same Sqlite database (#2). But if each jRDC2 server is accessing different Sqlite databases (#1) then you can do updates simultaneously because they are separate databases. Or if you are using PostgreSQL then you can have as many servers accessing the same database at the same time (#2) because it has better record locking than Sqlite.
 

rgarnett1955

Active Member
Licensed User
Rob,

I'm not sure why you are going to all this trouble of having more than one jRDC2 server running on the same computer.

1) Are you doing this so you can have different jRDC2 servers running on the same machine and each server would handle requests for a different application (like one server for PoS and the other server for displaying stock quotes)?

2) Or is the code in each jRDC2 server identical except for the port, so both servers are handling requests from the same type of application?

If #1 then it would allow you to save money and put 2 servers on the same machine to start with. Then if it gets too slow later on just move one of the servers to another computer.

If #2 then there is no benefit because jRDC2 is multi-threaded and can handle dozens (hundreds?) of requests simultaneously.


This is true if both servers are accessing the same Sqlite database (#2). But if each jRDC2 server is accessing different Sqlite databases (#1) then you can do updates simultaneously because they are separate databases. Or if you are using PostgreSQL then you can have as many servers accessing the same database at the same time (#2) because it has better record locking than Sqlite.


Hi Diceman,

Thanks for your comments.

I didn't realise that jRDC2 is multi-threaded. I am going to add the config filename as an arg. so that the server can be easily connected to different databases and queries when starting new instances. So I might have a connection to sqLite, SQL Server PostgreSQL or MS Access etc, using instances of the same app but a different config file.

Having more than one server isn't difficult. I start them at system boot using the Task Scheduler. Also having more than one enables me to kill one of them whilst doing mods and leaving the others going.

In addition I can use one port for local connection with firewall blocking of this port whilst providing a subset of the data to remote computers on another port with different queries with the firewall enabled for this port. With sqLite I would only let one of the ports do inserts, updates and deletes.

This was my thinking.

The reason I asked about this was to see if there were any absolute no no's doing this.

Again thanks for your comments they have been very helpful.

Best regards

Rob
 

OliverA

Expert
Licensed User
Top