help with resultSet and Statement in Java
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: help with resultSet and Statement in Java

  1. #1
    Join Date
    Jan 2010
    Posts
    161

    help with resultSet and Statement in Java

    Hello everyone
    I dont know what is the problem so please help me figure it out as i cant work it out

    i have this code which is being used as servlet,im using out.println as break points to see till where the codes works,,,

    try{
    Statement stmt=conn.createStatement();
    out.println("break 1");
    ResultSet rs1=stmt.executeQuery(sql2);
    out.println("break 2");
    rs1.next();
    if(rs1.getRow()==0){out.println("No Person wit these details!");}
    out.println(rs1.getRow());
    if((rs1.getInt("studentno")!=Integer.parseInt(studentnum)) && (studentName.equals(rs1.getString("firstname"))))
    {

    out.println(pageLayout()+"wrong ID or Name");

    }
    rs1=null;
    stmt.close();

    out.println("break 3");
    rs1=stmt.executeQuery(sql1);
    rs1.last();
    out.println("Number of row" + rs1.getRow());
    if (rs1.getRow()>=5) {out.println(pageLayout()+teamFull()); System.exit(1);}
    //rs1.close();
    out.println("break 4)");
    rs1=stmt.executeQuery(sql3);
    rs1.next();
    out.println("break 5");
    int teamcode=rs1.getInt("teamcode");
    String sql4="UPDATE students set teamcode="+ teamcode +" where firstname='"+studentName+"' and studentno="+studentnum;
    stmt.executeUpdate(sql4);
    rs1.close();
    String sql5="select firstname,surname,dob,coursename from students where studentno="+ studentnum+" and firstname='"+studentName+"'";
    rs1=stmt.executeQuery(sql5);


    // connecting to database

    /*CREATE YOUR CONNECTION, AND STATEMENT, BEFORE EXECUTING IT HERE*/
    while(rs1.next())
    {
    rs1.next();

    out.println(docType);
    out.println("<html><head>");
    out.println("<title>Student added</title>");
    out.println("<body>");
    out.println("<p>The Following student has been added to your team<P>");
    out.print(rs1.getString("firstname") + "<br>");
    out.print(rs1.getString("surname") + "<br>");
    out.print(rs1.getDate("dob") + "<br>");
    out.print(rs1.getString("coursename") + "<br>");

    }

    conn.close();

    }catch(SQLException e){
    System.err.println(e);
    }


    the codes work till out.println("break 3") which is outputted on the page but then it does not go further. I presume there is a problem with stmt or rs1 which are objects of Statement and ResultSet but don't know what exactly the problem is.

    Thank you

  2. #2
    Join Date
    May 2006
    Location
    UK
    Posts
    4,474

    Re: help with resultSet and Statement in Java

    the codes work till out.println("break 3") which is outputted on the page but then it does not go further
    Do you think it might have something to do with the line before the out.println("break 3") which closes the statement object?
    Posting code? Use code tags like this: [code]...Your code here...[/code]
    Click here for examples of Java Code

  3. #3
    Join Date
    Jan 2010
    Posts
    161

    Re: help with resultSet and Statement in Java

    Quote Originally Posted by keang View Post
    Do you think it might have something to do with the line before the out.println("break 3") which closes the statement object?
    It does not have to do with it as at the beginning i wasn't using it .
    I been reading around and it looks like the createStatement() does not support the multiple execution of queries, so in this case is better to use the prepareStatement.
    I know how to use it to set the data into the database but not how to retrieve specific data
    Ill try to see if i can find something and possibly post it here for other if they have the same problem

  4. #4
    Join Date
    May 2006
    Location
    UK
    Posts
    4,474

    Re: help with resultSet and Statement in Java

    It does not have to do with it as at the beginning i wasn't using it .
    It may not be the total problem but once you have called close() all subsequent attempts to call executeQuery() will fail, so it definitely should not be there.
    I been reading around and it looks like the createStatement() does not support the multiple execution of queries
    CreateStatement doesn't execute queries it creates Statement objects that execute queries. Unless there is a bug in a particular version of Java your statement is not correct for the following reasons:

    1. I've just used a single statement object created by calling createStatement() to run multiple queries.
    2. The API docs for Statement state:
    By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.
    which implies you can make multiple calls to executeQuery().

    Are you sure you are not getting confused here between statements and result sets, which you can only have one open one of per statement object.

    I know how to use it to set the data into the database but not how to retrieve specific data
    Ok so what sql statement are you trying to execute at the failure point?
    Posting code? Use code tags like this: [code]...Your code here...[/code]
    Click here for examples of Java Code

  5. #5
    Join Date
    Jan 2010
    Posts
    161

    Re: help with resultSet and Statement in Java

    Thank you Keang.
    I think I am confusing them but at this point I can't understand the reason why my codes break after out.println("break 3") and it does not reach out.println("break 4")

    I used the statement and resultSet close because i thought after closing and opening them again it will work. but it doesnt
    however even if i remove the stmt.close and rs1.close is still does not work

    what I am trying to do is executes multiple queries

    the first queey does check the database if there is a person with the ID and firstname inserted by the user(data that is captured through a form)
    this execution works because it comes up with a result 1 if the user is there

    the second query checks how many user are part of a specific team(which is again captured by a form)
    and it does not work and this is what me me thought that making another query is not possible and i need to close or reset something in order to make another query and retrieve and set data

    the third wants to retrieve the teamcode of that teamname from the database so it does the queries
    but it does not work either

    the sql statemenst are right as i tried them on sql and then put them in properly on java with all the escape slashes etc
    but I dont know why at that point i cannot execute the queries and retrieve or set data
    Last edited by cpu2007; November 29th, 2010 at 05:08 PM.

  6. #6
    Join Date
    May 2006
    Location
    UK
    Posts
    4,474

    Re: help with resultSet and Statement in Java

    The simplest way to check if it is the Statement object that won't run multiple queries is to just create a new one before your second query.

    You might want to put a println() statement between every line between your break 3 and break 4 print outs to make sure you know exactly which line is failing rather than assuming it is the line after break 3.
    Posting code? Use code tags like this: [code]...Your code here...[/code]
    Click here for examples of Java Code

  7. #7
    Join Date
    Jan 2010
    Posts
    161

    Re: help with resultSet and Statement in Java

    yeah I have created another before the second query
    ResultSet rs1,rs2;
    PreparedStatement,prst,prst2;

    now before I used rs1 and prst and then for the second query I used prst2 and rs2 but still the same error.
    what you mean println the break 3 and 4? I am printing them in the code thats how actuaally I am debugging the code to see where the code breaks.

  8. #8
    Join Date
    Jan 2010
    Posts
    161

    Re: help with resultSet and Statement in Java

    I can't work it out, I hope someone can tell me what is actually wrong with the code.
    I can't make another query, it does only execute one query and then after that it does break.
    This is the code:
    //------------------------------------------
    //Execute sql2 statement to check if the studentnum and studentName captured from the form do exist in the database
    PreparedStatement prst;
    ResultSet rs1;
    prst=conn.prepareStatement(sql2);
    prst.setInt(1, Integer.parseInt(studentnum));
    prst.setString(2, studentName);
    rs1=prst.executeQuery();
    rs1.next();
    if(rs1.getRow()==0){out.println("No Person wit these details!");}
    out.println("Break 1" + rs1.getString("firstname"));


    //Execute sql1 to check how many people are part of that team,if equal to 5 or more then no more add is available;
    prst=conn.prepareStatement(sql1);
    prst.setString(1, teamname);
    ResultSet rs2=prst.executeQuery();


    rs2.last();
    out.println("number of rows" +rs2.getRow());
    if (rs2.getRow()>=5) {out.println(pageLayout()+teamFull());}
    /*if((rs1.getInt("studentno")!=Integer.parseInt(studentnum)) && (studentName.equals(rs1.getString("firstname"))))
    {

    out.println(pageLayout()+"wrong ID or Name");

    }
    */
    out.println("break 3" );
    //-----------------------------------------------------------

    I used out.println("break 1-23"); to check where the codes break.
    In this case in does print >>> out.println("Break 1" + rs1.getString("firstname"));
    but it seems after that,when i start executing another query using prst and resultset ,it does not go throught and I can't reach break 3.

    can someone tell me if there is any error in quering the database when we need to have multiple queries?

    THank you

  9. #9
    Join Date
    Jan 2010
    Posts
    161

    Re: help with resultSet and Statement in Java

    Ok! I finally have been able to solve the problem.
    I started debuging the preparedStatement prst and see if was executing the statement properly and found out that in my statement there was no space before the clause WHERE ,so the query could not be executed properly as it was unrecognisable.

    However I had to initialize a new variable of ResultSet for every single execution that I wanted to retrieve the data from. why can't I use the a single variable object and assign values to it?

  10. #10
    Join Date
    May 2006
    Location
    UK
    Posts
    4,474

    Re: help with resultSet and Statement in Java

    However I had to initialize a new variable of ResultSet for every single execution that I wanted to retrieve the data from. why can't I use the a single variable object and assign values to it?
    Of course you can. An object variable just holds a reference to the object assigned to it. It can be assigned a reference to any other object of the same type or sub-type unless you've declared the variable as final of course.

    It doesn't matter if you use the same variable or different variables to hold the references to the objects returned by each of the calls to executeQuery(), either approach will work.
    Posting code? Use code tags like this: [code]...Your code here...[/code]
    Click here for examples of Java Code

  11. #11
    Join Date
    Jan 2010
    Posts
    161

    Re: help with resultSet and Statement in Java

    Quote Originally Posted by keang View Post
    Of course you can. An object variable just holds a reference to the object assigned to it. It can be assigned a reference to any other object of the same type or sub-type unless you've declared the variable as final of course.

    It doesn't matter if you use the same variable or different variables to hold the references to the objects returned by each of the calls to executeQuery(), either approach will work.
    I did it but it doesnt thats why i initialized a resultSet variable for every new query
    ResultSet rs1,rs2,rs3 ;etc

    before i was assigning

    rs1=prst.executeQuery(sql1);

    and it was working only for the first execution
    when i assigned
    rs1=prst.executeQuery(sql2);
    it did not work
    so i assingned the second execution to rs2 instead.

  12. #12
    Join Date
    May 2006
    Location
    UK
    Posts
    4,474

    Re: help with resultSet and Statement in Java

    When you say it did not work, what actually happened?

    Please show the full code for 2 query executions using a single result set and also for the multiple result sets so I can see the difference in the code you ran. And please make sure the code you show for the single result set is code you have actually run as opposed to just changing the current code back to the code you think you ran.
    Posting code? Use code tags like this: [code]...Your code here...[/code]
    Click here for examples of Java Code

  13. #13
    Join Date
    Jan 2010
    Posts
    161

    Re: help with resultSet and Statement in Java

    I have already cleaned the code and using the one that is working but here is how the code was when it was not working:
    //------------------------------------------------------
    //Execute sql2 statement to check if the studentnum and studentName captured from the form do exist in the database
    ResultSet rs1;
    PreparedStatement prst;
    prst=conn.prepareStatement(sql2);
    prst.setInt(1, Integer.parseInt(studentnum));
    prst.setString(2, studentName);
    rs1=prst.executeQuery();
    rs1.next();
    if(rs1.getRow()==0){out.println("No Person wit these details!");}


    //I put a out.println("break 1") here and it was printing it on the browser which means till this point
    //the code has been executed

    //Execute sql1 to check how many people are part of that team,if equal to 5 or more then no more add is available;

    prst=conn.prepareStatement(sql1);
    prst.setString(1, teamname);
    rs1=prst.executeQuery();
    rs1.last();

    if (rs1.getRow()>=5) {out.println(pageLayout()+teamFull());}

    //i put a out.prinntln("break 2") here but it was not executed which means somewhere here the code //broke
    //All this was in a try catch statement

    //---------------------------------------------------------




    -This is the code I am using now and is working

    //------------------------------------------------------------
    //Execute sql2 statement to check if the studentnum and studentName captured from the form do exist in the database
    ResultSet rs1;
    PreparedStatement prst;
    prst=conn.prepareStatement(sql2);
    prst.setInt(1, Integer.parseInt(studentnum));
    prst.setString(2, studentName);
    rs1=prst.executeQuery();
    rs1.next();
    if(rs1.getRow()==0){out.println("No Person wit these details!");}


    //Execute sql1 to check how many people are part of that team,if equal to 5 or more then no more add is available;

    prst=conn.prepareStatement(sql1);
    prst.setString(1, teamname);
    ResultSet rs2=prst.executeQuery();
    rs2.last();

    if (rs2.getRow()>=5) {out.println(pageLayout()+teamFull());}
    //-----------------------------------------

  14. #14
    Join Date
    May 2006
    Location
    UK
    Posts
    4,474

    Re: help with resultSet and Statement in Java

    The only reason I can think of that the single variable code might not work (assuming the sql is valid) is you haven't closed the result set before calling executeQuery the second time.

    BTW there's little point in using prepared statements if you construct them each time you want to use them. The idea is you create them in advance and then reuse the same prepared statement object multiple times.
    Posting code? Use code tags like this: [code]...Your code here...[/code]
    Click here for examples of Java Code

  15. #15
    Join Date
    Jan 2010
    Posts
    161

    Re: help with resultSet and Statement in Java

    yeah that might be the reason, i have used rs1.close(); but i do not remember if i did that once the code was working...i was doing it at the beginning (when i didn't know that there was an error in my sql statement) thinking that there was something wrong with the connection.

    I don't quiete understand what you mean by constructing a prepared statement everytime.
    I am not constructing it everytime or am i?
    what would be the right way if you can give me an example .

    another off topic question, hope you can enlighten me on this
    lets say i have some if statement that do a check if the captured data is correct,if wrong how can i stop the execution of the data below.
    i dont want to put all the code that needs to be executed (if the data is right) inside an if statement ,it will make all messed up, all i want if some of those if statement are not fullfilled then to print an error and stop the execution below.
    i read about system.exit(); which is advised to be used

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center