Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes How to deal with two queries Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to deal with two queries" Watch "How to deal with two queries" New topic
Author

How to deal with two queries

Ariane Bogain
Greenhorn

Joined: Apr 25, 2003
Posts: 15
When a user enter their password I'd like two tables of a database to be queried, one to check that the password is valid and one to check that a user is signed on another system and if these two queries are right then they can proceed.
The code is the following:
public void actionPerformed(ActionEvent e)
{
if (e.getSource() == submit)

try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url, user, password;
url ="jdbc dbc:STUDENT";
user = "MSC030";
password = "bagpuss";
Connection conn =DriverManager.getConnection(url, user, password);
String sta=id.getText();

String query="SELECT PASSWORD FROM MSC030.STAFF WHERE STAFF_NO='"+ sta + "'";
String vquery="SELECT SIGN FROM MSC030.AR WHERE STAFF_NO='"+ sta + "'";
ResultSet theResult;
ResultSet check;
Statement stmt=conn.createStatement();
theResult=stmt.executeQuery(query);
check=stmt.executeQuery(vquery);
while (theResult.next() && check.next()){
String spass=pas.getText();
String p=spass.trim();
String ar;
String valid;
valid=theResult.getString("PASSWORD");
ar=check.getString("SIGN");
String ch= ar.trim();
String v=valid.trim();

if (v.equals(p) && (ch=="Y"))
{Frame frame=new Frame();
frame.setTitle("Password");
Jobs test=new Jobs();
frame.setSize(250,250);
frame.setBackground(Color.lightGray);
frame.add(test);
frame.setVisible(true);
}
else
{
Frame frame=new Frame();
frame.setTitle("Jobs");
Staff test=new Staff();
frame.setSize(250,250);
frame.setBackground(Color.lightGray);
frame.add(test);
frame.setVisible(true);
}

}
}
catch(SQLException s)
{System.out.print("SQL Error:" +s.toString() + "" + s.getErrorCode() + "" + s.getSQLState());
}
catch(ClassNotFoundException s)
{System.out.print("Class not found");
}
}

The compiler finds no error but when I try it I receive this message:
SQL Error:java.sql.SQLException:ResultSet is closed0null
Could anyone tell me what I'm doing wrong?
Thanks
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Hi Ariane,
According to the code you have posted, it looks (to me) like you are trying to simultaneously open two different "ResultSet" objects using the one "Statement" object. As far as I know, this cannot be done. If I remember correctly, when you execute the second query, the first "ResultSet" is implicitly closed. (Hence the error message you claim to be getting -- I think.)
In my opinion, you need two, separate "Statement" objects. You can have two simultaneous "Statement" objects using the one "Connection" object.
Hope this helps.
Good Luck,
Avi.
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Alternatively you could try creating a join query and just use one ResultSet. Below is an example:
SELECT A.PASSWORD, B.SIGN FROM MSC030.STAFF AS A, MSC030.AR AS B WHERE A.STAFF_NO = '"+ sta+"'" AND B.STAFF_NO='"+sta+"'";
I am pretty sure the syntax is correct, but you may want to double check.
Basically then all you have to do now is check that ANYTHING was returned to the ResultSet. If the ResultSet is empty, then either the password was incorrect, or the SIGN was not there. If I understood your question correctly, both must have a valid return or no access is granted??


GenRocket - Experts at Building Test Data
Ariane Bogain
Greenhorn

Joined: Apr 25, 2003
Posts: 15
I've tried to create two statements as you suggested and this time there is no error but nothing is happening!
Here is my new code:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url, user, password;
url ="jdbc dbc:STUDENT";
user = "MSC030";
password = "bagpuss";
Connection conn =DriverManager.getConnection(url, user, password);
String sta=id.getText();

String query="SELECT PASSWORD FROM MSC030.STAFF WHERE STAFF_NO='"+ sta + "'";
ResultSet theResult;
Statement stmt=conn.createStatement();
theResult=stmt.executeQuery(query);
String vquery="SELECT SIGN FROM MSC030.AR WHERE STAFF_NO='"+ sta +"'";
ResultSet check;
Statement st=conn.createStatement();
check=st.executeQuery(vquery);
while (theResult.next() && check.next()){
String spass=pas.getText();
String p=spass.trim();
String valid;
valid=theResult.getString("PASSWORD");
String v=valid.trim();
String sig;
sig=check.getString("SIGN");
String ch=sig.trim();
if (v.equals(p) && ch=="Y")
{Frame frame=new Frame();
frame.setTitle("Booking");
Jobs test=new Jobs();
frame.setSize(250,250);
frame.setBackground(Color.lightGray);
frame.add(test);
frame.setVisible(true);
}
else
{
Frame frame=new Frame();
frame.setTitle("Password");
Staff test=new Staff();
frame.setSize(250,250);
frame.setBackground(Color.lightGray);
frame.add(test);
frame.setVisible(true);
}

}
Could anyone point out what I'm doing wrong?
Thanks
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Why don't you try my suggestion and use 1 statement instead of 2?
Ariane Bogain
Greenhorn

Joined: Apr 25, 2003
Posts: 15
I've tried your suggestion too but it doesn't work either! I think the problem comes from my AR table because when I do a simple query to see whether the AR table was being accessible like the following:
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url, user, password;
url ="jdbc dbc:STUDENT";
user = "MSC030";
password = "bagpuss";
Connection conn =DriverManager.getConnection(url, user, password);
String sta=id.getText();

String query="SELECT SIGN FROM MSC030.AR WHERE STAFF_NO='"+ sta +"'";
ResultSet check;
Statement st=conn.createStatement();
check=st.executeQuery(query);
String sig;
sig=check.getString("SIGN");
while (check.next()){
System.out.print ("hello");

}
I receive the following error: SQL Error: java.sql.SQL Exception [OBDC Driver Manager] invalid cursor state024000.
The table AR works fine in Oracle so can you suggest where the problem lies?
Thank you
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Ariane,
I'm sorry, I forgot to mention, that while you can create two "Statement"s from the same "Connection", I don't think you can invoke their "executeQuery()" methods simultaneously. I suggest that you execute the first query and extract the required data from the "ResultSet". Then close that "Statement" and then execute the second query.
(Remember, this is just a suggestion -- I have not tried this out myself.)
Regarding your error message:

SQL Error: java.sql.SQL Exception [OBDC Driver Manager] invalid cursor state024000

I think it is due to your SQL statement:



If the datatype for column STAFF_NO (in table AR) is NUMBER, then you don't need to use the single quote (i.e. ') delimiters. Single quotes are used to delimit string literals only (and not numeric literals). I guess that Oracle is probably clever enough to deal with this (i.e. it knows how to deal with numeric literals that are delimited with single quotes), but Micro$oft Access obviously isn't as clever.
(Again, this is only a guess.) So try removing the single-quote delimiters in your SQL statement.
In any case, it may be more correct to use a "PreparedStatement" instead of a "Statement". That way you don't need to worry about delimiters. Something like:

Hope this has helped you.
Good Luck,
Avi.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to deal with two queries
 
Similar Threads
Problems using GridLayout
problem in running AWT programs!!
organising panels
How to display contents of JList or drop down list?
java querying a database