Solved

Strange error related (I think) to database scrolling

Posted on 2004-08-09
14
302 Views
Last Modified: 2009-07-29
OK, I am creating an applet that accesses an MS Access database. Everything has worked perfectly, until I started trying to do scrolling operations with my ResultSet. Any time I try to reset the iterator (resultset.first(), for example), I get an error stating

"java.sql.SQLException: Result set type is TYPE_FORWARD_ONLY"

OK, so I attempted to solve the problem by setting the connection to support scrolling...

"this.statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);"

I also ran so *.supportsResultSetType(...) operations and determined that the metadata supported scrollable resultsets.

OK, so now I try to execute a query using  "this.statement.executeQuery(queryText)" and I get the error:

java.lang.StringIndexOutOfBoundsException: String index out of range: -1
        at java.lang.String.substring(String.java:1444)
        at sun.jdbc.odbc.JdbcOdbcResultSet.reWordAsCountQuery(JdbcOdbcResultSet.java:6562)
        at sun.jdbc.odbc.JdbcOdbcResultSet.calculateRowCount(JdbcOdbcResultSet.java:6355)
        at sun.jdbc.odbc.JdbcOdbcResultSet.initialize(JdbcOdbcResultSet.java:157)
        at sun.jdbc.odbc.JdbcOdbcStatement.getResultSet(JdbcOdbcStatement.java:424)
        at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:254)
        at Singh_jSQL.queryDB(Singh_jSQL.java:74)

I then tried reverting back to my original code, which used a default connection, (this.statement = connection.createStatement()) I ran the code again, and didn't get this wierd "StringIndex" error. I just got the same, expected, FORWARD_ONLY error.

What the heck? This makes no sense. Questions welcome. I'm guessing this one will be tough to solve...

NFS
0
Comment
Question by:velocityidp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 14

Accepted Solution

by:
Tommy Braas earned 500 total points
ID: 11758089
IFAIK there is no support for scroll insensitive cursors in the JDBC-ODBC bridge. The string index problem you're getting when changing to that type of resultset is merely a side effect.
0
 

Author Comment

by:velocityidp
ID: 11758160
OK... What do you suggest as an alternative?
0
 
LVL 4

Expert Comment

by:91mustang
ID: 11758596
Hello, I have used scroll insensitive cursors with the JDBC-ODBC bridge. Try this code: (the only difference is that I use CONCUR_UPDATABLE)


Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //load the Jdbc driver
             Connection con1 = DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=C:/data/scrollable","dba","sql"); //get a connection
             Statement stmt = con1.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:girionis
ID: 11760006
Why don't you use a type 4 driver? There are several free ones for almost any db: http://servlet.java.sun.com/products/jdbc/drivers
0
 

Author Comment

by:velocityidp
ID: 11763212
I'm using an MS Access database... haven't found a free one on there.
0
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 11764450
Will you be deploying your application ontop of M$ Access? If so, make sure you have the latest ODBC drivers for Access. If it has worked for 91Mustang, it's probably because he was using a newer version of ODBC. Another question, which version of Java are you using?
0
 

Author Comment

by:velocityidp
ID: 11764598
Java version is 1.4.2

ehh... not sure how to get the JDBC version... I thought the latest version was packaged with this. I only downloaded the SDK about 2 months ago.
0
 
LVL 35

Expert Comment

by:girionis
ID: 11765044
Well MS Access default driver does not support all JDBC operations. WHy don't you try to sue another db? MySQL is completely free and very powerful.
0
 
LVL 14

Expert Comment

by:Tommy Braas
ID: 11765374
I think the best thing to do in light of your problems and apparent need to support scrollable result sets (why do you need that?), would be to go with another database. Apart from mySQL, there is also PostgreSQL which supports a larger set of the SQL standard. PostgreSQL is also free.
0
 

Author Comment

by:velocityidp
ID: 11852818
I need to use MS Access since that is the database we've committed to. I just need to find a way to get a scrollable result!
0
 
LVL 35

Expert Comment

by:girionis
ID: 11853275
Try a different drivre and see if that helps: http://servlet.java.sun.com/products/jdbc/drivers
0
 

Author Comment

by:velocityidp
ID: 11874982
Hmm... looking for a Type 4 driver that can handle MS Access (not MS SQL Server) interactions... Well basically anything that will support scrollable resultsets w/ MS Access.

Any ideas? Basically, if there's a free one out there and you know of it, you're gonna get the 500 points.

Thanks!

NFS
0
 
LVL 35

Expert Comment

by:girionis
ID: 11878386
I am afraid the answer is: there isn't any.

There are type 2 and 3 though that support scrolling.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
hibernate example issues from command prompt 10 85
jboss wildfly 10.1 10 632
Java array sort 10 64
Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given PHP 5 44
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
Suggested Courses

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question