You can force an SQL warning condition at the point in the code where the function appears to have problems. To force the warning, set the sqlstate return argument to '01H xx', where you choose the numeric value of xx.
If you use parameter style SQL, you can also set the error_message return argument to return up to 256 SQL_TEXT characters.
The warning is issued as a return state of the function. The warning does not terminate the transaction; therefore, you must set the return value of the function to a valid value that the transaction can use.
Only one warning can be returned per request.
Here is an example of how to set the SQLSTATE result code and error message return argument in a scalar function that uses parameter style SQL:
#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>
void String_UDF(VARCHAR_LATIN *inputString,
CHARACTER_LATIN *result,
int *inputStringIsNull,
int *resultIsNull,
char sqlstate[6],
SQL_TEXT extname[129],
SQL_TEXT specific_name[129],
SQL_TEXT error_message[257])
{
if (strlen((char *)inputString) == 0) {
*result = 'F';
/* Force a warning using the SQLSTATE result code value */
strcpy(sqlstate, "01H01");
/* Set the error message return value */
strcpy((char *)error_message, "Zero length input string");
return;
}
...
}
In a BTEQ session, if String_UDF is passed a zero length string, a warning is reported with the SQLSTATE result code value and the error message return value:
SELECT String_UDF('');
Result:
*** Query completed. One row found. One column returned. *** Warning: 7505 in UDF dbname.String_UDF: SQLSTATE 01H01: Zero length input string *** Total elapsed time was 1 second.