-- Copyright © 1995, 2003, Oracle Corporation.  All Rights Reserved.

-- sql_dynamic_c.sqlmod
--
-- This SQL module provides the SQL procedures needed by the
-- sql_dynamic.c program.
--
-- The module illustrates how to use SQL module language to process
-- and re-execute SQL statements dynamically.
--
-------------------------------------------------------------------------------
-- Header Information Section
-------------------------------------------------------------------------------
MODULE          SQL_DYNAMIC     -- Module name
LANGUAGE        C               -- Language of calling program
AUTHORIZATION   SQL_SAMPLE      -- Provides default authorization id
PARAMETER COLONS                -- Parameter names prefixed with colons
-------------------------------------------------------------------------------
-- DECLARE Statements Section
-------------------------------------------------------------------------------

-- Declare transaction default

-- DECLARE TRANSACTION READ WRITE 

-------------------------------------------------------------------------------
-- Procedure Section
-------------------------------------------------------------------------------

-- This procedure prepares a statement for dynamic execution from the string
-- passed to it.  It also writes information about the number and data type of
-- any select list items in the statement to an SQLDA2 (specifically, 
-- the sqlda_out SQLDA2 passed to the procedure by the calling program). 
-- 

PROCEDURE PREPARE_STMT
    SQLCA
    :DYN_STMT_ID      INTEGER
    :STMT	     CHAR(1024);

    PREPARE :DYN_STMT_ID FROM :STMT;

-- This procedure writes information to an SQLDA (specifically,
-- the sqlda_in SQLDA passed to the procedure by the calling program)
-- about the number and data type of any parameter markers in the
-- prepared dynamic statement.  Note that SELECT statements may also
-- have parameter markers.

PROCEDURE DESCRIBE_SELECT
    SQLCA
    :DYN_STMT_ID    INTEGER
    SQLDA;

    DESCRIBE :DYN_STMT_ID SELECT LIST INTO SQLDA;

PROCEDURE DESCRIBE_PARM
    SQLCA
    :DYN_STMT_ID INTEGER
    SQLDA;

    DESCRIBE :DYN_STMT_ID MARKERS INTO SQLDA;


-- This procedure dynamically executes a non-SELECT statement.
-- SELECT statements are processed by DECLARE CURSOR, OPEN CURSOR,
-- and FETCH statements.
--
-- The EXECUTE statement specifies an SQLDA2 (specifically, 
-- the sqlda_in SQLDA2 passed to the procedure by the calling program) 
-- as the source of addresses for any parameter markers in the dynamic
-- statement.
--
-- Note that the EXECUTE statement with the USING DESCRIPTOR clause
-- also handles statement strings that contain no parameter markers.
-- If a statement string contains no parameter markers, SQL sets
-- the SQLD field of the SQLDA2 to zero.

PROCEDURE EXECUTE_STMT
    SQLCA
    :DYN_STMT_ID INTEGER
    :IN_SQLDA SQLDA
    :OUT_SQLDA SQLDA
    ;
    EXECUTE :DYN_STMT_ID INTO DESCRIPTOR :OUT_SQLDA USING DESCRIPTOR :IN_SQLDA;

-- Declare a cursor to process dynamic SELECT statements

PROCEDURE DECLARE_CURSOR
   SQLCA
   :C_NAME        CHAR(2)
   :DYN_STMT_ID INTEGER;

   DECLARE :C_NAME CURSOR FOR :DYN_STMT_ID;

-- This procedure opens the cursor C_NAME already declared.  It specifies
-- an SQLDA (specifically, the sqlda_in SQLDA2 passed to the procedure 
-- by the calling program) as the source of addresses for any parameter 
-- markers in the cursor's SELECT statement.

PROCEDURE OPEN_CURSOR
    SQLCA
    :C_NAME         CHAR(2)
    SQLDA;

    OPEN :C_NAME USING DESCRIPTOR SQLDA;


-- This procedure fetches a row from the opened cursor and writes it to
-- the addresses specified in an SQLDA2 (specifically, the sqlda_out SQLDA2
-- passed to the procedure by the calling program).

PROCEDURE FETCH_ROW
    SQLCA
    :C_NAME         CHAR(2)
    SQLDA;

    FETCH :C_NAME USING DESCRIPTOR SQLDA;


-- This procedure closes the cursor.

PROCEDURE CLOSE_CURSOR
    SQLCA
    :C_NAME         CHAR(2);

    CLOSE :C_NAME;

-- The procedure releases the prepared statement.  It frees all resources 
-- for the statement.  This statement should be used only when there is
-- a problem with resources. If used an error message will occur when
-- using the disconnect statement. Disregard the error message in this
-- case.

PROCEDURE RELEASE_STMT
    SQLCA
    :DYN_STMT_ID INTEGER;

    RELEASE :DYN_STMT_ID; 


-- This procedure commits the transaction.

PROCEDURE COMMIT_TRANSACTION
    SQLCA;

    COMMIT;


-- This procedure rolls back the transaction.

PROCEDURE ROLLBACK_TRANSACTION
    SQLCA;

    ROLLBACK;