DML Statements

This third column in my Learning SQLJ series explores how to add SQLJ statements to your Java programs that use embedded SQL Data Manipulation Language (DML) statements. DML statements may be used to retrieve and modify the contents of database tables. In this article, you will also learn how to process database null values and handle database exceptions.

These columns reference numerous SQL scripts, source code, and other files that are available for download at O'Reilly's Web site. For more specific information about the files used, or to get an introduction to SQLJ, read my first column, Setting Up Your Environment to Develop SQLJ Programs.

Single Row Queries

The SQL SELECT INTO statement is used to select a single row from a database table and store the specified column values in specified host variables. The values in those host variables may then be accessed in the Java program.

The following example illustrates the use of a SELECT INTO statement to retrieve the first_name, last_name, dob, and phone columns from the customers table for the row where the id column is equal to 2. The values retrieved are stored in host variables:

// declare host variables

int id = 2;

String first_name = null;

String last_name = null;

java.sql.Date dob = null;

String phone = null;



// perform SELECT to get the customer details for

//  the customer #2 from the customers table

#sql {

  SELECT

    first_name, last_name, dob, phone

  INTO

    :first_name, :last_name, :dob, :phone

  FROM

    customers

  WHERE

    id = :id

};

In this example, five host variables are declared. These are named id, first_name, last_name, dob, and phone. The id variable is an int, which is compatible with the NUMBER database type used for the id column. The first_name, last_name, and phone variables are declared as Java String types. The String type is compatible with the VARCHAR2 database type used by these columns. The dob variable is declared as java.sql.Date, which is compatible with the DATE database type.

Next, the SELECT INTO statement uses four host expressions (identified by the colons) to store the values from the first_name, last_name, dob, and phone columns into the respective host variables:

SELECT

  first_name, last_name, dob, phone

INTO

  :first_name, :last_name, :dob, :phone

The default mode for the host expressions in an INTO clause is OUT, meaning that the values for these four host variables are set by the statement.

The WHERE clause of the SELECT INTO statement uses the value stored in the id host variable to identify the row to be retrieved from the customers table:

WHERE

  id = :id

The default mode for a host variable in a WHERE clause is IN, meaning that the id value is read by the statement.

After the SELECT INTO statement has been executed, the first_name, last_name, dob, and phone host variables contain the column values retrieved from the customers table.

The previous SELECT statement relied on the default mode for each of the host variables. It's also possible to specify those modes explicitly, as in the following statement:

#sql {

  SELECT

    first_name, last_name, dob, phone

  INTO

    :OUT first_name, :OUT last_name, :OUT dob, :OUT phone

  FROM

    customers

  WHERE

    id = :IN id

};

The OUT mode in front of the first_name, last_name, dob, and phone variables indicate that the SELECT INTO statement will store values in those variables. The IN mode in front of the id variable indicates that the SELECT INTO statement will read the value stored in the id variable.

Built-in database functions can also be used in queries. The following example uses the COUNT() function to count the number of rows in the products table, and stores the result in a host variable named number_of_products:

int number_of_products;



#sql {

  SELECT

    COUNT(*)

  INTO

    :number_of_products

  FROM

    products

};

Host variables and expressions can also be used in SQL UPDATE, DELETE, and INSERT statements. These statements are shown in the following three sections.

Updating Rows

The SQL UPDATE statement is used to modify rows in a table. When an UPDATE statement is used in a SQLJ executable statement, host expressions can appear in the SET and WHERE clauses. Here's an example:

int new_quantity = 10;

int cust_id = 2;

int prod_id = 3;



#sql {

  UPDATE

    purchases

  SET

    quantity = :new_quantity

  WHERE

    purchased_by = :cust_id

  AND

    product_id = :prod_id

};

This example updates the quantity column for the row in the purchases table, where the product_id column is equal to 2.

Deleting Rows

The SQL DELETE statement is used to remove rows from a table. When a DELETE statement is used in a SQLJ executable statement, host expressions can appear in the WHERE clause. For example:

int cust_id = 2;



#sql {

  DELETE FROM

     customers

  WHERE

    id = :cust_id

};

This example deletes the row in the customers table, where the id column is equal to 2.