• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

Scrollable ResultSet not working

 
Ranch Hand
Posts: 103
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
what exception are you getting ?

Shailesh
 
Navin Pillu
Ranch Hand
Posts: 103
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
can you paste your code here ?

Shailesh
 
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Most probably your driver does not support a scrollable ResultSet.
 
Shailesh Chandra
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 1683
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Note that some ORACLE drivers support a scrollable ResultSet.
 
Shailesh Chandra
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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



Agreed

Shailesh
 
Navin Pillu
Ranch Hand
Posts: 103
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 103
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 103
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
We can fix it! We just need some baling wire, some WD-40, a bit of duct tape and this tiny ad:
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic