aspose file tools*
The moose likes JDBC and the fly likes Scrollable ResultSet not working Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Scrollable ResultSet not working" Watch "Scrollable ResultSet not working" New topic
Author

Scrollable ResultSet not working

Navin Pillu
Ranch Hand

Joined: Apr 19, 2005
Posts: 103
Hi All

I'm trying to use scrollable resultset. File is getting compiled but runtime throwing an exception. if we remove ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE from createStatement() method program woeks fine

Can anybody tell me why it is doing like this.

Server Tomcat 4.1.3
jdk1.4
classes12.zip

Thanks in advance
[ June 28, 2005: Message edited by: Navin Pillu ]
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

what exception are you getting ?

Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Navin Pillu
Ranch Hand

Joined: Apr 19, 2005
Posts: 103
Hi Shailesh
Thanks for respond

i'm writing statement as



And Tomcat throwing exception as
-----------------------------------------------------
javax.servlet.ServletException: Invalid operation for forward only resultset : first
at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:498)
at org.apache.jsp.index_jsp._jspService(index_jsp.java:78)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:92)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:162)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:240)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:187)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:200)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:146)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:209)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:596)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:433)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:948)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:144)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:596)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:433)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:948)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2358)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:133)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:596)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:118)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:594)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:116)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:594)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:433)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:948)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:127)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:596)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:433)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:948)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:152)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:536)

root cause

java.sql.SQLException: Invalid operation for forward only resultset : first
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:189)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:231)
at oracle.jdbc.driver.BaseResultSet.first(BaseResultSet.java:85)
at org.apache.jsp.index_jsp._jspService(index_jsp.java:69)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:92)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:162)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:240)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:187)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:809)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:200)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:146)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:209)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:596)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:433)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:948)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:144)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:596)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:433)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:948)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2358)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:133)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:596)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:118)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:594)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:116)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:594)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:433)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:948)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:127)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:596)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:433)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:948)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:152)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:536)
-----------------------------------------------------

Please let me know what is happing here

Thanks in Advance
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

can you paste your code here ?

Shailesh
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
Most probably your driver does not support a scrollable ResultSet.


SCJP 1.4, SCWCD 1.3, SCBCD 1.3
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Roger Chung-Wee:
Most probably your driver does not support a scrollable ResultSet.


Initially i thoght same,but since poster of this message is using classes12.zip which is more likely driver of oracle. and oracle supports scrollable Resultset.

Shailesh
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
Note that some ORACLE drivers support a scrollable ResultSet.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Roger Chung-Wee:
Note that some ORACLE drivers support a scrollable ResultSet.


Agreed

Shailesh
Navin Pillu
Ranch Hand

Joined: Apr 19, 2005
Posts: 103
Hi Everybody

Here i'm posting code & using oracle thin driver for creating connection.



Thanks all
[ June 28, 2005: Message edited by: Navin Pillu ]
Navin Pillu
Ranch Hand

Joined: Apr 19, 2005
Posts: 103
Hi All

One point i noted is if we close the statement {stmt.close()} then only it is throwing such exception otherwise ResultSet is working as scrollable. Now question is that why it is behaving like this? once i returned a ResultSet object is should be scrollable. Please if anybody knows, let me know about this nature of Statement & resultSet. And is it necessary to close the statement?

Thanks in advance
[ June 28, 2005: Message edited by: Navin Pillu ]
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Wow,
So much incorrect information in one discussion -- and by several people. And all caused by lack of details in the original question.

All the answers are available in the "JDBC Developer's Guide and Reference", which is part of the Oracle documentation and available from:

http://tahiti.oracle.com

The documentation states that the Oracle JDBC driver cannot create a scrollable "ResultSet" for any SQL query. The documentation also states that for such queries, the JDBC driver will "silently" convert the "ResultSet"s to "forward only". Hence, Navin, the problem is with your [SQL] query -- which you didn't provide.

Good Luck,
Avi.

[ June 28, 2005: Message edited by: Avi Abrami ]
[ June 28, 2005: Message edited by: Avi Abrami ]
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Avi Abrami:
the Oracle JDBC driver cannot create a scrollable "ResultSet" for any SQL query.


