java.sql.SQLException: Exhausted Resultset [message #173858] |
Wed, 24 May 2006 22:10 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cuongtv
Messages: 34 Registered: August 2005 Location: Vi�t Nam
|
Member |
![thulinhmin](/forum/theme/orafaq/images/yahoo.png)
|
|
This is my function
public String doPayname2Payid(String vstrPayname)
{
PreparedStatement stmt = null;
String strSQL = "";
try
{
strSQL = " SELECT Paytypeid FROM Paytype WHERE Payname= ?";
stmt = getDBTransaction().createPreparedStatement(strSQL, 0);
stmt.setString(1, vstrPayname);
//vstrPayname = "Thưởng vào tháng lương"
ResultSet rs = stmt.executeQuery();
rs.next();
if (!rs.next())
{
System.err.println("Does not exist");
}
rs.close();
stmt.close();
return rs.getString(1);
}
catch(SQLException se)
{
se.getMessage();
return "-1";
}
catch(Exception e)
{
Debug.println(strSQL);
e.printStackTrace();
return "-1";
}
}
Result debug I attach. Although I executed:
strSQL = " SELECT Paytypeid FROM Paytype WHERE Payname = '"+vstrPayname"'" ; //vstrPayname = "Thưởng vào tháng lương"
I don't know why it raise error. The resultset is Null
-
Attachment: strSQL.sql
(Size: 5.42KB, Downloaded 1743 times)
[Updated on: Wed, 24 May 2006 22:46] Report message to a moderator
|
|
|
Re: java.sql.SQLException: Exhausted Resultset [message #174001 is a reply to message #173858] |
Thu, 25 May 2006 08:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kmmfoo
Messages: 38 Registered: June 2005 Location: massachusetts
|
Member |
|
|
The error is raised for a number of reasons...
First, you do two calls to rs.next() in a row. Once you call it the first time on an empty ResultSet, that's the end of the line. If you call it again, it throws the error you report. So instead of
ResultSet rs = stmt.executeQuery();
rs.next();
if (!rs.next())
{
System.err.println("Does not exist");
}
instead you should do this...
ResultSet rs = stmt.executeQuery();
if (rs.next())
{
System.err.println("Does exist");
}
else
{
System.err.println("Does not exist");
}
Next, look down later in your code... after you've already determined that the ResultSet is empty, and closed it, you then try to pick up a value from it...
rs.close();
stmt.close();
return rs.getString(1);
Instead, you need to make a whole bunch of changes
-- set up a default return value
-- only pick up an alternative return value if there's something in the ResultSet
-- move your close functions to the finally clause
Something more like
PreparedStatement stmt = null;
ResultSet rs = null;
String strSQL = "";
String result = "";
try
{
strSQL = " SELECT Paytypeid FROM Paytype WHERE Payname= ?";
stmt = getDBTransaction().createPreparedStatement(strSQL, 0);
stmt.setString(1, vstrPayname);
rs = stmt.executeQuery();
if (rs.next())
{
result = rs.getString(1);
}
}
catch(SQLException se)
{
se.getMessage();
result = "-1";
}
catch(Exception e)
{
e.printStackTrace();
result = "-1";
}
finally
{
if (rs != null) rs.close();
if (stmt != null) stmt.close();
}
return result;
HTH...
|
|
|
|
Re: java.sql.SQLException: Exhausted Resultset [message #174625 is a reply to message #173858] |
Tue, 30 May 2006 02:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
cuongtv
Messages: 34 Registered: August 2005 Location: Vi�t Nam
|
Member |
![thulinhmin](/forum/theme/orafaq/images/yahoo.png)
|
|
Oh, my program was down. Yesterday, it run well, but now I have error with createPrepareStatement:
stmt = getDBTransaction().createPreparedStatement(strSQL, 0);
racle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT dayname, to_char(exday,'dd-MM-yyyy'), halfday, descr, fromtime, totime, slottime FROM V_EXTRAWORKREPORT WHERE Period = 'JAN-2006' AND EMPLOYEEID IN (SELECT EMPLOYEEID from VMS_EMPLOYEE E WHERE ( E.FIRSTNAME LIKE '%Dinh%' OR E.LASTNAME LIKE '%Dinh%' OR E.FIRSTNAME||E.LASTNAME = 'Dinh'))// This SQL I run it well, and 2 weeks ago I run well, but now it raise error
at oracle.jbo.server.DBTransactionImpl.createPreparedStatement(DBTransactionImpl.java:3371)
at oracle.jbo.server.DBTransactionImpl2.createPreparedStatement(DBTransactionImpl2.java:424)
at com.vms.ApplicationModule.AppModuleImpl.executeEWReport(AppModuleImpl.java:1081)
at com.vms.view.ExtraWorkReport.EWtoExcel(ExtraWorkReport.java:459)
at com.vms.view.ExtraWorkReport.btnPrint_actionPerformed(ExtraWorkReport.java:390)
at com.vms.view.ExtraWorkReport.access$6000171(ExtraWorkReport.java:96)
at com.vms.view.ExtraWorkReport$3.actionPerformed(ExtraWorkReport.java:225)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1786)
at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(AbstractButton.java:1839)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:245)
at java.awt.Component.processMouseEvent(Component.java:5100)
at java.awt.Component.processEvent(Component.java:4897)
at java.awt.Container.processEvent(Container.java:1569)
at java.awt.Component.dispatchEventImpl(Component.java:3615)
at java.awt.Container.dispatchEventImpl(Container.java:1627)
at java.awt.Component.dispatchEvent(Component.java)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3198)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3128)
at java.awt.Container.dispatchEventImpl(Container.java:1613)
at java.awt.Window.dispatchEventImpl(Window.java)
at java.awt.Component.dispatchEvent(Component.java)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:458)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:201)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:151)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:137)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:100)
## Detail 0 ##
java.sql.SQLException: Invalid argument(s) in call: setFetchSize
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:124)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:161)
at oracle.jdbc.driver.OracleStatement.setPrefetchInternal(OracleStatement.java:2421)
at oracle.jdbc.driver.OracleStatement.setFetchSize(OracleStatement.java:3363)
at oracle.jbo.server.OracleSQLBuilderImpl.doStatementSetRowPrefetch(OracleSQLBuilderImpl.java:976)
at oracle.jbo.server.DBTransactionImpl.createPreparedStatement(DBTransactionImpl.java:3346)
at oracle.jbo.server.DBTransactionImpl2.createPreparedStatement(DBTransactionImpl2.java:424)
at com.vms.ApplicationModule.AppModuleImpl.executeEWReport(AppModuleImpl.java:1081)
at com.vms.view.ExtraWorkReport.EWtoExcel(ExtraWorkReport.java:459)
at com.vms.view.ExtraWorkReport.btnPrint_actionPerformed(ExtraWorkReport.java:390)
at com.vms.view.ExtraWorkReport.access$6000171(ExtraWorkReport.java:96)
at com.vms.view.ExtraWorkReport$3.actionPerformed(ExtraWorkReport.java:225)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1786)
at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(AbstractButton.java:1839)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:245)
at java.awt.Component.processMouseEvent(Component.java:5100)
at java.awt.Component.processEvent(Component.java:4897)
at java.awt.Container.processEvent(Container.java:1569)
at java.awt.Component.dispatchEventImpl(Component.java:3615)
at java.awt.Container.dispatchEventImpl(Container.java:1627)
at java.awt.Component.dispatchEvent(Component.java)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3198)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3128)
at java.awt.Container.dispatchEventImpl(Container.java:1613)
at java.awt.Window.dispatchEventImpl(Window.java)
at java.awt.Component.dispatchEvent(Component.java)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:458)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:201)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:151)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:137)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:100)
java.lang.NullPointerException
at com.vms.view.ExtraWorkReport.EWtoExcel(ExtraWorkReport.java:463)
at com.vms.view.ExtraWorkReport.btnPrint_actionPerformed(ExtraWorkReport.java:390)
at com.vms.view.ExtraWorkReport.access$6000171(ExtraWorkReport.java:96)
at com.vms.view.ExtraWorkReport$3.actionPerformed(ExtraWorkReport.java:225)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1786)
at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(AbstractButton.java:1839)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:245)
at java.awt.Component.processMouseEvent(Component.java:5100)
at java.awt.Component.processEvent(Component.java:4897)
at java.awt.Container.processEvent(Container.java:1569)
at java.awt.Component.dispatchEventImpl(Component.java:3615)
at java.awt.Container.dispatchEventImpl(Container.java:1627)
at java.awt.Component.dispatchEvent(Component.java)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3198)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3128)
at java.awt.Container.dispatchEventImpl(Container.java:1613)
at java.awt.Window.dispatchEventImpl(Window.java)
at java.awt.Component.dispatchEvent(Component.java)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:458)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:201)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:151)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:137)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:100)
Process exited.
[Updated on: Tue, 30 May 2006 02:26] Report message to a moderator
|
|
|