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.b4x.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:
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:
You should add the following attribute:
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:
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+.
This tutorial will cover the differences between the two libraries. You can read more about the library methods here: http://www.b4x.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:
B4X:
#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 SubYou 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:
B4X:
SQL1.InitializeSQLite(File.DirApp, "data/1.db", True)
B4X:
#AdditionalJar: sqlite-jdbc-3.7.2DBUtils 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:
B4X:
Dim RS As ResultSet = SQL1.ExecuteQuery(...)
Do While RS.NextRow
Log(RS.GetString("col1"))
Loop
RS.CloseNotes
- 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:
