SQLException error codes

Connectivity

SQLException error codes

What SQLException error codes should I expect when exceeding query timeouts?

Here's what I'm seeing when using JDBC...

For MACRO calls, I’m using the java.sql.PreparedStatement.
For STORED PROCEDURE calls, I’m using the java.sql.CallableStatement.

For both MACRO and STORED PROCEDURE calls, I’m setting the timeout via the setQueryTimeout(int) method.

When a MACRO exceeds the timeout threshold, I receive the following SQLException information…
[NCR] [Teradata JDBC Driver] : HY000 802 : Timeout Packet stream read error: local="my server"/"my ip":"my port" remote="my server"/"my ip":"my port" cid=40e569 sess=1053567 Wed Apr 30 14:54:04 EDT 2008 java.net.SocketTimeoutException

The vendorCode/errorCode is 802.

When a STORED PROCEDURE exceeds the timeout threshold, I receive two different messages in the SQLException…
[NCR] [Teradata JDBC Driver] : executeQuery error: The statement has been canceled by another thread
at com.ncr.teradata.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:126)
at com.ncr.teradata.jdbc_4.util.ErrorFactory.makeDriverJDBCException(ErrorFactory.java:101)
at com.ncr.teradata.jdbc_4.util.ErrorAnalyzer.analyzeError(ErrorAnalyzer.java:35)
at com.ncr.teradata.jdbc_4.Statement.executeStatement(Statement.java:298)
at com.ncr.teradata.jdbc_4.PreparedStatement.execute(PreparedStatement.java:60)
at com.ncr.teradata.jdbc_3.ifjdbc_4.TeraLocalCallableStatement.execute(TeraLocalCallableStatement.java:160)

The vendorCode/errorCode is 751

I've also seen the same information as the MACRO timeout:
[NCR] [Teradata JDBC Driver] : HY000 802 : Timeout Packet stream read error: local="my server"/"my ip":"my port" remote="my server"/"my ip":"my port" cid=1ee926 sess=1411733 Tue Apr 29 21:30:13 EDT 2008 java.net.SocketTimeoutException:

However, I cannot recreate the last exception when debugging.

Does anyone know why I'm seeing mixed results between MACRO vs STORED PROCEDURE timeouts?
Does anyone know why I'm seeing mixed results between STORED PROCEDURE timeouts?
What other vendorCodes/errorCodes can I expect when a query via JDBC is canceled due to a timeout?

I need to be able to separate the differences between a sql timeout versus a "real" database problem. Since JDBC driver only throws SQLExceptions, I know of no other way than by inspecting SQLException attributes. I'm doing this by the following code snippet:

private void processSQLExceptionForTimeOut(SQLException e) throws SQLException
{

if(e.getErrorCode() == 802
|| e.getErrorCode() == 751
|| e.getMessage().contains("Timeout Packet stream read error")
|| e.getMessage().contains("executeQuery error: The statement has been canceled by another thread"))
{
//process timeout
}
else
{
//a "real" database problem may exist?
}
throw e;
}

Let me know if you need more information in terms of Driver Version and/or MACRO/PROCEDURE definitions, or information on the Teradata software/hardware version.

Thanks in advance,
Chris

1 REPLY
Teradata Employee

Re: SQLException error codes

Old versions of the Teradata JDBC Driver had bugs in this area, but we believe those have been corrected.

The intended behavior is as follows, regardless of the kind of SQL request that is executed:

SQLException.getErrorCode = 751 indicates that the application interrupted the operation with Thread.interrupt.

SQLException.getErrorCode = 802 indicates that a query timeout occurred.

Both these exceptions have getSQLState = HY000.