Brought to you by EarthWeb
ITKnowledge Logo Login Graphic Click Here!
Click Here!
ITKnowledge
Search this book:
 
Search the site:
 
EXPERT SEARCH ----- nav

EarthWeb Direct

EarthWeb Direct

EarthWeb sites: other sites

Previous Table of Contents Next


When a SQL statement returns more than one result, execute() has to be used to request execution of the statement. The section on multiple result sets discusses this issue in detail. Listing 5-10 shows how to execute a query.

Listing 5-10: How to execute a query.

// how to execute a query
import java.sql.*;
class SimpleExample
{
  public static void main(String args[])
  {
  String url = “jdbc:odbc:mysource”;
         try
         {
         Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
         DriverManager.setLogStream(
                 java.lang.System.out);
                 Connection myConnection =
                         DriverManager.getConnection(url,
                                 “javauser”, “hotjava”);
                 Statement myStatement =
                          myConnection.createStatement();
                 ResultSet rs = myStatement.executeQuery(
  “SELECT name, id, salary FROM employees ORDER BY” +
  “salary DESC”);
                           myConnection.close();
         }
         catch(java.lang.Exception ex)
         {
                 ex.printStackTrace();
         }
  }
}

The example in Listing 5-11 illustrates the executeUpdate() method:

Listing 5-11: How to perform an update.

// how to execute an update
import java.sql.*;
class SimpleExample
{
  public static void main(String args[])
  {
  String url = “jdbc:odbc:mysource”;
         try
         {
         Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
         DriverManager.setLogStream(
                          java.lang.System.out);
                 Connection myConnection =
                          DriverManager.getConnection(url,
                 “javauser”, “hotjava”);
                 Statement myStatement =
                          myConnection.createStatement();
                 int res = myStatement.executeUpdate(“UPDATE” +
  employees SET salary = salary*1.1 WHERE id = 1”);
                 myConnection.close();
         }
         catch(java.lang.Exception ex)
         {
                 ex.printStackTrace();
         }
  }
}

Closing a Statement

It is often necessary to close explicitly a statement after the DBMS executes it. Indeed, database context and JDBC statement resources stay open until the connection is closed or the statement is garbage-collected. It is not desirable to consume resources when there is no reason to do so. Closing the statement also closes the returned result set.

Statement

void close();

void close();

The close() method is simply called on a statement object and takes no arguments.

Sending BLOBs

It is sometimes necessary to send and retrieve pictures, sound, or other multimedia files to a database. This kind of object is called a Binary Large Object (BLOB). While it has absolutely nothing to do with Java objects [nor C++ or other object-oriented programming (OOP) language object], BLOB is not restricted to images, sound, and multimedia content. For example, a multikilobyte or multimegabyte text is also a BLOB. BLOBs do not have to be ASCII or Unicode; they may be pure binary.

Insertion, update, or retrieval of very large values is usually done by passing the values in small chunks of data. This approach is often more convenient for programmers. JDBC, however, uses data streams. JDBC provides three kinds of streams: ASCII streams, Unicode streams, and binary streams.

Because there is no way to send streams within a simple SQL statement, the streams pass as parameters. Refer to the dynamic SQL section to learn how to send BLOBs to the database.

Adjusting Properties

Properties that change the default behavior of JDBC or of the driver exist. They can be set before sending the SQL statement to the database.

DATA TRUNCATION WHEN SENDING DATA

Data truncation happens when inserting or updating data in the database. This truncation is dependent on the DBMS and driver that fixes the maximum size for data types. An error usually happens if the truncation occurs during a database write. JDBC provides a method to limit the size of a field to a maximum value; if the limit is exceeded, JDBC raises a SQLException.

Statement

void setMaxFieldSize(int max);
int getMaxFieldSize();

void setMaxFieldSize(int max);

The method setMaxFieldSize() allows the programmer to set a maximum field size that will be valid on the current statement. The parameter is the number of bytes allowed.

int getMaxFieldSize();

The method getMaxFieldSize() returns the maximum size allowed for the current statement. Listing 5-12 shows data truncation on write.

Listing 5-12: Data truncation on write.

// data truncation on write
import java.sql.*;
class SimpleExample
{
  public static void main(String args[])
  {
  String url = “jdbc:odbc:mysource”;
         try
         {
...
...
Connection myConnection =
  DriverManager.getConnection(url, “javauser”, “hotjava”);
Statement myStatement = myConnection.createStatement();
myStatement.setMaxFieldSize(12);
int res = myStatement.executeUpdate(“UPDATE
  employees SET comment = 'The quick br...’ WHERE name=’jones’”);
...
...
       }
       catch(java.lang.Exception ex)
       {
                 ex.printStackTrace();
       }
  }
}

Time-outs

The execution of a statement may be delayed for one of several reasons. A mechanism called time-out exists so programmers have a way to handle such situations. The query time-out is the time the JDBC driver will wait for a statement to execute. If the limit is exceeded, the driver raises an exception. This exception may be caught, and one or more retries initiated.

Statement

void setQueryTimeout(int seconds);
int getQueryTimeout();

void setQueryTimeout(int seconds);

This is the method used to set a limit to the time-out mechanism. The parameter is the number of seconds it will wait before raising the SQLException. A zero value means no limit.

int getQueryTimeout();


Previous Table of Contents Next
HomeAbout UsSearchSubscribeAdvertising InfoContact UsFAQs
Use of this site is subject to certain Terms & Conditions.
Copyright (c) 1996-1999 EarthWeb Inc. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.