Professional Web Applications Themes

v8.1 - Problem calling SP from UDF - IBM DB2

I understand from other posts that a SP cannot be called from a trigger, and that the recommended solution is to wrap the SP in a UDF. However, I am running into problems calling the SP from my UDF. The SP works as desired, but the UDF does not. My goal is to allow users to "register" with us so that when certain information is modified, they will receive an update. There will be from 0 to n users who register to be updated on a specific chunk of info (represented by a single row in a table). I have ...

  1. #1

    Default v8.1 - Problem calling SP from UDF

    I understand from other posts that a SP cannot be called from a
    trigger, and that the recommended solution is to wrap the SP in a UDF.
    However, I am running into problems calling the SP from my UDF. The SP
    works as desired, but the UDF does not.

    My goal is to allow users to "register" with us so that when certain
    information is modified, they will receive an update. There will be
    from 0 to n users who register to be updated on a specific chunk of
    info (represented by a single row in a table).

    I have included the source of the SP and the UDF below.

    UDF:

    CREATE FUNCTION EXSU.FUNC_UPDATE_NOTIFY( p_solution_id INTEGER )
    SPECIFIC EXSU.FUNC_UPDATE_NOTIFY
    ------------------------------------------------------------------------
    -- SQL UDF (Scalar)
    ------------------------------------------------------------------------
    F1: BEGIN ATOMIC
    DECLARE v_result INTEGER;
    CALL PROC_NOTIFY_UPDATE(p_solution_id, ?);
    END

    SP:

    CREATE PROCEDURE EXSU.PROC_UPDATE_NOTIFY (IN p_solution_id INTEGER,
    OUT p_result INTEGER)
    SPECIFIC EXSU.PROC_UPDATE_NOTIFY
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN

    ------------------------------------------------------------------------
    -- Declare notifications variables
    ------------------------------------------------------------------------
    DECLARE v_notification_id INTEGER;
    DECLARE v_notification_type VARCHAR(15) DEFAULT 'Internet Mail';
    DECLARE v_notification_date DATE;
    DECLARE v_notification_time TIME;
    DECLARE v_notification_ttl VARCHAR(40);
    DECLARE v_notification_text VARCHAR(1024);
    DECLARE v_modify_datetime INTEGER;
    DECLARE v_address VARCHAR(80);
    DECLARE v_num_affected INTEGER DEFAULT 0;

    ------------------------------------------------------------------------
    -- Declare solution variables
    ------------------------------------------------------------------------
    DECLARE v_description VARCHAR(2000);
    DECLARE v_solution CLOB(32700);

    DECLARE SQLSTATE CHAR(5);
    ------------------------------------------------------------------------
    -- Declare cursor
    ------------------------------------------------------------------------
    DECLARE c_recipients CURSOR FOR SELECT DISTINCT(ADDRESS) FROM
    EXSU.NOTIFY_USER_GROUP WHERE PEOPLE_ID IN (SELECT DISTINCT(PEOPLE_ID)
    FROM EXSU.PEOPLE WHERE ACCESS_ID IN (SELECT DISTINCT(ACCESS_ID) FROM
    SOL_UPDATE_NOTIFY WHERE SOLUTION_ID = p_solution_id)) AND
    NOTIFICATION_TYPE = 'Internet Mail';

    SELECT DESCRIPTION, SOLUTION
    INTO v_description, v_solution
    FROM EXSU.SOLUTIONS
    WHERE SOLUTION_ID = p_solution_id;

    SET v_notification_ttl = ('Solution ID' || RTRIM(CAST(p_solution_id AS
    CHAR(10))) || ' has been updated.');
    SET v_notification_text = SUBSTR((v_description || ' ' || v_solution),
    1, 900);
    SET v_notification_text = v_notification_text || CAST(CHR(10)||CHR(13)
    AS CHAR(2)) || 'If this message has been truncated, please view the
    solution on the web.';
    SET v_notification_date = CURRENT DATE;
    SET v_notification_time = CURRENT TIME;
    SET v_modify_datetime = (DAYS(CURRENT DATE) - DAYS('1990-01-01')) *
    86400;

    BEGIN ATOMIC
    OPEN c_recipients;
    FETCH FROM c_recipients INTO v_address;
    WHILE (SQLSTATE = '00000') DO
    SET v_num_affected = v_num_affected + 1;
    SELECT NEXT_NUMBER
    INTO v_notification_id
    FROM EXSU.COUNTERS
    WHERE COUNTER_NAME = 'NOTIFICATIONS';
    UPDATE EXSU.COUNTERS SET NEXT_NUMBER = NEXT_NUMBER + 1 WHERE
    COUNTER_NAME = 'NOTIFICATIONS';
    INSERT INTO EXSU.NOTIFICATIONS ( NOTIFICATION_ID,
    NOTIFICATION_TYPE, NOTIFICATION_DATE, NOTIFICATION_TIME,
    NOTIFICATION_TTL, NOTIFICATION_TEXT, MODIFY_DATETIME, ADDRESS)
    VALUES (v_notification_id, v_notification_type,
    v_notification_date, v_notification_time, v_notification_ttl,
    v_notification_text, v_modify_datetime, v_address);
    FETCH FROM c_recipients INTO v_address;
    END WHILE;
    CLOSE c_recipients;
    DELETE FROM EXSU.SOL_UPDATE_NOTIFY WHERE SOLUTION_ID =
    p_solution_id;
    END;
    SET p_result = v_num_affected;
    END P1

    Thanks to all in advance!!

    -corky
    Corky Guest

  2. #2

    Default Re: v8.1 - Problem calling SP from UDF

    Until we have call from trigger, you can't call a modifies stored proc
    from a udf (can only use read access sps).

    Corky wrote:
     

    Sean Guest

  3. #3

    Default Re: v8.1 - Problem calling SP from UDF

    Can I use a modifies SP from an external UDF? Do you have any
    suggestions on how I might be able to pull this off, or suggest any
    other approaches?

    -corky
    Corky Guest

  4. #4

    Default Re: v8.1 - Problem calling SP from UDF

    Also, if it is not possible to call a SP that modifies data from an
    external UDF, is it allowable for that external UDF to call an .exe
    that will connect and execute the SP?

    I tried using an OLE Automation library as an external UDF, but I keep
    getting errors that I am not allowed to make any connections to the
    DB. My VB OLE code follows:

    Option Explicit

    Dim Conn As Object

    Const SQL_TF_OPEN = -1
    Const SQL_TF_CLOSE = 1
    Const SQL_TF_FETCH = 0


    Public Sub solChanged(solutionID As Long, rc As Long, _
    insolutionID As Integer, inrc As Integer, _
    sqlstate As String, fname As String, fspecname As
    String, msg As String, _
    scratchpad() As Byte, calltype As Long)

    Dim strSQL As String

    If (calltype = SQL_TF_OPEN) Then

    Set Conn = CreateObject("ADODB.Connection")
    Conn.Open "DSN=SUPPORT", "*******", "********"

    ElseIf (calltype = SQL_TF_CLOSE) Then
    Conn.Close
    Set Conn = Nothing

    ElseIf (calltype = SQL_TF_FETCH) Then

    strSQL = "CALL PROC_UPDATE_NOTIFY(" & solutionID & ",?)"
    Conn.Execute strSQL
    End If
    rc = 0
    End Sub

    Thanks for your continued help...it sux being a newbie...

    -corky
    Corky Guest

  5. #5

    Default Re: v8.1 - Problem calling SP from UDF

    Unfortunately, I don't think there's a 'nice' way to do want you want to
    at present (you can't call a modifies sp from any UDF). Your idea to
    connect from a forked exe to drive the update would probably work, but
    you can't connect from the udf itself. You'd have a problem with
    transactional integrity etc (since you'd be running in a seperate app,
    you couldn't commit/rollback the action as part of your UOW). Also,
    you'd want to make sure you were running fenced if you did this.

    Corky wrote: 

    Sean Guest

  6. #6

    Default Re: v8.1 - Problem calling SP from UDF

    Thanks Sean. I think I almost have it, but I'm running into a little
    issue. I created a "wrapper" exe that successfully calls the SP. I've
    tested it from the command line, and hammered it using a couple
    scripts so I know that cuncurrent executions won't be an issue.

    The problem I have, however, is the OLE Automation routine I call from
    an external UDF. For some reason, the .exe is called over and over
    infinitely after the first call. I can watch Task Manager and the .exe
    keeps popping up over and over, each time with a different process ID.

    Maybe my code will shed light on this...I don't see any issue with it.
    Thank you again for all your expert advice, I totally appreciate it,
    and would spring for a couple rounds of drinks if we were ever to
    meet.

    OLE component:

    Option Explicit

    Public Sub solChanged(solutionID As Long, rc As Long, _
    insolutionID As Integer, inrc As Integer, _
    sqlstate As String, fname As String, fspecname As
    String, msg As String)

    Shell "c:\execsolup.exe " & CStr(solutionID), vbHide
    rc = 0
    End Sub

    Function creation:

    create function FUNC_UPDATE_NOTIFY(INTEGER) RETURNS INTEGER SPECIFIC
    FUNC_UPDATE_NOTIFY EXTERNAL NAME 'solutionupdate.solupdate!solChanged'
    NOT DETERMINISTIC EXTERNAL ACTION NOT NULL CALL LANGUAGE OLE PARAMETER
    STYLE DB2SQL NO SQL DISALLOW PARALLEL;
    Corky Guest

  7. #7

    Default Re: v8.1 - Problem calling SP from UDF

    FYI, here's the contents of the executable...

    Sub Main()
    Dim Conn As Object
    Dim cmdln()
    Dim strSQL As String
    Dim strSolution As String
    Dim result As Integer
    cmdln() = GetCommandLine
    If UBound(cmdln) < 1 Then
    Exit Sub
    End If
    strSolution = CStr(cmdln(1))
    Set Conn = CreateObject("ADODB.Connection")
    Conn.Open "DSN=SUPPORT", "****", "****"
    'strSQL = "CALL PROC_UPDATE_NOTIFY(" & strSolution & ",?)"
    Conn.PROC_UPDATE_NOTIFY strSolution, result
    Conn.Close
    Set Conn = Nothing
    End Sub

    Function GetCommandLine(Optional MaxArgs)
    Dim C, CmdLine, CmdLnLen, InArg, i, NumArgs
    If IsMissing(MaxArgs) Then MaxArgs = 10
    ReDim ArgArray(MaxArgs)
    NumArgs = 0: InArg = False
    CmdLine = Command()
    CmdLnLen = Len(CmdLine)
    For i = 1 To CmdLnLen
    C = Mid(CmdLine, i, 1)
    If (C <> " " And C <> vbTab) Then
    If Not InArg Then
    If NumArgs = MaxArgs Then Exit For
    NumArgs = NumArgs + 1
    InArg = True
    End If
    ArgArray(NumArgs) = ArgArray(NumArgs) & C
    Else
    InArg = False
    End If
    Next i
    ReDim Preserve ArgArray(NumArgs)
    GetCommandLine = ArgArray()
    End Function
    Corky Guest

  8. #8

    Default Re: v8.1 - Problem calling SP from UDF

    Corky <com> wrote:
     

    How do you call the UDF, i.e. what's the SQL statement you use?

    A UDF is evaluated for every row in the query/insert statement. If you have
    something like:

    SELECT udf(...)
    FROM t

    you will call the UDF and, thus, your procedure once for each row in the
    table T.

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Guest

  9. #9

    Default Re: v8.1 - Problem calling SP from UDF

    Thanks, Knut. That's exactly what's happening. I think I'll use a use
    a 'where key = (select max(key) from t)

    That should do the trick...
    Corky Guest

  10. #10

    Default Re: v8.1 - Problem calling SP from UDF

    Corky <com> wrote:
     

    Or you call it like this:

    VALUES yourUdf(...);

    or if you like SELECTs better:

    SELECT yourUDF(...)
    FROM sysibm.sysdummy1

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Guest

Similar Threads

  1. Flash calling asp problem
    By ahmultHoAngel in forum Macromedia Flash Data Integration
    Replies: 3
    Last Post: December 22nd, 01:32 AM
  2. Problem calling gdi32 from ASP.NET via Xsl
    By Matt in forum ASP.NET Security
    Replies: 0
    Last Post: November 19th, 09:35 PM
  3. getURL problem when calling by IP
    By DanielRowles in forum Macromedia Flash Actionscript
    Replies: 1
    Last Post: February 26th, 02:50 PM
  4. problem calling by unique id from database in php
    By Vincent J. Gullotta in forum PHP Development
    Replies: 4
    Last Post: January 28th, 06:55 AM
  5. Problem Calling web service from ASP
    By Ken Pinard in forum ASP.NET Web Services
    Replies: 7
    Last Post: December 27th, 11:44 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139