B4J Question The problem that multiple user on the web server store data in the database at the same time!

[Jones Hone]

A b4j web server(jetty server). A web page for users to place an order.
After the server receives the order, it will be stored in the SQLite database.
If 30~50 users place an order at the same time.
Excuse me,
At this time, what will happen to the order save to database?
For example: transaction conflict, save failure, etc.
Or will it save all the data correctly in sequence.

 

[EnriqueGonzalez]

For example: transaction conflict, save failure, etc.
Or will it save all the data correctly in sequence.

SQLite uses a blocking approach. 5 users can read at any given time. 1 user can write at any given time but while he does the others will have to wait.

Somewhere on forum there is the code to do this look for the keyword pragma.

Now 50 concurrent calls at the "same" time is not that many. They will not occurr in the same second (consider that the blockings happens in milliseconds) if they happen in a span of a minute you are good.

The more users impact the database you will feel slowness on the responses of the server. You should use a real concurrent database if you want to avoid that scenario.

 

[EnriqueGonzalez]

If you are not ready to use a big db you can start with h2.

H2 Database

This is a very quick tutorial on the H2 database - https://www.h2database.com/html/main.html The main features of H2 are: Very fast, open source, JDBC API Embedded and server modes; in-memory databases Browser based Console application Small footprint: around 2 MB jar file size Other...

www.b4x.com

It will be better than SQLite and it is fully compatible with postgres as the post says

 

[EnriqueGonzalez]

More on the topic of SQLite:

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

 

[Jones Hone]

Thank you for providing a lot of useful information.
So it will archive all the data correctly in order.
I don't want it to be fast, just write all the data correctly!

 

[hatzisn]

Thank you for providing a lot of useful information.
So it will archive all the data correctly in order.
I don't want it to be fast, just write all the data correctly!

Why don't you use SQL server? Search in the forum for jtds-1.3.1.

 

[EnriqueGonzalez]

jtds-1.3.1.

Whaaaat nooooo, jtds hasn't been updated since 2003, please don't recommend it, you have to use the Microsoft official drivers.

 

[hatzisn]

Whaaaat nooooo, jtds hasn't been updated since 2003, please don't recommend it, you have to use the Microsoft official drivers.

I have tried jtds and works fine both in windows and linux. Are there any security issues that point out that it is better to use the Microsoft JDBC driver? The owner of a business I used to work for in the past used to say that: "Does it work? If it works we do not change it...". My only concern are the security issues...

 

[EnriqueGonzalez]

I have tried

If you want to use it, that's fine, totally your choice. But don't suggest to new users to use deprecated technology.

My only concern are the security issues...

How would yo know, if there aren't even someone to reach it to?? If someone finds a security issue who is reporting it to? Who will fix it? I think 20 years is more than enough to find a couple

 

[hatzisn]

How would yo know, if there aren't even someone to reach it to?? If someone finds a security issue who is reporting it to? Who will fix it? I think 20 years is more than enough to find a couple

This is correct and on the other hand it is not at the same time. This is because I suppose that during the transition period from jtds to Microsoft JDBC there would have been some articles in StackOverflow or CodeProject or anything like that pointing out the security issues in favour of the Microsoft JDBC and Google would certainly know it. The last build was built before 9 years so there would be some recent (relatively) articles.

Anyway I tried to download it from here:

Download - JDBC Driver for SQL Server

Download the Microsoft JDBC Driver for SQL Server to develop Java applications that connect to SQL Server and Azure SQL Database.

learn.microsoft.com

Then I tried to integrade it but I cannot find the driver name. I tried to reverse engineer the jar file and found a class com.microsoft.sqlserver.jdbc (but cannot look inside it) which when I insert it in

Dim sql As SQL
sql.Initialize("com.microsoft.sqlserver.jdbc", sConnectionString)

I get an error response that class was not found. Can you write an example code on how to initiate the connection?

 

[EnriqueGonzalez]

Then I tried to integrade it but I cannot find the driver name

com.microsoft.sqlserver.jdbc.SQLServerDriver

Can you write an example code on how to initiate the connection?

url must be:
jdbc:sqlserver://iport;databaseName=database;encrypt=false
jdbc:sqlserver://192.168.1.10:1433;databaseName=master;encrypt=false

hen I tried to integrade it but I cannot find the driver name.

Using the JDBC driver - JDBC Driver for SQL Server

This section provides quick start instructions to make a simple connection to a SQL Server database using the Microsoft JDBC Driver for SQL Server.

learn.microsoft.com

Some (i mean why even risk):

 

[tchart]

As @EnriqueGonzalez said there are many discussions on the forum regarding using SQLite.

There is nothing wrong with SQLite. There is no reason to have to install/configure/maintain a full relational database, it is another piece of software you have to maintain. If you dont believe me read the thread and plus why would Pocketbase choose it - https://pocketbase.io/faq/

Even without optimizations, PocketBase can easily serve 10 000+ concurrent and persistent realtime connections on a cheap $6 VPS (Hetzner 2vCPU, 4GB RAM).

I have many customers using my apps in production with SQLite and large databases. I deliberately chose SQLite since know from previous experience that instruction your client to troubleshoot a SQL database is just a big problem.

As the thread below mentions, you will likley never encounter any issues unless you magically scale overnight.

The Java SQLite driver queues transactions so unless you have a long running transaction the clients/users will never know. Best practice is to enable WAL mode and have two connections - one read only and the other write only - this will mean long running reads will not block writes.

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

 

[OliverA]

My only concern are the security issues...

If you are really concerned about security, then you will use properly signed certificates with MS SQL Server and use TLS between the server and your client application. For that, and for the latest security, you'll need the Microsoft JDBC drivers. That's just one piece of the puzzle for SQL security. I see so many people here on the forum using SQL Server and then either turning transport security off or changing their code to accept any certificate (because they are using self-signed certificates), which amounts to almost having no transport-level security at all.