Link to home
Start Free TrialLog in
Avatar of 10Pints
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.
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

Open in new window

Avatar of momi_sabag
momi_sabag
Flag of United States of America image

you will have to set all fields, something like

update
set col1 = case when str1='field1' the str2 else col1 end
Avatar of Kent Olsen
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
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;

Open in new window

Avatar of 10Pints
10Pints

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
Avatar of 10Pints

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?
Avatar of 10Pints

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
Avatar of 10Pints

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
ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 10Pints

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