CMPSC 431W Technical FAQ

 

1.    IMPORTANT: PLEASE USE SEPARATE MACHINE DURING DEBUGGING/DEVELOPING PROJECT
To avoid interference to/from other students, at any time one machine can be used by at most one student for project.
 

2.    IMPORTANT: PLEASE SHUTDOWN TOMCAT SERVER BEFORE CLOSING NETBEANS IDE
To avoid interference with others, please shutdown Tomcat Server when you finish working.
 

3.    SQL*PLUS username & password

Q: I am able to type "sqlplus" on the command line then it asks for username and password. What should they be?

A: The username is the same as your UNIX userid and password is your SSN without dash or space.

Q: How do I reset my sqlplus password?

A: Use "PASSW" or "PASSWORD" commands in sqlplus.

4.    Before shutdown Netbeans IDE, please first shutdown tomcat server

Q: I cannot shutdown Netbeans IDE. What should I do?

A: Check whether you shutdown Tomcat Server.
 

4.    Before run Netbeans IDE, please setup environment

Q: I cannot run Netbeans IDE. What should I do?

A: Check whether you setup environment "CATALINA_HOME", "CATALINA_BASE" and "JAVA_HOME" correctly.  

5.    Q: How can I include two foreign keys referencing to the same attribute of the same table.

A: Here is how you can do it:

            CREATE TABLE t_stupid (
            a INTEGER,
            b INTEGER,
            FOREIGN KEY(a) REFERENCES cia(population),
            FOREIGN KEY(b) REFERENCES cia(population));

            In this example both attributes "a" and "b" references "population" attribute in "cia" table.
 

6.    Q: How can I dump sql output to a file?

A: You can dump your sql outputs using the following command:

                sqlplus <user_name>/<password> @<sql_script> > <output_file>

            don't forget "quit" at the end of your scripts...

 

7.    Q: I was trying to write some of the SQL statements from the book but I am running into a weird error. Whenever I try to execute a statement like the following:

        SELECT Temp.managerid
        FROM (SELECT D1.managerid, SUM(D1.budget) AS totbudget,
        FROM Dept D1
        GROUP BY D1.managerid)AS Temp
        WHERE Temp.totbudget>5000000;
 

A:  There are some issues with Oracle DBMS so you should change this SQL as:

        SELECT Temp.managerid
        FROM (SELECT D1.managerid, SUM(D1.budget) AS totbudget (!!!comma removed!!!)
        FROM Dept D1
        GROUP
BY D1.managerid) (!!!"AS" Removed!!!) Temp WHERE Temp.totbudget>5000000;

            Also;

            1) instead of EXCEPT you should use MINUS

            2) HAVING ANY and HAVING EVERY is not accepted in Oracle. So you need to find other ways to write these queries.

 

8.    Q: How to combine Java code into the JSP files?

A: A JSP file is a html file containing some java code with the tag <% %>. Therefore, we you modify your java code into a JSP file just put the Java code in the section of <% %>.

 

9.    Q: We design our interface using the DreamRiver. How can we combine the JSP files with the interface?

A: You just copy the JSP source code to the html file and use the tag of <% %>.

 

10.Q: How can we display all categories in the database?

A: What you have to do is to trace all the tree of the categories.

 

11.Q: How can we transfer parameters between JSP files without using html forms?

A: You can use session as follows:

           _• Setting a session attribute:

         session.setAttribute("username",username);

 

   • Reading a session attribute:

String username = (String) session.getAttribute("username");

 

12.Q: I was trying to write the following SQL statements

            SELECT Temp.did, Temp.weird
            _FROM _ (
                       SELECT D.did, AVG(E.age) as weird
                       FROM     Dept D, Works W, Emp E
                       _WHERE   _ E.eid=W.eid
                       _AND   _ W.did=D.did
                       _GROUP BY D.did
                       _) Temp
            _WHERE Temp.weird = (SELECT MAX(Temp.weird) FROM Temp);
 
            _Unfortunatly, it gives me the following error: WHERE Temp.weird = (SELECT MAX(Temp.weird) FROM Temp)
                                                                                                                              _    _ *
            ERROR at line 9: ORA-00942: table or view does not exist
 

A: Temp doesn't exist in (SELECT MAX(Temp.weird) FROM Temp). Maybe you can try to create another view for Temp.

 

13.Q: For problem 2 of HW3, I want to structure my query so that it will calculate the average age of employees per department._ I am not sure how to do this outside of a query for each department. Is it possible to do this within one query? And if so, how?

A: It is possible to do this within one query. First, you have to find the highest average age of employees. And then you can find the company whose average age of employee is equal to the highest average age of employees.

 

14.How to create custom functions in Oracle?

A: The following is provided by Megan Greensfelder:

CREATE or REPLACE FUNCTION getCurrentPrice(AID IN INT)

   RETURN REAL IS

   bidCount INT;

   highBid REAL;

   lowBid REAL;

   cp REAL;

BEGIN

cp := -1;

SELECT COUNT(*) INTO bidCount FROM bid WHERE bid_item = AID;

IF bidcount = 0 THEN

   cp := 0;

ELSE

   SELECT MAX(bid_amount) INTO highBid FROM bid WHERE bid_item = AID;

   IF bidcount = 1 THEN

              lowbid := highbid;

   ELSE

              SELECT MAX(bid_amount) * 1.05 INTO lowBid FROM bid

                         _WHERE bid_item = AID AND bid_amount < highBid;

   END IF;

   IF highBid < lowBid THEN

              cp := highBid;

   ELSE

              cp := lowBid;

   END IF;

END IF;

RETURN cp;

END;

/

 

After the above code is run in sqlplus (and the / at the end is important) the function getCurrentPrice can be called in any SQL

Statement. E.g., SELECT ai_id, getCurrentPrice(ai_id) FROM auction_item;

-Megan

 

15.How to send emails from the system?

The attached file is a java program which shows how to send email by the system. To use this sample._

(a) Use tar to extract the javamail.tar_

(b) modify the context of sample.java as follows

 _ em.sendmail("Receiver_Email_Address", "Your_PSU_Email_Address", "Email Sample","Email Body");

 _ -change "Receiver_Email_Addr" to the email address you want to send.

 _ -change "Your_PSU_Email_Address" to your PSU email address.

 _ -change "Email Sample" to the subject of the mail

 _ -change "Email Body" to what you want to send.

_(c) Type ‘run.sh’ to compile and execute the java program.

 

18. There is a limitation (150) on the number of connections to Oracle. To avoid connection problems, you should  put 'try...catch' blocks around each entire JSP file, and close the connection when an exception is caught.

For example:

//Declare Database class instance here

try{

//JSP CODE

Database.connect("username","password");

//SQL CODE, JSP CODE

Database.disconnect();

}

catch(Exception e) //at very end of file

{

out.print("Exception: " + e.getMessage();

}

finally

{

Database.disconnect();

}

In this way, the connection to the database will be closed even if an exception is thrown between the connect and disconnect statements in the try block.

19. Can I get some information about nonstandard Oracle SQL statements?

            Try this:  http://www-db.stanford.edu/~ullman/fcdb/oracle/or-nonstandard.html#data%20types.