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: 5,446
  • JdbcSQLExample.zip
    8.1 KB · Views: 5,624

DonManfred

Expert
Licensed User
Longtime User
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

Well-Known Member
Licensed User
Longtime User
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.
 

keirS

Well-Known Member
Licensed User
Longtime User
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

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

This is my modifications:
B4X:
#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:
B4X:
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.
 

OliverA

Expert
Licensed User
Longtime User
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

Expert
Licensed User
Longtime User
I think I need to put the IP of the computer, but it did not work either (same message):
B4X:
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

Expert
Licensed User
Longtime User
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

Expert
Licensed User
Longtime User
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

Well-Known Member
Licensed User
Longtime User
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

Expert
Licensed User
Longtime User
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

Well-Known Member
Licensed User
Longtime User
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.
 

ctd

Member
Licensed User
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.
 

Attachments

  • JdbcSQL.zip
    8.3 KB · Views: 1,073
Status
Not open for further replies.
Top