--######################################################################################### -- -- Module: spl_lib1.spl -- Author: Peter R. Schmidt -- Description: Stored Procedure Library 1: -- --######################################################################################### -- -- Module: get_note1.spl -- Author: Peter R. Schmidt -- Description: Stored Procedure: Get va_note_text_h and va_note_text_d -- -- Change Log -- -- Date Person Description -- -- 09/16/99 Peter Schmidt Started program -- ------------------------------------------------------------------------------------------- drop procedure get_note1; create procedure get_note1 (serial_id integer) returning varchar(40); define l_note_text varchar(40); define l_note_seq integer; -- Note: this traps all errors, which suppresses the default error handling -- Right now, I prefer the default error handling. -- define esql, eisam integer; -- ON EXCEPTION -- set esql, eisam -- let l_note_text = "Esql error: " || esql || ", Isam error: " || eisam; -- return l_note_text; -- END EXCEPTION foreach select note_text, note_seq into l_note_text, l_note_seq from va_note_text_h, va_note_text_d where note_id = serial_id and fk_note_id = note_id order by note_seq return l_note_text with resume; end foreach end procedure; --######################################################################################### -- -- Module: insert_note1.spl -- Author: Peter R. Schmidt -- Description: Stored Procedure: Insert into va_note_text_h ONLY -- Execute insert_note2.spl to insert the rest of the text. -- -- Change Log -- -- Date Person Description -- -- 09/16/99 Peter Schmidt Started program -- ------------------------------------------------------------------------------------------- drop procedure insert_note1; create procedure insert_note1 (note_source char(18)) returning integer; define serial_id integer; insert into va_note_text_h values (0,note_source); -- Insert header record let serial_id = DBINFO('sqlca.sqlerrd1'); -- get serial_id of last row inserted return serial_id; end procedure; --######################################################################################### -- -- Module: insert_note2.spl -- Author: Peter R. Schmidt -- Description: Stored Procedure: Insert into va_note_text_d ONLY -- This is intended to be called after executing insert_note1.spl -- and would be called one time for each line of text. -- Each text field must contain < 256 chars. -- -- Change Log -- -- Date Person Description -- -- 09/16/99 Peter Schmidt Started program -- ------------------------------------------------------------------------------------------- drop procedure insert_note2; create procedure insert_note2 (serial_id integer, seq integer, note_text varchar(255) ) insert into va_note_text_d values (serial_id, seq, note_text); end procedure; --######################################################################################### -- -- Module: insert_note3.spl -- Author: Peter R. Schmidt -- Description: Stored Procedure: Insert into va_note_text_h and va_note_text_d -- This version will only store a single 255 char text field. -- -- Change Log -- -- Date Person Description -- -- 09/16/99 Peter Schmidt Started program -- ------------------------------------------------------------------------------------------- drop procedure insert_note3; create procedure insert_note3 (note_source char(18), note_text varchar(255)) returning integer; define serial_id integer; insert into va_note_text_h values (0,note_source); -- Insert header record let serial_id = DBINFO('sqlca.sqlerrd1'); -- get serial_id of last row inserted insert into va_note_text_d values (serial_id, 0, note_text); return serial_id; end procedure; --######################################################################################### -- -- Module: delete_note1.spl -- Author: Peter R. Schmidt -- Description: Stored Procedure: Delete from va_note_text_d ONLY -- -- Change Log -- -- Date Person Description -- -- 09/16/99 Peter Schmidt Started program -- ------------------------------------------------------------------------------------------- drop procedure delete_note1; create procedure delete_note1 (serial_id integer) delete from va_note_text_d where fk_note_id = serial_id; end procedure; --######################################################################################### -- -- Module: delete_note2.spl -- Author: Peter R. Schmidt -- Description: Stored Procedure: Delete from va_note_text_d and va_note_text_h -- -- Change Log -- -- Date Person Description -- -- 09/16/99 Peter Schmidt Started program -- ------------------------------------------------------------------------------------------- drop procedure delete_note2; create procedure delete_note2 (serial_id integer) delete from va_note_text_h where note_id = serial_id; delete from va_note_text_d where fk_note_id = serial_id; end procedure; --######################################################################################### -- -- Module: insert_note1_ORIG.spl -- Author: Peter R. Schmidt -- Description: Stored Procedure: Insert into va_note_text_h and va_note_text_d -- -- Note: this procedure was designed to write a string > 256 char. It might have worked, -- except you can't pass a string large then 256 anyway. So what good is it ? -- Saved here for posterity. The substr thing worked OK. -- -- Change Log -- -- Date Person Description -- -- 09/16/99 Peter Schmidt Started program -- 09/17/99 Peter Schmidt Deleted program - replace with newer version -- ------------------------------------------------------------------------------------------- -- --drop procedure insert_note1; -- --create procedure insert_note1 (note_source char(18), note_text char(4000)) returning integer; -- -- define serial_id, x, y, seq, len, len2 integer; -- define str40 char(40); -- -- let seq = 0; -- let len = LENGTH(note_text); -- -- insert into va_note_text_h values (0,note_source); -- Insert header record -- -- let serial_id = DBINFO('sqlca.sqlerrd1'); -- get serial_id of last row inserted -- -- for x = 1 to len step 40 -- -- let seq = seq + 1; -- let y = x + 39; -- -- if (y > len) then -- let y = len; -- end if -- -- let len2 = ((y-x)+1); -- let str40 = substr(note_text,x,len2); -- -- insert into va_note_text_d values (serial_id,seq,str40); -- -- if (y >= len) then -- exit for; -- end if -- -- end for -- -- return serial_id; -- --end procedure; -- --######################################################################################### -- -- Module: substr.spl -- Author: Peter R. Schmidt (from program by Vince Nichols) -- Description: Stored Procedure: Create a sub-string -- -- Change Log -- -- Date Person Description -- -- 09/16/99 Peter Schmidt Started program -- ------------------------------------------------------------------------------------------- DROP PROCEDURE substr; CREATE PROCEDURE substr(str VARCHAR(255), pos INTEGER, len INTEGER DEFAULT 0) RETURNING VARCHAR(255); DEFINE i INTEGER; DEFINE length_str INTEGER; DEFINE retstr VARCHAR(255); IF str IS NULL OR pos IS NULL OR len IS NULL THEN RETURN NULL; END IF; LET retstr = ''; -- len can't be less then 0. IF len < 0 THEN RETURN retstr; END IF; -- pos can't be equal to 0. IF pos = 0 THEN RETURN retstr; END IF; -- if pos is possitive the counting is done from the begining of str to the right. IF pos > 0 THEN IF pos > 1 THEN FOR i = 2 TO pos LET str = str[2,255]; END FOR; END IF; FOR i = 1 TO len LET retstr = retstr || str[1,1]; LET str = str[2,255]; END FOR; -- if pos is negative the counting is done from the end of str to the left. ELIF pos < 0 THEN LET length_str = length(str); LET pos = length_str + pos + 1; IF pos > 1 THEN FOR i = 2 TO pos LET str = str[2,255]; END FOR; END IF; FOR i = 1 TO len LET retstr = retstr || str[1,1]; LET str = str[2,255]; END FOR; END IF; RETURN retstr; END PROCEDURE; --######################################################################################### -- -- Module: clipped.spl -- Author: Peter R. Schmidt -- Description: Stored Procedure: do clipped -- -- Change Log -- -- Date Person Description -- -- 09/16/99 Peter Schmidt Started program -- ------------------------------------------------------------------------------------------- drop procedure clipped; create procedure clipped (str char(4096)) returning char(4096); define ret_val char(4096); define len, i integer; define null_char char(1); let ret_val = ""; let null_char = ""; let len = LENGTH(str); FOR i = 1 TO 4000 if (i <= len) then LET ret_val = ret_val || str[1,1]; else LET ret_val = ret_val || null_char; end if LET str = str[2,4000]; END FOR; return ret_val; end procedure; --#########################################################################################