B4J Library Modded jRDC2 w/SQLite support and more

Erel · Oct 31, 2017

I haven't checked the code. Make sure to go over these instructions: https://www.b4x.com/android/forum/t...ent-access-to-sqlite-databases.39904/#content

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.

inakigarm · Oct 31, 2017

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 · Oct 31, 2017

Erel said:
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 · Oct 31, 2017

inakigarm said:
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).

inakigarm said:
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).

inakigarm said:
If you want, I can send you the code

That would be great!

Johan Hormaza · Jul 1, 2018

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

I would appreciate if you send it to me too

Diceman · Jul 13, 2018

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 · Jul 13, 2018

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 · Jul 14, 2018

OliverA said:
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.

OliverA · Jul 23, 2018

Diceman said:
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 · Jul 25, 2018

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

Diceman · Jul 25, 2018

OliverA said:
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.

OliverA said:
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.

OliverA said:
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.

OliverA said:
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.

OliverA · May 28, 2020

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

rgarnett1955 · Jun 2, 2020

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 · Jun 2, 2020

rgarnett1955 said:
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 · Jun 2, 2020

rgarnett1955 said:
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 · Jun 5, 2020

Diceman said:
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

Mashiane · Jul 16, 2021

Hi there. Has this been tried with a password protected sqlite database?

If so what are the lessons learned, I seem to not be winning at the moment.

Thanks.

OliverA · Jul 16, 2021

Mashiane said:
Has this been tried with a password protected sqlite database?

What version on encrypted SQLite are you using?

Mashiane · Jul 16, 2021

Hi OliverA

Im not sure about the "version", but I had chosen the RC4 and managed to open the db with the tool. Im just not sure how to set it up for the connection via the jRDC code.

Perhaps if you have some knowledge on how best to do it. Thanks.

B4J Library Modded jRDC2 w/SQLite support and more

Attachments

B4X founder

Well-Known Member

Expert

Expert

Well-Known Member

Active Member

Expert

Active Member

Attachments

Expert

Active Member

Active Member

Expert

Active Member

Expert

Active Member

Active Member

Expert

Expert

Expert