User's Guide
   PART 3. Java in the Database
     CHAPTER 18. Data Access Using JDBC       

JDBC overview

JDBC provides a SQL interface for Java applications: if you want to access relational data from Java, you do so using JDBC calls.

This chapter is not a thorough guide to the JDBC database interface. Instead, it provides some simple examples to introduce JDBC and to illustrate how it can be used inside and outside the server. It provides more details on the server-side use of JDBC, running inside the database server.

     The examples illustrate the distinctive features of using JDBC in Adaptive Server Anywhere. For more information about JDBC programming, see any JDBC programming book.

JDBC and Adaptive Server Anywhere 

You can use JDBC with Adaptive Server Anywhere in the following ways:

• JDBC on the client     Java client applications can make JDBC calls to Adaptive Server Anywhere. The connection takes place through the Sybase jConnect JDBC driver.

  In this chapter, the phrase client application applies both to applications running on a user's machine and to logic running on a middle-tier application server.

• JDBC in the server     Java classes installed into a database can make JDBC calls to access and modify data in the database, using an internal JDBC driver.

The focus in this chapter is on server-side JDBC.

JDBC resources 

• Required software     You need the Sybase jConnect driver and TCP/IP in order to use JDBC from an external application.

  The Sybase jConnect driver may already be available, depending on your installation of Adaptive Server Anywhere. For more information about the jConnect driver and its location, see The jConnect driver zip file.

• Example source code     Source code for the examples in this chapter can be found in the file JDBCExamples.java in the jxmp subdirectory under your Adaptive Server Anywhere installation directory.

       For instructions on how to install the Java examples, including the JDBCExamples class, see Installing the Java examples.

  JDBC program structure

The following sequence of events is typical of a JDBC application:

1. Create a Connection object     A Connection object is created by calling getConnection class method of the DriverManager class. This establishes a connection with a database.

2. Generate a Statement object     The Connection object is used to generate a Statement object.

3. Pass a SQL statement     The Statement object is passed a SQL statement that is executed within the database environment. If the statement is a query, this action causes a ResultSet object to be returned.

   The ResultSet object contains the data returned from the SQL statement, but exposes it one row at a time (similar to the way a cursor works).

4. Loop over the rows of the result set     The next method of the ResultSet object performs two actions:

   • The current row (the row in the result set which is being exposed through the ResultSet object) is advanced one row.

   • A Boolean value (true/false) is returned to indicate whether there is, in fact, a row to advance to.

5. For each row, retrieve the values     Values are retrieved for each column in the ResultSet object by identifying either the name or position of the column. The getDate method is one method used to get the value from a column on the current row.

Java objects can use JDBC objects to interact with a database and get data for their own use, such as to manipulate or for use in other queries.

  Differences between client- and server-side JDBC

The difference between JDBC on the client and in the database server is in establishing a connection with the database environment.

• Client side     In client-side JDBC, establishing a connection requires the Sybase jConnect JDBC driver. The connection is established by passing arguments to the DriverManager.getConnection method. The database environment is an external application from the perspective of the client application.

  jConnect required     Depending on the package in which you received Adaptive Server Anywhere, Sybase jConnect may or may not be included. You must have jConnect in order to use JDBC from external applications. You can use internal JDBC without jConnect.

• Server-side     When JDBC is used within the database server, a connection already exists. A value of jdbc:default:connection is passed to DriverManager.getConnection, which provides the JDBC application with the ability to work within the current user connection. This is a quick, efficient and safe operation because the client application has already passed the database security in order to establish the connection: the user ID and password have been provided and do not need to be provided again.

You can write JDBC classes in such a way that they can be run both at the client and at the server by employing a single conditional statement for constructing the URL. An external connection requires the machine name and port number, while the internal connection requires jdbc:default:connection.

Server-side JDBC version 

The internal JDBC driver supports JDBC version 1.1.