aspose file tools*
The moose likes JDBC and the fly likes Geting an SQL Exception while passing the resultset to a bean Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Geting an SQL Exception while passing the resultset to a bean" Watch "Geting an SQL Exception while passing the resultset to a bean" New topic
Author

Geting an SQL Exception while passing the resultset to a bean

sri jaisi
Greenhorn

Joined: Sep 02, 2006
Posts: 23
Hi ,

I have a table with two columns :no and name and i have 4 records in it.If i try to save the data in a bean iam getting the below error.if i uncomment those lines its not throwing any exception.
java.sql.SQLException: [Oracle][ODBC]Invalid column number <1>

Here is the code:
package samples;
import java.sql.*;
public class Retrievedata
{
private static Connection con1=null;
private static Statement st=null;
private static ResultSet rs=null;

public static void main(String args[])
{
//public void setdata()
//{
try
{
dbconnect db1=new dbconnect();
Connection con1=db1.getdbConnection();
st=con1.createStatement();
rs=st.executeQuery("Select * from xmltext");
while(rs.next())
{
int no=rs.getInt(1);
String name=rs.getString(2);
System.out.println("number"+ no +"name" + name);
dbbean bean1=new dbbean();

bean1.setNumber(rs.getInt(1));
bean1.setName(rs.getString(2));
}
}catch(Exception e){System.out.println(e);}
try
{
if(st!=null)
st.close();
if(con1!=null)
con1.close();
}catch(SQLException e){System.out.println(e);}

}
}


Thanks in advance.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2510
    
  10

Try using

and, to make things easier for you in the future, don't use select *Regards, Jan


OCUP UML fundamental and ITIL foundation
youtube channel
sri jaisi
Greenhorn

Joined: Sep 02, 2006
Posts: 23
Thanks Jan.......It works now;
But just want to know why the earlier one failed;If incase if we have more no.of fields to be set into bean .this process will be a tedious one;Is there any other way to store the result set to a bean.....(thanks in advance)
What iam exactly trying to do is..saving the resultset in a bean.....and calling the bean in a servlet and forwarding it to jsp by setting the bean in the request scope;
Now i have only one record in the db;
But still iam getting the servlet as wellas jsp with
no 0
name null;


Here is the code:

import java.io.Serializable;

public class dbbean {
private int number;
private String name;

public void setNumber(int number)
{
this.number=number;
}

public void setName(String name)
{
this.name=name;
}

public int getNumber()
{
return number;
}

public String getName()
{
return name;
}
}

servlet:
public class Getdata extends HttpServlet{
public void doGet(HttpServletRequest req,HttpServletResponse res)
throws ServletException,IOException
{
res.setContentType("text/html");
PrintWriter out=res.getWriter();
dbbean db1=new dbbean();

//call setters to initialize bean
req.setAttribute("dbBean", db1);
int no=db1.getNumber();
String name=db1.getName();
out.println("<html");
out.println("<body");
out.println("<h1>Hi </h1>");
out.println("number:"+ no);
out.println("<br>");
out.println("Name:" + name);
out.println("</body");
out.println("</html>");
// ServletContext sc = getServletContext();
// RequestDispatcher rd = sc.getRequestDispatcher("/disp.jsp");
// rd.forward(req, res);
}

}


Jsp:
<html>
<head>
<title>Data from xml</title>
</head>
<body>
<%@ page import="java.sql.*" %>
<%@ page import="javax.servlet.*" %>
<%@ page import="javax.servlet.http.*" %>
<%@ page import="javax.servlet.jsp.*" %>
<%@ page language="java" import="samples.dbbean.*" %>

<jsp:useBean id="dbBean" type="samples.dbbean" scope="request"/>
Number : <input type="text" name="text1" value=<%=dbBean.getNumber() %> /><br>
Name: <input type="text" name="text2" value=<%=dbBean.getName() %> />
</

</body>
</html>


Thanks again
Craig Jackson
Ranch Hand

Joined: Mar 19, 2002
Posts: 405
I have a couple of suggestions:



This is fine:


But you should reference your data by using the following:


Also, you should insert code to close your resultSet when finished:


Also you are not saving your bean or passing it back to the calling program:


When you exit the while loop your bean is out of scope.
One suggestion is to save each individual bean in a collection and then return the collection to the calling program or your jsp page:

Once you return your bean to the servlet you can iterate through the collection and place the bean in the jsp page scope for display.

I hope this helps.
sri jaisi
Greenhorn

Joined: Sep 02, 2006
Posts: 23
Thanks Craig

I tried as per your comments.......and i have 5 records in the db but only one record is showing up....Here is the code.......as iam not sure of passing the reference back to jsp(how it can be done?) iam displaying it in the servlet itself and here is the code:

public void doGet(HttpServletRequest req,HttpServletResponse res)
throws ServletException,IOException
{
res.setContentType("text/html");
PrintWriter out=res.getWriter();
Retrievedata rd1=new Retrievedata();
Collection l=rd1.setdata();
Iterator it=l.iterator();
dbbean db1=new dbbean();
while(it.hasNext())
{
db1=(dbbean)it.next();
no=db1.getNumber();
name=db1.getName();
out.println("<html>" + "<body>");
out.println("no"+ no +"<br>"+ "name" + name);
out.println("</body>+</html>");

}

}
Craig Jackson
Ranch Hand

Joined: Mar 19, 2002
Posts: 405
Why don't you try and post your html tags outside of the while loop:



See if that make a difference.

Otherwise there may be an issue in your setdata method.
[ November 17, 2007: Message edited by: Craig Jackson ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Geting an SQL Exception while passing the resultset to a bean