Problem in retrieving data and store it in string

[Micheal John]

I am running an application in tomcat 5.0. I have used the following coding for retreving the data from db and store it in string:

public static String getEmployeeIdAndNameDeveloper(Connection conn,Statement stmt,ResultSet rs,String projectName) throws SQLException{
String EmployeeInfo= "-Select-" + COLUMN_SEPARATOR + "-Select-";
sql = "SELECT EMPLOYEEID,EMPLOYEENAME FROM EMPLOYEEINFO WHERE ACTIVATIONSTATUS='Y' AND EMPLOYEEGRADE LIKE 'E%' ORDER BY EMPLOYEENAME ";
System.out.println("sql ::"+sql);
rs = stmt.executeQuery(sql);
while(rs.next()){
System.out.println("in while EmployeeInfo ::"+EmployeeInfo);
EmployeeInfo = EmployeeInfo+ ROW_SEPARATOR  +rs.getString("EMPLOYEEID") + COLUMN_SEPARATOR + rs.getString("EMPLOYEENAME");
}
System.out.println("EmployeeInfo ::"+EmployeeInfo);
return EmployeeInfo;
}
 

The above coding worked properly. I have added one new employee and the above code is not working and in the tomcat console it's just hacked. I have copied that below. If i am deleting the added user,then the application is working. What will be problem???

From TOmcat Console
[CONSOLE]
sql ::SELECT EMPLOYEEID,EMPLOYEENAME FROM EMPLOYEEINFO WHERE ACTIVATIONSTATUS='Y
' AND EMPLOYEEGRADE LIKE 'E%' ORDER BY EMPLOYEENAME
in while EmployeeInfo ::-Select-~-Select-
in while EmployeeInfo ::-Select-~-Select-^6000~Mukesh Ambani
in while EmployeeInfo ::-Select-~-Select-^6000~Mukesh Ambani^6001~Tom
in while EmployeeInfo ::-Select-~-Select-^6000~Mukesh Ambani^6001~Tom^9000~anil
kumar
in while EmployeeInfo ::-Select-~-Select-^6000~Mukesh Ambani^6001~Tom^9000~anil
kumar^1000~asdad
in while EmployeeInfo ::-Select-~-Select-^6000~Mukesh Ambani^6001~Tom^9000~anil
kumar^1000~asdad^9002~ganesh
in while EmployeeInfo ::-Select-~-Select-^6000~Mukesh Ambani^6001~Tom^9000~anil
kumar^1000~asdad^9002~ganesh^9001~hari
in while EmployeeInfo ::-Select-~-Select-^6000~Mukesh Ambani^6001~Tom^9000~anil
kumar^1000~asdad^9002~ganesh^9001~hari^6002~jack
in while EmployeeInfo ::-Select-~-Select-^6000~Mukesh Ambani^6001~Tom^9000~anil
kumar^1000~asdad^9002~ganesh^9001~hari^6002~jack^7878~pete
in while EmployeeInfo ::-Select-~-Select-^6000~Mukesh Ambani^6001~Tom^9000~anil
kumar^1000~asdad^9002~ganesh^9001~hari^6002~jack^7878~pete^9005~pravin
_
[/CODE]

[Bill Johnston]

"The above coding worked properly. I have added one new employee and the above code is not working"

Not sure if it's working or not?

[Micheal John]

I executed the below query in the toad and it returned 12 rows.

SELECT EMPLOYEEID,EMPLOYEENAME FROM EMPLOYEEINFO WHERE EMPLOYEEGRADE LIKE 'E%' AND ACTIVATIONSTATUS='Y' ORDER BY EMPLOYEENAME

EMPLOYEEID EMPLOYEENAME                                                         
---------- --------------------------
 6000 Mukesh Ambani                                                        
 6001 Tom                                                                  
 9000 anil kumar                                                           
 1000 asdad                                                                
 9002 ganesh                                                               
 9001 hari                                                                 
 6002 jack                                                                 
 7878 pete                                                                 
 9005 pravin                                                               
 1001 sam                                                                  
 2000 super                                                                
 9004 vengat                                                               
 
12 rows selected

By the coding, It's returning upto the record 9000-pravin and it is stored in the string EmployeeInfo, after that it just hanged and nothing is happening and the page is keep on loading.

Since I am having only limited records (12 records only), why it is not returning all the records..?

[A Kumar]

Can you paste the code where you are invoking this method..

[Micheal John]

I executed the below query in the toad and it returned 12 rows.

SELECT EMPLOYEEID,EMPLOYEENAME FROM EMPLOYEEINFO WHERE EMPLOYEEGRADE LIKE 'E%' AND ACTIVATIONSTATUS='Y' ORDER BY EMPLOYEENAME

