Topic
  • 7 replies
  • Latest Post - ‏2015-02-03T14:44:38Z by dtrotter
dtrotter
dtrotter
5 Posts

Pinned topic FieldProc issue when called from SQL

‏2014-08-07T21:05:16Z |

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.

  • dtrotter
    dtrotter
    5 Posts
    ACCEPTED ANSWER

    Re: FieldProc issue when called from SQL

    ‏2014-09-08T19:05:01Z  

    I would suggest 2 things.  First, ensure you have the most recent version of the DB Group PTF installed on your system.  Second, try using the Graphical Debugger from the System i Navigator Run SQL Scripts.  You'll find Debugger link on Run menu.  If that Debugger doesn't get invoked for a "SELECT * FROM mytable" request from Run SQL Scripts, then I'd open a PMR with IBM Support.

    I opened a PMR with IBM Support on this issue and after quite a bit of testing at IBM's request, I have a work-around.

    At IBM's suggestion, I dropped the fieldproc with Alter Table, then re-added it with Alter Table..  While this worked, I would like to see a resolution so that it works with a simple Create Table.  But at least I can make it work.

    Thanks for your suggestions, Kent.

  • dtrotter
    dtrotter
    5 Posts
    ACCEPTED ANSWER

    Re: FieldProc issue when called from SQL

    ‏2015-02-03T14:44:38Z  
    • dtrotter
    • ‏2014-09-09T15:37:05Z

    PMR # is  27199,122,000

    IBM has approved the following PTFs for this issue:

    V7R1M0 -- SI55381
    V7R2M0 -- SI55382


     

  • krmilligan
    krmilligan
    450 Posts

    Re: FieldProc issue when called from SQL

    ‏2014-08-07T22:49:43Z  

    Looks like an invalid type might be passed back on Decode calls to the FieldProc program.  SQL requests typically invoke the FieldProc program in a separate system thread, so you would need to use the STRSRVJOB for a debugging session.

  • dtrotter
    dtrotter
    5 Posts

    Re: FieldProc issue when called from SQL

    ‏2014-08-11T15:15:02Z  

    Looks like an invalid type might be passed back on Decode calls to the FieldProc program.  SQL requests typically invoke the FieldProc program in a separate system thread, so you would need to use the STRSRVJOB for a debugging session.

    Thanks, Kent. 

    I have tried debugging this issue using service entry points and service jobs, both with RDp 8.0 and green-screen STRDBG and the sbreak command as described by Susan Gantner.

    I am able to get the program to break using both methods when the fieldproc is called from any non-SQL method, but cannot get it to break at all when called from interactive SQL or an embedded SQL statement.

    The following values from SysColumns2 on this table/field are:
    SYSTEM_COLUMN_NAME = MYFIELD
    DATA_TYPE = CHAR
    LENGTH = 19

    From SysFields, the values are:
    SYSTEM_COLUMN_NAME = MYFIELD
    DATA_TYPE = CHAR
    LENGTH = 32

    My fieldproc is using AES 256-bit encryption/decryption and again, works perfectly when invoked with non-SQL method.  Debugging the fieldproc when called from a non-SQL call, my variables are as follows:

    C     *Entry        Plist
    C                   Parm                    FuncCode      - defined as 5i 0
    C                   Parm                    OptParms      - defined as LikeDs(SQLFOPVD)
    C                   Parm                    DeCodTyp     - defined as LikeDs(SQLFPD)
    C                   Parm                    DeCodDta     - defined as 19A
    C                   Parm                    EnCodTyp      - defined as LikeDs(SQLFPD)
    C                   Parm                    EnCodDta      - defined as 32A
    C                   Parm                    SqlState         - defined as 5A
    C                   Parm                    SqMsgTxt      - defined as LikeDs(SQLFMT)

    FUNCCODE = 04.

    (Note: I do  not have optional parameters defined on my fieldproc definition in the DDL)

    OPTPARMS.SQLFODL = 8
    OPTPARMS.SQLFST00 = 0
    OPTPARMS.SQLFBL00 = 452
    OPTPARMS.SQLFL00 = 19
    OPTPARMS.SQLFP00 = 0
    OPTPARMS.SQLFS00 = 19
    OPTPARMS.SQLFC00 = 0
    OPTPARMS.SQLFAL00 = 0
    OPTPARMS.SQLRSV100 = ' _            '
    OPTPARMS.SQLRSV2 = '    TÃ_ë®q_Ì'
    OPTPARMS.SQLFPD01 = 'Q'

    DECODTYP.SQLFST = 452
    DECODTYP.SQLFBL = 19
    DECODTYP.SQLFL = 19
    DECODTYP.SQLFP = 0
    DECODTYP.SQLFS = 0
    DECODTYP.SQLFC = 37
    DECODTYP.SQLFAL = 0
    DECODTYP.SQLRSV1 = '              ' 

    ENCODTYP.SQLFST = 452
    ENCODTYP.SQLFBL = 32
    ENCODTYP.SQLFL = 32
    ENCODTYP.SQLFP = 0
    ENCODTYP.SQLFS = 0
    ENCODTYP.SQLFC = 37
    ENCODTYP.SQLFAL = 0
    ENCODTYP.SQLRSV1 = '              '

    Do any of the above appear incorrect?  Do you have any tips for getting this thing to break when called from an SQL Stateent?

    I'm am never quick to suspect an IBM issue, but I am to that point now on this issue.  I struggle to see how the fieldproc can work properly when called from a non-SQL method, but fail when called using SQL.  Since the parameters are out of my control and passed to the fieldproc by the database manager, I just don't know what I can do to resolve this.

    I've been chasing this for the better part of a month and am about out of ideas.

    Updated on 2014-08-11T15:16:23Z at 2014-08-11T15:16:23Z by dtrotter
  • krmilligan
    krmilligan
    450 Posts

    Re: FieldProc issue when called from SQL

    ‏2014-08-11T16:57:05Z  
    • dtrotter
    • ‏2014-08-11T15:15:02Z

    Thanks, Kent. 

    I have tried debugging this issue using service entry points and service jobs, both with RDp 8.0 and green-screen STRDBG and the sbreak command as described by Susan Gantner.

    I am able to get the program to break using both methods when the fieldproc is called from any non-SQL method, but cannot get it to break at all when called from interactive SQL or an embedded SQL statement.

    The following values from SysColumns2 on this table/field are:
    SYSTEM_COLUMN_NAME = MYFIELD
    DATA_TYPE = CHAR
    LENGTH = 19

    From SysFields, the values are:
    SYSTEM_COLUMN_NAME = MYFIELD
    DATA_TYPE = CHAR
    LENGTH = 32

    My fieldproc is using AES 256-bit encryption/decryption and again, works perfectly when invoked with non-SQL method.  Debugging the fieldproc when called from a non-SQL call, my variables are as follows:

    C     *Entry        Plist
    C                   Parm                    FuncCode      - defined as 5i 0
    C                   Parm                    OptParms      - defined as LikeDs(SQLFOPVD)
    C                   Parm                    DeCodTyp     - defined as LikeDs(SQLFPD)
    C                   Parm                    DeCodDta     - defined as 19A
    C                   Parm                    EnCodTyp      - defined as LikeDs(SQLFPD)
    C                   Parm                    EnCodDta      - defined as 32A
    C                   Parm                    SqlState         - defined as 5A
    C                   Parm                    SqMsgTxt      - defined as LikeDs(SQLFMT)

    FUNCCODE = 04.

    (Note: I do  not have optional parameters defined on my fieldproc definition in the DDL)

    OPTPARMS.SQLFODL = 8
    OPTPARMS.SQLFST00 = 0
    OPTPARMS.SQLFBL00 = 452
    OPTPARMS.SQLFL00 = 19
    OPTPARMS.SQLFP00 = 0
    OPTPARMS.SQLFS00 = 19
    OPTPARMS.SQLFC00 = 0
    OPTPARMS.SQLFAL00 = 0
    OPTPARMS.SQLRSV100 = ' _            '
    OPTPARMS.SQLRSV2 = '    TÃ_ë®q_Ì'
    OPTPARMS.SQLFPD01 = 'Q'

    DECODTYP.SQLFST = 452
    DECODTYP.SQLFBL = 19
    DECODTYP.SQLFL = 19
    DECODTYP.SQLFP = 0
    DECODTYP.SQLFS = 0
    DECODTYP.SQLFC = 37
    DECODTYP.SQLFAL = 0
    DECODTYP.SQLRSV1 = '              ' 

    ENCODTYP.SQLFST = 452
    ENCODTYP.SQLFBL = 32
    ENCODTYP.SQLFL = 32
    ENCODTYP.SQLFP = 0
    ENCODTYP.SQLFS = 0
    ENCODTYP.SQLFC = 37
    ENCODTYP.SQLFAL = 0
    ENCODTYP.SQLRSV1 = '              '

    Do any of the above appear incorrect?  Do you have any tips for getting this thing to break when called from an SQL Stateent?

    I'm am never quick to suspect an IBM issue, but I am to that point now on this issue.  I struggle to see how the fieldproc can work properly when called from a non-SQL method, but fail when called using SQL.  Since the parameters are out of my control and passed to the fieldproc by the database manager, I just don't know what I can do to resolve this.

    I've been chasing this for the better part of a month and am about out of ideas.

    I would suggest 2 things.  First, ensure you have the most recent version of the DB Group PTF installed on your system.  Second, try using the Graphical Debugger from the System i Navigator Run SQL Scripts.  You'll find Debugger link on Run menu.  If that Debugger doesn't get invoked for a "SELECT * FROM mytable" request from Run SQL Scripts, then I'd open a PMR with IBM Support.

  • dtrotter
    dtrotter
    5 Posts

    Re: FieldProc issue when called from SQL

    ‏2014-09-08T19:05:01Z  

    I would suggest 2 things.  First, ensure you have the most recent version of the DB Group PTF installed on your system.  Second, try using the Graphical Debugger from the System i Navigator Run SQL Scripts.  You'll find Debugger link on Run menu.  If that Debugger doesn't get invoked for a "SELECT * FROM mytable" request from Run SQL Scripts, then I'd open a PMR with IBM Support.

    I opened a PMR with IBM Support on this issue and after quite a bit of testing at IBM's request, I have a work-around.

    At IBM's suggestion, I dropped the fieldproc with Alter Table, then re-added it with Alter Table..  While this worked, I would like to see a resolution so that it works with a simple Create Table.  But at least I can make it work.

    Thanks for your suggestions, Kent.

  • krmilligan
    krmilligan
    450 Posts

    Re: FieldProc issue when called from SQL

    ‏2014-09-09T15:00:33Z  
    • dtrotter
    • ‏2014-09-08T19:05:01Z

    I opened a PMR with IBM Support on this issue and after quite a bit of testing at IBM's request, I have a work-around.

    At IBM's suggestion, I dropped the fieldproc with Alter Table, then re-added it with Alter Table..  While this worked, I would like to see a resolution so that it works with a simple Create Table.  But at least I can make it work.

    Thanks for your suggestions, Kent.

    Thanks for the update - can you share the PMR number?

  • dtrotter
    dtrotter
    5 Posts

    Re: FieldProc issue when called from SQL

    ‏2014-09-09T15:37:05Z  

    Thanks for the update - can you share the PMR number?

    PMR # is  27199,122,000

  • dtrotter
    dtrotter
    5 Posts

    Re: FieldProc issue when called from SQL

    ‏2015-02-03T14:44:38Z  
    • dtrotter
    • ‏2014-09-09T15:37:05Z

    PMR # is  27199,122,000

    IBM has approved the following PTFs for this issue:

    V7R1M0 -- SI55381
    V7R2M0 -- SI55382