error message "two different data types are being set" - documentation? advice?

Connectivity
Enthusiast

error message "two different data types are being set" - documentation? advice?

Hello - I got an error which sounds like it is supposed to mean I tried to insert an incompatible data type but I'm having trouble finding useful information about why the conversion failed.

Here's the error message:

SQL state [HY000]; error code [857]; [Teradata JDBC Driver] [TeraJDBC 14.10.00.17] [Error 857] [SQLState HY000] Two different data types are being set for parameter 6 (449 & 481); nested exception is com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 14.10.00.17] [Error 857] [SQLState HY000] Two different data types are being set for parameter 6 (449 & 481)

I've been trying to find documentation that tells me why 449 and 481 are incompatible data types - but no luck so far.

I know that I am trying to insert a java double into a decimal(11,2) - is this normally a problem?

Thanks in advance for any direction you can give in identifying the cause of this error.

Luke

3 REPLIES
Teradata Employee

Re: error message "two different data types are being set" - documentation? advice?

This error typically occurs when using a PreparedStatement batch.

The Teradata Database requires that all parameter values in batch that are bound to the same parameter marker must be the same data type.

In other words, when your application binds multiple rows of data in a PreparedStatement batch, you must ensure that on a column-by-column basis, all column values are the same data type.

Important: This includes NULLs.

Your application will typically bind non-null values using the data-type-specific PreparedStatement setter methods such as setInt, setString, etc. But your application will typically bind null values using setNull. With setNull, you must specify the data type using a constant from java.sql.Types.

Example 1 -- how to cause this error -- bind two different data types to the same parameter marker:

PreparedStatement ps = con.prepareStatment("insert into mytable(column1) values(?)";

ps.setInt(1, 12345);

ps.addBatch();

ps.setString(1, "oops");

ps.addBatch();

Example 2 -- how to cause this error -- bind null and non-null with two different data types to the same parameter marker:

PreparedStatement ps = con.prepareStatment("insert into mytable(column1) values(?)";

ps.setInt(1, 12345);

ps.addBatch();

ps.setNull(1, Types.VARCHAR);

ps.addBatch();

Enthusiast

Re: error message "two different data types are being set" - documentation? advice?

Thanks for the reply - I am using Spring jdbcTemplate's batchUpdate method to do some inserts - one record has a null value in parameter 6.  So you're exactly right.

I see two options to solve this:

1) don't use batchUpdate method

2) find a way to force the NULL value to be the correct data type - to do this, I think I would need to determine how my null java ref is being converted into the varchar ('null' - i'm guessing) when passed to the JDBC driver. 

I think I'll go with #1 for now - I don't have enough insight into the inner workings of the JDBC driver to worry about #2.

Thanks again,

Luke

Enthusiast

Re: error message "two different data types are being set" - documentation? advice?

A solution involving more work: a colleague suggests using Spring's interface BatchPreparedStatementSetter.  Implementing that allows you to override whatever default behavior Spring is using to set the null data types.