SEO

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

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:

> 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

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

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

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:
> 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

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;

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 <mowere@konicabt.com> wrote:

> 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.

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

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 <mowere@konicabt.com> wrote:

> 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...

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