John K. Joachm
joachimj@usa.net
The proceeding build log is a supplement to Assignment #4 for CSCI-541
(Spring 2000),
distributing a JDBC-powered interface over multiple clients using the
Oracle
8 Server on
IUPUI's DS9 server.
Misnomer: It was discovered on Monday, 13 March, that the Java
RMI library was
not necessary to complete this assignment. Instead, creating
database links in Oracle's
SQL*Plus did the required tasks Please see the "Database
Link Setup" link in the
Summary below).
541_jdbc.zip (contains everything listed
above)
Here's the assignment, taken from http://www.cs.iupui.edu/~dhoang/bukhres/cs541/JDBC.html:
1. Create a Java application program and use JDBC to
connect to the Oracle database on ds9.
2. Using your program, create the four tables student,
class, enrolled and Instructor (from HW2 ) and add
data into them from your Java program.
3. Using your program, execute the same set of queries
given in HW2 assignment
You have to follow
2-tiered client/server architecture: Java program will be the
application program with a GUI, and the server will be the Oracle database
server
running on ds9.
You have to build
GUI in your Java program that has at least 1 text field to type in
queries, 1 button to submit your queries to the Oracle server, and 1 text
area to
display your query results.
Execute joint operation between
two tables residing at two different databases, which
are located under two different
user accounts. You can use your acount and Dong's
account to execute the joint
queries.
> Dong Hoang
> John, the URL is right. I would like to give
one more
information FYI:
>
> Class.forName("oracle.jdbc.driver.OracleDriver");
> String url = "jdbc:oracle:thin:@134.68.140.211:1521:OS80";
>
> You are welcome to go for an extra credit!!!
About accessing
my Oracle account, since I am keeping
class information (4-last
ssn and grades) in this account, I
need to think how to handle
this situation. Pls wait for a while.
If you can, pls ask your
classmate for sharing (ORACLE) account
...
>
> Also I have put the JDBC (Oracle thin) library
file in the class
home page.
Sunday, 27 February, 2000
This distribution will be accomplished using the RMI interface:
package kava.rmi;
public interface Remote {
}
All methods called remotely will be specified:
public interface RemoteBall extends java.rmi.Remote {
public abstract void hit() throws java.rmi.RemoteException;
public abstract int getPosition() throws Remoteexception;
}
A Class must be written to implement RemoteClass:
import jave.rmi.RemoteException;
import java.rmi.server.UnicastRmoteObject;
public class Ball extends UnicastRemoteObject
implements RemoteBall {
private int position = 0;
public Ball() throw RemoteException {
super()
}
public int getPosition () {
return position;
}
public void hit() {
position += calculateDistance();
}
protected int calculateDistance() {
return 10;
}
}
Potential URL lookup:
ball(remoteBall)Naming.lookup("rmi://athens.imaginary.-com/Ball");
After the class is written, the final step will be to run the RMI compiler:
rmic Ball
Monday, 28 February, 2000
This morning I e-mailed Dong on the class list regarding the project:
Dong, for the extra credit I was going to
use the
RMI API. Am I pursuing this the wrong way?
Will I
be able to use the RMI compiler ("rmic")
on DS9 ?
And Dong's response:
John I am not sure what exactly you are going
to do with RMI,
however, I am sure
that you will not have any problems
to use RMI here on
DS9. There are 2 JDK versions you can
use on ds9 are in:
/opt/local/java/jdk1.2.2
and
/opt/local/java/jdk_1.1.7/java1.1
good luck
Dong
Just for kicks, I'll be using JDK 1.2.2.
Tuesday, 29 February, 2000
This evening at IUPUI I'll need to download the latest version (1.2.2)
onto my
home directory on DS9.
Information on the Remote Method Invocation (RMI) can be found at
http://java.sun.com/products/jdk/1.1/docs/guide/rmi/index.html
The RMI stub compiler can be downloaded from
http://java.sun.com/products/jdk/1.1/docs/tooldocs/solaris/rmic.html
The Java Remote Object Registry can be found at
http://java.sun.com/products/jdk/1.1/docs/tooldocs/solaris/rmiregistry.html
Information on setting the CLASSPATH can be found at
http://java.sun.com/products/jdk/1.1/docs/tooldocs/solaris/classpath.html
I did a little reading on the RMI registry, and how to set it up (see
http://java.sun.com/products/jdk/1.1/docs/guide/rmi/getstart.doc.html
for tutorial,
and for elaboration on the following). Here's a direct quote:
The RMI registry is a simple server-side name server that allows
remote clients to
get a reference to a remote object. Typically, it is used only to
locate the first remote
object an application needs to talk to. Then that object in turn
would provide
application-specific support for finding other objects.
Note: Before you start the rmiregistry, you must make sure that the
shell or window in
which you will run the registry either has no CLASSPATH set or has
a CLASSPATH
that does not include the path to any classes that you want downloaded
to your client,
including the stubs for your remote object implementation classes.
If you start the rmiregistry, and it can find your stub classes in
its CLASSPATH, it will
ignore the server's java.rmi.server.codebase property, and as a
result, your client(s) will
not be able to download the stub code for your remote object.
To start the registry on the server, execute the rmiregistry command.
This command
produces no output and is typically run in the background. For more
on the rmiregistry,
you can refer to the Solaris rmiregistry manual page (see above
links, John) or the Win32
rmiregistry manual page.
Wedsesday, 1 March, 2000
A few postings from the ClassList:
Hi Dong and All,
I'm having problems trying to run an applet
from my house the will connect to the Oracle
database on DS9. It is well known that an
applet does not have these priveleges. However,
that is the most effecient way for me to do
my
homework. So my question is what can I do to
run my applet from home and have it connect
to the Oracle database on DS9?
I've perused the docs on oracle.com and on
java.sun.com and there is something there about
"signing an applet" but I would rather not open
up that can of worms. An alternative would be
to
store the applet class on my account on DS9;
this supposedly would eliminate the security
violation since the database is on the same
machine, but I'm not sure if I can open an HTML
document that is stored on DS9. Does DS9 have
web server software installed?
Basically, what to do now?
Thanks in advance,
Kerry
And Dong's reply:
You probably cannot connect to Oracle Server
by using applet. If you want to use applet to
connect to Oracle Server, you probably will
need more thing to take care of. My advice is
do not use applet, use java appliation instead.
To connect to Oracle from your home computer,
just create a telnet connection to ds9 and run
your Java application under your WINDOWS
environment, You will be able to connect to
the Oracle Server in DS9.
The question why you cannot use (only) Apllet
to connect to Oracle is left for you to find
the answer...
Dong
Another question:
Dong,
So, should I just use AWT on
top of my Java
application to satisfy the
GUI requirements
for the assignments?
And Dong's reply:
You will use AWT inside your java application I guess.
One more question:
Writing a Java program to access our Database
through a telnet
program seems straightforward enough, but then to
what extent
can the program have a Graphical User Interface?
Bill Higdon
Dong's reply:
It is a long discussion, I think
to have a button to clicK and
small window to enjoy the queries results will be
much better
than B&W text console....
This is indeed an interesting resolution to this matter. It's
tempting to rant about what Sun's
Java had promised, and what it has actualy become.
<--- WARNING !! OFF-TOPIC !! -->
Sun had introduced Java as a language for writing cross-platform GUI-based
applications. Java is very fast, very efficient,
and very secure; but the GUI
component has not yet evolved to coincide with what Java is supposed
to be
(the Java GUI sucks, actually). Interestingly enough,
Java has provided some
incredibly powerful class libraries and add-on's (RMI, IDL, JNDI, JavaBeans,
etc.), which don't necessaily need the GUI component to perform (no
more than
a C++ program needs a GUI to perform it's system calls). And
so, "Java Enter-
prise" has emerged -- Java without the GUI components. Strangely
enough, Java
Enterprise has, to some degree, implemented the GUI component into
applications
(an example of this: since so much is being performed over a distributed
network,
it's helpful to get a graphical monitor of what's going on), and has
helped to evolve
the GUI component (the introduction of Swing, for example).
It's rather anti-climactic
to me, really, how the true power of Java has been stifled by the promises
of its
GUI front-end, while the developments within "Java Enterprise" are
nothing short of
revolutionary. It all just seems ironic; but then again, Java's
only a few years old, and
developers are trying to too much, too thoroughly, and too fast (and
this is a bad thing?).
Moreover, it's peculiar even more that Bill Gates' Microsoft "involvement"
in empowering
Java is, you guessed it, the GUI component -- as if to say, this is
the best Microsoft has
to offer (which incidently, called Application Foundation Classes
-
AFC - has been
"outlawed" for not-quite two years now). A sign of the temultuous
times, no doubt ....
maybe things won't be so crazy five years from now (did C++ have this
problem in it's
humble beginnings ?) ....
Forgive me for this off-topic on an off-topic on an off-topic.
We now return to our previously
schedueled project. ;O
<-- END OF OFF-TOPIC RANT -->
Monday, 6 March, 2000
Yesterday Dong released the distributed server logon information:
We have an extra Oracle account for an extra
credit with
JDBC assignment.
Userid: cs541
password: distex1
This is an Oracle account, NOT another UNIX account.
In other words, two
applications on the same server will be communicating, NOT
two processes on
different servers. I'm sure RMI can do this, but may not
be necessary.
Both accounts are still password-protected, though, so the two Oracle
accounts may
best be considered two applications on the same network, but not on
the same
workstation.
Further development on this project will be suspended until Tomorrow
evening, after
mid-term exams.
Wednesday, 8 March, 2000
This morning I uploaded the first-draft
RMI files to my web server (tomorrow I'll upload
these files to DS9).
Part 2 of the assignment -- creating the DDL -- is now completed.
Basic form of each table:
stmt.executeUpdate(“CREATE TABLE STUDENT”
“(Snum VARCHAR2(10),
Name VARCHAR2(30),
Major VARCHAR2(10),
Levels VARCHAR2(5),
Age INTEGER,
PRIMARY KEY (Snum)
)
“);
Basic form of each inserted row:
Stmt.executeUpdate(“INSERT INTO STUDENT”
“VALUES ('1234567890', 'John Smith', 'CS',
'SR', 23)
“);
Thursday, 9 March, 2000
Java RMI will still be utilized for this project, since the two Oracle8
accounts are at
different positions on the DS9 server.
I e-mailed Dong about this issue this morning:
How
will I "prove" that I am accessing two different Oracle8
accounts ?
In my code the JDBC driver is loaded
only once, but I'll be
making two separate connections,
right?
For example,
Connection conjj = DriverManager.getConnection
(jdbc:oracle:thin:@134.68.140.211:1521:0S80,
"my_login","my_password");
Connection concs541 = DriverManager.getConnection
(jdbc:oracle:thin:@134.68.211.1521:0S80,"cs541","distex1");
Now, this next question I'm just
guessing, so I would appreciate
if you just answer it and not try
to make any jokes about it
(if you don't know, just say "I
don't know" -- or say, "that's
not necessary").
Should I create a new table while
logged on to the cs541 Oracle
account, and then THIS is the table
I'll be accessing remotely?
For example,
This new table, CLASS_EXPENSES, summarizes
the REQUIRED fees
involved for taking each given class.
This table does NOT include
textbooks purchased in the university
bookstore, but DOES include
the following:
Bounded class notes $15.00
Lab fees $45.00
Computer Room Proximity Cards $10.00
Electrical Tool Kits $50.00
Not all classes require all expenses (NULL).
CREATE TABLE CLASS_EXPENSES (
Name VARCHAR2(30),
IiD VARCHAR2(10),
Notes VARCHAR2(20),
Lab_Fee VARCHAR2(10),
Prox VARCHAR2(10),
Toolkit VARCHAR(10),
PRIMARY KEY (Name),
FOREIGN KEY (iID)
REFERENCES instructor(iID)
);
Thanks for your assistance,
John Joachim
Here's Dong's reply, regarding the connection protocol:
You can prove by making 2 connection and it is
a only way
in the mean time like:
>
> Connection conjj = DriverManager.getConnection
> (jdbc:oracle:thin:@134.68.140.211:1521:0S80,
"my_login","my_password");
>
> Connection concs541 = DriverManager.getConnection
>
(jdbc:oracle:thin:@134.68.211.1521:0S80,"cs541_login",
"cs541_password");
... and regarding the new table:
I do not understand what the TABLE
CLASS_EXPENSES used
for. It is not related
to the homework which is asking
you to deal with 4 tables in assignment
2. I will not say
you should or should not. but if
you want to do this part,
put 2 tables for example, student
and class in your Oracle
account and enrolled and instructor
in cs541 and try to do
queries using 2 connection to these
4 tables.
Also, there's some code from the April 2000 issue of WebTechniques
for creating outlined
text in Java (see http://www.webtechniques.com/sourcecode/2000/04/java/1.lst
for details):
// outline text
void OutlineText(Graphics g, String s, int x, int y,
Color c1, Color c2)
{
int amt=1;
g.setColor(c1);
g.drawString(s,x-amt,y-amt);
g.drawString(s,x-amt,y+amt);
g.drawString(s,x+amt,y-amt);
g.drawString(s,x+amt,y+amt);
g.setColor(c2);
g.drawString(s,x,y);
}
Friday, 10 March, 2000
I began the construction of the Java applet.
Here's an example (info on this can be found at
http://www.oreilly.com/catalog/javawt/excerpt/appc.htm):
Saturday, 11 March, 2000
Design of this application contines, on three different dimensions:
1) Wriitng the code for JDBC to process queries;
2) Writing the code for the visual applet;
3) Modifying the code to work over multiple Oracle accounts
Today, I plan to upload the DDL for two of the tables into the cs541
Oracle
account (and I might as well upload the DDL for the other two tables
onto
my personal Oracle account).
First draft of the readme file has been written, and links to all files
have been added
to the build log (see top of this document Log).
I also set up the .cshrc file on my DS9 account to compile and run java
files (since I
recall having a few problems doing this last semester, I'm pritning
out the modifications
here so that I don't forget next time ;O ... please excuse my
ignorance ):
The first line of the .cshrc file, the set path=
, has one more path added to it,
/usr/include/sys /opt/loca/java/jdk_1.1.7/bin :
set path=(. /bin /usr/bin /usr/ucb /etc /usr/openwin/bin
/opt/NeWSprint/bin \
/opt/local/bin /opt/gnu/bin /usr/ccs/bin /usr/include
/usr/include/sys /opt/local/java/jdk_1.1.7/bin )
Also, an additional setenv statement was added:
setenv CLASSPATH .:/opt/local/java/jdk_1.1.7/lib/classes.zip:/home/
jjoachim/jdbc/lib/classes.zip
Oh, yeah, and then back at the command prompt, I need to "source" this
file (after saving
the changes):
source .cshrc
A few more bookmarks of interest:
Creating an Applet from an Application:
http://java.sun.com/docs/books/tutorial/jdbc/basics/applet.html
[and here's the code for the applet used, OutputApplet.java]
List of Applets: A list of all applets in the The JavaTM Tutorial:
http://java.sun.com/docs/books/tutorial/listofapplets.html
JDBC Data Access API Product Tour (includes JDBC TestTool)
http://java.sun.com/products/jdbc/prodtour.html
I successfully loaded and compiled OutputApplet.java onto DS9 (actually,
I
compiled 7 files, all from JDBC(TM) API Tutorial and Reference);
but of course,
I'm unable to actually run these programs from work, where I was telnetting
into
DS9 (plus, I need to register and load the JDBC driver). I'll
experiement with this
Tuesday evening.
I've accomplished quite a bit today, but still quite a ways to go.
Writing a GUI for this
assignment is going to be a bit more work than I'd like it to be.
Sunday, 12 March, 2000
Without further ado, I had my first stab at writing the code (called "First.java").
import java.net.URL; // req.,
due to the database url
import java.sql.*;
// req., contains the JDBC classes
public class FirstProgram
{
public static void
main(String args[]) {
class.forName("oracle.jdbc.driver.OracleDriver");
Connection
con =DriverManager.getConnection(
"jdbc:oracle:thin:@134.68.140.211L1521:0S80",
"jjoachim","j5736joa");
}
}
--- proceded by my first set of errors:
ds9{jjoachim}20: javac First.java
First.java:9: Identifier expected.
class.forName("oracle.jdbc.driver.OracleDriver");
^
First.java:5: Public class FirstProgram
must be
defined in a file called "FirstProgram.java".
public class FirstProgram
^
2 errors
Changing the line public class FirstProgram to public
class First got rid
of the second error. But the first error persisted. I figured
maybe there was a typo
somewhere; and sure enough, there was (in the getConnection line --
missing a " : ")
Attempt #3:
import java.net.URL;
import java.sql.*;
public class First
{
public static void main(String args[]) {
// REGISTER DRIVER
try {
Driver d = (Driver)Class.forName(
"sun.jdbc.odbc.JdbcOdbcDriver").newInstance
();
} catch (Exception e) {
System.out.println(e)
}
// GET CONNECTION
Connection con;
try{
con = DriverManager.getConnection("jdbc:oracle:thin:@134.68.211:1521:0S80",u
serName,password);
}catch(Exception e){
System.out.println(e);
}
}
}
---. and a similiar error:
ds9{jjoachim}10: javac First.java
First.java:13: ';' expected.
System.out.println(e)
^
1 error
import java.net.URL;
import java.sql.*;
public class First
{
public static void main(String args[]) {
// REGISTER DRIVER
try {
Driver d = (Driver)Class.forName(
"oracle.jdbc.driver.OracleDriver").newInstance
();
} catch (Exception e) {
System.out.println(e)
[missing
semi-colon here !!];
}
// GET CONNECTION
Connection con;
try{
con = DriverManager.getConnection(
"jdbc:oracle:thin:@134.68.211:1521:0S80",
"jjoachim","j5736joa");
}catch(Exception e){
System.out.println(e);
}
}
}
No errors .... whew ....
... but unable to run it :
ds9{jjoachim}11: java First
java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
java.sql.SQLException: No suitable driver
ds9{jjoachim}12: java First.class
Can't find class First.class
ds9{jjoachim}13: java First
java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
java.sql.SQLException: No suitable driver
This might be because I can't run the program remotely ...
Monday, 13 March, 2000
I learned last night, from reading
Oracle
8: The Complete Reference, how to set up
database links between databasesn at different locations:
The first statement in the .sql file containg the DDL for the tables
on my personal
Oracle account needs to have this commenad:
create public database link REMOTE_CONNECT
connect to cs541 identified by distex1
using 'connect string';
But I get this error:
ERROR at line 1:
ORA-01031: insufficient privileges
I wrote Dong about this.
In the meantime, I have modified the readme file, so that it states
that all foreign key
constraints are lsited in the 541_4_ddl_1 file (since the
foreign keys across tables
cannot be designated until all tables have been created).
Also, I included the following lines of code in the 541_4_ddl_1
file. This set up
"synonyms" for the remote tables (i.e., those on the cs541 account)
so that queries
written in the applet could be written as if all tables were on the
same account (as
opposed to writing row@REMOTE_CONNECT for each remote
table request):
create synonym ENROLLED
for ENROLED@REMOTE_CONNECT;
create synonym INSTRUCTOR
for INSTRUCTOR@REMOTE_CONNECT;
A good link on Effective Layout Management:
http://developer.java.sun.com/developer/onlineTraining/GUI/AWTLayoutMgr/shortcourse.html
Online Resources from Sun:
http://java.sun.com/docs/books/tutorial/information/resources.html
=====================
List of things to do at IUPUI:
1) Re-run Intersolv's applet from a Sun Box
2) Run First.java
3) Just for fun, try running the (7) programs from JDBC API
book
4) Run OutputApplet.java,and other programs in 541_JDBC directory:
ds9{jjoachim}1: cd 541_JDBC
/home/jjoachim/541_JDBC
ds9{jjoachim}2: dir
AllComponents.java InsertSuppliers.class
MiniPIM.class
CreateCoffees.class InsertSuppliers.java
MiniPIM.java
CreateCoffees.java Join.class
OutputApplet
CreateSuppliers.class Join.java
OutputApplet.class
CreateSuppliers.java MiniPIM$1.class
OutputApplet.java
First.class
MiniPIM$2.class RMI/
First.java
MiniPIM$3.class SUBMISSIONS/
InputForm.class
MiniPIM$4.class TransactionPairs.class
InputForm.java
MiniPIM$5.class TransactionPairs.java
InsertCoffees.class MiniPIM$6.class
classes111.zip
InsertCoffees.java MiniPIM$7.class
Tuesday, 14 March, 2000
This morning I got an e-mail from Dong:
John, if
thing is relating to the "privileges" I am out of power.
if you
pursuit your way, and you need more privileges to this
account
please advice with Scott. I can not help.
So I contacted Scott Orr. Here's his response.
Do you know offhand
what privileges yo need. The subject line
indicates DBA but I
am afraid I cannot go that far. Please let me
know.
And my response:
Thanks for getting back to me.
I'm not sure the exact
command, but I "need" the privileges
enabled to do the following
command:
create public database link REMOTE_CONNECT
connect to cs541 identified by distex1
using 'connect_string';
'connect_using' is defined
in the
TNSNAMES.ORA
file on the
local host to
determine which database to connect to.
For example, the TNSNAMES.ORA file:
connect_string=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=HOST1)
(PORT=1521)
(CONNECT DATA=
(SID=remote))
Thanks,
John Joachim
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Wednesday, 14 March, 2000
After some preliminary thought on the matter, I have decided that the
GUI will be limited
to the following components:
1) Text: "Type a complete Oracle-compliant query ..."
2) Text Field
3) Submit button
4) Output Window
5) "Close Connection" window
*******
I just has an idea (an alternative to teh above): Wite a GUI with seven
buttons and one output
window: each button, when pressed, executes one of the seven
queries from Assignment #2.
Doing this wil allow me to concentrate on getting the SQL execution
code correct, and then
concentrate on the GUI code. I came to this idea when I considered
that JOINS will be interesting
(i.e., a challenge) to port from the JDBC to the Oracle database.
I wonder if the AWT allows a
description of what each button does when a button is "moused over"
(like ALT's in HTML).
Not only would this GUI be alittle more easier to write, it's alos
more real-world: how many
university office managers have the time to master SQL (to the degree
that I needed to
complete Assignment #2 ??)
********
Last night I bought a copy of O'Reilly's Java AWT Reference.
Most of what I'm
interested in is in Chapter 8, "Input Fields." For once I had
the foresight to download
the source code beforehand, while at IUPUI. Here's the directory
for the Chapter 8
code:
/home/jjoachim/541_JDBC/awt/jaz/BookCode/chap8
ds9{jjoachim}11: dir
MyAL.class
sizetext.html
textas.class
MyTextField.class sizetext.java
textas.html
Rot13.class
text11.class textas.java
Rot13.java
text11.html
textas11.class
SizedTextField.class text11.java
textas11.html
SizedTextField.java
text12.class textas11.java
TextFieldSetter.class text12.html
texts.class
readonly.class text12.java
texts.html
readonly.html
text13.class texts.java
readonly.java
text13.html
sizetext.class text13.java
Thursday, 15 March, 2000
This morning I re-sent my message to Scott Orr, requesting additional privileges.
Also, changes were made to the readme file to reflect yesterday's
new idea of having
the applet able to perform only the (seven) queries from Assignment
#2, not any and
every query requested (the following is the new applet, as noted in
the readme):
This file is the actual assignment program. It has been
written to run as a stand-alone application. This applet
does the following tasks:
1) Connect to the IUPUI DS9 server;
2) Load the JDBC driver;
3) User has a choice of (eight) buttons to press - each button
initiates one of the
(eight) queries from Assignment #2
(see actual queries
below)
[previously, this step read "Run a query input by the user,
which may be accessing two separate Oracle accounts on the
DS9 server;"]
4) Show results of the query in output window
5) Run another query if needed by user;
6) Show any exception errors that arise
7) Close applet if requested by user
The queries were also listed in the readme file, but descriptions of
each query were
also added this morning (i.e., Find the names of all CS Majors (Major
= "CS")
who are enrolled in the course "CS541").
Note that there are EIGHT queries, not just SEVEN. This will make
the design a
bit more easier to work out (two rows of four).
Here's the code that will need to be ported into the java code:
select Name from Student ,Enrolled
where Student.Major ='CS' and
Student.Snum = Enrolled.Snum and
Enrolled.Classname ='CS541';
String query = “SELECT NAME” +
“FROM STUDENT, ENROLLED “ +
“WHERE STUDENT.MAJOR = ‘CS’ and “ +
“STUDENT.SNUM = ENROLLED.SNUM and ” +
“ENROLLED.CLASSANME = ‘CS541’ ” ;
ResultSet rs = stmt.executeQuery(query)
System.out.println(“All CS Majors Erolled in the
Course "CS541" : “);
While (rs.next() ) {
String Name = getString (“NAME”);
System.out.println(“ “+Name);
select Name from Student, Enrolled
where Student.Major='CS' and
Student.Snum=Enrolled.Snum and
Enrolled.Classname='CS503' and
Student.Levels='SR';
tring query = “SELECT NAME” +
“FROM STUDENT, ENROLLED “ +
“WHERE STUDENT.MAJOR = ‘CS’ and “ +
“STUDENT.SNUM = ENROLLED.SNUM and ” +
“ENROLLED.CLASSNAME=’CS503’ and “ +
“STUDENT.LEVELS =’SR’ “;
ResultSet rs = stmt.executeQuery(query)
System.out.println(“All CS Majors enrolled in "CS503"
and are Senior: “);
While (rs.next() ) {
String Name = getString (“NAME”);
System.out.println(“ “+Name);
Select Class.Name from Class, Instructor
where Class.iID = Instructor.iID and
(Class.Room = 'SL 210' or Instructor.Name ='King');
String query = “SELECT CLASS.NAME” +
“FROM CLASS, INSTRUCTOR “ +
“WHERE CLASS.IID = INSTRUCTOR.IID and “ +
“CLASS.ROOM = ‘SL 210’ or INSTRUCTOR.NAME =’KING’ ”;
ResultSet rs = stmt.executeQuery(query)
System.out.println(“Classes Meeting in Room SL 210
or Taught by Proffesor King : “);
While (rs.next() ) {
String Class = getString (“CLASS”);
System.out.println(“ “+Class);
select Name from Student
where Snum in (select Snum from Enrolled
where Classname in (select Classname from Enrolled
group by Classname having count(*)>1));
String query = “SELECT NAME” +
“FROM STUDENT “ +
“WHERE SNUM IN (SELECT SNUM FROM ENROLLED “ +
“WHERE CLASSNAME IN (SELECT CLASSNAME FROM ENROLLED” +
“GROUP BY CLASSNAME HAVING COUNT(*) >1))”;
ResultSet rs = stmt.executeQuery(query)
System.out.println(“Students Enrolled in Some Class
Together : “);
While (rs.next() ) {
String Name = getString (“NAME”);
System.out.println(“ “+Name);
select Name from Student
where Snum in (select Snum from Enrolled
where Classname in (select Name from Class
where Time in (select Time from Class
group by Time having count(*)>1)));
String query = “SELECT NAME ” +
“FROM STUDENT “ +
“WHERE SNUM IN (SELECT SNUM FROM ENROLLED “ +
“WHERE CLASSNAME IN (SELECT NAME FROM CLASS”
“WHERE TIME IN (SELECT TIME FROM CLASS” +
“GROUP BY TIME HAVING COUNT(*)>1)))”;
ResultSet rs = stmt.executeQuery(query)
System.out.println(“Students Enrolled in Two Classes
Meet at the Same Time: “);
While (rs.next() ) {
String coffeeName = getString (“NAME”);
System.out.println(“ “+Name);
select Instructor.Name from Instructor, Class
where Instructor.iID=Class.iID and
Class.Time='MW 5:15-7:00 pm';
String query = “SELECT INSTRUCTOR.NAME” +
“FROM INSTRUCTOR, CLASS “ +
“WHERE INSTRUCTOR.IID = CLASS.IID and “ +
“CLASS.TIME=’MW 5:15-7:00 PM”;
ResultSet rs = stmt.executeQuery(query)
System.out.println(“Instructors Teaching in the time
period "MW 5:15-7:00 PM: “);
While (rs.next() ) {
String Name = getString (“INSTRUCTOR.NAME”);
System.out.println(“ “+Name);
select Student.name from student
where Student.Snum in
(select Enrolled.Snum from Enrolled
group by Enrolled.Snum
having count(*) = (select count(*)
from Class)
);
String query = “SELECT STUDENT.NAME” +
“FROM STUDENT “ +
“WHERE SSTUDENT.SNUM IN (SELECT ENROLED.SNUM FROM ENROLLED“ +
“GROUP BY ENROLED.SNUM” +
“HAVING COUNT(*) =(SELECT COUNT(*) FROM CLASS);”
ResultSet rs = stmt.executeQuery(query)
System.out.println(“Students Enrolled in All Classes
: “);
While (rs.next() ) {
String Name = getString (“STUDENT.NAME”);
System.out.println(“ “+Name);
select Name from Student
where Snum not in (select Snum from Enrolled
where Classname in (select Name from Class
where iid = (select iID from Instructor
where name='King') ) );
String query = “SELECT NAME” +
“FROM STUDENT “ +
“WHERE SNUM NOT IN (SELECT SNUM FROM ENROLLED“ +
“WHERE CLASSNAME IN(SELECT NAME FROM CLASS
“WHERE IID = (SELECT IID FROM INSTRUCTOR” +
“WHERE NAME =’KING’)));”
ResultSet rs = stmt.executeQuery(query)
System.out.println(“Students Not Enrolled in Any
Class Taught by Proffesor King: “);
While (rs.next() ) {
String Name = getString (“NAME”);
System.out.println(“ “+Name);
Friday, 16 March, 2000
No response yet from Scott Orr. I take this to mean that I will
not
(understandably) be
granted privileges to link databses (unless he set the link for me
but didn't reply -- not likely,
since I've contacted him twice, and no response). I suppose I'll
just turn in code as if I
could.
Most of the coding is finished (located on a file called 541_JDBC.doc).
Tuesday March, 2000
Files have been submitted to DS9 for grading (except for the build log
-- I'll send that later today) .
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
David Flanagan, Java Examples in a Nutshell (Sebastopol:
O'Reilly press, 1997).
Java in a Nutshell (2nd Ed.) (Sebastopol: O'Reilly
press, 1997).
et. al., Java Enterprise in a Nutshell (Sebastopol: O'Reilly
press, 1999).
Bruce Eckel, Thinking in Java (Upper Saddle River, NJ:Prentice-Hall,
1998).
John Zukowski. Java AWT Reference (Sebastobol: O'Reilly
Press, 1997).
George Koch & Kevin Loney, Oracle 8: The Complete Reference
(Berkeley:Oracle Press, 1997).
George Reese, Database Programming with JDBC and Java
(Sebastopol: O'Reilly Press, 1997).
Seth White, et. al., JDBC(TM) API Tutorial and Reference, Second
Edition: Universal Data
Access for the Java(TM) 2 Platform (Java Series). (Addison-Wesley,
1999).
Art Taylor, JDBC Developer's Resource: Database Programming on
the Internet
(Prentice-Hall,
1997).