Oracle8i SQLJ Developer's Guide and Reference Release 8.1.5 A64684-01 |
|
This section presents examples that demonstrate some of the basic essentials of SQLJ. These samples are located in the following directory:
[Oracle Home]/sqlj/demo
Before beginning, connect to the database following the procedures described in "Set Up the Runtime Connection". Note that this includes creating the following SALES
table:
CREATE TABLE SALES ( ITEM_NUMBER NUMBER, ITEM_NAME CHAR(30), SALES_DATE DATE, COST NUMBER, SALES_REP_NUMBER NUMBER, SALES_REP_NAME CHAR(20));
This example demonstrates the use of a named iterator.
For information about named iterators (and positional iterators as well), see "Multi-Row Query Results--SQLJ Iterators".
// ------------------ Begin of file NamedIterDemo.sqlj ----------------------- // // Invoke the SQLJ translator with the following command: // sqlj NamedIterDemo.sqlj // Then run as // java NamedIterDemo /* Import useful classes. ** ** Note that java.sql.Date (and not java.util.Date) is being used. */ import java.sql.Date; import java.sql.SQLException; import oracle.sqlj.runtime.Oracle; /* Declare an iterator. ** ** The comma-separated terms appearing in parentheses after the class name ** serve two purposes: they correspond to column names in the query results ** that later occupy instances of this iterator class, and they provide ** names for the accessor methods of the corresponding column data. ** ** The correspondence between the terms and column names is case-insensitive, ** while the correspondence between the terms and the generated accessor names ** is always case-sensitive. */ #sql iterator SalesRecs( int item_number, String item_name, Date sales_date, double cost, Integer sales_rep_number, String sales_rep_name ); class NamedIterDemo { public static void main( String args[] ) { try { NamedIterDemo app = new NamedIterDemo(); app.runExample(); } catch( SQLException exception ) { System.err.println( "Error running the example: " + exception ); } } /* Initialize database connection. ** ** Before any #sql blocks can be executed, a connection to a database ** must be established. The constructor of the application class is a ** convenient place to do this, since it is executed once, and only ** once, per application instance. */ NamedIterDemo() throws SQLException { /* if you're using a non-Oracle JDBC Driver, add a call here to DriverManager.registerDriver() to register your Driver */ // set the default connection to the URL, user, and password // specified in your connect.properties file Oracle.connect(getClass(), "connect.properties"); } void runExample() throws SQLException { System.out.println(); System.out.println( "Running the example." ); System.out.println(); /* Reset the database for the demo application. */ #sql { DELETE FROM SALES }; /* Insert a row into the cleared table. */ #sql { INSERT INTO SALES VALUES( 101,'Relativistic redshift recorder', TO_DATE('22-OCT-1997','dd-mon-yyyy'), 10999.95, 1,'John Smith') }; /* Insert another row in the table using bind variables. */ int itemID = 1001; String itemName = "Left-handed hammer"; double totalCost = 79.99; Integer salesRepID = new Integer(358); String salesRepName = "Jouni Seppanen"; Date dateSold = new Date(97,11,6); #sql { INSERT INTO SALES VALUES( :itemID,:itemName,:dateSold,:totalCost, :salesRepID,:salesRepName) }; /* Instantiate and initialize the iterator. ** ** The iterator object is initialized using the result of a query. ** The query creates a new instance of the iterator and stores it in ** the variable 'sales' of type 'SalesRecs'. SQLJ translator has ** automatically declared the iterator so that it has methods for ** accessing the rows and columns of the result set. */ SalesRecs sales; #sql sales = { SELECT item_number,item_name,sales_date,cost, sales_rep_number,sales_rep_name FROM sales }; /* Print the result using the iterator. ** ** Note how the next row is accessed using method 'next()', and how ** the columns can be accessed with methods that are named after the ** actual database column names. */ while( sales.next() ) { System.out.println( "ITEM ID: " + sales.item_number() ); System.out.println( "ITEM NAME: " + sales.item_name() ); System.out.println( "COST: " + sales.cost() ); System.out.println( "SALES DATE: " + sales.sales_date() ); System.out.println( "SALES REP ID: " + sales.sales_rep_number() ); System.out.println( "SALES REP NAME: " + sales.sales_rep_name() ); System.out.println(); } /* Close the iterator. ** ** Iterators should be closed when you no longer need them. */ sales.close() ; } }
This example demonstrates the use of a positional iterator.
For information about positional iterators (and named iterators as well), see "Multi-Row Query Results--SQLJ Iterators".
// ---------------------- Begin of file PosIterDemo.sqlj --------------------- // // Invoke the SQLJ translator as follows: // sqlj PosIterDemo.sqlj // Then run the program using // java PosIterDemo import java.sql.* ; // JDBC classes import oracle.sqlj.runtime.Oracle; // Oracle class for connecting /* Declare a ConnectionContext class named PosIterDemoCtx. Instances of this class can be used to specify where SQL operations should execute. */ #sql context PosIterDemoCtx; /* Declare a positional iterator class named FetchSalesIter.*/ #sql iterator FetchSalesIter (int, String, Date, double); class PosIterDemo { private PosIterDemoCtx ctx = null; // holds the database connection info /* The constructor sets up a database connection. */ public PosIterDemo() { try { /* if you're using a non-Oracle JDBC Driver, add a call here to DriverManager.registerDriver() to register your Driver */ // get a context object based on the URL, user, and password // specified in your connect.properties file ctx = new PosIterDemoCtx(Oracle.getConnection(getClass(), "connect.properties")); } catch (Exception exception) { System.err.println ( "Error setting up database connection: " + exception); } } //Main method public static void main (String args[]) { PosIterDemo posIter = new PosIterDemo(); try { //Run the example posIter.runExample() ; //Close the connection posIter.ctx.close() ; } catch (SQLException exception) { System.err.println ( "Error running the example: " + exception ) ; } } //End of method main //Method that runs the example void runExample() throws SQLException { /* Reset the database for the demo application. */ #sql [ctx] { DELETE FROM SALES -- Deleting sales rows }; insertSalesRecord ( 250, "widget1", new Date(97, 9, 9), 12.00, new Integer(218), "John Doe" ) ; insertSalesRecord ( 267, "thing1", new Date(97, 9, 10), 700.00, new Integer(218), "John Doe" ) ; insertSalesRecord ( 270, "widget2", new Date(97, 9, 10), 13.00, null, "Jane Doe" // Note: Java null is same as SQL null ) ; System.out.println("Sales records before delete") ; printRecords(fetchSales()) ; // Now delete some sales records Date delete_date; #sql [ctx] { SELECT MAX(sales_date) INTO :delete_date FROM SALES }; #sql [ctx] { DELETE FROM SALES WHERE sales_date = :delete_date }; System.out.println("Sales records after delete") ; printRecords(fetchSales()) ; } //End of method runExample //Method to select all records from SALES through a positional iterator FetchSalesIter fetchSales() throws SQLException { FetchSalesIter f; #sql [ctx] f = { SELECT item_number, item_name, sales_date, cost FROM sales }; return f; } //Method to print rows using a FetchSalesIter void printRecords(FetchSalesIter salesIter) throws SQLException { int item_number = 0; String item_name = null; Date sales_date = null; double cost = 0.0; while (true) { #sql { FETCH :salesIter INTO :item_number, :item_name, :sales_date, :cost }; if (salesIter.endFetch()) break; System.out.println("ITEM NUMBER: " + item_number) ; System.out.println("ITEM NAME: " + item_name) ; System.out.println("SALES DATE: " + sales_date) ; System.out.println("COST: " + cost) ; System.out.println() ; } //Close the iterator since we are done with it. salesIter.close() ; } //End of method runExample //Method to insert one row into the database void insertSalesRecord( int item_number, String item_name, Date sales_date, double cost, Integer sales_rep_number, String sales_rep_name) throws SQLException { #sql [ctx] {INSERT INTO SALES VALUES (:item_number, :item_name, :sales_date, :cost, :sales_rep_number, :sales_rep_name ) } ; } //End of method insertSalesRecord } //End of class PosIterDemo //End of file PosIterDemo.sqlj
This example demonstrates the use of host expressions.
For information about host expressions, see "Java Host Expressions, Context Expressions, and Result Expressions".
import java.sql.Date; import java.sql.SQLException; import oracle.sqlj.runtime.Oracle; class ExprDemo { public static void main( String[] arg ) { try { new ExprDemo().runExample(); } catch( SQLException e ) { System.err.println( "Error running the example: " + e ); } } ExprDemo() throws SQLException { /* if you're using a non-Oracle JDBC Driver, add a call here to DriverManager.registerDriver() to register your Driver */ // set the default connection to the URL, user, and password // specified in your connect.properties file Oracle.connect(getClass(), "connect.properties"); } int[] array; int indx; Integer integer; class Demo { int field = 0; } Demo obj = new Demo(); int total; void printArray() { System.out.print( "array[0.." + (array.length-1) + "] = { " ); int i; for( i=0;i<array.length;++i ) { System.out.print( array[i] + "," ); } System.out.println( " }" ); } void printIndex() { System.out.println( "indx = " + indx ); } void printTotal() { System.out.println( "total = " + total ); } void printField() { System.out.println( "obj.field = " + obj.field ); } void printInteger() { System.out.println( "integer = " + integer ); } void runExample() throws SQLException { System.out.println(); System.out.println( "Running the example." ); System.out.println(); /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Expressions 'indx++' and 'array[indx]' are evaluated in that order. Because 'indx++' increments the value of 'indx' from 1 to 2, the result will be stored in 'array[2]': Suggested Experiments: - Try preincrement operator instead of post-increment - See what happens if the array index goes out of bounds as a result of being manipulated in a host expression */ array = new int[] { 1000,1001,1002,1003,1004,1005 }; indx = 1; #sql { SELECT :(indx++) INTO :(array[indx]) FROM DUAL }; printArray(); System.out.println(); /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Expressions 'array[indx]' and 'indx++' are evaluated in that order. The array reference is evaluated before the index is incremented, and hence the result will be stored in 'array[1]' (compare with the previous example): */ array = new int[] { 1000,1001,1002,1003,1004,1005 }; indx = 1; #sql { SET :(array[indx]) = :(indx++) }; printArray(); System.out.println(); /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Expressions 'x.field' and 'y.field' both refer to the same variable, 'obj.field'. If an attempt is made to assign more than one results in what is only one storage location, then only the last assignment will remain in effect (so in this example 'obj.field' will contain 2 after the execution of the SQL statement): */ Demo x = obj; Demo y = obj; #sql { SELECT :(1), :(2) INTO :(x.field), :(y.field) FROM DUAL }; printField(); System.out.println(); /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ All expressions are evaluated before any are assigned. In this example the 'indx' that appears in the second assignment will be evaluated before any of the assignments take place. In particular, when 'indx' is being used to assign to 'total', its value has not yet been assigned to be 100. The following warning may be generated, depending on the settings of the SQLJ translator: Warning: Repeated host item indx in positions 1 and 3 in SQL block. Behavior is vendor-defined and non portable. */ indx = 1; total = 0; #sql { BEGIN :OUT indx := 100; :OUT total := :IN (indx); END; }; printIndex(); printTotal(); System.out.println(); /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Expression 'indx++' in the following example is evaluated exactly once, despite appearing inside a SQL loop construct. Its old value before increment is used repeatedly inside the loop, and its value is incremented only once, to 2. */ indx = 1; total = 0; #sql { DECLARE n NUMBER; s NUMBER; BEGIN n := 0; s := 0; WHILE n < 100 LOOP n := n + 1; s := s + :IN (indx++); END LOOP; :OUT total := s; END; }; printIndex(); printTotal(); System.out.println(); /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ In the next example there are two assignments to the same variable, each inside a different branch of an SQL 'if..then..else..end if' construct, so that only one of those will be actually executed at run-time. However, assignments to OUT variable are always carried out, regardless of whether the SQL code that manipulates the return value has been executed or not. In the following example, only the first assignment is executed by the SQL; the second assignment is not executed. When the control returns to Java from the SQL statement, the Java variable 'integer' is assigned twice: first with the value '1' it receives from the first SQL assignment, then with a 'null' value it receives from the second assignment that is never executed. Because the assignments occur in this order, the final value of 'integer' after executing this SQL statement is undefined. The following warning may be generated, depending on the settings of the SQLJ translator: Warning: Repeated host item indx in positions 1 and 3 in SQL block. Behavior is vendor-defined and non portable. Suggested experiments: - Use a different OUT-variable in the 'else'-branch - Vary the condition so that the 'else'-branch gets executed */ integer = new Integer(0); #sql { BEGIN IF 1 > 0 THEN :OUT integer := 1; ELSE :OUT integer := 2; END IF; END; }; printInteger(); System.out.println(); /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ } }