SQL – Structured Query Language, is a standard language to access relational databases.
Practically, the term can also used to define a database that supports SQL.
SQL databases come in many different sizes and configurations. Starting from single file, server-less databases, such as SQLite and up to scalable databases servers such as MySQL or MS SQL Server.
Android and iOS have built-in support for SQLite databases.
There are two types of SQL libraries in B4X:
B4A SQL and B4i iSQL, are libraries that work with the built in SQLite engine.
B4J jSQL and B4A JdbcSQL libraries are wrappers for Java JDBC API. They can work with any database engine, assuming that there is a supported JDBC driver.
B4J is preinstalled with a SQLite JDBC driver, which makes it very simple to use SQLite with all three platforms.
If you need a local database for your app then there is no doubt that you should choose SQLite.
Connecting to a remote database server requires a bit more thinking.
Directly connecting to a remote database from an Android device, using JdbcSQL, is far from being ideal. It is not secure, difficult to maintain, not scalable and can be difficult to implement properly.
jRDC2, is a middleware HTTP server, that solves these issues. The client devices connect to the jRDC2 server and jRDC2 server connects to the database. It is quite simple to configure and the performance is excellent.
jRDC2 tutorial: https://www.b4x.com/android/forum/threads/61801/#content
The libraries discussed above include both synchronous and asynchronous methods. Both are simple to use. If working with remote database then only use the async methods.
Async methods tutorial: [B4X] SQL with Wait For
Tip: the number one cause for performance issues related to SQL is when inserting many records without creating a transaction (not needed if using the async methods).
If connecting to a remote database from a B4J client and without using jRDC2 then use a connection pool (jServer or jConnectionPool). This will save you many efforts required to maintain a valid connection.
Video tutorial about SQL + relational databases: