使用Java ODBC创建连接会导致java.sql.SQLException：无效的游标类型异常

I am trying to create a Java program that accesses a ODBC datasource. Using the following code...

我正在尝试创建一个访问ODBC数据源的Java程序。使用以下代码......

Connection conn;

try {
    Driver d = (Driver)Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
    DriverManager.registerDriver(d);
    String URL = "jdbc:odbc:AR System ODBC Data Source";
    conn = DriverManager.getConnection(URL);
} catch (SQLException | InstantiationException | IllegalAccessException | ClassNotFoundException e) {
    Logger.error(this, e);
} 

Statement s = null;
ResultSet rs = null;

try {
    s = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    rs = s.executeQuery("select count(*) as rows from table");

    if (rs.next()) {
        System.out.print("Count of all rows is " + rs.getInt("rows"));
    }
} catch (SQLException e) { 
     e.printStackTrace();
} finally {
    DBUtils.safelyClose(s, rs);
}

...I get the following Exception:

...我得到以下例外:

java.sql.SQLException: The result set type is not supported.
   at sun.jdbc.odbc.JdbcOdbcStatement.initialize(Unknown Source)
   at sun.jdbc.odbc.JdbcOdbcConnection.createStatement(Unknown Source)
   at sun.jdbc.odbc.JdbcOdbcConnection.createStatement(Unknown Source)
   at com.csc.remedyarchiver.data.input.ODBCConnection.main(ODBCConnection.java:38)

Originally, when I was attempting to resolve this on my own, I was using the empty argument createStatement() call but this lead to the above exception (hence is why I used the TYPE_FORWARD_ONLY result set type but still the same result):

最初,当我试图自己解决这个问题时,我正在使用空参数createStatement()调用,但这会导致上述异常(因此我使用了TYPE_FORWARD_ONLY结果集类型,但仍然是相同的结果):

Is there anything else I can try with this or does this need a different approach?

还有什么我可以试试这个或者这需要一个不同的方法吗?

3 个解决方案

#1

From the Oracle Documentation for retrieving data sets:

从Oracle Documentation中检索数据集:

Note: Not all databases and JDBC drivers support all ResultSet types. The method DatabaseMetaData.supportsResultSetType returns true if the specified ResultSet type is supported and false otherwise.

注意:并非所有数据库和JDBC驱动程序都支持所有ResultSet类型。如果支持指定的ResultSet类型,则DatabaseMetaData.supportsResultSetType方法返回true,否则返回false。

https://docs.oracle.com/javase/tutorial/jdbc/basics/retrieving.html

So to start, you may want to check that your connection supports the result sets you are trying to use.

首先,您可能需要检查您的连接是否支持您尝试使用的结果集。

connection.getMetaData().supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY)
connection.getMetaData().supportsResultSetType(ResultSet.CONCUR_READ_ONLY)

The above methods both return true for my configuration. And for this reason the code you have posted works in my environment. I would bet that one of them (or both) will return false for you and my guess is that it is a problem with your database itself or the version of Oracle's JDBC driver you are using. You may want to ensure you are using the latest OJDBC driver which you can get from here:

以上方法对我的配置都返回true。因此,您发布的代码在我的环境中工作。我敢打赌,其中一个(或两个)将为您返回false,我的猜测是您的数据库本身或您正在使用的Oracle JDBC驱动程序版本存在问题。您可能希望确保使用最新的OJDBC驱动程序,您可以从此处获取:

http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

If you have verified that you are using the latest driver, I would verify the version of the Database you are using, and the result sets supported by it.

如果您已验证您使用的是最新的驱动程序,我将验证您正在使用的数据库的版本以及它支持的结果集。

#2

"select count(*) as rows from table" . Try first to replace the alias from "rows" to "Result" or something . Rows is one of the reserved keywords in Oracle . I don't think that select is returning any result besides an error ora 00923

“选择计数(*)作为表中的行”。首先尝试将别名从“行”替换为“结果”或其他内容。行是Oracle中保留的关键字之一。除了错误ora 00923之外,我认为select不会返回任何结果

#3

Would it be worth trying the following, firstly to confirm that it's unsupported, then again with differing ResultSet types to find one that is?

是否值得尝试以下内容,首先要确认它是不受支持的,然后再次使用不同的ResultSet类型来查找它?

System.out.println(conn.getMetaData().supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY));

#1