DO NOT REPLY [Bug 11372] - Using Ant for Creating Procedures in DB2 database

[bugzilla]

DO NOT REPLY TO THIS EMAIL, BUT PLEASE POST YOUR BUG 
RELATED COMMENTS THROUGH THE WEB INTERFACE AVAILABLE AT
<http://nagoya.apache.org/bugzilla/show_bug.cgi?id=11372>.
ANY REPLY MADE TO THIS MESSAGE WILL NOT BE COLLECTED AND 
INSERTED IN THE BUG DATABASE.

http://nagoya.apache.org/bugzilla/show_bug.cgi?id=11372

Using Ant for Creating Procedures in DB2 database





------- Additional Comments From [EMAIL PROTECTED]  2002-08-01 16:42 -------
We r having problem, when we use the ANT for creating the Stored Procedures in 
DB2 we need to format the Script code like this...

CREATE PROCEDURE PKG_RETRIEVE_PROCESS_EVENT_Q_AKC
-- This stored procedure is used to retrive an event from the process event 
queue
-- for the property AKC. The event retrieved is the oldest event (out of the 
remaining
-- events in the queue) which satisfies the given criteria. Once the event is 
selected,
-- its status is updated to "In Process" accordingly. 
(       
    IN in_SELECTSTATUS          CHAR(2),
    IN in_UPDATESTATUS          CHAR(2),
    IN in_SELECTSOURCE          CHAR(2),
    OUT out_EVENTID             BIGINT,
    OUT out_PROPERTYCODE        CHAR(3),
    OUT out_EVENT               CHAR(254),
    OUT out_SOURCE              CHAR(2),
    OUT out_CAMPAIGNPROCESSED   CHAR(254)
)
LANGUAGE SQL 
BEGIN
    -- Initialize out parameter variables
    SET out_EVENTID = 0; --
    SET out_PROPERTYCODE = ''; --
    SET out_EVENT = ''; --
    SET out_SOURCE = ''; --
    SET out_CAMPAIGNPROCESSED = ''; --
    -- Select the event from the queue
    SELECT I_EVENTID, C_PROPERTYCODE, C_EVENT, C_SOURCE, C_CAMPAIGNPROCESSED 
INTO out_EVENTID, out_PROPERTYCODE, out_EVENT, out_SOURCE, 
out_CAMPAIGNPROCESSED FROM PROCESSEVENTQAKC WHERE D_CREATED = ( SELECT MIN
(D_CREATED) FROM PROCESSEVENTQAKC ) AND C_STATUS = in_SELECTSTATUS AND C_SOURCE 
= in_SELECTSOURCE; --
    -- Update the event from the queue with the "In Process" status
    UPDATE PROCESSEVENTQAKC SET C_STATUS = in_UPDATESTATUS, D_TIMESTAMP = 
CURRENT TIMESTAMP WHERE I_EVENTID = out_EVENTID; END
;


Rather than having the Script like this

CREATE PROCEDURE PKG_SUMMARIZE_ENTRY
(       IN in_drawingId                 INTEGER,
        IN in_winetId                   BIGINT,
        IN in_totalEntrySummary         INTEGER,
        IN in_entryId                   INTEGER
)
-- This stored procedure summarizes entries for a patron at the drawing level.
-- A new row is inserted in the Entry Summary table for a patron in case the 
-- entry summarization for a drawing happens for the first time.
-- An existing row is updated with a new entry count in case there already 
exists
-- a row for a patron for a drawing in the EntrySummary table.
-- A row is inserted in the EntryUsedInSummary table for each drawing the entry 
-- has been summarized for.
LANGUAGE SQL

BEGIN
        
        -- The insert is executed in case the following Update statement fails.
        DECLARE CONTINUE HANDLER FOR NOT FOUND 
        INSERT INTO
                EntrySummary(
                        i_drawingId,
                        i_winetId,
                        i_totalEntrySummary,
                        d_dateSummarized
                )
                VALUES(
                        in_drawingId,
                        in_winetId,
                        in_totalEntrySummary,
                        CURRENT TIMESTAMP
                ); 
        
        -- The Update statement is the first statement to be executed.
        UPDATE
                EntrySummary
        SET
                i_totalEntrySummary = i_totalEntrySummary + 
in_totalEntrySummary,
                d_dateSummarized = CURRENT TIMESTAMP
        WHERE 
                i_drawingId = in_drawingId
        AND
                i_winetId = in_winetId; 

        -- The Insert is executed at all times irrespective of which statement 
above gets executed.
        INSERT INTO
                EntryUsedInSummary(
                        i_entryId,
                        i_drawingId
                )
                VALUES
                (
                        in_entryId,
                        in_drawingId
                ); 
END

--
To unsubscribe, e-mail:   <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>