Character with Bit/hex confusion in DB2

This works:

SELECT TASKT_ID FROM DATA . TASKT WHERE TASK_WEB_IDENTIFIER = CAST ( HEXTORAW ( '0213725501A421D384233E5001' ) AS CHAR ( 26 ) ) ;

Since that work I put it into the procedure:

BEGIN

DECLARE GET_TASKT_ID_BY_TASK_WEB_IDENTIFIER_C1 CURSOR WITH RETURN FOR
SELECT TASKT_ID FROM DATA . TASKT WHERE TASK_WEB_IDENTIFIER = CAST ( HEXTORAW ( P_WEB_IDENTIFIER ) AS CHAR ( 26 ) ) ;

OPEN GET_TASKT_ID_BY_TASK_WEB_IDENTIFIER_C1 ;
END 

The procedure has the one parameter P_WEB_IDENTIFIER which is a CHAR(26) for bit data with the CCSID 65535

However, when I now call it with the string like so:

call PROGRAM . GET_TASKT_ID_BY_TASK_WEB_IDENTIFIER ('0213725501A421D384233E5001');

I get back that the argument for VARBINARY_FUNCTION is invalid by length or data type.

Also, this works:

call PROGRAM . GET_TASKT_ID_BY_TASK_WEB_IDENTIFIER (CAST('0213725501A421D384233E5001' as char(26)));

What Can I do to make sure that string converts with only the string being passed?

Answer

What are you using to call the stored procedure?

What version and release of Db2 for i?

In the Run SQL Scripts component of IBM ACS or the older Access for Windows, string literals in your statements are treated as varchar.

Thus the CAST('0213725501A421D384233E5001' as char(26)) makes sense. What doesn’t is the error message. Normally, you’d get a procedure not found error as the Db is looking for a procedure named PROGRAM.GET_TASKT_ID_BY_TASK_WEB_IDENTIFIER that takes a varchar parameter and the only thing that exists is a procedure that takes a char(26).

IBM’s tools have gotten better at implicitly converting when needed. But I usually go with an explicit conversion when testing manually (as you’ve done here). Or I just make the parms varchar to start. And convert to character within the procedure if needed.

The char/varchar difference doesn’t usually matter to the client code, as it can be specific in it’s type definitions. It’s only a factor for interactive tools like ACS that are executing dynamic statements.