Let me explain my situation. I have a process that reads messages from a JMS Queue and queries the Teradata database with the contents in the message and then if the quesrying to teradata database is successful , the message is deleted from the queue.
Now my problem is, If terardata is down, or it is an invalid userid or an authentication or timeout error from Teradata, i categorize those as TECHNICAL exceptions which means i will not delete the message from the queue because it was not successfully processed because there was some technical errors in teradata.
But say, i am querying the data base as select * from TestTable where rollno = 12 (some integer value which i get from the message) and rollno column in table TestTable is integer. Now in my message i get say 12a(a character along with an integeer) . In that case, the query will be created as select * from TestTable where rollno = 12a , which is a invalid data, so i categorize these type of data errors BUSINESS and i delete the message from the queue.
Now my question is with the error codes that teradata returns how do i categorize data errors(passing a character to an integer field) from technical errors (like invalid pwd, databse down,timeout) etc.
the sqlstate 42000 is returned for both data errors (passing a character to an integer filed) and for authorization erros(user not authorized to query the table.)So i am not able to differentiate my errors absed on the sqlstate alone.
Is there a way using error number i can distinguish the data errors alone (invalid character passed for an integer filed) from other technical errors? Will all error number that start with 37 are data errorr?
Thanks for being patient in reading my question. Thanks in advance for everyone who can throw some light for my question.