ASA Programming Guide
  JDBC Programming
    Using JDBC to access data

Queries using JDBC

The Statement object executes static queries, as well as statements that do not return result sets. For queries, you use the executeQuery method of the Statement object. This returns the result set in a ResultSet object.

The following code fragment illustrates how queries can be handled within JDBC. The code fragment places the total inventory value for a product into a variable named inventory. The product name is held in the String variable prodname. This example is available as the Query method of the JDBCExamples class.

The example assumes an internal or external connection has been obtained and is held in the Connection object named conn.

public static int Query () {
int max_price = 0;
    try{
      conn = DriverManager.getConnection(
                   "jdbc:default:connection" );

      // Build the query
      String sqlStr =  "SELECT id, unit_price "
          + "FROM product" ;

      // Execute the statement
      Statement stmt = conn.createStatement();
      ResultSet result = stmt.executeQuery( sqlStr );

      while( result.next() ) {
          int price = result.getInt(2);
          System.out.println( "Price is "  + price );
          if( price > max_price ) {
             max_price = price ;
          }
      }
    }
    catch( Exception e ) {
      System.out.println("Error: " + e.getMessage());
      e.printStackTrace();
    }
      return max_price;
  }

Running the example 

Once you have installed the JDBCExamples class into the sample database, you can execute this method using the following statement in Interactive SQL:

CALL JDBCExamples>>Query()

Notes 

• The query selects the quantity and unit price for all products named prodname. These results are returned into the ResultSet object named result.

• There is a loop over each of the rows of the result set. The loop uses the next method.

• For each row, the value of each column is retrieved into an integer variable using the getInt method. ResultSet also has methods for other data types, such as getString, getDate, and getBinaryString.

  The argument for the getInt method is an index number for the column, starting from 1.

• Adaptive Server Anywhere supports bidirectional scrolling cursors. However, JDBC provides only the next method, which corresponds to scrolling forward through the result set.

• The method returns the value of max_price to the calling environment, and Interactive SQL displays it on the Results tab in the Results pane.