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).
The files needed to be place there are:
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.
And 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:
- The driver to be used (in our case com.sybase.jdbc4.jdbc.SybDriver).
- The JDBC URL (in our case jdbc:sybase:Tds:10.0.0.1:5000).
- The user name to use to connect (we’ll use sa).
- 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.