Re: SQL Encrypt Function in a Trigger

[Simon Coulter <shc@xxxxxxxxxxxxxxxxx>]

On 18/03/2009, at 10:00 AM, Eftimios Pantzopoulos wrote:

> A FETCH, SELECT, CALL, SET, or VALUES INTO cannot be performed  
> because the data type of host variable &2 is not compatible with the  
> data type of the corresponding list item.
>
> Is there some other sort of declaration I need internal to the  
> trigger?

Documentation for ENCRYPT_RC2 says:

Data Type of the First Argument		Data Type of the Result
BINARY or VARBINARY			VARBINARY
CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC	VARCHAR FOR BIT DATA
BLOB, CLOB, or DBCLOB			BLOB

Thus, in your case, the output from ENCRYPT_RC2 is a VARCHAR  
therefore, because the SQL trigger gets the field definition from the  
file, you must either declare the correct data type in the file or  
cast the result to the desired type.

> CREATE TABLE MXP02(FLD01 CHAR (32 ) for bit data)

CREATE TABLE MXP02(FLD01 VARCHAR(32 ) for bit data)

or (untested):

create trigger insert_mxp02
before insert on mxp02
referencing new row as n

for each row
begin
set n.fld01= CAST(encrypt_rc2(n.fld01,'eftimios') as CHAR);
end

Regards,
Simon Coulter.
--------------------------------------------------------------------
   FlyByNight Software         OS/400, i5/OS Technical Specialists

   http://www.flybynight.com.au/
   Phone: +61 2 6657 8251   Mobile: +61 0411 091 400        /"\
   Fax:   +61 2 6657 8251                                   \ /
                                                             X
                 ASCII Ribbon campaign against HTML E-Mail  / \
--------------------------------------------------------------------