(Post 29/11/2005) In the first
article, we set up a basic Adaptive Server Anywhere, in this article,
I'll present the techniques required to build the actual client application
using Java…
In the first
article, we set up a basic Adaptive Server Anywhere (ASA) database
(named mobile_sales.db) consisting of four tables:
- CUSTOMER
- PRODUCTS
- OPPORTUNITIES
- SALES
In this article, I'll present the techniques required
to build the actual client application using Java (either J2SE or a PersonalJava
runtime environment are required). Note that we won't delve into J2ME
MIDP here—neither ASA nor the UltraLite deployment option are supported
on MIDP devices. J2SE or PersonalJava runtimes are currently available
for Win32, Linux, Symbian, Windows CE, and Palm OS platforms—making Java
a popular development language for developers who want to write data access
code once and deploy it to a variety of platforms. I say "data access
code" because the user interface will generally need to be customized
for each individual platform to accommodate changes in form factor and
screen real estate from device to device.
Connecting to ASA
Connections to ASA databases are made via the supplied
jConnect JDBC Driver, which is contained in the jconn2.jar file
stored in the Sybase\Shared\jConnect-5_5 directory after installation.
To access this JAR file from your application, simply include jconn2.jar
in your classpath, as you would any other Java archive. This archive includes
Sybase's 100% pure JDBC driver. A separate driver is included for applications
that want to connect to an RDBMS using a JDBC-ODBC bridge. As with any
JDBC application, the first step is to obtain a java.sql.Connection
object that can be used to query the database. The following code snippet
illustrates how this would be done:
Properties props = new Properties();
props.put("user", "admin");
props.put("password", "123456");
Class.forName("com.sybase.jdbc.SybDriver").newInstance();
String URL = "jdbc:sybase:Tds:localhost";
Connection conn = DriverManager.getConnection(URL , props); |
The connection above was obtained using a "ServiceName"
parameter, which must be of the following syntax:
| jdbc:sybase:Tds:machine-name:port-number?ServiceName=DBN |
Typecasting the com.sybase.jdbc.SybDriver class
allows us to also use the SybDriver class' setRemotePassword()
method to pass additional parameters.
Querying the Database
After the connection has been obtained, standard java.sql
classes (such as PreparedStatement, Statement, and RowSet) can be used
to query the database and retrieve data. Because this concept should be
familiar to Java developers, I'll just give a quick example here:
PreparedStatement tempStmt = conn.prepareStatement("select
ID from CUSTOMER");
ResultSet result = tempStmt.executeQuery(); while (result.next())
{
ID = result.getInt(1);
}
result.close();
tempStmt.close(); |
Adaptive Server Anywhere databases also support the
execution of Java classes in the database itself. The ASA runtime includes
an internal Java VM that allows Java classes to be developed, installed
in the database, and then accessed by SQL statements as functions. Java
classes installed in the database can also be used as data types for columns
in tables. The key point to take away from this discussion is that Java
developers are first-class citizens in the ASA world. The full power of
JDBC is made available to you, even on mobile devices.
Java UltraLite Development
As explained in Part 1 of this tutorial, UltraLite is
the "lite" deployment option for SQL Anywhere Studio 8.0 applications.
UltraLite databases are single files and the UltraLite runtime database
engine is actually compiled into your application (as opposed to ASA,
which exists as a separate application and communicates with your Java
application using a TCP/IP socket). Unlike ASA, UltraLite databases do
not support advanced concepts such as stored procedures, triggers, internal
security, and Java in the database. An UltraLite database file can be
built in one of two ways:
- Using the ulinit to generate an UltraLite schema based
on an existing ASA database schema
- Using the included UltraLite Schema Painter utility (see Figure
1) to build a new schema from scratch
Figure 1
UltraLite Schema Painter utility. |
 |
Stepping through the UltraLite Schema Painter utility,
you'll notice that several of the ASA datatypes aren't available in UltraLite—including
VARCHAR and TEXT (two data types we used to create our mobile_sales.db
database). After you create the desired schema named mobile_sales (for
consistency), you'll notice that the Painter utility created a mobile_sales.usm
file for you. This file must be deployed with your application. The first
time your application is run, the UltraLite Java runtime will read in
the .usm file and will create the mobile_sales.udb database file to be
used by your application. UltraLite Java code also looks a little different
than ASA, particularly when setting up the initial connection to the database.
UltraLite applications make use of the ianywhere.native_ultralite.DatabaseManager
class in order to retrieve a database connection. After the database connection
is retrieved, UltraLite supports a much simpler subset of database operations.
Contents of individual tables can be retrieved using "find"
(that is, findFirst(), findNext(), findBegin(), and so on) or
"lookup" (that is, lookupBegin(), lookupForward(), lookupBackward())
methods. Up to four username/password combinations can be stored in the
schema in order to provide very simplistic user authentication. As you
can see, UltraLite applications will typically be very simple and straightforward
database applications, with a few tables and no complicated query or data
processing logic.
One other important difference exists between ASA's jConnect
libraries and the Native UltraLite for Java libraries. UltraLite's Java
classes make use of JNI native methods "beneath the hood" to
perform database access. This means that the jul8.jar file must
be in your classpath and the uil8.dll file included in your installation's
ultralite\NativeUltraLiteForJava\win32 directory must be included in your
system path.
Conclusion
As mentioned earlier, Java developers are first-class
citizens in the SQL Anywhere Studio world. A number of Java development
options (from JDBC to UltraLite to Java in the database) are provided
for those who have chosen Java as their project's development language.
Currently, Java ASA/UltraLite applications can be run on a number of popular
platforms, including Win32, Windows CE, Palm OS, and Symbian.
Bryan Morgan (from www.informit.com/articles) |
