------------------------------------------------------------------------------
-- Test Dynamic SQL Statement Creation
------------------------------------------------------------------------------
--
-- This program will insert 10 consecutive
records in a test table
-- using DYNAMIC SQL TECHIQUE
--
-- Created by: Thibault Dambrine
-- Date: February 2, 2009
--
--******************************************************************************
CREATE PROCEDURE DYNSQLTEST
LANGUAGE SQL
SET OPTION OUTPUT = *PRINT, DBGVIEW = *SOURCE
-- START PROCEDURE
BEGIN
-- DECLARE CURSOR VARIABLES
DECLARE SQLSTATE CHAR(5) DEFAULT '00000' ;
DECLARE SQLCODE INT DEFAULT 0 ;
DECLARE LOOPVAR1 INT ;
DECLARE LOOPTMS TIMESTAMP ;
DECLARE SQLDYNCMD CHAR(250) ;
--------------------------------------------------------------------------------
--
Set SQLVAR initial Value
-- Note the question mark ("?") characters. They are
to be replaced with
-- parameters.
--------------------------------------------------------------------------------
SET SQLDYNCMD
= 'INSERT INTO DYNTABLE VALUES( ?, ? ) ' ;
SET LOOPVAR1 =
0 ;
INS_LOOP:
IF LOOPVAR1 < 10
THEN
SET LOOPVAR1 = LOOPVAR1 +
1 ;
SET LOOPTMS = CURRENT TIMESTAMP ;
PREPARE
DYN_INSERT FROM SQLDYNCMD ;
EXECUTE
DYN_INSERT USING LOOPVAR1, LOOPTMS ;
ELSE
LEAVE INS_LOOP ;
END IF ;
END
END
--------------------------------------------------------------------------------
--
END OF PROCEDURE --
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--
This is the table the program above is writing to
--
(to be compiled from a separate member)
--------------------------------------------------------------------------------
--
--
Test of a dynamic SQL code fragment written using SQL compiled language.
--
-- Created by: Thibault Dambrine
-- Date:
February 2, 2009
--------------------------------------------------------------------------------
CREATE
TABLE DYNTABLE
(
VAR_INT INT ,
VAR_TMS TIMESTAMP
) ;
--------------------------------------------------------------------------------
--
LABEL
ON TABLE DYNTABLE IS 'Test for dynamic SQL'