1) are you connecting to the same data base? 2) when you run your JDBC select does it retrieve the data 2.a) if it doesn't find what you expect... do a describe table to see how the data is represented.

This is Oracle 10i. The code I am running is not my code but an example from the class material (I am taking Advanced Java). When I looked at the prepared statement (SELECT) I thought the same thing about the single quotes missing but why when I run the code to query for the 1st record in the table, I get the correct result and an error when query for the 2nd record? Both queries use the same exact code, same tomcat, same everything. That, to me is messed-up!

Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338

posted Nov 13, 2007 21:45:00

0

Originally posted by Higgledy Smith: This is Oracle 10i. The code I am running is not my code but an example from the class material (I am taking Advanced Java). When I looked at the prepared statement (SELECT) I thought the same thing about the single quotes missing but why when I run the code to query for the 1st record in the table, I get the correct result and an error when query for the 2nd record? Both queries use the same exact code, same tomcat, same everything. That, to me is messed-up!

Please post the queries, the results, and the error. If you do that... we should be able to resolve this in few moments. Also execute DESC Course and post the results, too.

And while we are at it... you're not executing iSQL if you're in Oracle. ;) [ November 13, 2007: Message edited by: Paul Campbell ]

To find out what SQL statement is actually hitting the database from your Java app I suggest using extended SQL trace.

After your Java app has established a database connection, and before issuing the query, turn on level 4 extended trace for the Java apps session. Do this by getting the SID, SERIAL# and SPID of the Java connection from v$session

Here is the result from iSQL: ID SEMESTER COURSENUMBER TITLE 2 Winter CMIS241 Introduction to Java Programming

Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338

posted Nov 14, 2007 15:32:00

0

Chris, that is all very good advice... but some of those suggestions require privileges beyond what most developers and users are allowed... at least everywhere I've worked so far. V$ privileges are normally revoked from public.

then the problem must be in your code and how your handling you're result set and not a "whacky db" issue. [ November 14, 2007: Message edited by: Paul Campbell ]

Higgledy Smith
Ranch Hand

Joined: Mar 07, 2006
Posts: 192

posted Nov 14, 2007 15:43:00

0

Chris, I tried the sql to get the sid, here is the results:

Higgledy Smith
Ranch Hand

Joined: Mar 07, 2006
Posts: 192

posted Nov 14, 2007 15:47:00

0

Paul,

Normally, I too would blame my code but the code worked when the 1st record in the Course Table was selected. So why would the code work for one record and not the other?

Phil

Chris Hendy
Ranch Hand

Joined: Mar 04, 2006
Posts: 98

posted Nov 14, 2007 15:57:00

0

Try using sys.v_$SESSION instead of v$session, and sys.v_$PROCESS instead of v$process.

If that doesn't work then I fear Paul is correct and you don't have the privileges.

It may be time to start practising another real world Developer skill - wheedling the DBA. Either to temporarily grant them to you, or to do the tracing for you.

Chris Hendy
Ranch Hand

Joined: Mar 04, 2006
Posts: 98

posted Nov 14, 2007 16:58:00

0

Plan B.

This only works if your oracle user has alter session privilege, and you either have access to the user_dump_dest or if not can get the DBA to give you your trace file.

Since you can amend your own Java code, don't try to turn trace on from another session, turn it on in Java by executing the following SQL before your query on COURSE

alter session set tracefile_identifier = 'HIGGLEDY';

alter session set events '10046 trace name context forever, level 4';

The trace file will be named

<oracle_sid>_ora_<SPID>_higgledy.trc

so you don't need to know the SPID.

Plan C.

Can you post the part of your Java code concerned with the query, results and the logic for generating the message "The course that you have selected does not yet exist; please select another one" so we can look at it?

Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338

posted Nov 14, 2007 18:16:00

0

Originally posted by Higgledy Smith: Paul,

Normally, I too would blame my code but the code worked when the 1st record in the Course Table was selected. So why would the code work for one record and not the other?

Phil

Phil,

My first rule for myself when wearing a developer hat... is I always blame my code first. It is the most volatile part of the equation and it is something that must be validated prior to going into production anyway.

Everything Chris suggests is 100% a good idea... I just would start his Plan C before proceeding to Plan A --> Plan B if it were me.

Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338

posted Nov 14, 2007 18:18:00

0

Originally posted by Higgledy Smith: Chris, I tried the sql to get the sid, here is the results:

It means you don't have select on v$.

Higgledy Smith
Ranch Hand

Joined: Mar 07, 2006
Posts: 192

posted Nov 14, 2007 18:34:00

0

Below is my prepared statement declaration and the code which sets the 2 variables. How can I see stmt query value before it is exequted? The system.out.println returns a the object's value.

Chris Hendy
Ranch Hand

Joined: Mar 04, 2006
Posts: 98

posted Nov 14, 2007 18:36:00

0

I just would start his Plan C before proceeding to Plan A --> Plan B if it were me.

Totally agree.

Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338

posted Nov 14, 2007 19:17:00

0

Originally posted by Higgledy Smith: Below is my prepared statement declaration and the code which sets the 2 variables. How can I see stmt query value before it is exequted? The system.out.println returns a the object's value.

Phil,

What do you do with the result set after it is returned? The part that produces the course that you have selected message? Also, is there more to the data than we are seeing... like future courses not available based on date parameters?

Higgledy Smith
Ranch Hand

Joined: Mar 07, 2006
Posts: 192

posted Nov 14, 2007 19:40:00

0

I am getting an exception before the it even connects to the database. So there is something wrong in my context.xml or web.xml files or some other dB reference. The context.xml is in META-INF and the web.xml is in WEB-INF.

Chris Hendy
Ranch Hand

Joined: Mar 04, 2006
Posts: 98

posted Nov 14, 2007 19:41:00

0

Since you are using a prepared statement and setString I don't think you need the single quotes. They are only used for string literals.

"SELECT * FROM Course WHERE semester='?' AND courseNumber='?'"

should be

"SELECT * FROM Course WHERE semester=? AND courseNumber=?";

Higgledy Smith
Ranch Hand

Joined: Mar 07, 2006
Posts: 192

posted Nov 15, 2007 09:50:00

0

I believe this error is related to the context definition. Should the docbase parameter have a value?

Specifies whether connections obtained through the given resource manager connection factory reference can be shared.

Valid values: * Shareable * Unshareable

Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338

posted Nov 16, 2007 08:11:00

0

Phil,

You should deal with the jdbc/sql oriented questions in this thread... for the other issues you likely should post in a different forum so that you can help... the error sounds like a local configuration problem, but I really can't help you with that one.

Paul

Higgledy Smith
Ranch Hand

Joined: Mar 07, 2006
Posts: 192

posted Nov 16, 2007 16:53:00

0

I have narrowed my jdbc problem to my IDE, BEA Workshop. I placed my project into my tomcat webapps directory, started tomcat. I was able to run my project successfully.