Passing NULL values to a TERADATA sp, using Teradata .NET provider 1.0

Third Party Software

Passing NULL values to a TERADATA sp, using Teradata .NET provider 1.0

Passing NULL values to a TERADATA sp, using Teradata .NET provider

Teradata provides Teradata .NET provider driver for connecting to Teradata using .NET 2.0,
I was initally using ODBC driver, but for calling SP using CommandType.Text or CommandType.StoredProcedure, both do not return any value, so we switched to Teradata provider provided by NCR corporation . ( Can ODBC driver be used for retrieving the SP return value ??? )

But I was a facing a problem related to passing NULL values to the SP.
The documentation provide to use DBNull.Value, but this works perfectly well for DATE and TIME type parameters but not for STRING type parameters, where the system gives the exception NULLReferenceException.
This SP works pefectly well with NULL values by using NULL values in Teradata SQL Assistant.

I have even tried using parameter values as NULL,'NULL',"NULL" but all fail for one or the other parameter types, sometimeg giving Invalid DATE and time and for string types.. not accepting it as a NULL but instead as a blank space.

If I need to be more elaborate, please do tell me..
I can paste my code as well..

Help needed is urgent.

Thank
3 REPLIES

Re: Passing NULL values to a TERADATA sp, using Teradata .NET provider 1.0

Hi,

I am trying to copy the data from mysql to teradata, but the problem here is i am unable to copy the data if i have got null value in any column. It says :

Error codes: [HY000, 1063]
Driver exception: com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata JDBC Driver] [TeraJDBC 13.10.00.18] [Error 1063] [SQLState HY000] null is not supported as a data value with this variant of the setObject method; use the setNull method or the setObject method with a targetSqlType parameter

Please help out in fixing this issue. Its very urgent

Thanks in Advance !!!

-Nick

Re: Passing NULL values to a TERADATA sp, using Teradata .NET provider 1.0

I am trying to load the data from splunk to teradata using JDBC driver.As nick highlighted in previous post we are not able to load null value in to the table.

Following is the error I am getting

[Teradata JDBC driver][TeraJDBC 14.10.00.17][Error 1063][SQLState HY000] null is not supported as a data value with this variant of the setObject method; use the setNull method or the setObject method with a targetSqlType parameter

Please help out in fixing the issue.

Teradata Employee

Re: Passing NULL values to a TERADATA sp, using Teradata .NET provider 1.0

Let's assume you have an Object with a null value in your application:

Object obj = null ;

The error message is indicating that your application cannot use the PreparedStatement.setObject(int,Object) method to bind the null value:

PreparedStatement ps = con.prepareStatement ... ;

ps.setObject(1, obj) ; // will throw exception if obj is null

Instead, your application must specify the data type of the null value using one or the other of these methods:

ps.setObject(1, obj, Types.VARCHAR) ; // or some other java.sql.Types constant

// or

ps.setNull(1, Types.VARCHAR) ; // or some other java.sql.Types constant

The Teradata Database requires the data type to be specified for a NULL value, so the Teradata JDBC Driver, in turn, must require the application to specify the java.sql.Types constant for a null value.