|Oracle8i SQLJ Developer's Guide and Reference
You can test your database, JDBC, and SQLJ setup using demo applications defined in the following source files:
There is also a Java properties file,
connect.properties, that helps you set up your database connection. You must edit this file to set appropriate user, password, and URL values.
These demo applications are provided with your SQLJ installation in the
You must edit some of the source files as necessary and translate and/or compile them as appropriate (as explained in the following subsections).
The demo applications provided with the Oracle SQLJ installation refer to tables on a database account with user name
scott and password
tiger. Most Oracle installations have this account. You can substitute other values for
tiger if desired.
Running the demo applications requires that the
This section describes how to update the
connect.properties file to configure your database connection for runtime. The file is in the
demo directory and looks something like the following:
# Users should uncomment one of the following URLs or add their own. # (If using Thin, edit as appropriate.) #sqlj.url=jdbc:oracle:thin:@localhost:1521:ORCL #sqlj.url=jdbc:oracle:oci8:@ #sqlj.url=jdbc:oracle:oci7:@ # # User name and password here sqlj.user=scott sqlj.password=tiger
scott and password
tiger are used for the demo applications.)
If you are using a JDBC OCI driver (OCI 8 or OCI 7), then uncomment the
oci8 URL line or the
oci7 URL line, as appropriate, in
If you are using the JDBC Thin driver, then uncomment the
thin URL line in
connect.properties and edit it as appropriate for your database connection. Use the same URL that was specified when your JDBC driver was set up.
If you are using a non-Oracle JDBC driver, then add a line to
connect.properties to set the appropriate URL, as follows:
Use the same URL that was specified when your JDBC driver was set up.
You must also register the driver explicitly in your code (this is handled automatically in the demo and test programs if you use an Oracle JDBC driver). See "Driver Selection and Registration for Runtime".
The following tests assume a table called
SALES. If you compile and run
TestInstallCreateTable it will create the table for you if the database and your JDBC driver are working and your connection is set up properly in
If you already have a table called
If you do not want to use
TestInstallCreateTable, you can instead create the
SALES table using the following command in a database command-line processor (such as
CREATE TABLE SALES ( ITEM_NUMBER NUMBER, ITEM_NAME CHAR(30), SALES_DATE DATE, COST NUMBER, SALES_REP_NUMBER NUMBER, SALES_REP_NAME CHAR(20));
If you want to further test the Oracle JDBC driver, use the
Verify that your connection is set up properly in
connect.properties as described above, then compile and run
The program should print:
Now translate and run the
TestInstallSQLJ demo, a SQLJ application that has similar functionality to
TestInstallJDBC. Use the following command to translate the source:
After a brief wait you should get your system prompt back with no error output. Note that this command also compiles the application and customizes it to use an Oracle database.
On Solaris, the
sqlj script is in
[Oracle Home]/bin which should already be in your
PATH as described above. (On Windows, use the
sqlj.exe executable in the
bin directory.) The SQLJ
translator.zip file has the class files for the SQLJ translator and runtime, is located in
[Oracle Home]/sqlj/lib, and should already be in your
CLASSPATH as described above.
Now run the application:
The program should print:
If the SQLJ translator is able to connect to a database, then it can provide online semantics-checking of your SQL operations during translation. The SQLJ translator is written in Java and uses JDBC to get information it needs from a database connection that you specify. You provide the connection parameters for online semantics-checking using the
sqlj script command line or using a SQLJ properties file (called sqlj.properties by default).
While still in the
demo directory, edit the file sqlj.properties and update, comment, or uncomment the
sqlj.driver lines as appropriate to reflect your database connection information, as you did in
connect.properties. For some assistance, see the comments in the
Following is an example of what the appropriate driver, URL, and password settings might be if you are using the Oracle JDBC OCI8 driver (the username will be discussed next):
Online semantics-checking is enabled as soon as you specify a username for the translation-time database connection. You can specify the username either by uncommenting the sqlj.user line in the
sqlj.properties file or by using the -user command-line option. (The user, password, URL, and driver options all can be set either on the command line or in the properties file. This is explained in "Connection Options".)
You can test online semantics-checking by translating the file
TestInstallSQLJChecker.sqlj (located in the
demo directory) as follows (or using another username if appropriate):
This should produce the following error message if you are using one of the Oracle JDBC drivers:
TestInstallSQLJChecker.sqlj:41: Warning: Unable to check SQL query. Error returned by database is: ORA-00904: invalid column name
TestInstallSQLJChecker.sqlj to fix the error on line 41. The column name should be
ITEM_NAME instead of
ITEM_NAMAE. Once you make this change, you can translate and run the application without error using the following commands:
If everything works, this prints: