String Index out of range Exception in jdbc programming

Intriguing picture Intriguing · Sep 19, 2013 · Viewed 7.1k times · Source

I have a database table consisting of 6 columns and 6 rows. I want to access all 6 column values of the 2nd row, but I'm unable to figure out why I'm getting this exception.

public void send()
{
int row=2;
    try
    {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection cn=DriverManager.getConnection("jdbc:odbc:DSN2");
    Statement st=cn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
    ResultSet rs=st.executeQuery("select *from Table1");
    rs.absolute(row);
    System.out.println(rs.getInt(1)+"\t"+rs.getInt(2)+"\t"+rs.getInt(3)+"\t"+rs.getInt(4)+"\t"+rs.getInt(5)+"\t"+rs.getInt(6));

    }

java.lang.StringIndexOutOfBoundsException: String index out of range: -1 at java.lang.String.substring(String.java:1958) at sun.jdbc.odbc.JdbcOdbcResultSet.reWordAsCountQuery(JdbcOdbcR‌​esultSet. java:6557) at sun.jdbc.odbc.JdbcOdbcResultSet.calculateRowCount(JdbcOdbcRe‌​sultSet.j ava:6350) at sun.jdbc.odbc.JdbcOdbcResultSet.initialize(JdbcOdbcResultSet‌​.java:154) at sun.jdbc.odbc.JdbcOdbcStatement.getResultSet(JdbcOdbcStateme‌​nt.java:4 23) at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStateme‌​nt.java:2 53)at Router.send(Router1.java:37)at Router1.main(Router1.java:54)

Answer

SnakeDoc picture SnakeDoc · Sep 19, 2013

Your SQL statement is not valid, likely causing the issue (assuming what you pasted in your OP is what you are really using)

select *from Table1

should be

select * from Table1

The * needs to have a space between it and from.

Also, it's worth mentioning that it's best practice with SQL to make your keywords all caps... so your statement re-written as such:

SELECT * FROM Table1

Also best practice to have your tables not begin with capital letters, or have caps in them at all... but this would have to be delt with when creating the database.

Also, just as a learning note:

Your current method will open a new database connection every time you run this method... which will cause significant performance bottlenecks/problems if this method is called often. Instead, it's much better to open the connection as your program starts up, then you can use that same connection over and over to run statements, then when your program is terminating, have it close the database connection. This removes a lot of network activity/overhead in communicating with your database and will yield much better performance and scalability of your code.

Also, it's worth mentioning, look into PreparedStatement. These will allow you to create your statement before you need them, and then just re-use the same statement over and over, which will yield even greater performance, as well as provide more security/safety if your queries will use user-input at some point (PreparedStatement automatically escapes input strings to avoid SQL-Injection attacks).

UPDATE --

Some code to try:

public void send() {

    try {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection cn=DriverManager.getConnection("jdbc:odbc:DSN2");
        Statement st=cn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        ResultSet rs=st.executeQuery("SELECT * FROM Table1");

        // debug section
        ResultSetMetaData rsmd = rs.getMetaData();
        System.out.println("DEBUG: Number of columns = " + rsmd.getColumnCount());
        // end debug

        while(rs.next()) {
            System.out.println(rs.getInt(1)+"\t"+rs.getInt(2)+"\t"+rs.getInt(3)+"\t"+rs.getInt(4)+"\t"+rs.getInt(5)+"\t"+rs.getInt(6));
        }

    } catch(Exception e) {
        e.printStackTrace();
    }
}

This will print a line telling us how many columns are in your ResultSet