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

[CR95]

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]

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 ?

 

[aeric]

ServerExampleNoMySQL

I think the purpose of this example is for beginner to understand the basic without needing knowledge of database. Once a beginner understands how it works then next step is to make an upgrade to use database which most web app is normally powered by.

 

[Hamied Abou Hulaikah]

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.

 

[CR95]

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)

 

[aeric]

If SQLite can do the job, why not? As you mentioned, the server only serve for very limited users in a small network.

 

[Magma]

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

 

[Magma]

May be using MQTT... Internet of Things... will be easier...

 

[Hamied Abou Hulaikah]

May be using MQTT... Internet of Things... will be easier...

Yes, it is easier and typical use for your case.

 

[tchart]

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.

 

[tchart]

jServer & SQLite [Multiple Request Stress Test]

Should I use SQLite as my web server database? This has come up before and last week it came up again in the "Chit Chat" forum. The question has already been answered here before and there are many arguments for and against using SQLite, especially when you have lots of users who may be reading...

www.b4x.com

Would you recommend SQLite?

Well, I intend to rewrite a small POS system for B4J. It will be used by small restaurants, so I don't expect a lot of competing readings and recordings, or a lot of data, and it will be used on a local network. My intention is to use SQLITE, to facilitate the installation, but I never used this...

www.b4x.com

MySql vs SQLite vs MongoDB

hi, i have started working on an eCommerce website for a customer. for the server-side i am planning to use b4j. my question is about the DB i should use. i am running everything on my own server so i was thinking of using a local DB like SQLite. is this a bad idea? the other 2 options are...

www.b4x.com

concurrent execution with JSERVER and SQLITE

I have a web server built with Jserver and Sqlite running on a 4 core processor. The application uses a single SQL object. A call to sql.ExecQuerySingleResult("PRAGMA journal_mode = wal") has been made, even though there are hardly any updates but basically reads from the database. The...

www.b4x.com

 

[CR95]

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 ?

 

[Magma]

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