Solved

DB2 CURSORS

Posted on 2010-11-11
9
1,435 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:10Pints
  • 5
  • 3
9 Comments
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
you will have to set all fields, something like

update
set col1 = case when str1='field1' the str2 else col1 end
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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

0
 

Author Comment

by:10Pints
Comment Utility

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
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
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
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:10Pints
Comment Utility
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?
0
 

Author Comment

by:10Pints
Comment Utility
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
0
 

Author Comment

by:10Pints
Comment Utility
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
0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
Comment Utility
Hi 10pints,

Here's an article on using DB2 recursive SQL to do that:

  http://www.experts-exchange.com/Database/DB2/A_3629-Recursive-SQL-in-DB2-Converting-Columns-to-Rows.html

If you want to use this as a learning exercise, great!  Otherwise, I suggest that it's overkill.

If you're trying to parse a parameter string that looks something like this:

  A=1,B=2,C=3

All you really want to do is loop on the string.

The code below is 90% of it.  It needs some sanity checks and tests of the last name/value pair (as there is always 1 pair that doesn't have a comma after it).


Good Luck,
Kent


DECLARE pstart       integer;

  DECLARE vstart       integer;

  DECLARE cpos         integer;

  DECLARE searchstring varchar (200) = 'A=1,B=2,C=3';

  DECLARE Name         varchar (50);

  DECLARE Value        varchar (50);



  SET pstart = 1;



  WHILE (pstart > 0)

  LOOP

    SET cpos = locate (search_string, ',');   -- find the next ','

    SET vstart = locate (search_string, '='); -- find the next '='

    SET Name = substr (searchstring, 1, vstart-1);    

    SET Value = substr (searchstring, vstart+1, cpos-vstart-1);

  END LOOP;

Open in new window

0
 

Author Closing Comment

by:10Pints
Comment Utility
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...
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now