File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Whacky db problem.

 
Higgledy Smith
Ranch Hand
Posts: 192
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Anyone get erroneous results when querying a db? Thru iSQL, I run

This is the result: (which I know is correct).
ID SEMESTER COURSENUMBER TITLE
1 Spring CMIS102 Introduction to Problem Solving
2 Winter CMIS241 Introduction to Java Programming

But, when I query for Spring CMIS102 via Java, I get this error:

There were problems processing your request:

* The course that you have selected does not yet exist; please select another one.


How do I even begin to debug this error? I am lost with this one. Thank you.

Higgledy
 
Paul Campbell
Ranch Hand
Posts: 338
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
what happens when you run the same query you are using in JDBC in iSQL?
 
Higgledy Smith
Ranch Hand
Posts: 192
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Good question. This is my retrieve declaration:

private static final String RETRIEVE_STMT = "SELECT * FROM Course WHERE semester=Winter AND courseNumber=CMIS241";
 
Paul Campbell
Ranch Hand
Posts: 338
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
few things you can check:

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.
 
Rob Spoor
Sheriff
Pie
Posts: 20372
44
Chrome Eclipse IDE Java Windows
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Higgledy Smith:
Good question. This is my retrieve declaration:

private static final String RETRIEVE_STMT = "SELECT * FROM Course WHERE semester=Winter AND courseNumber=CMIS241";

Use quotes around the string literals:
"SELECT * FROM Course WHERE semester = 'Winter' AND courseNumber = 'CMIS241'"
 
Paul Campbell
Ranch Hand
Posts: 338
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Rob Prime:

Use quotes around the string literals:
"SELECT * FROM Course WHERE semester = 'Winter' AND courseNumber = 'CMIS241'"



If that were the problem he should be receiving an ORA-00904: invalid identifier for both Winter and CMIS241... unless there is an oracle exception being ignored.

Edit: my bad... surely DB2 (since this is iSQL) makes you bind variables in query, doesn't it? ... okay... this is going to bug me until I download personal DB2 to find out tonight.
[ November 13, 2007: Message edited by: Paul Campbell ]
 
Higgledy Smith
Ranch Hand
Posts: 192
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 338
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Chris Hendy
Ranch Hand
Posts: 98
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
iSQL is a browser based version of SQL*Plus

http://www.oracle.com/technology/obe/obe10gdb/install/isqlplus/isqlplus.htm
 
Chris Hendy
Ranch Hand
Posts: 98
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

SELECT vs.SID, vs.SERIAL#, vp.SPID
FROM v$session vs, v$process vp
WHERE vs.username = '&java_user'
AND vs.paddr = vp.addr;

and turning trace on from a separate SQL*Plus session using

exec dbms_system.set_ev(&sid,&serial,10046,4,'')

Once the query has returned in Java, drop the connection and go look at the trace file in the user_dump_dest directory of the database server. Find this in SQL*Plus by typing

SHOW PARAMETER user_dump_dest

The SQL statement issued by the Java APP will be in a text file called
<oracle_sid>_ora_<SPID>.trc along with any bind variable values if you used a prepared statement.

PARSING IN CURSOR #1 len=50 dep=0 uid=32 oct=3 lid=32 tim=18446744071330715763 hv=2604616925 ad='1b57dc50'
select user_id from all_users where username = :b1
END OF STMT
PARSE #1:c=0,e=6713,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=0,tim=18446744071330715758
BINDS #1:
bind 0: dty=1 mxl=32(10) mal=00 scl=00 pre=00 oacflg=03 oacfl2=10 size=32 offset=0
bfp=06932354 bln=32 avl=05 flg=05
value="JAVAUSER"
EXEC #1:c=0,e=861,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=18446744071330717956
FETCH #1:c=0,e=58,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=3,tim=18446744071330718234

The bind values are displayed in the same order as they appear in the SQL statement.

Check out the following for a fuller explanation.

http://www.rittmanmead.com/work_stuff/extended_sql_trace_and_tkprof.htm

http://www.juliandyke.com/Diagnostics/Trace/EnablingTrace.html
 
Higgledy Smith
Ranch Hand
Posts: 192
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is how my prepared statment is declared:


value 1 resolves to Winter
value 2 resolves to CMIS241

In iSQL, I exec this query:


Here is the result from iSQL:
ID SEMESTER COURSENUMBER TITLE
2 Winter CMIS241 Introduction to Java Programming
 
Paul Campbell
Ranch Hand
Posts: 338
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Paul Campbell
Ranch Hand
Posts: 338
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you run it in SQL



and your results are
ID SEMESTER COURSENUMBER TITLE
1 Spring CMIS102 Introduction to Problem Solving

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
Posts: 192
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Chris,
I tried the sql to get the sid, here is the results:

 
Higgledy Smith
Ranch Hand
Posts: 192
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 98
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 98
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 338
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 338
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 192
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 98
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I just would start his Plan C before proceeding to Plan A --> Plan B if it were me.


Totally agree.
 
Paul Campbell
Ranch Hand
Posts: 338
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 192
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 98
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 192
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I believe this error is related to the context definition. Should the docbase parameter have a value?

Problem looking up jdbc/oracleDS: javax.naming.LinkException: [Root exception is javax.naming.NameNotFoundException: While trying to lookup 'jdbc.oracleDS' didn't find subcontext 'jdbc'. Resolved ''; remaining name 'jdbc/oracleDS']; Link Remaining Name: 'jdbc/oracleDS'


 
Higgledy Smith
Ranch Hand
Posts: 192
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Also, my IDE, BEA Workshop for JSP, flagged a warning on web.xml:

CHKJ4019W: Invalid res-sharing-scope; valid values are "Shareable" or "Unshareable".4.4.courseRegistrationDB/WebContent/WEB-INFweb.xmlorg.eclipse.jst.j2ee.common.internal.impl.ResourceRefImpl@13399d7 (description: null, name: jdbc/oracleDS, type: javax.sql.DataSource, auth: Container, link: null, resSharingScope: <unset>)119514415256521


BEA's documentation states this is optional, but should I set it and to which value?
http://edocs.bea.com/wls/docs81/webapp/web_xml.html#1045815


<res-sharing-scope>
Optional

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

Valid values:
* Shareable
* Unshareable
 
Paul Campbell
Ranch Hand
Posts: 338
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 192
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Thanks for all the help!
 
Kaushik Yeleswarapu
Greenhorn
Posts: 5
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Even I faced the same error
javax.naming.LinkException: [Root exception is javax.naming.NameNotFoundException: While trying to lookup 'jdbc.claudenmDb' didn't find subcontext 'jdbc'. Resolved ''; remaining name 'jdbc/claudenmDb']; Link Remaining Name: 'jdbc/claudenmDb'

It got resolved, the reason being my datasources were not bound to AdminServer targets.
I had to go to Weblogic admin console and set the targets for my datasources.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic