B4A Library JdbcSQL - Directly connect to remote databases

Status
Not open for further replies.
This is a port of B4J jSQL library.
The SQL type was renamed to JdbcSQL and ResultSet was renamed to JdbcResultSet, this allows using it together with the SQL library.

SQL library - local SQLite databases.
JdbcSQL - Any remote SQL database with a supported JDBC driver. MySQL and MS SQL are supported. Others are also supported.

The recommended method to connect to remote databases is jRDC2: https://www.b4x.com/android/forum/threads/61801/#content

Connecting to the database directly has several disadvantages:

- Insecure - it will be quite simple for a hacker to get the username and password and directly access the database.
- Hard to maintain - changes to the database design will require updating the app.
- Server and client performance issues - The Jdbc drivers are not optimized for mobile usage.
- Reliability issues due to unstable connectivity.

I don't recommend using it in a consumer app. However if you are creating a small in-house solution then it might be a good fit.

As explained in the jSQL tutorial, you need to copy the jdbc jar to the additional libraries folder and add a reference with #AdditionalJar.
For example:
B4X:
#AdditionalJar: mysql-connector-java-5.1.34-bin
Note that you should use Java 7 jars. MySQL connector: https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.34.tar.gz
MS SQL: http://jtds.sourceforge.net/

As we are connecting to a remote database, all requests must be asynchronous. It is quite simple to make asynchronous requests with the resumable subs feature: [B4X] SQL with Wait For

Don't expect the remote SQL connection to stay valid indefinitely. A good practice is to make a new connection when needed and close it when done.

It is still a good idea to disable the network on main thread check as iterating over large result sets can cause more requests to be sent (DisableStrictMode in the example code).

An example is attached. The database code is in the Starter service. The example code requires B4A v7.3+.

If you are testing it with a local PC (with WAMP for example) then you need to open the relevant port in Windows firewall.
 

Attachments

  • JdbcSQL.zip
    13.6 KB · Views: 3,594
  • JdbcSQLExample.zip
    8.1 KB · Views: 3,771

Ivan Aldaz

Member
Licensed User
Hi, I'm trying to use JdbcSQL library to manage Firebase Database service (for an online scoreboard), but don't know if it's possible and, if so, which JDBC driver do I have to use.
 

DonManfred

Expert
Licensed User
Hi, I'm trying to use JdbcSQL library to manage Firebase Database service

1. You should always start a new thread for your questions.
2. You can not connect to a Firebase Database (say it RealtimeDatabase or Firebase Firestore') using an JDBC Driver. The Databases are different.
 

catyinwong

Active Member
Licensed User
Not quite understand what changes should be made if I want to connect to a MSSQL

#AdditionalJar: jtds-1.3.1

Public mysql As JdbcSQL
Private driver As String = "com.mysql.jdbc.Driver" <- what to change??
Private jdbcUrl As String = "jdbc:mssql://192.168.0.6/test"
Private Username As String = "username"
Private Password As String = "password"
 
Status
Not open for further replies.
Top