Results 1 to 1 of 1
  1. #1
    Join Date
    Jun 2010
    Posts
    1

    Unanswered: Setting Row Datatype on SP in Java

    The following is what I have for my stored procedure and it is all working well.

    Code:
    CREATE TYPE occurrenceRow AS ROW (
    	CLHO_OCC_CODE CHAR(2),
          	CLHO_OCC_FROM_DT DATE,
          	CLHO_OCC_TO_DT DATE)
    
    CREATE TYPE occurrence_obj AS occurrenceRow ARRAY[4]
    
    CREATE PROCEDURE ins_occurrence_code (IN ECTN CHAR(12), IN OCC_CODES occurrence_obj)
    BEGIN
    	INSERT INTO OCCURENCE_CODE
    		SELECT ECTN, 
    		T.OCC_CD,T.CLHO_OCC_FROM_DT,T.CLHO_OCC_TO_DT FROM UNNEST(OCC_CODES) 
    		AS T(OCC_CD, CLHO_OCC_FROM_DT, CLHO_OCC_TO_DT);
    END
    Here is my test for the Stored Procedure, it works as well:

    Code:
    begin 
    	DECLARE VAR_OCCURENCE_CODES occurrence_obj;
    	SET VAR_OCCURENCE_CODES[1] = (select '01','2010-06-01','2010-06-01' FROM SYSIBM.SysDummy1);
    	SET VAR_OCCURENCE_CODES[2] = (select '02','2010-06-02','2010-06-02' FROM SYSIBM.SysDummy1);
    	SET VAR_OCCURENCE_CODES[3] = (select '03','2010-06-03','2010-06-03' FROM SYSIBM.SysDummy1);
    	SET VAR_OCCURENCE_CODES[4] = (select '04','2010-06-04','2010-06-04' FROM SYSIBM.SysDummy1);
    	CALL ins_occurrence_code('0123456789AB', VAR_OCCURENCE_CODES);
    end
    Where I'm stuck is setting the "occurrence_obj" type on the prepared statement inside Java. I can't find any mention of setting a custom type Array. Any references would be great.

    edit: I've posted this question here as well
    Last edited by Emmy2; 06-09-10 at 11:48.

Posting Permissions

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