help with resultSet and Statement in Java

**cpu2007** · November 29th, 2010, 04:10 PM

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("The Following student has been added to your team");
out.print(rs1.getString("firstname") + " ");
out.print(rs1.getString("surname") + " ");
out.print(rs1.getDate("dob") + " ");
out.print(rs1.getString("coursename") + " ");

}

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

**keang** · November 29th, 2010, 04:46 PM

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?

**cpu2007** · November 29th, 2010, 05:05 PM

Originally Posted by keang

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

**keang** · November 29th, 2010, 05:49 PM

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?

**cpu2007** · November 29th, 2010, 06:05 PM

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

**keang** · November 29th, 2010, 06:20 PM

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.

**cpu2007** · November 30th, 2010, 05:27 AM

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.

**cpu2007** · November 30th, 2010, 06:57 AM

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

**cpu2007** · November 30th, 2010, 08:36 AM

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?

**keang** · November 30th, 2010, 09:32 AM

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.

**cpu2007** · November 30th, 2010, 10:00 AM

Originally Posted by keang

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.

**keang** · November 30th, 2010, 10:10 AM

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.

**cpu2007** · November 30th, 2010, 11:06 AM

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());}
//-----------------------------------------

**keang** · November 30th, 2010, 11:24 AM

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.

**cpu2007** · November 30th, 2010, 12:30 PM

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

Thread: help with resultSet and Statement in Java

Thread Tools

Display

help with resultSet and Statement in Java

Re: help with resultSet and Statement in Java

Re: help with resultSet and Statement in Java

Re: help with resultSet and Statement in Java

Re: help with resultSet and Statement in Java

Re: help with resultSet and Statement in Java

Re: help with resultSet and Statement in Java

Re: help with resultSet and Statement in Java

Re: help with resultSet and Statement in Java

Re: help with resultSet and Statement in Java

Re: help with resultSet and Statement in Java

Re: help with resultSet and Statement in Java

Re: help with resultSet and Statement in Java

Re: help with resultSet and Statement in Java

Re: help with resultSet and Statement in Java

Posting Permissions