B4J Connection to a SAP ASE Database…

In a previous post (B4J Connection to Database Example…) I demonstrated through a very simple application how to connect within a B4J application to a SAP SQL Anywhere Database. It’s not always easy to find how we can connect to a database. This is because, when connecting to a database using JDBC, we have to know which driver we will use and also the required JDBC URL to communicate with the database server. This information isn’t always easy to find, as they are different versions of clients, and each database client may have different driver & settings!

As mentioned in the title, this time I will give an example of a connection to a SAP ASE Database. Currently, I will be testing the connectivity to versions 15.7 and 16.0 of the product. As with any database, the first thing we need to check is that the JDBC driver option of the client was checked and installed during client’s installation (this can be done through control panel add/remove programs option). The second is to copy two files to out Additional  Libraries folder (this path is declared in B4J IDE as shown in the following screenshot).

AdditionalLibs

The files needed to be place there are:

  1. jconn4.jar
  2. jTDS3.jar

Both files can be found in the following path (which may need to be modified depending on the path we choose when installing the client): C:\SAP\jConnect-16_0\classes. When those files are put in our additional libraries folder, we need first of all to tell our application that we will use them. This is done by adding in the Region Project Attributes (normally found at the beginning of our script) the following lines:

#AdditionalJar: jconn4
#AdditionalJar: jTDS3

After that we are ready to use the JDBC Driver. We need to check the jSQL library from B4J’s IDE Library Manager.

jSQLSelectionAnd now we have done all the prerequisite to connect to our database. We can connect using the Initialize2 function in jSQL. This functions requires four parameters, which are:

  1. The driver to be used (in our case com.sybase.jdbc4.jdbc.SybDriver).
  2. The JDBC URL (in our case jdbc:sybase:Tds:10.0.0.1:5000).
  3. The user name to use to connect (we’ll use sa).
  4. The password for the user.

Our code will be:

Sub Process_Globals
 Private fx As JFX
 Private MainForm As Form
 Private sql As SQL
End Sub

Sub AppStart (Form1 As Form, Args() As String)
Try
 sql.Initialize2("com.sybase.jdbc4.jdbc.SybDriver", "jdbc:sybase:Tds:10.0.0.1:5000", "sa", "some password")
 If sql.IsInitialized Then
 Dim command = "use db1" As String
 sql.ExecNonQuery(command)
 Dim command = "select @@version" As String
 fx.Msgbox(LoginForm, sql.ExecQuerySingleResult(command), "Info:")
 sql.Close()
 End If
Catch LastException
 Log(LastException.Message)
End Try
End Sub

In sql.initialize2 function, the first parameter is the JDBC Driver to use for the connection and the second the JDBC URL. Notice that the URL starts with jdbc:sybase:Tds: and then is followed by the ip of our server and the port which he is listening to. For ASE the default port is 5000. That’s why our URL is jdbc:sybase:Tds:10.0.0.1:5000. Now, when we run our small application, normally it will run without giving any exception to the log. If we do get any exception, then we should read it and try to imagine what is our issue. Normally the messages we get are clear and show exactly what is the problem.

Hope you found this little info helpful.

Andreas.

Advertisements

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