RE: SQL ENCRYPT_RC2() function Question

["Mike" <koldark@xxxxxxxxx>]

How do you do that in DDS? I would prefer to keep it DDS if possible. Not
that I am against SQL...

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Monday, December 03, 2007 5:42 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL ENCRYPT_RC2() function Question

Try declaring ACCOUNT as VARCHAR FOR BIT DATA as that is the return type
from ENCRYPT_RC2 function.

Celebrating 10-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com
 

-----Original Message-----
Subject: SQL ENCRYPT_RC2() function Question

I am trying to set my field size for this. From what I understand based on
the the documentation for the size I have set, it should be correct (see
below). However, I am getting -303 SQLCOD errors (truncation errors).

Field size = 17
Add for encryption = 8
equals 25
round to the next 8-byte boundary
equals 32

Does this look correct? However, to get this to work in RPG, I had to set
the field to 128. Then I decided to try to follow an article I found (
http://www.ibmsystemsmag.com/i5/june04/administrator/8475p4.aspx) and create
the trigger they mention:

RPG works:
     C/exec sql
     C+ set encryption password = :encryptionKey
     C/end-exec

     C/exec sql
     C+ insert into TESTP (ACCOUNT, TESTKEY)
     c+ VALUES ENCRYPT_RC2('12345678901234567'), 1
     C/end-exec

Trigger doesn't:
CREATE TRIGGER MWLIBR.TEST_INSERT
    BEFORE INSERT ON MWLIBR.TESTP
    REFERENCING NEW AS N
    FOR EACH ROW
    MODE DB2ROW
    BEGIN
        DECLARE PASSWD VARCHAR ( 127 ) ;
        SET PASSWD = 'my20charpasswordhere' ;
        SET N . ACCOUNT = ENCRYPT_RC2 ( N . ACCOUNT , PASSWD ) ;
    END  ;

I then tried to do an insert in STRSQL and I get the following error:

INSERT INTO MWLIBR/TESTP (ACCOUNT, TESTKEY) VALUES(12345678901234567, 2)
SQL trigger TEST_INSERT in MWLIBR failed with SQLCODE -303 SQLSTATE

Any thoughts to the problem? Is my field size calculation wrong or is it a
different problem. We are on V5R3.

--
Mike Wills