× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I'm not familiar with writing triggers in SQL, hopefully someone else can help you with that part of it.

But, I wanted to write to point out that RC2 is very weak encryption. I don't recommend using it for credit card numbers or anything else of importance.

In V5R4, IBM added an ENCRYPT_TDES function to SQL, which is better.
In V6R1, they added an ENCRYPT_AES function which is what I recommend.



Eftimios Pantzopoulos wrote:
_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________


I'm trying to encrypt a 16 byte credit card number and thought I'd try the SQL encrypt function. I found some code on the net that uses a trigger, but can't get my interpretation to work. When I use the encrypt function in an insert statement it works fine, but when I include it in a trigger it fails. Here is the sequence of events:

* Create a test file of 1 field long enough to hold the encrypted form (16+8=32)

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

* Test by using insert. This woks fine:

INSERT INTO QGPL/MXP02 (FLD01)
VALUES(encrypt_rc2('1234567890123456','password'))
1 rows inserted in MXP02 in QGPL.

* Create a before trigger to encrypt the data before insert (I'm not an SQL/PL expert. I copied and hacked this code:

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

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

* I get the following message when I try to insert a record into the MXP02 table:

SQL trigger INSERT_MXP02 in *N failed with SQLCODE -303 SQLSTATE 220

The IBM SQL Error Code finder tells me (amongst other things):

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?

Thanks in advance.

Mike Pantzopoulos


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.