B4J Connection to Database Example…

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:

  • Connect2DB
  • GetDepartmentsList
  • DisConnectFromDB

Also it will contains a basic form with two buttons and a TableView (see screenshot bellow):B4J2ASAConnectionMainForm

We have two buttons, named ButtonExitButtonRetrieve 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:

  • ButtonRetrieve_MouseClicked
  • ButtonExit_MouseClicked

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:

B4J2ASAConnectionMainFormExecution

Hope you found this usefull.

Andreas.

Advertisements

One thought on “B4J Connection to Database Example…

  1. Pingback: B4J Connection to a SAP ASE Database… | Andrew's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s