This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Whacky db problem. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Whacky db problem." Watch "Whacky db problem." New topic
Author

Whacky db problem.

Higgledy Smith
Ranch Hand

Joined: Mar 07, 2006
Posts: 192
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

Joined: Oct 06, 2007
Posts: 338
what happens when you run the same query you are using in JDBC in iSQL?
Higgledy Smith
Ranch Hand

Joined: Mar 07, 2006
Posts: 192
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

Joined: Oct 06, 2007
Posts: 338
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

Joined: Oct 27, 2005
Posts: 19654
    
  18

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'"


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
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

Joined: Mar 07, 2006
Posts: 192
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
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

Joined: Mar 04, 2006
Posts: 98
iSQL is a browser based version of SQL*Plus

http://www.oracle.com/technology/obe/obe10gdb/install/isqlplus/isqlplus.htm
Chris Hendy
Ranch Hand

Joined: Mar 04, 2006
Posts: 98
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

Joined: Mar 07, 2006
Posts: 192
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

Joined: Oct 06, 2007
Posts: 338
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

Joined: Oct 06, 2007
Posts: 338
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

Joined: Mar 07, 2006
Posts: 192
Chris,
I tried the sql to get the sid, here is the results:

Higgledy Smith
Ranch Hand

Joined: Mar 07, 2006
Posts: 192
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
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
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
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
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
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
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
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
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
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
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

Joined: Mar 07, 2006
Posts: 192
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

Joined: Oct 06, 2007
Posts: 338
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
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

Joined: Aug 27, 2009
Posts: 5
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Whacky db problem.
 
Similar Threads
using a session value on sql query
Unpredictable protocol violation error
what wrong with 1=1 ?
Difference Between Named Queries and Stored Procedures
To get DB column names dynamically at run time.