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

LucaMs

Expert
Licensed User
Longtime 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, ...).

Ah, ok, thank you.
 

Softflow Systems

Member
Licensed User
Longtime User
I have started using Basic4Android just a couple of days back and want to use Microsoft SQl database without Web Service but I am having lots of trouble using all the solutions provided including MSSQL library.


While compiling the following error comes.
I am using Ver 3.0

Parsing code. Error
Error parsing program.
Error description: Attribute not supported: additionaljar
Occurred on line: 8
#AdditionalJar: sqljdbc.jar

1. AddditionalJar attribute is not supported and also can you give some example for the use with Microsoft Sql Server.
2. Where is JSQL Library as mentioned in the post.

I have also posted my problem in MSSQL forum but there is no reply as yet.

Your early response will be highly appreciated.
 

derez

Expert
Licensed User
Longtime User
You are mixing B4A and B4J. Libraries that start with J are B4J libraries. #AdditionalJar also belongs to B4J.
 

Gabino A. de la Gala

Active Member
Licensed User
Longtime User
Finally I've got it!!!.

I can connect with Firebird like this:

B4X:
#Region  Project Attributes

    #MainFormWidth: 600
    #MainFormHeight: 400
    #AdditionalJar: jaybird-full-2.2.4.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("Layout1") 'Load the layout file.
    MainForm.Show
    sql1.Initialize("org.firebirdsql.jdbc.FBDriver","jdbc:firebirdsql:localhost/3050:C:\Datos\BaseDatos.Fdb?user=USER&password=xyz")
End Sub
 

Gabino A. de la Gala

Active Member
Licensed User
Longtime User
SQL Server connection:
B4X:
#Region  Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 400
    #AdditionalJar: jtds-1.3.1.jar
#End Region

Sub Process_Globals
    Private sql1 As SQL
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    Try
    sql1.Initialize("net.sourceforge.jtds.jdbc.Driver","jdbc:jtds:sqlserver://localhost:1433/NombreBD;user=xxx;password=yyy")
    Catch
        Log("Error de conexión con la BD "
    End Try

End Sub
 

Rafal Ciesielski

Member
Licensed User
Longtime User
Connection to Oracle SQL :

B4X:
#Region  Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 400
    #AdditionalJar: ojdbc6.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)
    Dim rs As ResultSet
    Try
        sql1.Initialize("oracle.jdbc.driver.OracleDriver","jdbc:oracle:thin:user/pass@localhost:1521:ERP")
        rs = sql1.ExecQuery("select * from dect")
        Do While rs.NextRow
            Log(rs.GetString("id"))
        Loop
        rs.Close
    Catch
        Log("Error")
    End Try       
End Sub
 

Sytek

Active Member
Licensed User
Longtime User
Hello
I prefer RDC 'cause what if "someone" decompiles the jar file, all the info is there (User, Password) or I'm Wrong?
Best Regards!
 

AscySoft

Active Member
Licensed User
Longtime User
Microsoft Access local connection like this
Download Ucanaccess zip, put only ucanaccess.jar in b4j additional libs folder, and create (for simplicity) another folder in there, in my case 'lib'). There put all the 4 additional jar files (there are in ucanaccess.zip) and code like this:
B4X:
#Region  Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 400
    #AdditionalJar: ucanaccess-2.0.5.jar
    #AdditionalJar: lib\jackcess-2.0.4.jar
    #AdditionalJar: lib\hsqldb.jar
    #AdditionalJar: lib\commons-logging-1.1.1.jar
    #AdditionalJar: lib\commons-lang-2.6.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("Layout1") 'Load the layout file.
    MainForm.Show
    Dim rs As ResultSet
    Try
      SQL1.Initialize ("net.ucanaccess.jdbc.UcanaccessDriver","jdbc:ucanaccess://E:/DATABASES/.....path to your file/Note.mdb")
        rs = SQL1.ExecQuery("SELECT * FROM XUSERS;")
        Do While rs.NextRow
            Log(rs.GetString("NOMUTIL"))
        Loop
        rs.Close
    Catch
        Log(LastException)
    End Try     
    ExitApplication
End Sub
Erel, do you think I could use this in a b4J server application (I need to query a mdb file over the internet from a web browser)?
 

Erel

B4X founder
Staff member
Licensed User
Longtime User
Thank you for sharing this. Note that #AdditionalJar attribute was not tested with sub-folders.

Erel, do you think I could use this in a b4J server application
There is nothing special in a server app. It will work. You should use a single threaded handler as it is not clear whether this driver supports multithreaded solutions.
 

CanelaKid

Member
Licensed User
Longtime 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:
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+.

In B4J how do we know the size of the resultset (short of iterating through) e.g. equivalent of cursor.rowcount
 

alan1968

Active Member
Licensed User
Longtime User
Hi all !

Why not works ms sql 2005 ? log: Error de conexión à la BD (Exception) Not initialized

B4X:
#Region  Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 400
    #AdditionalJar: jtds-1.3.1.jar


#End Region

Sub Process_Globals
    Private Fx As JFX
    Private MainForm As Form
    Private Sql1 As SQL
    Private Url As String
    Private Driver As String
    Private UserName As String
    Private PassWord As String
End Sub

Sub AppStart (Form1 As Form, Args() As String)
    MainForm = Form1
    MainForm.SetFormStyle("UNIFIED")
    MainForm.RootPane.LoadLayout("test") 'Load the layout file.
    MainForm.Show
    Try
    Driver = "net.sourceforge.jtds.jdbc.Driver"
    Url = "jdbc:jtds:sqlserver://serv01:1433/sqlexpress"
    UserName = "FOR_USER"
    PassWord= "FOR_PSW"
    Sql1.Initialize(Driver,Url & ";user=" & UserName & ";password=" & PassWord)
    Catch
    Dim e As Exception
        Log("Error de conexión à la BD " & e)
    End Try
End Sub
Sub Button1_MouseClicked (EventData As MouseEvent)
Dim rs As ResultSet
Try
rs = Sql1.ExecQuery("select * from person")
        Do While rs.NextRow
            Log(rs.GetString("id"))
        Loop
        rs.Close
    Catch
        Log("Error")
    End Try      

   
End Sub
 

alan1968

Active Member
Licensed User
Longtime User
works !

remplace: Url = "jdbc:jtds:sqlserver://serv01:1433/sqlexpress"
by : Url = "jdbc:jtds:sqlserver://serv01:1433;instance=sqlexpress"'

;-)
 

rudolf

Member
need help,
i need to build simple application insert, update and delete connected with mysql database,
the data showed in tableview,
the problem is :
1. i dont know how to build mysql database connected in b4j, What I need to install xampp or uniserver?
2. Can someone give me the source code to my learned
sorry i am beginner,
 
Top