B4J Tutorial SQL Tutorial

Erel · Dec 1, 2013

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

LucaMs · Dec 18, 2013

Erel said:
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 · Jan 12, 2014

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 · Jan 12, 2014

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

Gabino A. de la Gala · Feb 7, 2014

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 · Feb 17, 2014

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 · Feb 19, 2014

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

viktor wahyu · Apr 15, 2014

can i connect to ms.SQL 2000/2005/2008 server in my local network with this lib?
many many thanks

Erel · Apr 15, 2014

Yes. You can connect to any database that has a JDBC driver.

Sytek · May 9, 2014

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!

Erel · May 11, 2014

This tutorial is about SQL library which accesses local SQLite databases, not remote databases.

AscySoft · May 16, 2014

How about connecting to Access mdb (jet) files? I see there is a UCanAccess http://ucanaccess.sourceforge.net/site.html which is based on Jackcess(which is included as a library in B4J). Does anyone know the proper implementation/connection? Is there possible only to use Jackcess.

Erel · May 18, 2014

UCanAccess should work. Make sure to add all the required jars with multiple #AdditionalJar declarations.

The two values required are explained here: http://ucanaccess.sourceforge.net/site.html

You can use Jackcess but not with jSQL library as Jackcess by itself doesn't implement a Jdbc driver.

AscySoft · May 19, 2014

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 · May 19, 2014

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 · Oct 21, 2014

Erel said:
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+.

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

Erel · Oct 21, 2014

The information is not available (it is not available to the JDBC driver). If it is important for you then you can first make a count query and then make the query itself.

alan1968 · Jul 30, 2015

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

Erel · Jul 30, 2015

Change e to LastException:
Log("Error: " & LastException)

alan1968 · Aug 7, 2015

works !

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

;-)

rudolf · Oct 8, 2015

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,

B4J Tutorial SQL Tutorial

Expert

Member

Expert

Well-Known Member

Well-Known Member

Member

New Member

B4X founder

Active Member

B4X founder

Active Member

B4X founder

Active Member

B4X founder

Member

B4X founder

Active Member

B4X founder

Active Member

Member

Privacy & Transparency

Privacy & Transparency