Back to Tylogix Home Page

Back to Tylogix SQL Page

------------------------------------------------------------------------------

--  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:

      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 LOOP  ;

  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'

 

Go Back to the Top