B4J Tutorial SQL Tutorial

Discussion in 'B4J Tutorials' started by Erel, Dec 1, 2013.

  1. Erel

    Erel Administrator Staff Member Licensed User

    B4J jSQL library is similar to Basic4android SQL library.

    This tutorial will cover the differences between the two libraries. You can read more about the library methods here: http://www.basic4ppc.com/android/forum/threads/sql-tutorial.6736/#content

    jSQL library is not tied to any specific SQL engine. jSQL wraps Java JDBC mechanism and allows you to access any type of SQL database. Including remote databases and local databases.

    In order to connect to a database you need to get the database native jar driver and you need to know the connection string (JdbcUrl) and driver class.

    In order to add the native jar file you need to:

    1. Copy the file to the libraries folder (internal or external).
    2. Add the new module attribute: #AdditionalJar: <jar name>
    This attribute tells the compiler to add the jar to the package.

    For example to connect to a MySQL database:
    Code:
    #Region  Project Attributes
       
    #MainFormWidth: 600
       
    #MainFormHeight: 400
       
    #AdditionalJar: mysql-connector-java-5.1.27-bin.jar
    #End Region

    Sub Process_Globals
       
    Private fx As JFX
       
    Private MainForm As Form
       
    Private sql1 As SQL
    End Sub

    Sub AppStart (Form1 As Form, Args() As String)
       MainForm = Form1
       MainForm.RootPane.LoadLayout(
    "1")
       MainForm.Show
       sql1.Initialize(
    "com.mysql.jdbc.Driver""jdbc:mysql://localhost/test?characterEncoding=utf8")
    End Sub
    You can download the native MySQL jar from: http://dev.mysql.com/downloads/connector/j/

    Google for <database type> JDBC to find the native jar and the required settings.

    SQLite

    The SQLite native jar is included in the IDE installation. There is also a helper method, similar to B4A SQL initialize method:
    Code:
    SQL1.InitializeSQLite(File.DirApp, "data/1.db"True)
    You should add the following attribute:
    Code:
    #AdditionalJar: sqlite-jdbc-3.7.2
    DBUtils module can be used with SQLite databases. It will most likely fail with other engines as the query syntax and datatypes are not exactly the same.

    ResultSet

    Queries return a ResultSet object. It is similar to B4A Cursor. The difference is that you iterate over the items with:
    Code:
    Dim RS As ResultSet = SQL1.ExecuteQuery(...)
    Do While RS.NextRow
     
    Log(RS.GetString("col1"))
    Loop
    RS.Close
    Notes

    - You can add multiple #AdditionalJar lines. One for each dependent jar.
    - If you are accessing a remote database then you should use the asynchronous commands. Otherwise the program will freeze during the communication.
    - B4J ResultSet indices start from 0. The native Java ResultSet indices start from 1.
    - You can use Initialize2 instead of Initialize if you need to pass credentials.
    - Basic4android databases are SQLite databases.
    - This tutorial is relevant for B4J v1.00 Beta 6+.
     
    Last edited: Dec 8, 2013
    Edgardo Nakaya and billzhan like this.
  2. derez

    derez Expert Licensed User

    I dont understand what to write in this:
    Code:
    sql1.Initialize("com.mysql.jdbc.Driver""jdbc:mysql://localhost/test?characterEncoding=utf8")
    For a Mysql DB file "D:\Family\Family.db"
     
  3. Erel

    Erel Administrator Staff Member Licensed User

    MySQL database is a server, not a single file (like SQLite). You should connect to the server.
     
  4. derez

    derez Expert Licensed User

    In version 5 I initialized like this:
    Code:
    SQL1.Initialize("D:\Family","Family.db",False)
    abnd it worked.
    What do I do now ?
     
  5. Erel

    Erel Administrator Staff Member Licensed User

    Your database is a SQLite database, not MySQL.

    See the SQLite section in the tutorial.
     
  6. derez

    derez Expert Licensed User

    OK clear !
     
  7. alienhunter

    alienhunter Active Member Licensed User

    Hi Erel ,
    this seems to replace the RDC ? like a built in solution ?
    thanks AH
     
  8. Erel

    Erel Administrator Staff Member Licensed User

    RDC is a complete server. Its main purpose is to allow the Android apps to access remote databases.

    There is some overlap between the two solutions.
     
  9. Rafal Ciesielski

    Rafal Ciesielski New Member Licensed User

    Can I use this solution for connections to the Oracle server ?
     
  10. Erel

    Erel Administrator Staff Member Licensed User

  11. peggjones

    peggjones Active Member Licensed User

    When I add #AdditionalJar: sqlite-jdbc-3.7.2 to the attributes in my B4J program I get
    "attrribute not suppoted: additional jar".

    Any ideas?

    Thanks
     
    Last edited by a moderator: Dec 4, 2013
  12. Erel

    Erel Administrator Staff Member Licensed User

    Make sure to download the latest beta version (BETA 6).
     
    peggjones likes this.
  13. udg

    udg Expert Licensed User

    Hi Erel,

    I'm trying to connect to a remote mysql db, but I'm having no luck so far.
    Relevant messages showed on Log:
    "com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure"
    "Caused by: java.net.ConnectException: Connection timed out: connect"

    I played with values as high as 30secs for timeout (both connectTimeout and socketTimeout since I don't know which one is right for me..) but the error message stays the same.
    Code:
    sql1.Initialize2("com.mysql.jdbc.Driver""jdbc:mysql://sql.barmilano.bo.it:3306/barmilan58096",username,pwd)
    mysql-connector-java-5.1.27-bin.jar file once extracted from its zip file was copied in my B4J extralib and system variable CLASSPATH was updated to firstly look up in that same extralib directory.
    Finally, I opened up TCP OUT port 3306 on my firewall.

    So, what to check next? TIA

    Umberto
     
    Last edited: Dec 7, 2013
  14. Erel

    Erel Administrator Staff Member Licensed User

  15. udg

    udg Expert Licensed User

    SOLVED!

    Thank you Erel.
    I went (again) through the link you posted but this time I didn't take for granted I'm allowed to reach my DB from remote..
    and infact I CAN'T !!!
    MySQL Workbench was of great help here since it showed "error 10060" and that suggested me to check against my provider's settings.

    So, let's go back to the "classical" PHP-script on server access method :-(

    Sooner or later I'd have to pay again for a dedicated server..

    Umberto
     
  16. udg

    udg Expert Licensed User

  17. jonydoboi

    jonydoboi Member Licensed User

    ResultSet

    Queries return a ResultSet object. It is similar to B4A Cursor. The difference is that you iterate over the items with:
    Code:
    Dim RS As ResultSet = SQL1.ExecuteQuery(...)
    Do While RS.NextRow
    Log(RS.GetString("col1"))
    Next
    RS.Close
    Do While
    Loop

    'Non-UI application (console application)
    #Region Project Attributes
    #CommandLineArgs:
    #AdditionalJar: sqlite-jdbc-3.7.2
    #End Region

    Sub Process_Globals
    Dim jon As SQL

    End Sub

    Sub AppStart (Args() As String)
    jon.InitializeSQLite(File.DirApp, "tele.db", True)
    jon.ExecNonQuery("CREATE TABLE IF NOT EXISTS tele (id INTEGER PRIMARY KEY, name TEXT, phone TEXT)")
    Dim RS As ResultSet = jon.ExecQuery("SELECT * FROM tele")
    Do While RS.NextRow
    Log(RS.GetString("name"))
    Loop
    RS.Close
    End Sub

    If the file does not exist then it will be create under the program folder name and Objects folder. A data folder is not created. When I created one manually the program still did not save anything to the data folder. I copied a backup copy(tele.db) to the Object folder and could read from it using Log. In other words it works.
     
    Last edited: Dec 8, 2013
  18. Erel

    Erel Administrator Staff Member Licensed User

    Fixed. Thank you.
     
  19. LucaMs

    LucaMs Expert Licensed User

    Surely you will tell me to open another thread (I can possibly move the question with some command of this editor?)

    I have not yet had the time and concentration to use B4J (I think I missed a lot).

    Put the attribute is import the library because B4J does not have the "Referenced libraries" unlike B4A?

    Anyway, I appreciate all your tutorials, especially on db, thanks Erel
     
  20. Erel

    Erel Administrator Staff Member Licensed User

    No. The SQL library can work with any jdbc jar library. So it is not possible to hardcode the native jar as usually done (@DependsOn annotation).

    With this attribute you can use the SQL library with any type of database (MySQL, SQLite, Oracle, ...).
     
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