11.1 Static and Dynamic SQL
All the SQL statements you have seen so far in this book have been static, that is, the tables and columns referenced have remained fixed during the execution of a program. The following is an example of a static SQL statement:
SELECT id, first_name, last_name FROM customers;
The next statement is still considered static, even though the asterisk (*) is used to select all the columns:
SELECT * FROM products;
A dynamic SQL statement is one that may be set when the program is run, and in which the tables and columns referenced in the statement may be changed from time to time. The following example, which uses JDBC calls, shows a dynamic SQL statement being generated and executed at runtime:
String sql_statement = "SELECT id, first_name, last_name " + "FROM customers " + "WHERE " + where_clause; PreparedStatement prepared_statement jdbc_conn.prepareStatement(sql_statement); ResultSet result_set = prepared_statement.executeQuery( );
The prepareStatement( ) method sends the sql_statement string containing the SQL statement to the database for parsing. The executeQuery( ) method runs the SQL statement and returns a ResultSet object, which is the JDBC equivalent of a SQLJ iterator. I talk more about JDBC result sets later in this chapter.
The WHERE clause in this example can be passed in from the command line when the program is run, and can contain any valid SQL WHERE clause syntax. This is the power of dynamic SQL: the ability to specify the SQL statement when the program runs.
11.1.1 Dynamic SQL in SQLJ 9i
In this section, I show how to use SQLJ 9i to handle dynamic SQL statements directly in your SQLJ programs. The best way to learn how to use dynamic SQL using SQLJ 9i is to examine an example. The following example generates a dynamic SQL statement that selects the first_name, last_name, and phone column values from the customers table for customer #1:
int id = 1; String first_name = null; String last_name = null; String phone = null; String table = "customers"; String query = "id = " + id; String column = "first_name"; #sql { SELECT :{column}, last_name, phone INTO :first_name, :last_name, :phone FROM :{table} WHERE :{query} }; The table variable holds the name of the table ("customers"), the query variable holds the details of the WHERE clause ("id = 1"), and the column variable holds the name of a column used in the query ("first_name"). You can see that the dynamic parts of the SQL statement are placed within curly braces, which are in turn prefixed by a colon. These constructs are known as meta bind expressions. At runtime, the meta bind expressions in the previous statement are evaluated, and their values are substituted into the final statement, which appears as follows:
#sql { SELECT first_name, last_name, phone INTO :first_name, :last_name, :phone FROM customers WHERE id = 1 }; The full syntax for a meta bind expression is:
:{ bind_expression [:: database_replacement ] } The syntax elements are as follows:
- bind_expression
-
Specifies a Java String value, or an expression that evaluates to a Java String value, and may be used to specify any of the following items:
-
Table name
-
Column name
-
WHERE clause
-
Schema name
-
Function call
-
Role name
-
Package name
- database_replacement
-
Specifies a database table, column, WHERE clause, etc., that is used by SQLJ when translating the dynamic SQL statement. This is optional, but it enables SQLJ to perform compile-time semantic checking of your statement (to get SQLJ to do this, you must use the -user command-line option; see Appendix B for details). When SQLJ translates the statement, it will use what you have specified in database_replacement in place of bind_expression to check the semantics of your statement. If you don't specify database_replacement, your dynamic SQL statement will not be semantically checked.
You can use meta bind expressions in both DML and DDL statements, but there are some limitations that you need to be aware of. You cannot use a meta bind expression for either of the following purposes:
-
Specifying an INTO clause
-
Supplying a value to a CALL, VALUES, SET, FETCH, or CAST statement
The following example is derived from the first example in this section. The difference is that I've added database replacements, which allow SQLJ to perform compile-time semantic checking of the statement:
#sql { SELECT :{column :: first_name}, last_name, phone INTO :first_name, :last_name, :phone FROM :{table :: customers} WHERE :{query :: id = 1} }; So, if you invoke the sqlj command-line utility with the -user option to have it check the semantics of your SQL statements, it uses the database replacements when checking the dynamic SQL statement. In effect, the statement that it checks is as follows:
#sql { SELECT first_name, last_name, phone INTO :first_name, :last_name, :phone FROM customers WHERE id = 1 }; Database replacements are useful because the SQLJ compiler has no way of executing your code to determine which values it will actually supply at runtime. By using database replacements, you can specify reasonable values to use when checking the semantics of the statement at compile time. Of course, the database replacement could be anything, and needn't even remotely match what your statement actually does at runtime. They are just optional guides to the online checker.
11.1.2 Example Program: Sqlj9iDynamicExample1.sqlj
This section contains a complete program, shown in Example 11-1, illustrating the use of dynamic SQL in a SQLJ 9i program. This program performs the following steps:
-
Creates and initializes three variables, named table, query, and column, that contain the strings "customers", "id = 1", and "first_name", respectively.
-
Uses a dynamic SQL statement to retrieve the details of customer #1 from the customers table.
-
Displays the customer details.
Example 11-1. SqljJ9iDynamicExample1.sqlj
/* The program Sqlj9iDynamicExample1.sqlj illustrates how to embed dynamic SQL statements directly in a SQLJ program. */ // import required packages import java.sql.*; import oracle.sqlj.runtime.Oracle; public class Sqlj9iDynamicExample1 { public static void main(String [] args) { try { Oracle.connect( "jdbc:oracle:thin:@localhost:1521:orcl", "fundamental_user", "fundamental_password" ); int id = 1; String first_name = null; String last_name = null; String phone = null; String table = "customers"; String query = "id = " + id; String column = "first_name"; #sql { SELECT :{column}, last_name, phone INTO :first_name, :last_name, :phone FROM :{table} WHERE :{query} }; System.out.println("Details for customer " + id + ":"); System.out.println(column + " = " + first_name); System.out.println("last_name = " + last_name); System.out.println("phone = " + phone); Oracle.close( ); } catch (SQLException e) { System.err.println("SQLException " + e); System.exit(1); } } // end of main( ) } The output from this program is as follows:
Details for customer 1: first_name = John last_name = Smith phone = 650-555-1212
