• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to deal with two queries

 
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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??
 
Ariane Bogain
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 15304
6
Mac OS X IntelliJ IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Why don't you try my suggestion and use 1 statement instead of 2?
 
Ariane Bogain
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Shiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic