In this post we will see a Database Connection example in B4J. The database we will use is SQL Anywhere 16 (Developer Edition). So we need to have installed SQL Anywhere 16 (Developer Edition) to our computer.
First thing we must have in mind is that B4J produces java code. To connect to a database we need to have a JDBC driver. SQL Anywhere does provide a JDBC driver (sajdbc4.jar). To use it in B4J we have to copy the sajdbc4.jar & jodbc4.jar to our additional libraries folder. Those files are provided with SQL Anywhere client installation.
If all steps mentioned before are done, then we are ready to write our first application in B4J to connect and query a SQL Anywhere Database. For the purpose of our example we will use the sample database provided with SQL Anywhere 16.
Our applications will contain the following subroutines:
Also it will contains a basic form with two buttons and a TableView (see screenshot bellow):
We have two buttons, named ButtonExit & ButtonRetrieve and a TableView named TableViewQueryResult, in our case.
The code for Connect2DB subroutine will be the following:
Sub Connect2DB() Try sql.Initialize("sybase.jdbc4.sqlanywhere.IDriver", "jdbc:sqlanywhere:DSN=SQL Anywhere 16 Demo;UserID=dba;Password=sql;") Catch LastException Log(LastException.Message) End Try Return End Sub
The code for subroutineDisConnectFromDB will be the following:
Sub DisConnectFromDB() If sql.IsInitialized() Then sql.Close() Return End Sub
The code for GetDepartmentsList subroutine will be the following:
Sub GetDepartmentsList() Dim cursor As ResultSet Dim myList As List cursor = sql.ExecQuery("select * from GROUPO.Departments") myList.Initialize() Do While cursor.NextRow Dim myObject(3) As Object myObject(0) = cursor.GetInt2(0) myObject(1) = cursor.GetString2(1) myObject(2) = cursor.GetInt2(2) myList.Add(myObject) Loop TableViewQueryResult.Items = myList Return End Sub
In the Region Project Attributes we will have to add “reference” to the two additional jar, as shown in bold bellow:
#Region Project Attributes #MainFormWidth: 600 #MainFormHeight: 600 #AdditionalJar: jodbc4 #AdditionalJar: sajdbc4 #End Region
In the Process_Globals we should add the following delarations as shown in bold bellow:
Sub Process_Globals Private fx As JFX Private MainForm As Form Private sql As SQL Private ButtonExit As Button Private ButtonRetrieve As Button Private TableViewQueryResult As TableView End Sub
And in the AppStart suboutine, the following lines (as shown in bold bellow):
Sub AppStart (Form1 As Form, Args() As String) MainForm = Form1 MainForm.RootPane.LoadLayout("ASAQueryResult") 'Load the layout file. MainForm.Show TableViewQueryResult.SetColumnHeader(0, "Department Id") TableViewQueryResult.SetColumnHeader(1, "Deoartment Name") TableViewQueryResult.SetColumnHeader(2, "Department Head Id") End Sub
Finally we have to define some actions to be done for our buttons, when any of them is clicked. This is done by adding two more subroutines:
The full code for those routines will be:
Sub ButtonRetrieve_MouseClicked (EventData As MouseEvent) Connect2DB GetDepartmentsList DisConnectFromDB End Sub Sub ButtonExit_MouseClicked (EventData As MouseEvent) ExitApplication End Sub
At this point our first application quering a SQL Anywhere Database is ready to run. Follows a screen shot of the result:
Hope you found this usefull.