[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

Strange error related (I think) to database scrolling

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
velocityidp
Asked:
velocityidp
  • 5
  • 4
  • 3
  • +1
1 Solution
 
Tommy BraasCommented:
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
 
velocityidpAuthor Commented:
OK... What do you suggest as an alternative?
0
 
91mustangCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
girionisCommented:
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
 
velocityidpAuthor Commented:
I'm using an MS Access database... haven't found a free one on there.
0
 
Tommy BraasCommented:
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
 
velocityidpAuthor Commented:
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
 
girionisCommented:
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
 
Tommy BraasCommented:
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
 
velocityidpAuthor Commented:
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
 
girionisCommented:
Try a different drivre and see if that helps: http://servlet.java.sun.com/products/jdbc/drivers
0
 
velocityidpAuthor Commented:
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
 
girionisCommented:
I am afraid the answer is: there isn't any.

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

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now