what language does the stored procedure use?

Nov 29, 2009 at 7:57 AM

any spec or samples?

Coordinator
Nov 29, 2009 at 1:00 PM
Edited Nov 29, 2009 at 1:12 PM

We don't have specs yet but its a subset of what HSQL has http://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html. CLR SPs are not supported.

You may be able to get an idea by following SP (which is used internaly for LOB support),

CREATE PROCEDURE ALLOC_BLOCKS (IN B_COUNT INT, IN B_OFFSET INT, IN L_ID BIGINT)
 
 MODIFIES SQL DATA BEGIN ATOMIC 
DECLARE LOB_ADDR INT DEFAULT NULL; 
DECLARE REMAINING_COUNT INT DEFAULT 0; 
DECLARE BL_ADDR INT DEFAULT NULL;

DECLARE TEMP_COUNT INT DEFAULT 0;

DECLARE BL_OFFSET INT DEFAULT 0; 
SET REMAINING_COUNT = B_COUNT; 
SET BL_OFFSET = B_OFFSET;

 
MAIN_LOOP: LOOP

  
SET BL_ADDR = (SELECT BLOCK_ADDR FROM BLOCKS WHERE BLOCK_COUNT = B_COUNT AND TX_ID = 0 FETCH 1 ROW ONLY);

  
IF BL_ADDR IS NOT NULL THEN   
 CALL CONVERT_BLOCK (BL_ADDR, REMAINING_COUNT, BL_OFFSET, L_ID);   
 IF LOB_ADDR IS NULL THEN    
 SET LOB_ADDR = BL_ADDR;  
  END IF;  
 LEAVE MAIN_LOOP;  
END IF;  
 SET (BL_ADDR, TEMP_COUNT) = (SELECT BLOCK_ADDR, BLOCK_COUNT FROM BLOCKS WHERE BLOCK_COUNT < B_COUNT AND TX_ID = 0 FETCH 1 ROW ONLY);

   IF BL_ADDR IS NOT NULL THEN

    CALL CONVERT_BLOCK (BL_ADDR, REMAINING_COUNT, BL_OFFSET, L_ID);   
IF LOB_ADDR IS NULL THEN
      SET LOB_ADDR = BL_ADDR;  
  END IF;

    SET REMAINING_COUNT = REMAINING_COUNT - TEMP_COUNT; 
   SET BL_OFFSET = BL_OFFSET + TEMP_COUNT;   
 SET BL_ADDR = NULL;   
SET TEMP_COUNT = 0;
   ELSE

    CALL CREATE_EMPTY_BLOCK (BL_ADDR, REMAINING_COUNT);

    CALL CONVERT_BLOCK (BL_ADDR, REMAINING_COUNT, BL_OFFSET, L_ID);  
  IF LOB_ADDR IS NULL THEN
     SET LOB_ADDR = BL_ADDR;    
 LEAVE MAIN_LOOP;
 
   END IF;
   END IF; 
END LOOP MAIN_LOOP;
END;

 

Nov 29, 2009 at 1:27 PM

wow, really amazingly powerful, really impressive. does it support cursor and CLR? hsqldb's sp supports java, it would be great for effiproz support clr in sp.

Coordinator
Nov 29, 2009 at 2:46 PM

Doesn't support cursors inside SPs or CLR SPs yet, though we had partial CRL (doesn't expose a DbConnection object) support in previous release . I agree that would be nice to add full CLR support in future.