ExecuteUpdate -- -row count return type is int

Connectivity
Enthusiast

ExecuteUpdate -- -row count return type is int

I noticed that the statement.executeUpdate() method returns int data type , which conforms to JDBC standard. However, here is the question: it is Teradata!!! the affected row count can easily blow away the integer range. How to deal with such issue?
Noticeably the BTEQ affected row count is also relies on a smaller 32 bit data type range limit. Any tricks that can be played to get around the issue?

7 REPLIES
Teradata Employee

Re: ExecuteUpdate -- -row count return type is int

The Teradata Database handles this issue.

Here is the relevant excerpt from the Teradata Database Messages manual, for code 6813:

6813 Numeric overflow in internal counters. The number of rows returned is actual number of rows returned, modulo 2^32.

Explanation: DR64568-VP185005-01 -> This warning message is sent within SUCCESS/OK parcel when the activitycount (i.e., the number of rows) returned, overflows the current max limit of (2^32 - 1). The request is successful. The value returned to the user is : (actual number of rows returned) modulo 2^32. NOTE: If the user knows that the actual number of rows returned cannot exceed 2^33, then the actual number of rows returned can be derived as follows: actual number of rows returned = 2^32 + the value returned to the user. DR64568-VP185005-01 <-

Generated By: DIS modules.

For Whom: End User.

Remedy: This message is for informational purposes only.
Enthusiast

Re: ExecuteUpdate -- -row count return type is int

Is there a way to get the "activityCount" (or updated rows/selected rows) after using the execute() method? (outside of looping thru & retrieving every row)? I've been trying to use the last() method then & using the getrow(), but i can't seem to get that to work -- it always gives me an exception saying that my result set is FORWARD ONLY... I even tried creating a statement with a TYPE_SCROLL_INSENSITIVE resultsetType, but it doesn't work. Any ideas (or suggestions)? It seems like this should be super simple, but i can't seem to figure it out.

thanks!
Teradata Employee

Re: ExecuteUpdate -- -row count return type is int

For a DML statement that does not return rows, such as an INSERT, UPDATE, or DELETE statement, the Teradata "activity count" is the same concept as the JDBC API "update count". You can retrieve the update count from the Statement.getUpdateCount method.

However, it sounds as though you want to obtain the Teradata activity count for a DML statement that returns rows, such as a SELECT statement. The JDBC API does not offer a way for an application to obtain the row count of a result set.

The Teradata JDBC Driver and the Teradata Database have supported TYPE_SCROLL_INSENSITIVE result sets for several years now, beginning with Teradata JDBC Driver 03.02.00.00 (released in 2004) and Teradata Database V2R5.1 (released in 2003).

As you noted, there is an inefficient workaround for obtaining the row count -- you can use a TYPE_SCROLL_INSENSITIVE result set, you can position to the last row of the result set with the ResultSet.last method, and then obtain the row count from the ResultSet.getRow method.
Enthusiast

Re: ExecuteUpdate -- -row count return type is int

Apparently, the TYPE_SCROLL_INSENSITIVE only works IF... the LOB_SUPPORT is ON. For whatever reason, i had that set to "OFF" when i connected to the database; once i turned that ON... the resultSet.last(); & then resultSet.getRow() worked perfectly. The timing wasn't bad with a small dataset -- am wondering how bad it might be if it's millions of records.

But, I would have thought that I should have gotten an exception when i tried to createStatement() & specifically requested the SCROLL_INSENSITIVE... shouldn't have said -- "Lob_support not on, scrollable cursor not permitted?" -- or something?

stmt=tdConn.createStatement(ResultSet.TYPE_SCROLL_INSE NSITIVE, ResultSet.CONCUR_READ_ONLY);

Also -- when debugging, there's a m_nActivityCount inside of the resultSet structure... which already has the result count in it - after i execute my query. It just seems odd that i can't figure out a way to get to that value and/or that they haven't provided the means to share that critical piece of information.

PS -- thanks for the reply. Its much appreciated.

Teradata Employee

Re: ExecuteUpdate -- -row count return type is int

>>> I would have thought that I should have gotten an exception when i tried to createStatement() & specifically requested the SCROLL_INSENSITIVE... shouldn't have said -- "Lob_support not on, scrollable cursor not permitted?" -- or something?

In that situation, a JDBC Driver is supposed to provide a SQLWarning and "downgrade" the Statement, rather than throw an exception.

In particular, the Teradata JDBC Driver adds a SQLWarning to the Connection's warning chain -- with Error Code 1241, and Message "Downgraded to ResultSet.TYPE_FORWARD_ONLY because TYPE_SCROLL_INSENSITIVE is not supported when LOB_SUPPORT is OFF"

The application can obtain the Connection's warning chain by calling the Connection.getWarnings method.
Teradata Employee

Re: ExecuteUpdate -- -row count return type is int

The activity count is an accurate row count most of the time; however, under certain conditions the actual row count can differ from the activity count.

There is feature request (JDBC RFC 121720) for the Teradata JDBC Driver to make the activity count available to an application. We haven't scheduled work on JDBC RFC 121720 yet.

If and when we implement JDBC RFC 121720, it would be a bad idea for an application to expect the activity count to exactly match the actual row count in all cases. Instead, the application should treat the activity count as an approximate row count.

Teradata Employee

Re: ExecuteUpdate -- -row count return type is int

Beginning with Teradata Database 14.10 and Teradata JDBC Driver 14.00.00.25, for row count values received from the Teradata Database that are too large to fit into a signed 32-bit integer, the Teradata JDBC Driver will return an update count of Integer.MAX_VALUE to the application, and will provide a SQLWarning with error code 1474 that lists the actual row count in the message text.

 

For more information, please refer to the relevant section of the Teradata JDBC Driver User Guide:

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#B64BITAC