Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    70

    Unanswered: Stored Procedure and input arrays (formated string)

    I have to make a procedure that will take an "array" of VARCHAR(255)'s. Format is just comma delimited. How do I parse it though?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Here's a UDF that returns a table from a comma separated list :

    CREATE FUNCTION CSV_TABLE(INLIST VARCHAR(1000))
    RETURNS TABLE(LISTVAL CHAR(10))
    BEGIN ATOMIC
    RETURN
    WITH TEMP1(STR1) AS
    (
    VALUES(REPLACE(INLIST||',''''','''',''))
    )
    ,
    TEMP2(STR3,STR4) AS
    (
    SELECT LEFT(STR1,POSSTR(STR1,',')-1),SUBSTR(STR1,POSSTR(STR1,',')+1) FROM TEMP1
    UNION ALL
    SELECT LEFT(STR4,POSSTR(STR4,',')-1),SUBSTR(STR4,POSSTR(STR4,',')+1) FROM TEMP2 WHERE LENGTH(STR4)>0
    )
    SELECT STR3 FROM TEMP2 ;
    END


    Usage :


    select upper(col1) from table(csv_table('London,Leeds,York')) tab1(col1)

    returns

    col1
    -------
    London
    Leeds
    York

    HTH

    Sathyaram

    Quote Originally Posted by JamesAvery22
    I have to make a procedure that will take an "array" of VARCHAR(255)'s. Format is just comma delimited. How do I parse it though?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2004
    Posts
    70
    Thanks sathyaram_s,
    always love the lightning fast replies

  4. #4
    Join Date
    Jan 2004
    Posts
    70
    Whats the max VARCHAR size?

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Refer to the 'SQL Limits' section in DB2 Manuals.

    Quote Originally Posted by JamesAvery22
    Whats the max VARCHAR size?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jan 2004
    Posts
    70
    "Maximum length of VARCHAR (in bytes) 32739"

    http://publib.boulder.ibm.com/infoce...mstlimtabs.htm

  7. #7
    Join Date
    Jan 2004
    Posts
    70
    How about this?

    Code:
    CREATE PROCEDURE CleanUp(IN IN_NameArray VARCHAR(8192),
    						IN IN_commitcount integer)
    	modifies sql data
    	not deterministic
    	language sql
    BEGIN
    	declare sqlcode integer default 0;
    	declare mysqlcode int;
    	declare VAR_name varchar(255);
    	declare deletedcount integer;	
    	
    	declare CURSOR_name cursor with hold for
    	 	with t2(ordinal, indx) as 
    			(values ( 0, 0 ) 
    			union all 
    			select ordinal+1, coalesce(nullif(locate(',', IN_NameArray, indx+1), 0),length(IN_NameArray)+1) 
    				from t2 where ordinal < 10000 and locate(',', IN_NameArray, indx+1) <> 0 ),
    		t(ordinal, indx) as        
    			(select max(ordinal)+1, length(IN_NameArray)+1 from t2
    			union all 
    			select ordinal, indx from t2 ),
    		mytable(elems) as
    			(select substr(IN_NameArray, t1.indx+1, t.indx - t1.indx - 1) from t as t1 join t as t on t.ordinal = t1.ordinal+1)
    		select elems from mytable;
    	 
    	open CURSOR_name;
    	set mysqlcode = sqlcode;
    	  
    	set deletedcount = 0;
    	while ( mysqlcode = 0 ) do
    		fetch CURSOR_name into VAR_name;
    		set mysqlcode = sqlcode;
    		if mysqlcode <> 100 then
    			DELETE FROM MyTable WHERE name = VAR_name;	
    			set deletedcount = deletedcount + 1; 
    			if deletedcount = IN_commitcount then
    				commit;
    				set deletedcount = 0; 
    			end if;
    		end if;
    	end while;
    	
    	commit;
    	
    	close CURSOR_name;	
    END
    ;

    Taken from:
    http://www-128.ibm.com/developerwork...03stolze1.html
    Last edited by JamesAvery22; 02-16-06 at 15:54.

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Thanks for sharing your code ..

    I would recommend my solution because everything's done in one pass instead of two...

    Cheers

    Sathyaram


    Quote Originally Posted by JamesAvery22
    How about this?

    Code:
    CREATE PROCEDURE CleanUp(IN IN_NameArray VARCHAR(8192),
                            IN IN_commitcount integer)
        modifies sql data
        not deterministic
        language sql
    BEGIN
        declare sqlcode integer default 0;
        declare mysqlcode int;
        declare VAR_name varchar(255);
        declare deletedcount integer;    
     
        declare CURSOR_name cursor with hold for
             with t2(ordinal, indx) as 
                (values ( 0, 0 ) 
                union all 
                select ordinal+1, coalesce(nullif(locate(',', IN_NameArray, indx+1), 0),length(IN_NameArray)+1) 
                    from t2 where ordinal < 10000 and locate(',', IN_NameArray, indx+1) <> 0 ),
            t(ordinal, indx) as        
                (select max(ordinal)+1, length(IN_NameArray)+1 from t2
                union all 
                select ordinal, indx from t2 ),
            mytable(elems) as
                (select substr(IN_NameArray, t1.indx+1, t.indx - t1.indx - 1) from t as t1 join t as t on t.ordinal = t1.ordinal+1)
            select elems from mytable;
     
        open CURSOR_name;
        set mysqlcode = sqlcode;
     
        set deletedcount = 0;
        while ( mysqlcode = 0 ) do
            fetch CURSOR_name into VAR_name;
            set mysqlcode = sqlcode;
            if mysqlcode <> 100 then
                DELETE FROM MyTable WHERE name = VAR_name;    
                set deletedcount = deletedcount + 1; 
                if deletedcount = IN_commitcount then
                    commit;
                    set deletedcount = 0; 
                end if;
            end if;
        end while;
     
        commit;
     
        close CURSOR_name;    
    END
    ;

    Taken from:
    http://www-128.ibm.com/developerwork...03stolze1.html
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Jan 2004
    Posts
    70
    Quote Originally Posted by sathyaram_s
    Thanks for sharing your code ..

    I would recommend my solution because everything's done in one pass instead of two...

    Cheers

    Sathyaram

    I actually get a SQL1585N error when I try to run yours. DBAs are stuck on something else right now so I just tried that other way =\


    edit-----------------

    Just got this from the admin

    "System temporray tablespace can't be increased becasue it is filesystem growth automatically taken care by system.."

    So why does that way require more tablespace?
    Last edited by JamesAvery22; 02-16-06 at 18:01.

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    FYI, It's not the tablespace size, but the tablespace pagesize that matters ..

    Hmm .. but have to look further to identify the mininmum pagesize that this will require ..

    Thanks

    Sathyaram

    Quote Originally Posted by JamesAvery22
    I actually get a SQL1585N error when I try to run yours. DBAs are stuck on something else right now so I just tried that other way =\


    edit-----------------

    Just got this from the admin

    "System temporray tablespace can't be increased becasue it is filesystem growth automatically taken care by system.."

    So why does that way require more tablespace?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •