Database Connections and SQLJ Statements
Pages: 1, 2, 3

Database URL Structure

The database URL tells SQLJ where your database is located. The structure of the database URL is dependent on the brand of JDBC driver being used. In the case of the Oracle's JDBC drivers, the URL structure is as follows:

driver_name:@driver_specific_information

The syntax elements are as follows:

driver_name

Specifies the name of the Oracle JDBC driver that you want to use. This may be any one of the following:

jdbc:oracle:thin

Oracle JDBC Thin driver (for Oracle7 and above)

jdbc:oracle:oci

Oracle JDBC OCI driver (for Oracle9i and above)

jdbc:oracle:oci8

Oracle JDBC OCI8 driver (for Oracle8i and Oracle8)

jdbc:oracle:oci7

Oracle JDBC OCI7 driver (for Oracle7)

driver_specific_information

Specifies any driver-specific information required to connect to the database. This is dependent on the driver being used. In the case of the Oracle JDBC Thin driver, the driver-specific information may be specified in the following format:

host_name:port:database_SID

For all the Oracle JDBC drivers -- including the various OCI drivers -- the driver-specific information may be specified using an Oracle Net8 (or above) keyword-value pair, which may be specified in the following format:

(description=(address=(host=host_name)(protocol=tcp)(port=port)) (connect_data=(sid=database_sid)))

The syntax elements are as follows:

host_name

Specifies the name of the machine on which the database is running.

port

Specifies the port number on which the Net8 database listener waits for requests. 1521 is the default port number. Check with your Database Administrator (DBA) to ensure that this is correct value in your environment.

database_SID

Specifies the system identifier (SID) of the database instance to which you want to connect. Your DBA will be able to provide you with the correct database SID to use.

You may also use an Oracle Net8 (or above) TNSNAMES string -- for more information on this, speak with your DBA or consult the Oracle documentation.

The following example shows the connect() method being used to connect to a database using the Oracle OCI8 driver:

Oracle.connect(
  "jdbc:oracle:oci8:@(description=(address=(host=localhost)" +
    "(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))",
  "scott",
  "tiger"
);

The Oracle JDBC Thin driver has the least amount of system resource requirements, and is generally used in lightweight, client-based programs such as Java applets. The Oracle JDBC Thin driver may be used to access Oracle7 databases and above.

The various Oracle JDBC OCI drivers require more system resources than the thin driver, but they are faster and are suitable for middle tier programs. The OCI driver is used to access Oracle9i databases, and above. The OCI8 driver is used for accessing Oracle8i and Oracle8 databases. The OCI7 driver is used for accessing Oracle7 databases.

Once you've made a connection to the database using the connect() method, you may execute SQLJ statements that contain embedded SQL operations.

Simple SQLJ Statements

In this section you will learn how to write simple SQLJ statements that use embedded SQL Data Manipulation Language (DML) statements. DML consists of the following types of statements:

• SELECT
• INSERT
• UPDATE
• DELETE

You will also learn how SQLJ statements can share data with other Java statements in the program, through the use of host variables.

The Form of a SQLJ Statement

A SQLJ executable statement is a program line that contains an embedded SQL statement. There are two possible types of executable statements; the statement type is determined by whether or not the embedded SQL statement returns a value.

If an embedded SQL statement does not return a value, the syntax of the SQLJ executable statement is as follows:

#sql { SQL_statement };

The syntax element is as follows:

SQL_statement
     Specifies any valid SQL statement.

The following SQLJ executable statement invokes a SQL INSERT statement to add a row to the customers table:

#sql {
  INSERT INTO
    customers (id, first_name, last_name, dob, phone)
  VALUES
    (1, 'John', 'Smith', '13-NOV-1970', '650-555-1212')
};

Everything to the right of the #sql token in the syntax is the executable part of the SQLJ statement, and is known as the SQLJ clause. There are two types of SQLJ clauses. Because this SQLJ clause does not include a result expression, it is known as a statement clause.

If an embedded SQL statement does return a result, then you need a way to specify where that result should be placed. SQLJ syntax accommodates this need. When a value is returned, the syntax for a SQLJ executable statement is as follows:

#sql host_variable = { SQL_statement };

The syntax elements are as follows:

host_variable

Is a variable, declared in the Java program, which is used to store the value returned by the SQL statement. This may also be a Java array element or object attribute.

SQL_statement

Is any valid SQL statement that returns a value.

An example of the type of SQL statement that returns a value is a call to a PL/SQL function. The following example uses an assignment clause to store the result returned by a call to a PL/SQL function (you will learn more about using PL/SQL in SQLJ in a future column):

int result;
#sql result = { VALUES update_product_price_func(1, 2) };

The PL/SQL function update_product_price_func() is created by the fundamental_user_schema.sql script. It attempts to update the price column of the row in the products table, the ID of which is equal to the first parameter in the function call; the price column is multiplied by the second parameter in the function call. The function will return 0 if the product was found; otherwise, the function will return 1. The value returned from this function is assigned by the SQLJ statement to the Java variable named result.

Recall that everything to the right of #sql is known as a SQLJ clause. A SQLJ clause that contains a result expression, such as the one shown here, is known as an assignment clause. Assignment clauses are fine for storing the results of PL/SQL function calls, but one question I'm sure you're thinking is, "How does an SQLJ program retrieve the values stored in table columns into Java variables?" The answer is by using host variables and expressions.

Pages: 1, 2, 3

Next Page