7.4.1 How to retrieve data

(1) Generating a Statement object

Generate a Statement object, and then send the SELECT statement to the HADB server.

If a connection to the HADB server has already been established, you can use the createStatement method of the Connection object to generate a Statement object.

The following example generates a Statement object:

 // Connect to the HADB server
 Connection con = DriverManager.getConnection(url,info);
 
 // Generate a Statement object
 Statement stmt = con.createStatement();

To Page Top

(2) Executing the SELECT statement

Execute the SELECT statement by specifying it in the argument of the executeQuery method. The following example executes a SELECT statement:

 Statement stmt = con.createStatement();
 
 // Execute the SELECT statement and obtain the ResultSet object
 ResultSet rs = stmt.executeQuery("SELECT \"CODE\", \"STATE\" FROM \"SAMPLE\"");

When a SELECT statement is executed, the retrieval results are stored in a ResultSet object.

To Page Top

(3) Getting the retrieval results

The retrieval results are stored in a ResultSet object in a tabular format that consists of column numbers and values corresponding to the retrieval results. The following figure shows an example of the format of a ResultSet object.

Figure 7‒2: Example of the format of a ResultSet object

The ResultSet object contains a cursor that points to the current row. You obtain retrieval results from the ResultSet object by using the next method to move the cursor and then a getXXX method to obtain the data on the current row.

When a ResultSet object is generated, the cursor is positioned immediately before the first row. When the first next method is called, the cursor moves to the first row. Each time the next method is called thereafter, the cursor moves down by one row.

The following example obtains retrieval results data:

 ResultSet rs = stmt.executeQuery("SELECT \"CODE\", \"STATE\" FROM \"SAMPLE\"");
 
 // Repeat until there is no more result row
 while(rs.next())
 {
   // Get data from column 1
   int i = rs.getInt(1);
   // Get data from column 2
   String s = rs.getString(2);
   // Output the result data
   System.out.println("Retrieval results: " + i + ", " + s);
 }

To Page Top

(4) Note about executing multiple SELECT statements concurrently in the same connection

When you execute multiple SELECT statements concurrently in the same connection, HADB might be unable to supply enough processing real threads to execute the SELECT statements. In this situation, processing to allocate processing real threads is repeated until the required number is allocated. You need to use one of the following approaches to make sure that allocation processing will not continue indefinitely. We recommend that you use the first approach if possible.

Approaches to resolving the issue

1. Amend the application.

   If you are able to amend the application, amend it so that unnecessary ResultSet objects are closed. If you are unable to amend the application, look into whether you can change the server definition as follows.

2. Change the server definition.

   If you are able to change the server definition, change it so that it meets the following formula:

   A ≥ B x C x D

   A: Number of processing threads (adb_sys_rthd_num operand's value)

   B: Maximum number of SQL processing real threads (adb_sql_exe_max_rthd_num operand's value)

   C: Number of SELECT statements that can be executed concurrently in one connection (that are placed in cursor open status)

   D: Number of connections that can execute SQL statements concurrently

3. Set a wait time.

   If the preceding two approaches are unavailable to you, specify a wait time in the following method or property that governs how long HADB waits to allocate the required processing real threads:

   • setQueryTimeout method of the Statement interface

   • adb_clt_rpc_sql_wait_time in the system properties, user properties, or connection URL properties

To Page Top