Resultset Problem
I am trying to get the ids from category table and making query on the basis of these ids. The following code is throwingjava.lang.NullPointerException whereas the data exists.
please guide me where am making mistake?
thanks & kind regards
String category=req.getParameter("category");
String query="select e_id from event_category where category='"+category+"'";
rs=stm.executeQuery(query);
while(rs.next()){
int Eid=rs.getInt(1);
rs1=stm1.executeQuery("select event from event where e_id="+Eid);
while(rs1.next()){
out.println(rs1.getString(1));
}
}
[947 byte] By [
farakha] at [2007-11-27 1:17:33]
# 1
Well, it would help to know which line the error occurs on, and see some more code, especially the place where stm and stm1 are defined.
Edit:
And why not try:
Select b.event
from event_category a
event b
where a.category = ?
and b.e_id = a.e_id
That is presented as the query for a PreparedStatment. Change it yourself to insert the category variable instead of the ? if you insist on using a Statement instead.
# 2
> Well, it would help to know which line the error
> occurs on, and see some more code, especially the
> place where stm and stm1 are defined.
>
> Edit:
>
> And why not try:
>
> > Select b.event
> from event_category a
>event b
> a.category = ?
> and b.e_id = a.e_id
> /code]
>
> That is presented as the query for a
> PreparedStatment. Change it yourself to insert the
> category variable instead of the ? if you insist on
> using a Statement instead.
Thanks for your advice but please see again as its throwing:
[b]java.sql.SQLException: ORA-00933: SQL command not properly ended[/b] . thanks again
[code]String query="Select b.event from event_category a event b where a.category = ? and b.e_id = a.e_id";
PreparedStatement ps=cnn.prepareStatement(query);
ps.setString(1, category);
rs=ps.executeQuery();
while(rs.next()){
out.println(rs.getString(1));
}
ps.close();
# 3
A simple typo. Add a comma after the a i.e.Select b.event from event_category a, event b where a.category = ? and b.e_id = a.e_id
# 4
thanks masijade the record appeared with event_category a, event bbut it shows one record (? i.e. category has many records) thats why i used the rs1. please advicethanks again
# 5
wow! greate!masijade you are really genuisthanks a lot. The problem solved and please ignore my previous poststhanks thanks thanks........
# 7
[nobr]please see my code as this shows my query two times. where am going wrong
String category=req.getParameter("category");
String query1="select a.e_id,e.category,event,emonth,eyear,sdate,edate,location,country,website,organizer"+
" from event a,event_time b,event_location c,event_organizer d,event_category e"+
" where (a.e_id = b.e_id and a.e_id = c.e_id and a.e_id = d.e_id) "+
" and (b.eyear=?) and (b.emonth=? or b.emonth>=?) and e.category=? order by emonth asc";
PreparedStatement ps1=cnn.prepareStatement(query1);
ps1.setInt(1, year);
ps1.setInt(2, currentMonth);
ps1.setInt(3, currentMonth);
ps1.setString(4,category);
rs=ps1.executeQuery();
while(rs.next()){
out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(4)+" "+rs.getString(5)+" "+rs.getString(6)+" "+rs.getString(7)+" "+rs.getString(8)+" "+rs.getString(9)+" "+rs.getString(10)+" "+rs.getString(11)+"<br><br>");
}
ps1.close();
[/nobr]
# 8
What do you mean "it shows my query two times"? Do you mean it provides two results?But as a quick question, what is the purpose of(b.emonth=? or b.emonth>=?)the >= includes the = so the first part of that is not needed. It should simply beb.emonth>=?
# 9
(b.emonth=? or b.emonth>=?)
The left part of this OR is superfluous since it is also covered by the right part of this OR.
Further on, it might be useful if you take some time to follow a SQL tutorial. It would open the doors to a new world ;)
http://www.google.com/search?q=sql+tutorial
# 10
> What do you mean "it shows my query two times"? Do
> you mean it provides two results?
>
> But as a quick question, what is the purpose of
> > (b.emonth=? or b.emonth>=?)
>
>
> the >= includes the = so the first part of that is
> not needed. It should simply be
> > b.emonth>=?
>
thanks masijade and BauluC for correction but it still showing more than one record of same type and that should be because:
Scenario:
category has more than one record with same category whereas the event table has one record for one id thats why i used resultset and resultset1.
I am trying to get the ids from category where the user selected category and then trying to display events according to these ids
please advice thanks again
# 11
Your best bet would be to take the query (and only the query not all the Java stuff around it) to an SQL forum and ask the question there.
This is now a simple SQL issue and has nothing to do with Java.
They will, however, need the following information:
The structure of the tables involved (at least for the fields involved).
Sample Data from the tables that produce the reported result.
Which we would also need to help you further. But my guess would be because a number of those fields are not qualified (i.e. simply event rather than a.event) and may produce some ambiguity, although that should produce an error, rather than extra sets, I still, personally, don't like that.
# 12
thanks a lot masijade for guidence
