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.
midrange.com
