Software: Apache. PHP/5.5.15 uname -a: Windows NT SVR-DMZ 6.1 build 7600 (Windows Server 2008 R2 Enterprise Edition) i586 SYSTEM Safe-mode: OFF (not secure) E:\oracle\product\10.2.0\client_2\sqlj\doc\ drwxrwxrwx | |
| Viewing file: Select action/file-type: SQLJ: Tricks, Traps, and Gems Ekkehard Rohwedder, Oracle Corporation Who is this for? You know SQL and Java, and you want to learn Oracle SQLJ — read on!What is this? This is a primer —a small introductory pamphlet— on Oracle’s version of SQLJ. If you use paint primer, you cover the entire area, although the primer might not cover everything in the required depth or finish. You also use primer to ignite an explosive charge. We hope you will find this primer wide-ranging, brief, indispensable, and lighthearted — in other words: a blast.What else do I get? A truckload of warnings, references, and exercises.
Installing SQLJ Install the Java Development Kit 1.1 or later (you can get it from www.sun.com). Note that JDK 1.0.2 will not do. Make sure that the current directory ‘‘.’’ is in your CLASSPATH. You should now be able to say java and javac.Get an Oracle JDBC driver (unless you already have one installed). It contains the file classes111.zip (and classes12.zip if you are using JDK 1.2), which must be placed in your CLASSPATH. Depending on the type of driver you use, you might also need a dynamic library (*.so or *.dll) in your LD_LIBRARY_PATH or, respectively, PATH. Follow the JDBC installation instructions for this. You should now be able to compile and run JDBC programs.If you obtained Oracle SQLJ from an Oracle8i or later database installation, then you have an [Oracle Home]. The SQLJ executable (named sqlj or sqlj.exe depending on your flavor of operating system) lives in [Oracle Home]/bin, which will be in your PATH. You must also put [Oracle Home]/sqlj/lib/translator.zip —which contains the SQLJ translator— in your CLASSPATH.If you obtained Oracle SQLJ from the Oracle website, you must perform a bunch of untarring and (g-)unzipping, and you will eventually end up with a sqlj directory. The executable lives in sqlj/bin and must be in your PATH, and the sqlj/lib/translator.zip file must be in your CLASSPATH.Are you greeted with a help screen when you say sqlj? Yes? Then you are in business!
What’s in a Number? This primer describes the Oracle SQLJ 8.1.6 release. Applications that you created with the Oracle SQLJ 8.1.5 translator will continue to run with the 8.1.6 runtime, or you can recompile them to take advantage of new features, such as statement caching (see Section "5.8 Give Me Speed … or More Speed"). Although you would typically use the 8.1.6 version of SQLJ with Oracle’s 8.1.6 JDBC driver, you can also use it together with the 8.0.6 or 7.3.4 JDBC versions. The following features are new in version 8.1.6: the -checksource flag (see Section "1.1 I Need To Tell You"), the method Oracle. close() (see Section "1.4 Getting Connected"), support for JDBC 2.0 types and interfaces (java.sql.Struct/Ref/Array/Blob/ Clob and SqlData - see Sections "3.3 She Is An Oracle Type" and "5.5 Let’s Get Objective"), the -jdblinemap and -P-CshowThreads options (see Section "4.2 Where Is The Bug"), the offline SQL parser (see Section "5.7 Isn’t It — Portable"), performance enhancements (see Section "5.8 Give Me Speed … or More Speed"), and support for iterator subclassing (see Section "5.9 Classy Kinds of Iterators").Contents Section 1 Skeleton of a SQLJ Program: SQLJ command line / Online checking of SQLJ programs / SQLExceptions in SQLJ / Connecting to the database at runtime / Starting a TCP/IP listener on UnixSection 2 The Meat of A SQLJ Program: SQL statements in SQLJ / Host expressions / Stored function calls / Stored Procedure calls and argument modes / SQLJ iterator concept / Named iterators / Positional iteratorsSection 3 A Universe of Types: Some SQL, Some Java: JDBC types and NULL handling in SQLJ / SQLJ Stream types / Oracle type extensionsSection 4 Your Own Private Translator: Helpful SQLJ options / Debugging options: -linemap and -P-debug / Programming SQLJ appletsSection 5 The Rest of the Story — Advancing the Features: SQLJ-JDBC interoperability / Using connection context instances / Using execution contexts / Using typed connections / Using Oracle Objects / SQLJ in JServer / Portability / Performance / Iterator SubclassingFeedback and Acknowledgements Oracle SQLJ is supported through Oracle’s World Wide Support. Call 911 in the Bug Database to reach SQLJ — yes, the product number is 911! Please direct other questions, corrections, praise, scathing critique, postcards, solutions to exercises, suggestions, encouragement, and so on to the author of this pamphlet at erohwedd@us.oracle.com.Thank you Brian Becker, Brian Wright, and Ellen Barnes for comments on an earlier draft! Thanks Adrian, Pierre, and Jeremy for the encouragement!
1 Skeleton of a SQLJ Program SQLJ command line / Online checking of SQLJ programs / SQLExceptions in SQLJ / Connecting to the database at runtime / Starting a TCP/IP listener on Unix In this section we look at the essential components that every SQLJ program needs — in other words, the skeleton. Before getting the skeleton out of the closet, some preliminary remarks. 1.1 I Need To Tell You Let’s start out by looking at how you translate, compile, and run your SQLJ program. Make sure that it lives in a file with the extension *. sqlj (instead of *.java). Then translate and compile your files in one step.sqlj MyFile.sqlj MyOtherFile.sqlj MyJavaFile.java Yes, this even compiles your Java files in the same fell swoop. This should —if everything goes all right— create *. class files (and some *.ser files), and you can then issuejava MyFile provided, of course, that MyFile has a methodpublic static void main(String[] args) { ... } Even though you are familiar with . class files —the result of Java compilation— you will be curious about these .ser files that the SQLJ translator produces. We also call them (serialized) profiles. They are serialized Java objects that contain all the information about the static SQL statements in your .sqlj source files, such as the SQL code, the types and names of the host variables that occur in the SQL statement, and what kind of SQL statement this is (a commit/rollback, a query, a DML statement, and so on).
1.2 Want To Check It Out? - Get Online! Without a database, the SQLJ translator can perform only offline checking of your SQL code. If you want to get your database involved, that is, you want SQLJ to perform online checking, then you must tell the translator how to connect to it. Specifically, you must supply a user name (corresponding to the database schema you want to connect to) and a password. sqlj -user=scott/tiger MyFile.sqlj Of course, you also want to be able to say which database you’d like to talk to and how — that is, with which protocol. Because SQLJ uses JDBC underneath, this is accomplished by a JDBC URL. By default, SQLJ uses the OCI8 JDBC URL. This is the string "jdbc:oracle:oci8:@" - see Section "1.4 Getting Connected". However, you can also specify your own URL. For example, you can request the Oracle OCI7 driver (if available) as follows.sqlj -user=scott/tiger@jdbc:oracle:oci7:@ MyFile.sqlj And there is a special shorthand notation if you use Oracle’s thin JDBC driver. sqlj -user=scott/tiger@my_host:1521:my_oracle_sid MyFile.sqlj Finally, you can use "shorthand" on the command line and write -u instead of -user=, as follows.sqlj -u scott/tiger MyFile.sqlj Once you have gained some familiarity with SQLJ, you might want to try the exercises below to learn more about how the SQLJ translator reports errors, both offline and online (see also SQLJ Developer’s Guide and Reference, Chapter 9, Section "Internal Translator Operations" for more details). (a) What happens if you omit the password in the (b) You can use the sqlj.properties file for storing command line options used for sqlj invocation. Investigate the format of this file, and store the password information in it. What happens if an option is given in both the command line and the sqlj.properties file? 1.3 Errors Want To Be Caught One of the first lines in your SQLJ program will be import java.sql.SQLException; Whenever something goes wrong while running your SQLJ program, your SQLJ statements and any methods in the SQLJ runtime API throw a SQLException. Either declare that your program throws a SQLException, or puttry { ... } catch (SQLException exn) { ... } blocks in your program.
public class test { public static void main(String[] args) { #sql { ROLLBACK }; } } What do you see when you run sqlj test.sqlj? Why? How can you fix this? After fixing and translating, run java test. What happens? Why? Now read Section "1.4 Getting Connected" and fix this problem. 1.4 Getting Connected What good is a SQL program without a database connection? Another important import line is the following. import oracle.sqlj.runtime.Oracle; The first thing you must do before executing a SQLJ statement is to connect to the database. (Note: Not true in the server — the stored procedure or function that is implemented by a SQLJ method runs in a database session that already has a connection going for it!) Oracle.connect("jdbc:oracle:oci8:@", "scott", "tiger"); Your user name —equivalent to the database schema you are connecting to— is "scott" and your password "tiger". The first argument to connect() is the JDBC URL. If you want to connect to a different database, just place the database alias from your $ORACLE_HOME/work/tnsnames.ora after the "@". The following are connect strings for Oracle JDBC.
Table 1 - List of Oracle JDBC URLs. The following is a sample thin JDBC URL: " jdbc:oracle:thin:@localhost:1521:orcl".The connect() method also has a twin: Oracle.close() — always invoke this method to close your connection!
jdbc:oracle:oci:@ 1.5 Is Anybody Listening? If you want to connect to your database with the thin JDBC driver, then your database listener must listen on a TCP/IP port. If you belong to the GUI-challenged group of Unix users, you can achieve this by editing your $ORACLE_HOME/work/listener.ora file, adding an additional line to:LISTENER = (ADDRESS_LIST= as follows: LISTENER = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=port)) ) Now you must stop and then re-start your listener to pick up the new settings: lsnrctl stop; lsnrctl start
2 The Meat of A SQLJ Program SQL statements in SQLJ / Host expressions / Stored function calls / Stored Procedure calls and argument modes / SQLJ iterator concept / Named iterators / Positional iterators When you want to embed SQL in Java, you will inevitably use SQLJ statements and —in most cases— SQLJ iterators. This chapter explains the basic concepts of both. 2.1 SQLJ is Embedded SQL So how do we issue "COMMIT" to get the changes in our transaction committed, or —for that matter— how do we write other SQL statements? It's straightforward. #sql { UPDATE emp SET sal = 3000 WHERE ename = 'SCOTT' }; #sql { COMMIT }; You can put any SQL statement (including DDL, DML, PL/SQL declarations and blocks) between the curly braces, and it will get sent to the database as is - SQL comments and all!
What happens if you put a semicolon ";" at the end of the SQL statement (just before the closing curly brace)? 2.2 Cooler Than Host Variables: Host Expressions SQL statements that cannot retrieve values from or send values to the database are not terribly programmable. That's where host variables come in. They are Java variables prefixed with " :", placed inside the SQL statement, that can retrieve and/or send data values.String name = "SCOTT"; Double raise = new Double(1.08); Double salary; #sql { UPDATE emp SET sal = sal * :raise WHERE ename = :name }; #sql { SELECT sal INTO :salary FROM emp WHERE ename = :name }; But SQLJ is more flexible than that — you can use Java expressions instead of host variables. Just make sure that the host expression is enclosed between " :(" and ")".String[] emps = new String[] { "Scott", "Miller", "King" }; double[] raises = new double[] { 8.0, 4.0, 0.0 }; for (int i=0; i<emps.length; i++) #sql { UPDATE emp SET sal = sal * :(1.0 + raises[i] / 100.0) WHERE ename = :(emps[i].toUpperCase()) }; int j=0; double[] s = new double[emps.length]; while (j<emps.length) { #sql { SELECT sal INTO :(s[j]) FROM emp WHERE ename = :(emps[j++].toUpperCase()) }; }
2.3 Let's Get Results — Functions First We already saw how results can be received from a SQL statement when we used the SELECT-INTO statement. More often, results from a SQL operation are received by a SQLJ assignment statement. Let's look at a call to the (built-in) SQL function SYSDATE(). java.sql.Date today; #sql today = { VALUES( SYSDATE() ) }; System.out.println("The database thinks that today is "+today); The VALUES( ... ) syntax is SQLJ-specific syntax for calling a stored function. Such functions might also take arguments, such as in the following code snippet. String in10Days; #sql in10Days = { VALUES( DELTA_DATE(:today, 10) ) }; Note that we can receive a SQL DATE value in different formats in Java — in our examples, as a java.sql.Date and as a java.lang.String.Can you create the PL/SQL function in the SQLJ program itself? 2.4 Are We OUT-Moded Yet? — Getting Into Procedures In our discussion above, we glossed over the fact that host variables (host expressions) are used in different modes.
By default, host-expressions have the mode IN, with the exception of host-expressions in INTO-lists, which have the mode OUT. In all other cases, you have to explicitly prefix the host expression with the mode. For example: int x; int y = 10; #sql { BEGIN :OUT x := :y + :y; END }; Oooops. There is one more exception (but this is the last one, I promise): in the SET statement, which is part of the SQLJ language, the left-hand side of the assignment is implicitly OUT. Thus the following is functionally identical to our BEGIN ... END block above.#sql { SET :x = :y + :y }; Stored Procedures (and Oracle Stored Functions) can have parameters with all three modes. The SQLJ syntax for calling stored procedures is illustrated in the following code fragment. int x = 10; int y; int z = 20; #sql { CALL MyProc( :x, :OUT y, :INOUT z ) };
#sql { DECLARE ... PL/SQL declarations ... BEGIN ... PL/SQL statements ... END }; 2.5 Look Ma: Result Sets are Typed … and Are Called Iterators! When you execute a query in JDBC, it will return a java.sql.ResultSet. You then retrieve the rows in the result set through a processing loop. The next() method on the ResultSet returns true if another row is available. In this case, the row is retrieved, and the individual columns can be accessed through getXxxx(column_number) calls, where Xxxx represents the Java type, with which you want to retrieve the column, such as String, Int (for int), Double (for double), ...SQLJ does not have the "amorphous" result sets of JDBC. SQLJ query results are always strongly typed — each column in the result has a particular Java type. To differentiate these "typed result sets" from the JDBC notion of ResultSet and from the SQL notion of cursor, we call them iterators. SQLJ provides two flavors of iterators.
Enough talk — you want to see code? Hold on! 2.6 What's in a Name? So, how do you get your iterator with all of these names and types? You declare it, of course! #sql iterator NamedIter (String ename, Double sal); This line creates a Java class declaration for the NamedIter class — right where you wrote it. This class has next() and close() methods — just like the java.sql.ResultSet. Instead of the getXxxx(column_name) accessors, however, your NamedIter class sports two fully customized, tailor-made, individualized accessor methods known as String ename() and Double sal(). A minor detail: you will have most success with this declaration if you put it where Java class declarations are permitted.Let’s declare ourselves a NamedIter.NamedIter n; And —better yet— populate it with the result from a query. #sql n = { SELECT ename, sal FROM emp }; How do you use this iterator? Whaddayaknow, I told you all about these methods that you find in NamedIter.while (n.next()) { System.out.println(n.ename()+" would like to make "+ (n.sal()*2)); } n.close(); Open questions. You should now have a gazillion questions about named iterators, such as: Where do you declare an iterator type? Does the order in the SELECT list matter? How do you match SQL and Java names? What about case sensitive and case insensitive names? Can you say "SELECT * FROM EMP"? and so on. Not to worry — you will discover the answers to all these questions from the exercises below!
2.7 Get Into Position! Declarations for positional iterators are even easier than those for named iterators. #sql iterator PosIter (String, Double); In the processing loop for the positional iterator, you issue FETCH statements to retrieve the next row of data into host variables. After a FETCH, the endFetch() call returns true if the FETCH was successful, and false if there was no row left that could be fetched. Positional iterators require neither the next() method nor the accessors that we encountered on the SQLJ named iterator. All of this is best demonstrated by rewriting our earlier example to now use a positional iterator.String name = null; Double salary = null; PosIter p; #sql p = { SELECT ename, sal FROM emp }; while (true) { #sql { FETCH :p INTO :name, :salary }; if (p.endFetch()) break; System.out.println(name + " would like to make " + (salary * 2)); } p.close(); #sql { FETCH :p ..... }; if (p.endFetch()) break; ..... process fetched data ..... } Otherwise, many different things can (and will) go wrong!
3 A Universe of Types: Some SQL, Some Java JDBC Types and corresponding SQL Types / NULL handling in SQLJ / SQLJ Stream types / Oracle type extensions So far, we just used a bunch of Java types in our SQLJ program, but we really had no clue which types are permitted and how they are used. SQLJ includes all of the types in JDBC —this is described in the next section— and then some. 3.1 What Type Are You? A description of JDBC-supported types and how they are used in SQLJ. Numeric types. This includes: int, Integer, long, Long, short, Short, byte, Byte, boolean, Boolean, double, Double, float, Float, and —just so you can see I am not stuttering— java.math.BigDecimal. So, what’s the deal with supporting both the primitive type (such as int, or double) and the corresponding Java object type (such as Integer, or Double)? SQL NULL always maps to Java null — and the reverse. Thus, if you read a NULL value into an Integer, you receive a Java null, but if you read it into an int, you can get only a SQLException.Character types. The Java type String represents these very well, thank you. Note that the Java char and Character types are not supported by SQLJ or by JDBC (besides, they could only hold a single character, anyway). Also useful might be the character streams sqlj.runtime.AsciiStream and sqlj.runtime.UnicodeStream. We will examine them more closely in Section "3.2 Stumbling On Streams".Date and Time types. These include java.sql.Time, java.sql.Timestamp, and java.sql.Date. Yes, that is java.sql.Date, and not java.util.Date — don’t confuse the two!Raw types. Raw data can be represented as byte[], aka "byte-array", or —in stream form— as sqlj.runtime.BinaryStream, which we discuss in the next section.Result sets, cursors, and so on. What representation would you expect? java.sql.ResultSet and iterator types? Yes, right on the button! A little secret here: using these as host variables is not part of the JDBC specification, but permitted by Oracle.Did we miss some types here? Yes — let’s digress a bit into JDBC history: Java types for several useful SQL entities, such as ROWIDs, BFILEs, BLOBs, and structured types are, unfortunately, not in the JDBC 1.2 specification, which is what JDK 1.1.X uses. However, Java types for these are mentioned in JDBC 2.0, which goes together with JDK 1.2 — go figure! Anyway, this means that in a JDK 1.1 environment these types must be represented through Oracle-specific extensions that we talk about in Section "3.3 She Is An Oracle Type". So what are the corresponding SQL types that you can use for these Java types? Numeric types. Use any of the numeric SQL types, such as INTEGER, NUMBER(prec[,scale]), REAL, SMALLINT, and so forth — these are all some form of NUMBER, anyway. Of course, you also must be sure that your Java type can hold the values that you expect in the SQL type and the reverse.Character types are CHAR, VARCHAR, VARCHAR2, and LONG.Date and Time type is DATE.Raw types are RAW and LONG RAW.ResultSet/iterator type is REF CURSOR.Additionally, SQL, as well as SQLJ with its underlying JDBC driver, perform several implicit conversions. For example, you can retrieve numeric or date values as String, or you can insert Strings that represent numbers where numeric SQL values are expected.
What does this mean for type checking between SQL and Java types by the SQLJ translator? Take, for example, a positional iterator that contains a 3.2 Stumbling On Streams The SQLJ specification adds the new stream types sqlj.runtime.BinaryStream, sqlj.runtime.AsciiStream, and sqlj.runtime.UnicodeStream for "wrapping" a LONG (or LONG RAW) column in the database. All three stream types implement a java.io.InputStream. Note that when you retrieve the value of a LONG column, all data that occurs in the same row prior to that column is lost. This has a number of consequences.
3.3 She Is An Oracle Type If you are willing to go out with an Oracle Type, we have a whole roster of them for you to choose from. They all live in the same place: oracle.sql. You might want to call up JDBC Developer’s Guide and Reference, Chapter 4 "Oracle Extensions" for these types, rather than going on a blind date. Or, if you are more adventurous, perform a quick background check with javap oracle.sql.Xxxxx and then give Xxxxx a whirl!oracle.sql.ROWID - represents a database ROWID.oracle.sql.CLOB - represents a CLOB, a large character object.oracle.sql.BLOB - represents a BLOB, a large binary object.oracle.sql.BFILE - represents a BFILE, a binary file.oracle.sql.REF - represents a REF, a reference to a structured object. See Section "5.5 Let’s Get Objective".oracle.sql.ARRAY - represents a VARRAY or a nested table. See Section "5.5 Let’s Get Objective".oracle.sql.STRUCT - represents a user-defined structured object. See Section "5.5 Let’s Get Objective"."Wait a minute!" you are now going to say. "All these types (with the exception of ROWID) look rather familiar — I remember now, these are JDBC 2.0 types ( java.sql.Clob/Blob/Bfile/Ref/Array/Struct)." Right on the money! If you run SQLJ under JDK 1.2 (with the Oracle JDBC classes classes12.zip), you can also use these JDBC types instead of the Oracle types. In fact, all these Oracle types implement the interface of their corresponding JDBC type.The next set of types represent efficient "wrappers" that preserve the binary representation of data in the database. These types are ultra-fast when exchanging information with the database, because they require zilch conversion effort. And they represent the information completely faithfully down to the last original bit. You can use these types the same way you use their corresponding JDBC cousins. Note that these types are also endowed with conversions methods and constructors involving the Java-native types that we talked about earlier. oracle.sql.NUMBER - represents all numeric SQL data.oracle.sql.CHAR - represents all character data in SQL.oracle.sql.DATE - represents all date and time data in SQL.oracle.sql.RAW - represents all raw data in SQL.
However, in Oracle JDBC you can use oracle.jdbc2.Blob/Struct etc. types instead. (Note: SQLJ does not support types in oracle.jdbc2.) If your application uses oracle.jdbc2 types, you must recompile it if you want to run it under JDK 1.2. Why?
4 Your Own Private Translator Helpful SQLJ options / Debugging options: -linemap and -P-debug / Programming SQLJ applets This chapter describes features of the SQLJ translator itself. However, we will only cover the fun parts here. For the full story about basic translator features, see SQLJ Developer’s Guide and Reference, Chapter 8, Section "Basic Translator Options", and if —by golly— you want the advanced SQLJ translator features, too, you must see the doctor in SQLJ Developer’s Guide and Reference, Chapter 8, Sections "Advanced Translator Options" and "Translator Support and Options for Alternative Environments". 4.1 The Translator Is Talking Back So you are stuck, and you want to get help. Do not fret — the SQLJ translator might just be able to give you the assistance you need. sqlj -help - gives you a short help message with the main SQLJ command line options. Additionally, whenever you just say sqlj without other arguments, this is interpreted as a cry for help.sqlj -help-long - gives a really long message. This is most useful if you want to figure out which command line options the translator is actually using and where they come from. In this case, you might want to add your original command line as well. Or you can just check on some obscure translator option.sqlj -version-long - shows you the SQLJ translator version, as well as the version of your Oracle JDBC driver and your Java VM. If the JDBC driver has version 0.0, you know that you need to install it and put it in your CLASSPATH!sqlj -status ... - add the -status flag to your command line if you want to be entertained during those really long translations/compiles. The SQLJ translator will make sure that it regularly gets back to you to let you know what it is up to at the moment.sqlj -explain ... - add this option to your command line if you get one or more of these !@#$!% (incomprehensible) error or warning messages. The SQLJ translator will look up the Cause and/or Action explanation for the message in the SQLJ manual and print it out, just for you. Isn’t that a nice touch!sqlj -n ... - add this if you want to see what is actually invoked by the SQLJ wrapper script/wrapper executable. Or just use it to amaze yourself that you can use pretty much the same command line options you have grown to know and love in javac and in loadjava. And you can even see how they look in SQLJ-Translatorese. This option shows what would have been invoked, but does not run sqlj for real — just as make -n does.
4.2 Where Is The Bug? We trust you will have noticed that error messages issued by your Java compiler on code that originates from a SQLJ file are reported on the SQLJ file, and not on the generated Java file. However, when your program throws exceptions at runtime, line numbers (such as those issued by printStackTrace()) are shown in terms of the generated Java files.You knew, of course, that we’d have a cure for that problem, too. Just add the flag -linemap to your command line during translation. Then the translator will fix up the file names and the line numbers in those class files that were compiled from original SQLJ files.If you now pick up Sun’s Java debugger jdb to debug your SQLJ program, you’ll see that … it does not work: jdb refuses to show the SQLJ source. No wonder — they only taught it about .java source files! Okay, so we give you another magic command line, spell: -jdblinemap, to be used instead of -linemap whenever you must trick that silly little (de)bugger.If you must trace how your SQLJ program talks with the database, you can install a profile auditor in the SQLJ profile files (those pesky little . ser files that hold the static SQL part of your SQLJ program and that we first encountered in Section "1.1 I Need To Tell You"). After the usual SQLJ translation and compilation, tracing can be added by issuing the following command.sqlj -P-debug *.ser The above assumes that all the generated . ser files are in the current directory.At the end of the day, SQLJ runtime calls turn into calls to Oracle’s JDBC runtime. You can trace these, too with the following call to the JDBC API: java.sql.DriverManager.setLogStream(System.out).-linemap and -jdblinemap options.
Debug your SQLJ programs with the jdb debugger. Note: make sure to use -jdblinemap, instead of -linemap. 4.3 Appletmania Do not walk, but run, to the [Oracle Home] /sqlj/demo/applets directory. View index.html in your browser, and then click on the Applet.readme link, and do everything in it. See ya!Already back? Now read the AppletOracle.readme file, and do what it says.Congratulations — you are an applet expert! We just summarize the main gotcha’s for you.
5 The Rest of the Story — Advancing the Features SQLJ-JDBC interoperability / Using connection context instances / Using execution contexts / Using typed connections / Using Oracle Objects / SQLJ In JServer / Portability / Performance / Iterator Subclassing This chapter delves into a few of the advanced SQLJ features - but not very deeply. We’ll look at how to mesh dynamic SQL with SQLJ, at the mysteries of connection contexts and execution contexts, and we offer encouragement for those who want to start using Oracle Objects with SQLJ as well as for those who want to program with SQLJ in JServer. We consider how to write portable and performant SQLJ programs. The final SQLJ gem that we introduce in this primer is iterator subclassing. 5.1 A Dynamic Program SQLJ works just fine and dandy with static SQL — where you know the shape of SQL statements and queries beforehand, and only the actual data that is passed to (or from) the database varies. Now imagine that you must write a program that can make up the WHERE clause of a SELECT on the fly. Guess you’d better forget all about SQLJ, right? Nope — you can still use SQLJ! SQLJ and JDBC are close-knit buddies: JDBC connections and SQLJ connection contexts are mutually convertible, and so are java.sql.ResultSets and SQLJ iterators. Let’s look at the specifics. Connecting from JDBC to SQLJ. All connection context constructors and initializers can take an existing JDBC connection. Example:java.sql.Connection conn = DriverManager.getConnection( ....);Oracle.connect(conn); Now SQLJ and JDBC share the same session.
Connecting from SQLJ to JDBC. All SQLJ connection contexts have the getConnection() method, which allows you to retrieve the underlying JDBC connection. What? You say, you do not know how to get the SQLJ connection context that you set with Oracle.connect(....)? Of course, you don’t! Because I have not yet told you how. Now squint your eyes at the lines below.java.sql.Connection conn = As you can tell, the monster expression sqlj.runtime.ref.DefaultContext.getDefaultContext() gives you the value of the static(!) SQLJ default context.Passing result sets from JDBC to SQLJ. We want to pass off a JDBC result set as a SQLJ iterator. Well, the SQLJ standards committee, in their wisdom, agreed that it was not sufficient to just construct a SQLJ iterator instance from a JDBC ResultSet. You must assign it explicitly with a SQLJ CAST statement.SomeIterator iter; java.sql.ResultSet rs = stmt.executeQuery(); #sql iter = { CAST :rs }; Passing iterators from SQLJ to JDBC. This one is a breeze. You just call the iterator’s getResultSet() method and –voilà— your JDBC ResultSet.
5.2 Being Well Connected - Explicitly Up until now you have been brainwashed. We made you believe that there is only a single, static connection in your SQLJ program, that you set once with Oracle.connect(....) and then forget about. Though this helps sufferers of carpal tunnel syndrome (never type an explicit connection!) and makes great copy for SQLJ marketers ("look how short SQLJ programs are"), this is not the way the world works.
then you should, nay, you must use explicit SQLJ connections. Don’t worry — you’ll learn all about explicit connections in a jiffy. The most bland SQLJ connections are called sqlj.runtime.ref.DefaultContext, and we parade them next.import sqlj.runtime.ref.DefaultContext; ... DefaultContext ctx1 = new DefaultContext("jdbc:oracle:oci8:@", "scott", "tiger", false); DefaultContext ctx2 = new DefaultContext(aJdbcConnection); #sql [ctx1] { UPDATE emp SET sal = sal / 2 }; #sql [ctx2] { UPDATE emp SET sal = sal * 2 }; You see, you can specify explicitly which connection your SQLJ statements are supposed to use — just put the connection context instance (or an expression evaluating to one) in those square brackets: [context]. If you do not do this, your statement will use the default context, which you have been setting all along with Oracle.connect().Note that the connection context constructor DefaultContext() supports the same signatures as java.sql.DriverManager.getConnection(), with an additional boolean argument at the end that specifies whether auto-commit is on or off. Additionally, you can create a new context from a JDBC connection or (not shown) from another SQLJ context — this, of course, inherits both the session, as well as the session’s auto-commit setting from that connection.Let’s digress once more with a little JDBC/SQLJ background. In JDBC, auto-commit is by default on. This was considered a rather dorky default setting by the SQLJ proponents (which mostly come from big-database companies). Rather than having a default setting opposite to JDBC, the auto-commit on the SQLJ context must be specified explicitly. Still not satisfied, Oracle is providing the Oracle.connect() API that turns auto-commit off by default (although it also supports the extra boolean at the end for setting it explicitly).
Can you also demonstrate the different default settings for auto-commit? 5.3 Wrapping Up Updates Whenever we executed a SQLJ statement, we either obtained data through host variables or by assignment, or we experienced a SQLException containing some error message. At times we would like to obtain additional information about the SQL statement, such as the following.
This information is available on a sqlj.runtime.ExecutionContext. An example.import oracle.sqlj.runtime.Oracle; import sqlj.runtime.ref.DefaultContext; import sqlj.runtime.ExecutionContext; ... Oracle.connect(aJdbcConnection); DefaultContext ctx = new DefaultContext("jdbc:oracle:oci8:@", "scott", "tiger", false); #sql { DELETE FROM emp WHERE sal > 5000 }; #sql [ctx] { UPDATE emp SET sal = sal * 2 }; ExecutionContext ec1 = DefaultContext.getDefaultContext().getExecutionContext(); System.out.println( ec1.getUpdateCount() + " employees are laid off."); ExecutionContext ec2 = ctx.getExecutionContext(); System.out.println( ec2.getUpdateCount() + " employees are rejoicing.");
5.4 What Type Is Your Connection? Consider the following scenario: you want to write a SQLJ program that establishes connections to two different database schemas. In the PILOTS schema you keep personal data, schedules, flight hours, and so on of fighter pilots, and in the JETS schema you have the technical specs, the repair history, and maintenance schedule of fighter aircraft. Naturally, the SQLJ statements that operate on each of these schemas will utilize different tables, views, and different sets of stored procedures and functions. It would be nice if you could verify the correctness of the SQL statements in your program against both of these schemas. So far, we explained to you only how to specify a single database connection for online checking. This is where the notion of typed connection contexts comes in. We create two different context types. #sql context Pilots; #sql context Jets; At runtime, you must connect to the corresponding schema — unfortunately, SQLJ cannot check that for you. Pilots pconn = new Pilots("jdbc:oracle:oci8:@","PILOTS","ACE",false); Jets jconn = new Jets("jdbc:oracle:oci8:@","JETS","STRATOS",false); However, at translate time, SQLJ can determine whether you used a Pilots connection context or a Jets connection context in your SQLJ statement.#sql [pconn] { INSERT INTO pilot VALUES ( .... ) }; // Pilots context#sql [jconn] { UPDATE maintenance SET status = Checkup( .... ) }; // Jets contextCan we tell SQLJ at translate time how to connect to the database for these connection context types? Sure — that’s easy! sqlj -user@Pilots=pilots/ace -user@Jets=jets/stratos MyFile.sqlj
What happens if you replace -user@Jets=jets/stratos with -user=jets/stratos? What happens if you replace both preceding -user settings with -user=pilots/ace? 5.5 Let’s Get Objective Now what about those SQL objects, the SQL REFs and VARRAYS/Nested Tables? Yes, SQLJ supports all these features of Oracle SQL. You can use the "raw" representations ( oracle.sql.STRUCT/REF/ARRAY) for these types — at least for receiving values from the database.However, for full functionality, you should use JPublisher wrapper classes for these SQL types. This is a four-step process.
jpub -sql=Address -user=scott/tiger -url=jdbc:oracle:oci8:@ javac Address.java Easy, isn’t it? Of course, there are a whole lot of additional details that we omitted. Get the full picture from the following sources.
No way, José! We do not have space or time to explain this here. If you used the Oracle.connect() method and a default context on your SQL statements, you should be in good shape to debug and test your SQLJ program on the client. Then you "stuff it" into the server using the following steps (naturally, variations on this theme abound) .
Here we just pass a few tips along that relate to the client-side compilation Step 1.
Use the -d option to designate a root directory under which all the SQLJ-generated .class (and possibly .ser) files will be placed. sqlj -ser2class -d rootdir *.sqlj *.java Copy any .properties files that you require into the appropriate location under rootdir. Change to rootdir before issuing jar cvf0 myjar.jar *, then upload myjar.jar with loadjava.
5.7 Isn’t It — Portable! Now that we have introduced all these great Oracle features — how can we get rid of them and put the (Oracle)Genie back in its bottle? First off, you want to turn on portability warnings, so you get notified about Oracle-specific type usage. sqlj -warn=portable … Note that some types, such as iterators and result sets, are not Oracle specific, per se, but the standard SQLJ driver does not let you use them as parameters or in iterator columns. You’ll see warnings in these cases, as well. Secondly, you should avoid the use of Oracle-specific SQL constructs such as PL/SQL — you need some kind of standard SQL grammar in your SQLChecker component. There is good news and bad news on this. The good news is that we have a demo in [Oracle Home] /sqlj/demo/components with new SQL checkers (ParsingJdbcChecker and ParsingOfflineChecker) that use an actual SQL grammar to check the syntax of your SQL statements. Even better, you get the source for this grammar and can modify it to your heart’s content. On the downside, this checker is not part of the Oracle SQLJ product and, therefore, unsupported.
(c) Make it recognize SQL-92 Entry Level only. (d) Make it recognize your favorite flavor of SQL. 5.8 Give Me Speed Or … More Speed! You say you want speed, speed, and more speed? It’s coming to you in the 8.1.6 SQLJ release. And you can pick it up with little or no effort — compared to using JDBC. Buckle up, as we put the pedal to the metal! In the examples below we assume that your program uses only the default connection context. Otherwise, any required ExecutionContext and JDBC Connection objects will have to be obtained from your actual connection context instance.Speed through statement caching. By default, SQLJ automatically caches the last five SQLJ statements that you executed on a given connection. Execution time is cut up to 50% if a statement can be pulled from the cache. If you like to, you can also set a specific statement cache size during SQLJ translation or profile customization as follows.sqlj -P-Cstmtcache= cacheSize …Now what does a cacheSize of 0 do? Yep, it turns off the cache! Use it to see how your program performed under the previous versions of SQLJ. Or, more sensibly, set it to a higher value. Speed through batching. SQLJ now also supports batching through the setBatching() and setBatchLimit() methods on the ExecutionContext..ExecutionContext ec = DefaultContext.getDefaultContext().getExecutionContext(); ec.setBatching(true); This turns on batching of INSERT, DELETE, and UPDATE statements in your SQLJ program. If the same DML statement is executed repeatedly —such as in a loop— the parameter bindings are collected. Finally, when execution moves to a different SQLJ statement, the collected set of parameters is bound through an array bind and executed as a single statement. Speed through row prefetching. You can also set a row prefetch size for your queries (the default size is 10) to save on round trips to the database.OracleConnection conn = (OracleConnection) DefaultContext.getContext().getConnection(); conn.setDefaultRowPrefetch( prefetchSize);Speed through Hints for Variable-Size Parameters. If you use variable-size SQL types, such as CHAR, VARCHAR, or RAW as bind parameters in your SQLJ statements, then JDBC has to prepare for the worst case (such as a PL/SQL function returning 32kB of character data). Often, you know the actual maximum size (in bytes!) of these parameters ahead of time and can give a hint to the underlying JDBC engine. An example.#sql s = { /*(10)*/ VALUES( to_uppercase(:t/*(10)*/) ) }; The size hint is always given as a comment /*(size)*/ immediately after the host variable (or host expression), or —if this is the return value for a function— as the first comment in the SQL statement. You still have to tell SQLJ at translation or at customization time to pick up these hints through the -P-Coptparams flag.sqlj -P-Coptparams … If you use a specific statement cache size and parameter size hints, you must specify both flags at the same time.
-P-Coptparamdefaults flags, and demonstrate the use of default parameter hints. 5.9 Classy Kinds Of Iterators SQLJ iterator types have some "object flavor" but do not feel like genuine objects. You might wish that you could endow an iterator with a different character from that which SQLJ generates. Roll your own by subclassing an iterator type and providing your own (add-on) behavior! In the following example, we assume that we already have an Emp class.#sql iterator Iter(String ename, int empno); class SubIter extends Iter { SubIter(sqlj.runtime.profile.RTResultSet rs) throws SQLExecption { super(rs); } Emp getEmp() { return new Emp( ename(), empno() ); } } … SubIter iter; #sql iter = { SELECT * FROM EMP }; while (iter.next()) { System.out.println(iter.getEmp()); } Note the constructor Subclass (sqlj.runtime.profile.RTResultSet) that plugs into the corresponding superclass.You have now become a SQLJ Guru You made it. It’s time to get a life and have some fun! |
:: Command execute :: | |
--[ c99shell v. 1.0 pre-release build #13 powered by Captain Crunch Security Team | http://ccteam.ru | Generation time: 0.0312 ]-- |