Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    Argentina
    Posts
    6

    Unanswered: DB2 UDF C TRIGGER problem.

    Hi everybody,

    I'm working with DB2/NT 7.2 FIXPAK 5. I have a UDF wich calls a C DLL. The DLL is located in my D:\Program Files\SQLLIB\function folder, and the folder is in the PATH environment var.

    OK, here is the problem: When I use interactivley from command center:
    SELECT poner(1,1) from sysibm.sysdummy1;
    - there is no problem, the C program does what i want,

    But when I put a call to the UDF in a trigger nothing happens...

    The trigger text is this:
    CREATE TRIGGER DB2ADMIN.TRGMQ2
    AFTER INSERT ON DB2ADMIN.TBLEJEMPLO
    FOR EACH ROW
    MODE DB2SQL
    BEGIN ATOMIC
    VALUES (poner(1,1) );
    END

    (i also tried in trigger text **SELECT poner(1,1) from sysibm.sysdummy1;** instead of **VALUES (poner(1,1) ); **

    If anybody could help me...

    Thanks a LOT.

    Sudamericano

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The reason you trigger "does nothing" is that you have it doing nothing in particular. Triggers are for:
    1) validating data
    2) checking constraints (when too complicated to do otherwise)
    3) generate values for columns based on values in other tables
    4) change (insert, update, delete) data in other tables based on table data

    Your trigger is not persisting anything. Triggers cannot return result sets.

    What are you trying to do with the trigger?

    Andy

  3. #3
    Join Date
    Jan 2004
    Location
    Argentina
    Posts
    6
    Thanks for your answer Andy,

    What I'm trying to do with the UDF is put some data in a MQSeries queue. I need to put some message when in the table some insert or update occurs. In thiscase the UDF is a function in the database wich invokes a function in a C program.

    I have seen calling a UDF from a trigger is a valid action.

    Well, I'm putting the MQSeries call (in a C lang. dll) in the UDF, and I would like the trigger call the UDF. I'm not interested in returning any result set.

    If I can't call a Stored Procedure in a trigger (only OS/390) and I can't
    make the UDF runs well from the trigger, I really don't know what I could do... maybe I'm not choosing the right way.


    Thanks,
    Sudamericano.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Check out the following link:

    http://salz.is.informatik.uni-duisbu...d/ciiwsmqf.htm

    It might help you.

    Andy

  5. #5
    Join Date
    Jan 2004
    Location
    Argentina
    Posts
    6

    Talking

    Hi,
    Finally I found the solution to my problem.

    CREATE FUNCTION poner(float, float) returns float
    FENCED
    VARIANT
    NO SQL
    EXTERNAL ACTION (**Before I have here "NO EXTERNAL ACTION"**)
    LANGUAGE C
    PARAMETER STYLE DB2SQL
    EXTERNAL NAME 'udf!poner'


    I understood "NO EXTERNAL ACTION" is when you don't want to do nothing the database world. In this case I wanted to put a message in a MQSeries queue , that is, OUTSIDE de "database world"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •