B4J Question Need for understanding : best approach to connect to SQLite

CR95

Active Member
Licensed User
I developed a solution with B4A <-> HTML <-> jRDC2 running on Linux <-> SQLite on Linux too
and it works fine (Thanks to all the forum's providers of classes and examples and explanations).

I would like to replace HTML by WebSocket for implementing additional features.
In the B4J Tutorial [WebApp] Web Apps Overview, there is an example in the ServerExampleNoMySQL showing a connection to the WSDBUtilClass which seems simpler to develop and to use :
- No DBRequestManager and RequestsManager in B4A
- No external config.Properties file in B4J

Please could you explain why there are these differences between these two "approaches" ?
And which is the best for a WebSocket based implemmentation ?
Thanks
 

Magma

Expert
Licensed User
Longtime User
I developed a solution with B4A <-> HTML <-> jRDC2 running on Linux <-> SQLite on Linux too
Hi there... Reading from first line, something not "sounds" good to me... You are JRDC2 and SQLite... yes possible... but will be better using mariadb or mysql, or sql server... because of their features and their power to have more users connected the same time...

Now using websocket.. you want to make it, more web-app, with no need of client-app (desktop or android) ... need only html5 browser ?

What is your scope ?
 
Upvote 0

Hamied Abou Hulaikah

Well-Known Member
Licensed User
Longtime User
I developed a solution with B4A <-> HTML <-> jRDC2 running on Linux <-> SQLite on Linux too
and it works fine (Thanks to all the forum's providers of classes and examples and explanations).

I would like to replace HTML by WebSocket for implementing additional features.
In the B4J Tutorial [WebApp] Web Apps Overview, there is an example in the ServerExampleNoMySQL showing a connection to the WSDBUtilClass which seems simpler to develop and to use :
- No DBRequestManager and RequestsManager in B4A
- No external config.Properties file in B4J

Please could you explain why there are these differences between these two "approaches" ?
And which is the best for a WebSocket based implemmentation ?
Thanks
Before diving in your request: You must rethink again about your strategy .. SQLITE server side is not good, one of the worst situation is when 2 or more concurrent users try to connect to your app, sqlite will be locked for only first connected user, other users got connection error! also other problem you'll get such as sqlite file permanent corruption.
 
Upvote 0

CR95

Active Member
Licensed User
Thanks for your replies

I chose SQLite because it seemed easy to setup ! I found several samples using it in the forum. In my personal case, nomber of users will be 2 (my wife and me) with 1 phone and 1 tablet each, plus sometimes my children...
Later, Hamied says : SQLite does not support concurrent accesses. This is surprising me.
Is SQLite really a bad choice ???

@Magma :
Now using websocket.. you want to make it, more web-app, with no need of client-app (desktop or android) ... need only html5 browser ? What is your scope ?

This is not really a web app but more a client-server. My scope : the phone (or tablet) are used to send B4A "commands" to the B4J server which pilots at the beginning the home cinema system (a Raspberry replaces 6 IR remote boxes). Then, I added a hard disk and the SQLite database for managing adresses of videos, mp3, photos.. and return them to the phones or tablets (but also to the central HomeCinema).
I want now to add controls of home systems which need to "recall" the user(s). For example : somebody is ringing at the door. I understood that WebSocket was appropriate for managing these non continuous dialogs.
To be honest, for the moment, there is only one user. So, i did not verify concurrent accesses.

@aeric
I think the purpose of this example is for beginner to understand the basic without needing knowledge of database.
It was also my feeling, but with a few lines, it makes the job ! my need is similar : to sort/filter/show titles/parameters of videos/MP3/Photos (there was a huge work before in order to "normalize" and attach these parameters to the files). The videos/MP3/photos files are NOT in the database (only the disk adress of the file)
 
Upvote 0

Magma

Expert
Licensed User
Longtime User
..SQLite does not support concurrent accesses. This is surprising me.

Yes, he is right... but if only for home-family ---> Home cinema ---> 2-3 phones to a server... you will not have any big problem... from the other hand... changing to mariadb not big deal... is only setup at server side... the sql commands are the same.. also the use of jrdc...

I want now to add controls of home systems which need to "recall" the user(s). For example : somebody is ringing at the door. I understood that WebSocket was appropriate for managing these non continuous dialogs.
...Hmmm... i don;t understand how websocket will be useful for that...
 
Upvote 0

tchart

Well-Known Member
Licensed User
Longtime User
Before diving in your request: You must rethink again about your strategy .. SQLITE server side is not good, one of the worst situation is when 2 or more concurrent users try to connect to your app, sqlite will be locked for only first connected user, other users got connection error! also other problem you'll get such as sqlite file permanent corruption.
This is actually not correct.

There are several posts on the forum that prove that SQLite can handle tens of thousands of requests per second and handle many simultaneous connections. Locking only occurs when you are writing data and even then it is queued so if two users are writing at the exact same time there will be no error - this is a known feature of the JDBC driver.

As @aeric said it is much less admin overhead using SQLite and unless you have to scale to support tens of thousands of users (concurrent) then SQLite is actually very fit for purpose. Unless you have some magic unicorn app that is wildly popular you won’t get tens of thousands of concurrent users.

There are of course other embedded databases that support better functionality such as H2 that support a true server connection model if you really need that.

Maria, Postgres, SQL Server etc would be way down the list in my experience.
 
Upvote 0

tchart

Well-Known Member
Licensed User
Longtime User



 
Upvote 0

CR95

Active Member
Licensed User
Thanks to all.
My feeling is that SQLite is enough for the use I have with it. However, I will think to move to another SGBD.

Even if your answers are very valuable, I still don't know why there are so much differences between the minimum code in the ServerExampleNoSQL (connection to the WSDBUtilClass) and a "heavy" JRDC2 implementation ?

@Magma
i don;t understand how websocket will be useful for that...
Please could you tell me what you will use for communating at any time with a client application (not a browser) at the initiative of the server, knowing that I don't want to setup MQTT ?
 
Upvote 0

Magma

Expert
Licensed User
Longtime User
...If I understand your answer/question (because my poor english don't help), you don't want to "install" MQTT server at your local-server? (is simple)... but if you don't want to install, you can use free cloud MQTT server for that... and creating a client MQTT (targeting cloud MQTT server) for the server too..

So with this option you will have the feature of "remote" control your devices... ofcourse with lower speeds...

I don't know if streaming or downloading from server to clients videos/music ---> that option will stay (using apache, or ffmpeg for streaming)
But the control (simple commands) ----> can be from MQTT
 
Upvote 0
Top