autoGeneratedKeys

[jessica nesler]

I'm back again.

I am performing a simple SELECT statement from my table (named 2856, this is a company naming convention, not mine):

SELECT * FROM [2856]

The table has an index created using two of its fields. I'm running on Java 1.8.0_112 with ucanaccess4.0.0 and hsqldb 2.3.4.

Attempting to run the statement however produces the following error:
Cannot execute query! net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.0 Invalid argument in JDBC call: autoGeneratedKeys

I cannot find anyone else who has encountered this issue and as far as I can tell.

Any help would be appreciated.

Thank you!

[Marco Amadei]

I can't reproduce the issue naming a table 2856, and the exception is very unlikely caused by that select statement. May you post the complete stack trace?

[jessica nesler]

Sure thing! Here it is!

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.0 Invalid argument in JDBC call: autoGeneratedKeys
    at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:545)
    at ads.MatchReportQueries.prevRO(MatchReportQueries.java:490)
    at ads.MatchControl.readControlFile(MatchControl.java:447)
    at ads.MatchControl.<init>(MatchControl.java:124)
    at ads.MatchControl.main(MatchControl.java:472)
Caused by: java.sql.SQLException: Invalid argument in JDBC call: autoGeneratedKeys
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.invalidArgument(Unknown Source)
    at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
    at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:542)
    ... 4 more
Caused by: org.hsqldb.HsqlException: Invalid argument in JDBC call: autoGeneratedKeys
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    ... 8 more

[jessica nesler]

I was poking around in the HSQLDB library to see if that could shed some light on the issues.

I found that when I executed my SQL via a preparedstatement with a specification on the ResultSet to make it scrollable the exception occurred:

String roInfo = "SELECT * FROM ["+baseTable+"]";
PreparedStatement stmtsqlRO = connection.prepareStatement(roInfo,ResultSet.TYPE_SCROLL_SENSITIVE);
ResultSet roRS = stmtsqlRO.executeQuery();

However if I removed the "ResultSet.TYPE_SCROLL_SENSITIVE" information then I could execute the query without an issue. So I feel that this is an issue I need to explore with the HSQLDB end, because I need to be able to scroll through the ResultSet and it seems as though something in their backend is trying to interpret the ResultSet information as the auto generated keys.

Does this sound right?

[Marco Amadei]

See the javadoc https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#prepareStatement(java.lang.String,%20int), It may be useful.

[Marco Amadei]

prepareStatement(String sql, int autoGeneratedKeys) is called with an invalid autoGeneratedKeys parameter. Also, calling this method for a select query doesn't make sense. Use prepareStatement(String sql)instead.
Cheers Marco

[jessica nesler]

Thanks for confirming my suscipions Marco!

[Marco Amadei]

You're welcome.