Connection.createStatement fails to use ResultSet type information

Connectivity

Connection.createStatement fails to use ResultSet type information

These are the teradata jars in my classpath:

tdgssconfig-12.00.00.110.jar
terajdbc4-12.00.00.110.jar

I execute java.sql.Connection createStatement method passing in ResultSet.TYPE_SCROLL_INSENSITIVE, but the resulting statement comes back with a FORWARD_ONLY result set type, and indead the ResultSets returned from the statement are type forward only. Is this an issue that is addressed in the v13 drivers? I did not see a reference to this in the release notes.

Thanks,
Dave
5 REPLIES

Re: Connection.createStatement fails to use ResultSet type information

hmmm was reading through the response limit exceed post and the user guide, and came across this interesting bit:

When connection parameter LOB_SUPPORT=OFF is specified, Scrollable Result Sets and
Updatable Result Sets cannot be used. Requesting a Scrollable Result Set and/or Updatable
Result Set throws an exception due to a Teradata Database error:
[Teradata Database] : Parcel kind or ordering is invalid.

we had this response limit exceeded problem, and turned off LOB_SUPPORT. The interesting thing, Connection.prepareStatement honors the ResultSet type and allows scrollable resultsets. Again, I'm using version 12 of the JDBC driver. It's noteworthy that I did not receive the Parcel kind or ordering invalid error, the createStatement method just silently ignored ResultSet type information, always returning TYPE_FORWARD_ONLY.
Teradata Employee

Re: Connection.createStatement fails to use ResultSet type information

Teradata JDBC Driver version 12.00.00.110 *does* support scrollable result sets.

There are a couple of situations in which the Teradata JDBC Driver must "downgrade" your request for a Statement object that supports scrollable result sets to instead be Statement object that offers forward-only result sets.

Teradata Database V2R5.0 and earlier do not support scrollable result sets, so if you are using a very old Teradata Database version, then your Statement object will be downgraded to offer forward-only result sets.

LOB support is a prerequisite for scrollable result set support, so if you have turned off LOB support with the LOB_SUPPORT=OFF connection parameter, then your Statement object will be downgraded to offer forward-only result sets.

When the Teradata JDBC Driver downgrades your Statement object, a SQLWarning is added to your Connection object's SQLWarning chain, in order to notify you of what happened. Your application can access the Connection object's SQLWarning chain by calling the Connection getWarnings method.

Re: Connection.createStatement fails to use ResultSet type information

Can you speak to the issue of PreparedStatements not being downgraded? When I switch the same call from createStatement to prepareStatement, it works as I orginally expected. Thanks for the detailed response. I do in fact have: LOB_Support=OFF in my connection URL.
Teradata Employee

Re: Connection.createStatement fails to use ResultSet type information

Scrollable result sets' depending on LOB support is true for both PreparedStatement objects and regular non-prepared Statement objects. (And also true for CallableStatement objects.)

I just tested Teradata JDBC Driver version 12.00.00.110 with connection parameter LOB_SUPPORT=OFF.

I called Connection prepareStatement with argument TYPE_SCROLL_INSENSITIVE, and a SQLWarning was added to the Connection's warning chain:

java.sql.SQLWarning: [Teradata JDBC Driver] [TeraJDBC 12.00.00.110] [Error 1241] [SQLState HY000] Downgraded to ResultSet.TYPE_FORWARD_ONLY because TYPE_SCROLL_INSENSITIVE is not supported when LOB_SUPPORT is OFF

I then called PreparedStatement executeQuery and it returned a ResultSet object whose ResultSet getType method returned TYPE_FORWARD_ONLY (int value 1003).

Re: Connection.createStatement fails to use ResultSet type information

Hmm .. I just took another look and yes, you're right. PreparedStatement behaves consistently with Statement.