1 Reply Latest reply on Aug 20, 2001 1:34 PM by 807556

    JDBC:ODBC, Resultset with 0 rows throws SqlException

    807556
      For short, I have used forte for java version 3.0 ea to create an application which is manipulating data in an MS SQLServer database. Further I have installed JDK 1.3.1 and JRE 1.3.1, running on WINDOWS 2000 os.
      The DB-configuring is set to binary sort order.
      When my program is running a "rs = stmt.executQuery() then the situation is:
      1) If this query results in 1 or more rows in the resultset, everything seems to work fine.
      2) But if the resultset doesn't contains any rows, which is a quit legal result and actually a succes criteria in a certain point in my prg., an SQLException is thrown stating "Invalid object name + 'database tablename in uppercase'". Although the db-tables name is in mixedletters (see the code below).

      For testing purpose I have worked out a quit simpel prg. which reflects the real prg. I enclose it for examining purpose.

      I certaintly hope somebody is able to guide me to a solution.

      Thanks in advance.
      Kind regards
      Kuno Reck
      IBC Systems A/S

      TestCode:
      import java.sql.*;
      import javax.swing.*;
      //import javax.swing.table.*;

      class DBTest {
      private static final String driver = "sun.jdbc.odbc.JdbcOdbcDriver",
      dbUrl = "jdbc:odbc:KEKVRF",
      comUser = "tbs", password = "tbs";
      private static Connection conn;
      private static Statement stmt;
      private static ResultSet rs;
      // String queryDdi = "SELECT DISTINCT N.DdiNumber FROM DdiNumbers N left
      join DdiGroups G on N.DdiNumber = G.DdiNumber and N.ProviderId = G.ProviderId
      WHERE 1 = 1 AND N.DdiNumber BETWEEN 35248305 AND 35248309 ORDER BY N.DdiNumber";
      // String queryDdi = "SELECT DISTINCT N.DdiNumber FROM DdiNumbers N left
      join DdiGroups G on N.DdiNumber = G.DdiNumber and N.ProviderId = G.ProviderId
      WHERE 1 = 1 AND N.DdiNumber = 35248307 ORDER BY N.DdiNumber";
      String queryDdi = "SELECT DdiNumber FROM DdiNumbers WHERE DdiNumber =
      35548307";


      public static void main (String[] args){
      DBTest dbtest = new DBTest();
      }

      public DBTest() {
      try{
      Class.forName(driver);
      conn = DriverManager.getConnection(dbUrl, comUser, password);
      java.sql.DriverManager.setLogStream(java.lang.System.out);
      stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
      ResultSet.CONCUR_READ_ONLY);
      rs = stmt.executeQuery(queryDdi);

      if (rs.isBeforeFirst()){
      while (rs.next()) {
      // BEM?RK! KolonneV?rdierne skal l?ses ud i samme r?kkef?lge som de
      selectes i sql-statementet.
      System.out.println("DdiNumber = "+rs.getString("DdiNumber"));
      }

      }
      else{
      JOptionPane.showMessageDialog(null, "> Ddi-number does not exist
      < ", "Validation of input fields", JOptionPane.ERROR_MESSAGE);
      }
      }
      catch (SQLException e){
      JOptionPane.showMessageDialog(null, "No matching records found, " +
      e.getMessage(), "SQLException", JOptionPane.ERROR_MESSAGE);
      System.exit(0);
      }
      catch (ClassNotFoundException cnfe){
      JOptionPane.showMessageDialog(null, "ClassNotFoundException, " +
      cnfe.getMessage(), "ClassNotFoundException", JOptionPane.ERROR_MESSAGE);
      }
      }
      }


      LogStream:
      C:\jdk1.3.1\bin>java DBTest
      *Connection.createStatement
      Allocating Statement Handle (SQLAllocStmt), hDbc=148181752
      hStmt=148185176
      Setting statement option (SQLSetStmtOption), hStmt=148185176, fOption=6,
      vParam=3
      Setting statement option (SQLSetStmtOption), hStmt=148185176, fOption=7,
      vParam=1
      Registering Statement sun.jdbc.odbc.JdbcOdbcStatement@17d257
      *Statement.executeQuery (SELECT DdiNumber FROM DdiNumbers WHERE DdiNumber =
      3554830
      7)
      *Statement.execute (SELECT DdiNumber FROM DdiNumbers WHERE DdiNumber = 35548307)
      Free statement (SQLFreeStmt), hStmt=148185176, fOption=0
      Executing (SQLExecDirect), hStmt=148185176, szSqlStr=SELECT DdiNumber FROM
      DdiNumbe
      rs WHERE DdiNumber = 35548307
      Number of result columns (SQLNumResultCols), hStmt=148185176
      value=1
      Get statement option (SQLGetStmtOption), hStmt=148185176, fOption=6
      value=3
      SQLWarning: reason(Result set type has been changed.)
      Number of result columns (SQLNumResultCols), hStmt=148185176
      value=1
      Number of affected rows (SQLRowCount), hStmt=148185176
      value=0
      Fetching (SQLFetchScroll), hStmt=148185176
      End of result set (SQL_NO_DATA)
      Get statement option (SQLGetStmtOption), hStmt=148185176, fOption=14
      RETCODE = -1
      ERROR - Generating SQLException...
      SQLException: SQLState(24000) vendor code(0)
      java.sql.SQLException: [Microsoft][ODBC Driver Manager] Invalid cursor state
      at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6031)
      at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:6188)
      at sun.jdbc.odbc.JdbcOdbc.SQLGetStmtOption(JdbcOdbc.java:3773)
      at sun.jdbc.odbc.JdbcOdbcResultSet.calculateRowCount
      (JdbcOdbcResultSet.java
      :5982)
      at sun.jdbc.odbc.JdbcOdbcResultSet.initialize
      (JdbcOdbcResultSet.java:150)
      at sun.jdbc.odbc.JdbcOdbcStatement.getResultSet
      (JdbcOdbcStatement.java:420)

      at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery
      (JdbcOdbcStatement.java:250)

      at DBTest.<init>(DBTest.java:27)
      at DBTest.main(DBTest.java:18)
      Fetching (SQLFetchScroll), hStmt=148185176
      End of result set (SQL_NO_DATA)
      *Connection.createStatement
      Allocating Statement Handle (SQLAllocStmt), hDbc=148181752
      hStmt=148186208
      Setting statement option (SQLSetStmtOption), hStmt=148186208, fOption=6,
      vParam=0
      Setting statement option (SQLSetStmtOption), hStmt=148186208, fOption=7,
      vParam=1
      Registering Statement sun.jdbc.odbc.JdbcOdbcStatement@19c082
      *Statement.executeQuery (SELECT COUNT(*) FROM DDINUMBERS WHERE DDINUMBER =
      3554830
      7)
      *Statement.execute (SELECT COUNT(*) FROM DDINUMBERS WHERE DDINUMBER = 35548307)
      Free statement (SQLFreeStmt), hStmt=148186208, fOption=0
      Executing (SQLExecDirect), hStmt=148186208, szSqlStr=SELECT COUNT(*) FROM
      DDINUMBE
      RS WHERE DDINUMBER = 35548307
      RETCODE = -1
      ERROR - Generating SQLException...
      SQLException: SQLState(S0002) vendor code(208)
      java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid
      objec
      t name 'DDINUMBERS'.
      at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6031)
      at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:6188)
      at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:2494)
      at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:334)
      at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery
      (JdbcOdbcStatement.java:249)

      at sun.jdbc.odbc.JdbcOdbcResultSet.calculateRowCount
      (JdbcOdbcResultSet.java
      :6063)
      at sun.jdbc.odbc.JdbcOdbcResultSet.initialize
      (JdbcOdbcResultSet.java:150)
      at sun.jdbc.odbc.JdbcOdbcStatement.getResultSet
      (JdbcOdbcStatement.java:420)

      at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery
      (JdbcOdbcStatement.java:250)

      at DBTest.<init>(DBTest.java:27)
      at DBTest.main(DBTest.java:18)
      ResultSet.finalize sun.jdbc.odbc.JdbcOdbcResultSet@4ac268
      ResultSet.finalize sun.jdbc.odbc.JdbcOdbcResultSet@216869
      *ResultSet.close

      C:\jdk1.3.1\bin>
        • 1. Re: JDBC:ODBC, Resultset with 0 rows throws SqlException
          807556
          Hi,

          my suggestion: you can't use isBeforeFirst() on empty result set. Try following:
          rs = stmt.executeQuery(queryDdi);
          int count = 0;
          while (rs.next()) {
            count++;
            System.out.println("DdiNumber = "+rs.getString("DdiNumber"));
          }
          if (count == 0) {
           JOptionPane.showMessageDialog(null, "> Ddi-number does not exist < ", "Validation of input fields", JOptionPane.ERROR_MESSAGE);
          }
          Best Regards,
          Martin