B4J Tutorial SQL Tutorial

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:
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 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:
B4X:
SQL1.InitializeSQLite(File.DirApp, "data/1.db", True)
You should add the following attribute:
B4X:
#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:
B4X:
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:

sorex

Expert
Licensed User
Longtime User
Does anyone know what the driver name is for MS's original MSSQL JDB driver?
 

sorex

Expert
Licensed User
Longtime User
this is working

B4X:
#AdditionalJar: sqljdbc4.jar

B4X:
sql1.Initialize("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://sqlserver:1433;databaseName=intranet;user=xxx;password=xxx;")

sqljdbc.jar doesn't work on java 1.8

you can download sqljdbc_4.0.2206.100_enu.tar.gz which doesn't require an install from http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774

extract it or browse with Total Commander and copy the file sqljdbc_4.0\enu\sqljdbc4.jar to you additional libraries folder.
 

sorex

Expert
Licensed User
Longtime User
thanks for the hint, @Erel.

that one seems to be up to 4 times faster than the one from Microsoft in some cases.
 

sorex

Expert
Licensed User
Longtime User
it seems that I was too quick with my results.

when I ran it in release mode I got errors that the resultset was not initialized.

I had both MSSQL & JTDS as additional JAR in my project.

When I remove the MSSQL one it doesn't work in debug mode either.

Funny thing is that when I make a typo in the driver class name (net.sourceforge.jtds.jdbc.Driver) its not working either so my guess is it tried to use the right one anyway.

Is there an error that I can display that might show more info about the connect failure?
 

sorex

Expert
Licensed User
Longtime User
ok, I missed the JTDS part in the connection string which is apparently needed. Strange that it worked without when the MSSQL lib was added.

B4X:
sql1.Initialize("net.sourceforge.jtds.jdbc.Driver","jdbc:jtds:sqlserver://servername:1433/databasename;user=xxx;password=xxx")
 

vfafou

Well-Known Member
Licensed User
Longtime User
Hello!
Is it a good practice, when we have a thread pool of connections, to do the following(?):
B4X:
If Not(SQL.IsInitialized) Then SQL = Pool.GetConnection
Thank you in advance!
 

Croïd

Active Member
Licensed User
Longtime User
Thanks Erel

It is a base of a service provider. With B4A application you can connect

Is it different between B4A and b4j ?
 

Croïd

Active Member
Licensed User
Longtime User
Yes with " lib: MSMySql no SQL

B4X:
Dim db As MySQL
------
db.Initialize("MySQL","sqlx.freemysql.net","sqluser","sqlpassw","sqluser")
db.QueryASync("SELECT * FROM Table;","")
db.CloseDatabase

sample express write db
 
Last edited:

Croïd

Active Member
Licensed User
Longtime User
Because if I take the first post : Error: (UnknownHostException) java.net.UnknownHostException: sql7.freemysqlhosting.net;user=sqluser;password=passw

B4X:
#AdditionalJar: mysql-connector-java-5.1.39-bin.jar
 
Top