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

[bu...@apache.org]

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 vsampath@sapient.com  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:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>