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:

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

SQL1.InitializeSQLite(File.DirApp, "data/1.db", True)

You should add the following attribute:

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

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+.

 

[derez]

I dont understand what to write in this:

sql1.Initialize("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/test?characterEncoding=utf8")

For a Mysql DB file "D:\Family\Family.db"

 

[Erel]

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

 

[derez]

In version 5 I initialized like this:

SQL1.Initialize("D:\Family","Family.db",False)

abnd it worked.
What do I do now ?

 

[Erel]

Your database is a SQLite database, not MySQL.

See the SQLite section in the tutorial.

 

[derez]

OK clear !

 

[alienhunter]

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

 

[Erel]

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.

 

[Rafal Ciesielski]

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

 

[Erel]

Yes. See this blog: http://razorsql.com/docs/help_oracle.html

 

[peggjones]

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

 

[Erel]

Make sure to download the latest beta version (BETA 6).

 

[udg]

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.

  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

 

[Erel]

Check the answer here: http://stackoverflow.com/questions/...mmunicationsexception-communications-link-fai

 

[udg]

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

 

[udg]

Hi Erel,

don't know where to best post this "discovering":
http://www.hostdepartment.com/databases/mysql-hosting/
They offer a very cheap way to host a few Mysql DBs accepting remote connections. Surely for testing purposes it sounds good.

Umberto

 

[jonydoboi]

ResultSet

Queries return a ResultSet object. It is similar to B4A Cursor. The difference is that you iterate over the items with:

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.

 

[Erel]

Fixed. Thank you.

 

[LucaMs]

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

 

[Erel]

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

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, ...).