I have written a field procedure to encrypt/decrypt data in an SQL table and defined that field procedure on the table.
When I attempt to retrieve data from the table using either interactive SQL or an SQL statement embedded in an SQLRPGLE program, it fails. When I access data from an RPGLE program, Query, DFU, and other non-SQL methods, the field procedure works perfectly.
The errors I get are:
Unknown SQL type passed.. (CPF9898)
Field procedure error. (CPF504D, reason code 4 - The external program no longer exists or is not found.)
MyProc in MyLib not valid for operation. (SQL7009)
In my SQLRPGLE test program, I simply "Select * from MyTable". The results of the Fetch statement are SQLState = 55019, SQLCode = -7009.
I know I cannot have embedded SQL in my field procedure, and I don't. The error occurs only when I attempt to access data using an SQL Statement.
It seems to me that the fieldproc must be ok since it works when accessing the data without SQL.
I know the data in encoded as can run "select hex_encoded(MyField) from MyTable" successfully and see that the data is, in fact, encoded.
The fieldproc is Thread(*Serialize), dftactgrp(*no), actgrp(*CALLER).
Additionally, when I set a service entry point, my breakpoints fire when called from any non-SQL function. They do not break when called from SQL.
I appreciate any suggestions. Thanks.