B4A Library JdbcSQL - Directly connect to remote databases

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:

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

 

[jahswant]

@DonMafred Finally.

 

[DonManfred]

I´ll remove my library and i will use this Lib instead.
I also recoment it to anyone but keep the insecure in mind.

 

[incendio]

For a secure connection, we can use it via tunel, but for speed, I don't know if B4A has something like jServer in B4J. If it has, then speed is no problem anymore.

 

[Mashiane]

finally, is there a way this will be available for b4i?

 

[Erel]

This solution is based on the Java database drivers provided by the databases vendors. There are no similar drivers for iOS.

 

[keirS]

If you want to use JDBC on Android using a Resultset is the wrong approach IMO. Using a disconnected RowSet like the CachedRowSet is a far better choice as by design unlike a result set it will not send more requests for data to the server.




.

 

[asales]

I'm try to connect directly to a sqlite database in my pc, but don't works.

This is my modifications:

#AdditionalJar: sqlite-jdbc-3.20.0

Private driver As String = "org.sqlite.JDBC"
Private jdbcUrl As String = "jdbc:sqlite:C:\Projects\databases\mydb.db"
Private Username As String = ""
Private Password As String = ""

I get this error in unfiltered logs:

java.sql.SQLException: opening db: 'C:\Projects\databases\mydb.db': open failed: EROFS (Read-only file system)
    at org.sqlite.core.CoreConnection.open(CoreConnection.java:203)
    at org.sqlite.core.CoreConnection.<init>(CoreConnection.java:76)
    at org.sqlite.jdbc3.JDBC3Connection.<init>(JDBC3Connection.java:26)
    at org.sqlite.jdbc4.JDBC4Connection.<init>(JDBC4Connection.java:24)
    at org.sqlite.SQLiteConnection.<init>(SQLiteConnection.java:45)
    at org.sqlite.JDBC.createConnection(JDBC.java:114)
    at org.sqlite.JDBC.connect(JDBC.java:88)
    at java.sql.DriverManager.getConnection(DriverManager.java:179)
    at java.sql.DriverManager.getConnection(DriverManager.java:213)
    at anywheresoftware.b4j.objects.SQL.Initialize2(SQL.java:56)
    at anywheresoftware.b4j.objects.SQL$1.call(SQL.java:98)
    at anywheresoftware.b4j.objects.SQL$1.call(SQL.java:1)
    at anywheresoftware.b4a.BA$3.run(BA.java:451)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:422)
    at java.util.concurrent.FutureTask.run(FutureTask.java:237)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1112)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:587)
    at java.lang.Thread.run(Thread.java:818)

Any assistance on this would be helpful.

 

[DonManfred]

tabases\mydb.db': open failed: EROFS (Read-only file system)

the answer is given.....

Place it on a dir where the app has write rights

 

[OliverA]

Place it on a dir where the app has write rights

If this is called from an Android phone to access a SQlite database on the PC, then I don't think that is going to work. On Android, that path given most likely would cause a read only issue, no? The only way to access SQLite remotely that I know off of the top of my head would be jRDC2?

 

[asales]

I think I need to put the IP of the computer, but it did not work either (same message):

jdbc:sqlite://192.168.0.104:C:\Projects\databases\mydb.db

The dir has write rights, but the app don't connect to the IP.

 

[OliverA]

SQLite is not really a client server database such as PostgreSQL, MySQL, MSSql, etc. It is a local datastore that can be queried via SQL. The only way to connect to a remote SQLite database is through a form of drive/path mapping, but that even is not recommended by SQLite's website (https://sqlite.org/whentouse.html).

PS: This may need to be in a new thread, since it has nothing to do with jdbcSQL per se.

 

[asales]

SQLite is not really a client server database such as PostgreSQL, MySQL, MSSql, etc. It is a local datastore that can be queried via SQL. The only way to connect to a remote SQLite database is through a form of drive/path mapping, but that even is not recommended by SQLite's website (https://sqlite.org/whentouse.html).

PS: This may need to be in a new thread, since it has nothing to do with jdbcSQL per se.

Thanks!

 

[tigrot]

For a secure connection, we can use it via tunel, but for speed, I don't know if B4A has something like jServer in B4J. If it has, then speed is no problem anymore.

The issue is not data transfer. Is credential written in app's code. I have passed after this issue with a providing server. I ask credentials, they are are transfered in crypt format. Are stored in this format and are decrypted on the fly when needed.

 

[OliverA]

I also recoment it to anyone but keep the insecure in mind.

For a secure connection, we can use it via tunel

If supported by the JDBC driver and DB used, both sides can be configured for secure connection (no need for tunneling). That setup may be a pain and thus tunneling may be a "simpler" solution.

MySQL: https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-using-ssl.html
PostgeSQL: https://www.postgresql.org/docs/current/static/ssl-tcp.html
MSSQL: https://docs.microsoft.com/en-us/sql/connect/jdbc/using-ssl-encryption

 

[incendio]

If supported by the JDBC driver and DB used, both sides can be configured for secure connection (no need for tunneling). That setup may be a pain and thus tunneling may be a "simpler" solution.

MySQL: https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-using-ssl.html
PostgeSQL: https://www.postgresql.org/docs/current/static/ssl-tcp.html
MSSQL: https://docs.microsoft.com/en-us/sql/connect/jdbc/using-ssl-encryption

I use JDBC Firebird which is not support secure connection, so tunnel is the only way.

Pitty that B4A seem doesn't have something like jServer in B4J, so can't use connection pool for speed & stability.

 

[asales]

I use JDBC Firebird ...

Did you use JDBC Firebird in Android?
If yes, can you share an example?
I'm try to connect, but don't works. Thanks!

 

[incendio]

Did you use JDBC Firebird in Android?
If yes, can you share an example?
I'm try to connect, but don't works. Thanks!

No I don't.

Firebird for Android is not stable yet.
For Android, I use SQLite.

 

[ctd]

Dear Forum
I was trying to create a table with mysql jdbc driver but get the following error:
(MySQLSyntaxErrorException) com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''Export' (REF TEXT,DES TEXT,COBA TEXT,QUANTPHY INTEGER, USER TEXT, ZONE TEXT)' at line 1
Need help to fix this error.

 

[Erel]

Please start a new thread for this question. It is also useful to post the relevant code (even when you upload the project).