EMPLOYEEID EMPLOYEENAME                                                         
---------- --------------------------
 6000 Mukesh Ambani                                                        
 6001 Tom                                                                  
 9000 anil kumar                                                           
 1000 asdad                                                                
 9002 ganesh                                                               
 9001 hari                                                                 
 6002 jack                                                                 
 7878 pete                                                                 
 9005 pravin                                                               
 1001 sam                                                                  
 2000 super                                                                
 9004 vengat                                                               
 
12 rows selected

By the coding, It's returning upto the record 9000-pravin and it is stored in the string EmployeeInfo, after that it just hanged and nothing is happening and the page is keep on loading.

Since I am having only limited records (12 records only), why it is not returning all the records..?

[A Kumar]

Hi micheal,

Can you paste the code ...
where you invoke...this method ..

getEmployeeIdAndNameDeveloper(conn,stmt,rs,projectName);

Regards

[Jeanne Boyarsky]

EmployeeInfo = EmployeeInfo+ ROW_SEPARATOR  +rs.getString("EMPLOYEEID") + COLUMN_SEPARATOR + rs.getString("EMPLOYEENAME");

Note that this overwrites the EmployeeInfo variable. This means you will only get the last value returned in the string. (This doesn't necessarily have to be the last row in the table.)

Using the following will append the records into the EmployeeInfo variable.
EmployeeInfo += EmployeeInfo+ ROW_SEPARATOR + ...

[Bill Johnston]

No Jeane that is not true.

EmployeeInfo = EmployeeInfo+ ...

is the same as EmployeeInfo += ...

~Bill

[Bill Johnston]

I wouldn't pass the Statement Object and the ResultSet Object in, only the Connection Object. I'd rather keep the former two local, build them in the method and then close them in the method ... I think that is your problem, you are not closing them. Don't forget to close them in the opposite order you build them.

[Jeanne Boyarsky]

Originally posted by Bill Johnston:
No Jeane that is not true.

Doh! I knew that. For some reason, I read it as "EmployeeInfo = ROW_SEPARATOR ..." rather than what was actually written.

[Micheal John]

    
 
import java.io.*;
import java.util.*;
import java.text.*;
import java.sql.*;
 
import javax.servlet.*;
import javax.servlet.http.*;
 

public class Emp extends HttpServlet{
 
public static finalString ROW_SEPARATOR= "^";
public static finalString COLUMN_SEPARATOR= "~";
 
public void service(HttpServletRequest request,HttpServletResponse response)throws ServletException,IOException{
response.setContentType("text/html");
PrintWriter out = response.getWriter();

Statement stmt = null;
ResultSet rs= null;
Connection conn= null;
RequestDispatcher rd= null;
HttpSession session= request.getSession(false);
 
String sql ="";
String mode= request.getParameter("mode");
String entry= request.getParameter("entry");

 
try{
 
if(session != null){
UserIdentity = (String)session.getAttribute("UserLoggedOn");
userId = (String)session.getAttribute("userId");
grade = (String)session.getAttribute("grade");
employeeName = (String)session.getAttribute("empName");
}

conn = ConnectionPool.getConnection();

if(conn!= null){
stmt   = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
if (stmt!=null){

if("Add".equalsIgnoreCase(mode) && "Prepare".equalsIgnoreCase(entry)){
request.setAttribute("projectInfo",LinkInfoHelper.getProjectIdAndName(conn,stmt,rs,(String)session.getAttribute("userId")));
request.setAttribute("empInfo",LinkInfoHelper.getEmployeeIdAndNameDeveloper(conn,stmt,rs,projectName));
request.setAttribute("moduleInfo","-Select-" + COLUMN_SEPARATOR + "-Select-");
request.setAttribute("formatted_current_date",formatted_current_date);
request.setAttribute("assignForFlag",assignForFlag);
rd = getServletContext().getRequestDispatcher("/LinkInfoAdd.jsp");
rd.forward(request,response);
}
}
}else{
request.setAttribute("PageToRedirect","<%=contextPath %>/HomePage.jsp");
request.setAttribute("Message","Error Occured!! Please Try After Some Time!!");
rd = getServletContext().getRequestDispatcher("/Failure.jsp");
rd.forward(request,response);
}
}catch(SQLException e){
e.printStackTrace();
closeResources(conn,stmt,rs);
}catch(Exception e){
e.printStackTrace();
closeResources(conn,stmt,rs);
}finally{
closeResources(conn,stmt,rs);
}
 
}
 

private void closeResources(final Connection conn, final Statement stmt, final ResultSet rs) {
   if (rs != null) {
   try {
   rs.close();
   }
   catch (SQLException ignore) {
ignore.printStackTrace();
   }
   }
   if (stmt != null) {
   try {
   stmt.close();
   }
   catch (SQLException ignore) {
ignore.printStackTrace();
   }
   }
   if (conn != null) {
   try {
   conn.close();
   }
   catch (SQLException ignore) {
 
 //ignore.printStackTrace();
   }
   }
}
 

 

}
 

[Micheal John]

I have posted the coding...any help....