More information would be needed in order to determine the problem.
Please post your Java code that binds values to the parameter markers, and please post the DDL for the "LOV" object.
Thank you for your reply.Here is the DDL
CREATE MULTISET TABLE Test.LOV ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
VAL VARCHAR(60) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
ROW_ID INTEGER NOT NULL)
PRIMARY INDEX ( VAL );
And here is the java code snippet:
Connection conn = .. snip.. // get a connection first..
PreparedStatement ps = null;
try
{
String sql = "SELECT DISTINCT CASE WHEN 'All' = ? THEN LOV.VAL ELSE ? END FROM LOV";
// get prepared statement
ps = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY );
// NOTE: Error raised here even before I bind parameters
// bind parameters
long currTime = (new java.util.Date()).getTime();
ParameterMetaData pmd = ps.getParameterMetaData();
int pcount = pmd.getParameterCount();
for(int i=1; i<=pcount;i++)
{
int paramType = pmd.getParameterType(i);
switch(paramType)
{
case java.sql.Types.NUMERIC: case java.sql.Types.INTEGER: case java.sql.Types.FLOAT:
case java.sql.Types.DECIMAL: case java.sql.Types.BIGINT:
case java.sql.Types.SMALLINT:case java.sql.Types.TINYINT:
ps.setInt(i, 0);
break;
case java.sql.Types.DOUBLE:
ps.setDouble(i, 0);
break;
case java.sql.Types.VARCHAR: case java.sql.Types.CHAR: case java.sql.Types.NVARCHAR:
case java.sql.Types.NCHAR:
ps.setString(i, "0");
break;
case java.sql.Types.DATE:
ps.setDate(i, new java.sql.Date(currTime));
break;
case java.sql.Types.TIME:
ps.setTime(i, new java.sql.Time(currTime));
break;
case java.sql.Types.TIMESTAMP:
ps.setTimestamp(i, new java.sql.Timestamp(currTime));
break;
default:
ps.setNull(i, paramType);
}
}
// exeucte the query;
ResultSet rs = ps.executeQuery();
}
catch(Exception ex)
{
System.out.println(ex);
}
You have encountered a limitation with the Teradata Database.
For the question-mark parameter marker in the expression "CASE WHEN 'All' = ?", the Teradata Database is making the wrong assumption about the data type of the value that your application will later bind to the parameter marker. Instead of assuming that your application will bind a character value, the Teradata Database is assuming that your application will bind a numeric value.
The workaround is to add a cast around the parameter marker:
String sql = "SELECT DISTINCT CASE WHEN 'All' = cast(? as varchar(60)) THEN LOV.VAL ELSE ? END FROM LOV";
Thank you !
I just changed my query as suggested and now following line works fine.
ps = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY );
However, ParameterMetaData.getParameterCount() returns 4 where it should be two..
Any thought ?
ParameterMetaData pmd = ps.getParameterMetaData();
int pcount = pmd.getParameterCount(); // this gives 4 instead of 2..
You found a bug in the Teradata Database.
Here are my testing results:
TD 12.00.03.28 - correct - getParameterCount=2
TD 13.00.01.13 - incorrect - getParameterCount=4
TD 13.10.01.05 - incorrect - getParameterCount=4
TD 14.0 - correct - getParameterCount=2
If you're a customer, then I recommend that you open an incident with Teradata Customer Support, so you can get a fix for the Teradata Database release that you're using.
I was trying to do following with Teradata JDBC (14.00.00.09)
String sql = "SELECT DISTINCT CASE WHEN 'All' = ? THEN LOV.VAL ELSE ? END FROM LOV";
ps = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY );
And it gives me following error:
com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata Database] [TeraJDBC 14.00.00.09] [Error 3535] [SQLState 22003] A character string failed conversion to a numeric value.
Is bind variable in CASE WHEN statmenet not supported ? or is this a bug ?
Thank you.
Hide