B4A Library JdbcSQL - Directly connect to remote databases

Discussion in 'Additional libraries, classes and official updates' started by Erel, Sep 17, 2017.

Thread Status:
Not open for further replies.
  1. Erel

    Erel Administrator Staff Member Licensed User

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

    Attached Files:

  2. jahswani

    jahswani Active Member Licensed User

    @DonMafred Finally.
     
  3. DonManfred

    DonManfred Expert Licensed 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.
     
    aidymp, aymaann and jahswani like this.
  4. incendio

    incendio Well-Known Member Licensed 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.
     
  5. Mashiane

    Mashiane Expert Licensed User

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

    Erel Administrator Staff Member Licensed User

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

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




    .
     
  8. asales

    asales Well-Known Member Licensed User

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

    This is my modifications:
    Code:
    #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:
    Code:
    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.
     
  9. DonManfred

    DonManfred Expert Licensed User

    the answer is given.....

    Place it on a dir where the app has write rights
     
    ilan likes this.
  10. OliverA

    OliverA Expert Licensed User

    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?
     
  11. asales

    asales Well-Known Member Licensed User

    I think I need to put the IP of the computer, but it did not work either (same message):
    Code:
    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.
     
  12. OliverA

    OliverA Expert Licensed 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 likes this.
  13. asales

    asales Well-Known Member Licensed User

    Thanks!
     
  14. tigrot

    tigrot Well-Known Member Licensed User

    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.
     
  15. OliverA

    OliverA Expert Licensed 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
     
  16. incendio

    incendio Well-Known Member Licensed User

    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 likes this.
  17. asales

    asales Well-Known Member Licensed User

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

    incendio Well-Known Member Licensed User

    No I don't.

    Firebird for Android is not stable yet.
    For Android, I use SQLite.
     
    asales likes this.
  19. ctd

    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.
     

    Attached Files:

  20. Erel

    Erel Administrator Staff Member Licensed User

    Please start a new thread for this question. It is also useful to post the relevant code (even when you upload the project).
     
    Ejbh likes this.
Thread Status:
Not open for further replies.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice