error when returning a ResultSet

B

Biebel

Hello,

I've created a class which should help me handling SQL request to the
database : SQLHelp.
I use this class as a base class for all the classes that need to get or put
data in db.

In the getFuntion I return a ResultSet. But when I do that I've got an error
message (see below).
Can anybody help me with that? I don't see what I'm doing wrong.
Can I pass a recordset as an argument??

Thanks
regards
Bart




import java.sql.Connection;
import javax.naming.Context;
import javax.naming.InitialContext;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.Vector;


public class SQLHelp
{
public Connection getConnection(String database) throws Exception
{
try
{
Context ctx = new InitialContext();
javax.sql.DataSource ds = (javax.sql.DataSource) ctx.lookup("java:/" +
database);
return ds.getConnection();
}
catch (Exception e)
{
System.err.println("Could not locate datasource! Reason:");
e.printStackTrace();
throw e;
}
}

public boolean updateFunction(String sql)
{
ResultSet rs = null;
PreparedStatement pstmt = null;
Connection conn = null;
boolean returnValue = false;
try
{
conn = getConnection("MSSQLDS");
pstmt = conn.prepareStatement(sql);
int count = pstmt.executeUpdate();
pstmt.close();
returnValue = count>0;
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
try { if (pstmt != null) pstmt.close(); }
catch (Exception e) {}
try { if (conn != null) conn.close(); }
catch (Exception e) {}
}
return returnValue;
}

public ResultSet getFunction(String sql)
{
ResultSet rs = null;
PreparedStatement pstmt = null;
Connection conn = null;
try
{
conn = getConnection("MSSQLDS");
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next())
System.out.println(rs.getString(1));
pstmt.close();
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
try { if (pstmt != null) pstmt.close(); }
catch (Exception e) {}
try { if (conn != null) conn.close(); }
catch (Exception e) {}
}
return rs;
}
}




import javax.ejb.*;
import java.util.Vector;
import java.sql.ResultSet;
import yTools.SQLHelp;

public class yComboDataBean extends SQLHelp implements SessionBean
{
SessionContext sessionContext;
public void ejbCreate() throws CreateException { }
public void ejbRemove() {}
public void ejbActivate() {}
public void ejbPassivate() {}

public void setSessionContext(SessionContext sessionContext)
{
this.sessionContext = sessionContext;
}

public Vector getCountry()
{
return getData("SELECT country,id FROM list_country_nationality ORDER BY
country");
}

private Vector getData(String sql)
{
Vector v = new Vector();
try
{
ResultSet rs = getFunction(sql);
return v;
}
catch (Exception e)
{
e.printStackTrace();
}
return null;
}
}






09:10:39,131 ERROR [STDERR] java.sql.SQLException: Invalid state: ResultSet
closed.
09:10:39,151 ERROR [STDERR] at
net.sourceforge.jtds.jdbc.TdsResultSet.checkClosed(Unknown Source)
09:10:39,151 ERROR [STDERR] at
net.sourceforge.jtds.jdbc.TdsResultSet.next(Unknown Source)
09:10:39,151 ERROR [STDERR] at
yToolsBeans.yComboDataBean.getData(yComboDataBean.java:166)
09:10:39,151 ERROR [STDERR] at
yToolsBeans.yComboDataBean.getCountry(yComboDataBean.java:27)
09:10:39,151 ERROR [STDERR] at
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
09:10:39,151 ERROR [STDERR] at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
)
09:10:39,151 ERROR [STDERR] at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
..java:25)
09:10:39,161 ERROR [STDERR] at
java.lang.reflect.Method.invoke(Method.java:324)
09:10:39,161 ERROR [STDERR] at
org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(Stateles
sSessionContainer.java:629)
09:10:39,161 ERROR [STDERR] at
org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(Cach
edConnectionInterceptor.java:186)
09:10:39,161 ERROR [STDERR] at
org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSe
ssionInstanceInterceptor.java:72)
09:10:39,161 ERROR [STDERR] at
org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor
..java:84)
09:10:39,161 ERROR [STDERR] at
org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.
java:243)
09:10:39,161 ERROR [STDERR] at
org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:104)
09:10:39,161 ERROR [STDERR] at
org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:11
7)
09:10:39,161 ERROR [STDERR] at
org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:191)
09:10:39,161 ERROR [STDERR] at
org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinde
rInterceptor.java:122)
09:10:39,161 ERROR [STDERR] at
org.jboss.ejb.StatelessSessionContainer.internalInvoke(StatelessSessionConta
iner.java:322)
09:10:39,161 ERROR [STDERR] at
org.jboss.ejb.Container.invoke(Container.java:674)
09:10:39,161 ERROR [STDERR] at
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
09:10:39,161 ERROR [STDERR] at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
)
09:10:39,161 ERROR [STDERR] at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
..java:25)
09:10:39,161 ERROR [STDERR] at
java.lang.reflect.Method.invoke(Method.java:324)
09:10:39,171 ERROR [STDERR] at
org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispat
cher.java:284)
09:10:39,171 ERROR [STDERR] at
org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
09:10:39,171 ERROR [STDERR] at
org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:359)
09:10:39,171 ERROR [STDERR] at
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
09:10:39,171 ERROR [STDERR] at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
)
09:10:39,171 ERROR [STDERR] at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
..java:25)
09:10:39,171 ERROR [STDERR] at
java.lang.reflect.Method.invoke(Method.java:324)
09:10:39,171 ERROR [STDERR] at
sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:261)
09:10:39,171 ERROR [STDERR] at
sun.rmi.transport.Transport$1.run(Transport.java:148)
09:10:39,171 ERROR [STDERR] at
java.security.AccessController.doPrivileged(Native Method)
09:10:39,171 ERROR [STDERR] at
sun.rmi.transport.Transport.serviceCall(Transport.java:144)
09:10:39,171 ERROR [STDERR] at
sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:460)
09:10:39,171 ERROR [STDERR] at
sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:7
01)
09:10:39,171 ERROR [STDERR] at java.lang.Thread.run(Thread.java:536)
 
L

Lee Fesperman

Biebel said:
Hello,

I've created a class which should help me handling SQL request to the
database : SQLHelp.
I use this class as a base class for all the classes that need to get or put
data in db.

In the getFuntion I return a ResultSet. But when I do that I've got an error
message (see below).
Can anybody help me with that? I don't see what I'm doing wrong.
Can I pass a recordset as an argument??
Click to expand...

The code you posted will not give that exception. There is no call to next() in your
getData() method. Next time, cut and paste the actual code.

That said, the problem is that your getFunction() method closes the statement (pstmt)
which automatically closes the result set. Note: getFunction() also exhausts (reads all
the rows) in the result set before it returns...
public ResultSet getFunction(String sql)
{
ResultSet rs = null;
PreparedStatement pstmt = null;
Connection conn = null;
try
{
conn = getConnection("MSSQLDS");
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next())
System.out.println(rs.getString(1));
pstmt.close();
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
try { if (pstmt != null) pstmt.close(); }
catch (Exception e) {}
try { if (conn != null) conn.close(); }
catch (Exception e) {}
}
return rs;
}
}
Click to expand...

getData() receives a closed result set.
private Vector getData(String sql)
{
Vector v = new Vector();
try
{
ResultSet rs = getFunction(sql);
return v;
}
catch (Exception e)
{
e.printStackTrace();
}
return null;
}
}
09:10:39,131 ERROR [STDERR] java.sql.SQLException: Invalid state: ResultSet
closed.
09:10:39,151 ERROR [STDERR] at
net.sourceforge.jtds.jdbc.TdsResultSet.checkClosed(Unknown Source)
09:10:39,151 ERROR [STDERR] at
net.sourceforge.jtds.jdbc.TdsResultSet.next(Unknown Source)
09:10:39,151 ERROR [STDERR] at
yToolsBeans.yComboDataBean.getData(yComboDataBean.java:166)
Click to expand...

See the reference to next()? It's not in the the getData() you posted.

Finally, you should post JDBC questions to comp.lang.java.databases.
 
B

biebel

Sorry,
But I've madea little mistake in the code when I posted it.

Here's the correct code:


import java.sql.Connection;
import javax.naming.Context;
import javax.naming.InitialContext;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.Vector;


public class SQLHelp
{
public Connection getConnection(String database) throws Exception
{
try
{
Context ctx = new InitialContext();
javax.sql.DataSource ds = (javax.sql.DataSource) ctx.lookup("java:/" +
database);
return ds.getConnection();
}
catch (Exception e)
{
System.err.println("Could not locate datasource! Reason:");
e.printStackTrace();
throw e;
}
}

public boolean updateFunction(String sql)
{
ResultSet rs = null;
PreparedStatement pstmt = null;
Connection conn = null;
boolean returnValue = false;
try
{
conn = getConnection("MSSQLDS");
pstmt = conn.prepareStatement(sql);
int count = pstmt.executeUpdate();
pstmt.close();
returnValue = count>0;
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
try { if (pstmt != null) pstmt.close(); }
catch (Exception e) {}
try { if (conn != null) conn.close(); }
catch (Exception e) {}
}
return returnValue;
}

public ResultSet getFunction(String sql)
{
ResultSet rs = null;
PreparedStatement pstmt = null;
Connection conn = null;
try
{
conn = getConnection("MSSQLDS");
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
pstmt.close();
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
try { if (pstmt != null) pstmt.close(); }
catch (Exception e) {}
try { if (conn != null) conn.close(); }
catch (Exception e) {}
}
return rs;
}
}




import javax.ejb.*;
import java.util.Vector;
import java.sql.ResultSet;
import yTools.SQLHelp;

public class yComboDataBean extends SQLHelp implements SessionBean
{
SessionContext sessionContext;
public void ejbCreate() throws CreateException { }
public void ejbRemove() {}
public void ejbActivate() {}
public void ejbPassivate() {}

public void setSessionContext(SessionContext sessionContext)
{
this.sessionContext = sessionContext;
}

public Vector getCountry()
{
return getData("SELECT country,id FROM list_country_nationality ORDER BY
country");
}

private Vector getData(String sql)
{
Vector v = new Vector();
try
{
ResultSet rs = getFunction(sql);
while (rs.next())
v.addElement(new yComboDataClass(rs));
return v;
}
catch (Exception e)
{
e.printStackTrace();
}
return null;
}
}






09:10:39,131 ERROR [STDERR] java.sql.SQLException: Invalid state: ResultSet
closed.
09:10:39,151 ERROR [STDERR] at
net.sourceforge.jtds.jdbc.TdsResultSet.checkClosed(Unknown Source)
09:10:39,151 ERROR [STDERR] at
net.sourceforge.jtds.jdbc.TdsResultSet.next(Unknown Source)
09:10:39,151 ERROR [STDERR] at
yToolsBeans.yComboDataBean.getData(yComboDataBean.java:166)
09:10:39,151 ERROR [STDERR] at
yToolsBeans.yComboDataBean.getCountry(yComboDataBean.java:27)
09:10:39,151 ERROR [STDERR] at
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
09:10:39,151 ERROR [STDERR] at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
)
09:10:39,151 ERROR [STDERR] at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
..java:25)
09:10:39,161 ERROR [STDERR] at
java.lang.reflect.Method.invoke(Method.java:324)
09:10:39,161 ERROR [STDERR] at
org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(Stateles
sSessionContainer.java:629)
09:10:39,161 ERROR [STDERR] at
org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(Cach
edConnectionInterceptor.java:186)
09:10:39,161 ERROR [STDERR] at
org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSe
ssionInstanceInterceptor.java:72)
09:10:39,161 ERROR [STDERR] at
org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor
..java:84)
09:10:39,161 ERROR [STDERR] at
org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.
java:243)
09:10:39,161 ERROR [STDERR] at
org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:104)
09:10:39,161 ERROR [STDERR] at
org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:11
7)
09:10:39,161 ERROR [STDERR] at
org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:191)
09:10:39,161 ERROR [STDERR] at
org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinde
rInterceptor.java:122)
09:10:39,161 ERROR [STDERR] at
org.jboss.ejb.StatelessSessionContainer.internalInvoke(StatelessSessionConta
iner.java:322)
09:10:39,161 ERROR [STDERR] at
org.jboss.ejb.Container.invoke(Container.java:674)
09:10:39,161 ERROR [STDERR] at
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
09:10:39,161 ERROR [STDERR] at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
)
09:10:39,161 ERROR [STDERR] at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
..java:25)
09:10:39,161 ERROR [STDERR] at
java.lang.reflect.Method.invoke(Method.java:324)
09:10:39,171 ERROR [STDERR] at
org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispat
cher.java:284)
09:10:39,171 ERROR [STDERR] at
org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
09:10:39,171 ERROR [STDERR] at
org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:359)
09:10:39,171 ERROR [STDERR] at
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
09:10:39,171 ERROR [STDERR] at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39
)
09:10:39,171 ERROR [STDERR] at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
..java:25)
09:10:39,171 ERROR [STDERR] at
java.lang.reflect.Method.invoke(Method.java:324)
09:10:39,171 ERROR [STDERR] at
sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:261)
09:10:39,171 ERROR [STDERR] at
sun.rmi.transport.Transport$1.run(Transport.java:148)
09:10:39,171 ERROR [STDERR] at
java.security.AccessController.doPrivileged(Native Method)
09:10:39,171 ERROR [STDERR] at
sun.rmi.transport.Transport.serviceCall(Transport.java:144)
09:10:39,171 ERROR [STDERR] at
sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:460)
09:10:39,171 ERROR [STDERR] at
sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:7
01)
09:10:39,171 ERROR [STDERR] at java.lang.Thread.run(Thread.java:536)
 
S

Sudsy

biebel said:
Sorry,
But I've madea little mistake in the code when I posted it.

Here's the correct code:
Click to expand...

Even with the updated code, it's destined to fail. Go back and
read what Lee said. Here's what the javadocs have to say about
Statement#close:
Note: A Statement object is automatically closed when it is
garbage collected. When a Statement object is closed, its
current ResultSet object, if one exists, is also closed.
Click to expand...
+++++++++ ++++++

You should be using a different mechanism for returning the
results to the invoker.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Returning A ResultSet 9
Custom Minecraft launcher client error; I think regarding java 0
Error with server 3
Transaction Error due to whether JBoss or SQL Server Driver 0
Repeated SQL resultset into a method 1
Connected SQLite to my java program but information are not submitted 2
Statement and Resultset 23
javax.faces.FacesException: com.ibm.websphere.ce.cm.ObjectClosedException: DSRA9110E: ResultSet is c 0

Members online

Total: 43 (members: 1, guests: 42)
Robots: 207

Forum statistics

Threads
473,766
Messages
2,569,569
Members
45,042
Latest member
icassiem

Latest Threads

Top