Avi,

Thanks for the update but as of Oracle 9.2 release it's driver has scrollable result set implementation but still there are some limitation

As per Oracle JDBC Drivers release 9.2.0

The scrollable result set implementation has the following
limitation:

- setFetchDirection() on ScrollableResultSet does not do anything.
- refreshRow() on ScrollableResultSet does not support all combinations of sensitivity and concurrency. The following table depicts the supported combinations




you can find readme here


Shailesh
[ June 29, 2005: Message edited by: Shailesh Chandra ]
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Navin Pillu:
if we close the statement {stmt.close()} then only it is throwing such exception otherwise ResultSet is working as scrollable.


I think you got the answer of your problem ? Did you see java-docs's Statement.close() method, it has answer for you. Java docs says for statement.close() method:


Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. It is generally good practice to release resources as soon as you are finished with them to avoid tying up database resources.
Calling the method close on a Statement object that is already closed has no effect.

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.



so once you close the statement you can not use resultset associated to it.
Also in your method you are closing connection which will behave same and will end up in closing statement and resultset both if you close it, I am not sure why you are not getting this error





Now question is that why it is behaving like this? once i returned a ResultSet object is should be scrollable.


you sould process the resultset inside the method and then return an Arraylist of VO or DTO, you can also opt for CachedRowSet which will work even after closing objects

Shailesh
[ June 29, 2005: Message edited by: Shailesh Chandra ]
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

The first line of the stack trace that Navin posted holds the key to his problem:

javax.servlet.ServletException: Invalid operation for forward only resultset: first

The "first()" method (in "java.sql.ResultSet") is failing because the result set is not scrollable.

Even though Navin has requested a scrollable result set, the Oracle JDBC driver has not been able to fulfill his request and has created a non-scrollable result set. The reason why the Oracle JDBC driver does this is explained in the section entitled "Result Set Limitations and Downgrade Rules" in chapter 13 -- "Result Set Enhancements" -- of the "Oracle9i JDBC Developer's Guide and Reference, Release 2 (9.2)" (Part Number A96654-01).

Try the following URL:

http://tinyurl.com/7s6xb

Hope this clarifies things.

Good Luck,
Avi.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

It could be !! but I am not sure rather confused how same thing worked when Navin didn't close preparedstatement

Shailesh
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Shailesh,
Navin has presented his problem in a confusing manner. Therefore, I am assuming that the stack trace he posted is the most relevant information regarding his problem. Hence I mainly only related to the stack trace he posted.

As you noticed, several of the things that Navin said, did not make sense -- not to mention the code he posted. How can a method return a "ResultSet" when the same method closes the associated "Statement"?

But providing incorrect information when answering a question, really irritates me. Saying that some Oracle [JDBC] drivers support scrollable result sets, implies that there are also some that do not -- and that is simply not true (as far as I know). Can you show me an Oracle JDBC driver that does not support scrollable result sets?

Unfortunately, people reading this discussion -- and relying on its information, which they assume to be correct -- may become very confused when they see behaviour that contradicts what they read in these forums.

Please try to make sure that the information you provide is accurate and correct -- or at least mention the fact that you are not sure if the information you provide is correct.

Good Luck,
Avi.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Avi,

I agree that problem is represented in unclear way.

As far as point of incorrect information I alwasy validate information before posting because I also believe pleple reading the discussion will assume information as true.
As far as oracle driver that does not support scrollable result sets, whenever I agreed to this fact which was based on scrollable result set was introduced in JDBC 2.0 and any earlier JDBC driver would not support
this feature.
I have taken you point as well and I will mention "that I am sure or not" whenever I will find myself about any fact


thanks
Shailesh
[ June 30, 2005: Message edited by: Shailesh Chandra ]
Navin Pillu
Ranch Hand

Joined: Apr 19, 2005
Posts: 103
Hi All

I'm extreamly sorry for providing insufficient information but not incorrect. I'm using Bitmechanic connection pool. So conn.close() method will return connection to pool.

And thanks all for giving useful information. From next time i'll provide complete information.

Thanks once again
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Scrollable ResultSet not working