10Pints
asked on
DB2 CURSORS
Hi
I want to pass - and handle -"variable arguments" in a DB2 stored procedure.
I can pas them as a string and parse the string OK so get a row-set of attribute name/value pairs .
thing is I now wnat to be able to set fields in a table like
when the name is 'x' set field y to value.
I guess i need a cursor and a case statement - but I am having real problems with this - can't seem to find a decent example where the cursor is a rowset not an individual field.
I want to pass - and handle -"variable arguments" in a DB2 stored procedure.
I can pas them as a string and parse the string OK so get a row-set of attribute name/value pairs .
thing is I now wnat to be able to set fields in a table like
when the name is 'x' set field y to value.
I guess i need a cursor and a case statement - but I am having real problems with this - can't seem to find a decent example where the cursor is a rowset not an individual field.
drop procedure testx<<
create procedure testx()
LANGUAGE SQL
BEGIN
DECLARE TABLE x AS
(
ordinal integer,
nam VARCAHR(50),
val(50),
);
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE cursor CURSOR WITH RETURN TO CALLER FOR
-- example of a possible variable input
-- GetNVPairs(string) is my UDF which will parse the string into a table of (nam, val) pairs
select * from TABLE(GetNVPairs('field_1:val_1|field_2:val_2x|field_3:val_3y'));
-- want to do something like 'dynamically' insert into table_y ( field_1, field_2, field_3) values (val_1, val_2, val_3)
-- but how?
OPEN cursor;
fetch cursor into x;
while SQLCODE <> 100
do
fetch cursor into x;
end while;
close cursor;
END
Hi 10pints,
A cursor represents a single row within a result set. If I understand your question, I think that you want multiple columns in the set?
Kent
A cursor represents a single row within a result set. If I understand your question, I think that you want multiple columns in the set?
Kent
DECLARE time1 timestamp;
DECLARE time2 timestamp;
DECLARE mycursor CURSOR FOR
SELECT start_time, end_time FROM mytable;
OPEN mycursor;
FETCH mycursor INTO time1, time2;
CLOSE mycursor;
ASKER
thanks gentlemen:
I am really struggling with this one.
From Kent's input I see something I missed: so you can select multiple fields into a cursor
and assign them to multiple variables.
Right that gets me over the first hurdle so I can define a pair of variable for the name and the value data
But then I need to iterate the rows dont I and "switch" on the data: especial the field name
My scenario is:
I have a stored procedure (SP) that gets called from c++ code and it has to create a parent item and any number of child depenendncies.
the parent file item will be 8 fields or so including a unique ID, the child items will just be the child file names.
The parent item will be added to a file table,
the child items are added to another table: a simple link table that will relate the parent to its child items.
Now the number of children is variable, and our SP handler code will go to max of 8 parameters.
So when iterating the parameter string I will get a table of name value pairs from the strin parser UDF: GetNVPairs( params string).
so I need to run a select on this and look at each attribute name and decide what to do with it:
So in the SP I thought I could declare a cursor on the name value table returned by GetNVPairs
iterate each row ( field name. value)
if it is the parent filename : set the parent name field to the value
else if it say the id set the ID field and so on for the 8 parent fields
Then the remaining parameters will be added as separate ros to the child dependency table along with the parent Id - to save the dependency information.
Also I would like to use this variable args idea will be used for other SPs.
Thanks for your help - much appreciated!
T
Hi 10Pints,
My thought is that you're making this harder than it has to be, and possibly affecting performance as well.
From your description, it sounds like you need two tables (parent and child) with a foreign key relationship between them. Each row in the child table should contain the primary key of the parent row that it is associated with.
As far as the stored procedure goes, loop on the input string to find the name/value pairs. That's a lot less complicated and more efficient than having another SP parse the string into a table or even having a UDF process the same string repeatedly looking for successive items.
Kent
My thought is that you're making this harder than it has to be, and possibly affecting performance as well.
From your description, it sounds like you need two tables (parent and child) with a foreign key relationship between them. Each row in the child table should contain the primary key of the parent row that it is associated with.
As far as the stored procedure goes, loop on the input string to find the name/value pairs. That's a lot less complicated and more efficient than having another SP parse the string into a table or even having a UDF process the same string repeatedly looking for successive items.
Kent
ASKER
Interesting - the samples I found for parsing strings all use a table and recursion - which is a right pain
si I can use a function or SP and do a procedural style processing of the string - thta sounds too good to be true! So could use Locate() and substr in loop then?
si I can use a function or SP and do a procedural style processing of the string - thta sounds too good to be true! So could use Locate() and substr in loop then?
ASKER
Got it running - using the recursive approach - only just fathomed how to debug DB2 SPs using VS2088
That is a GREAT help!
All I need to do now is to call one SP from another and handle the return result set. (Any tips would be good!).
I find the IBM help verbose and mot very helpful - seems a sever lack of good examples - unless you are lucky in your search. Many thanks
T
That is a GREAT help!
All I need to do now is to call one SP from another and handle the return result set. (Any tips would be good!).
I find the IBM help verbose and mot very helpful - seems a sever lack of good examples - unless you are lucky in your search. Many thanks
T
ASKER
Right, i think the approach will be to parse the varaible name/value pairs from a string parameter into create a temporary table using a function like;
CREATE FUNCTION elemIdx ( string CLOB(64K) )
RETURNS TABLE ( ordinal INTEGER, a INTEGER, b INTEGER, c INTEGER)
LANGUAGE SQL
CONTAINS SQL
BEGIN ATOMIC
RETURN
WITH t(ordinal, a, b, c) AS
(
VALUES ( 0, 1, LOCATE(':', string, 1), LOCATE('|', string, 1 ))
UNION ALL
SELECT ordinal+1, c+1, LOCATE(':', string, c+1), COALESCE( NULLIF( LOCATE('|', string, c+1), 0), LENGTH(string)+1)
FROM t
WHERE ordinal < 10000 AND LOCATE(':', string, c+1) <> 0 -- terminate if there are no further delimiters remaining
)
SELECT ordinal, a, b, c
FROM t;
END
then to have an SP use this like;
REATE PROCEDURE GetNVPairs(str CLOB(64K))
LANGUAGE SQL
RESULT SETS 1
P1:BEGIN
DECLARE X integer;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE v_a VARCHAR(250);
DECLARE v_b VARCHAR(250);
DECLARE GLOBAL TEMPORARY TABLE SESSION.ITEMS
(
ordinal integer,
name VarChar(50),
val VarChar(50)
)
WITH REPLACE
ON COMMIT
PRESERVE ROWS
NOT LOGGED;
BEGIN
DECLARE iordinal integer;
DECLARE ia integer;
DECLARE ib integer;
DECLARE ic integer;
DECLARE cur CURSOR FOR SELECT * FROM TABLE(elemIdx( str)) ORDER BY ordinal;
DECLARE cur2 CURSOR WITH RETURN TO CALLER FOR SELECT * FROM SESSION.ITEMS;
DELETE FROM SESSION.ITEMS;
OPEN cur;
FETCH FROM cur INTO iordinal, ia, ib, ic;
WHILE(SQLSTATE = '00000') DO
IF ((ia <> 0) AND ( ib <> 0) AND ( ic <> 0)) then
INSERT INTO SESSION.items(ordinal, name, val) VALUES(iordinal, SUBSTR( str, ia, ib-ia), SUBSTR( str, ib+1, ic-ib-1));
END IF;
FETCH FROM cur INTO iordinal, ia, ib, ic;
END WHILE;
CLOSE cur;
OPEN cur2;
END;
END P1<<
That all works BUT...
I really would like to make the table returned really temporary so I don't get any clashes - i.deally. thread specific so ~i was thinging of using a with statement in the GetNVPairs, but I am having difficulty with that!
any tip/ - then I reckon I can close this one
CREATE FUNCTION elemIdx ( string CLOB(64K) )
RETURNS TABLE ( ordinal INTEGER, a INTEGER, b INTEGER, c INTEGER)
LANGUAGE SQL
CONTAINS SQL
BEGIN ATOMIC
RETURN
WITH t(ordinal, a, b, c) AS
(
VALUES ( 0, 1, LOCATE(':', string, 1), LOCATE('|', string, 1 ))
UNION ALL
SELECT ordinal+1, c+1, LOCATE(':', string, c+1), COALESCE( NULLIF( LOCATE('|', string, c+1), 0), LENGTH(string)+1)
FROM t
WHERE ordinal < 10000 AND LOCATE(':', string, c+1) <> 0 -- terminate if there are no further delimiters remaining
)
SELECT ordinal, a, b, c
FROM t;
END
then to have an SP use this like;
REATE PROCEDURE GetNVPairs(str CLOB(64K))
LANGUAGE SQL
RESULT SETS 1
P1:BEGIN
DECLARE X integer;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE v_a VARCHAR(250);
DECLARE v_b VARCHAR(250);
DECLARE GLOBAL TEMPORARY TABLE SESSION.ITEMS
(
ordinal integer,
name VarChar(50),
val VarChar(50)
)
WITH REPLACE
ON COMMIT
PRESERVE ROWS
NOT LOGGED;
BEGIN
DECLARE iordinal integer;
DECLARE ia integer;
DECLARE ib integer;
DECLARE ic integer;
DECLARE cur CURSOR FOR SELECT * FROM TABLE(elemIdx( str)) ORDER BY ordinal;
DECLARE cur2 CURSOR WITH RETURN TO CALLER FOR SELECT * FROM SESSION.ITEMS;
DELETE FROM SESSION.ITEMS;
OPEN cur;
FETCH FROM cur INTO iordinal, ia, ib, ic;
WHILE(SQLSTATE = '00000') DO
IF ((ia <> 0) AND ( ib <> 0) AND ( ic <> 0)) then
INSERT INTO SESSION.items(ordinal, name, val) VALUES(iordinal, SUBSTR( str, ia, ib-ia), SUBSTR( str, ib+1, ic-ib-1));
END IF;
FETCH FROM cur INTO iordinal, ia, ib, ic;
END WHILE;
CLOSE cur;
OPEN cur2;
END;
END P1<<
That all works BUT...
I really would like to make the table returned really temporary so I don't get any clashes - i.deally. thread specific so ~i was thinging of using a with statement in the GetNVPairs, but I am having difficulty with that!
any tip/ - then I reckon I can close this one
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Kent: as always you are a great help!
Clear concise and very helpful.
I guess the only solutions I found for DB2 were recursion based, so I 'ASSUMED' it was the way to go...
Clear concise and very helpful.
I guess the only solutions I found for DB2 were recursion based, so I 'ASSUMED' it was the way to go...
update
set col1 = case when str1='field1' the str2 else col1 end