Sample...

SQL*Engine - A Complete (and practical) Example

Here's a complete sample of an SQL*Engine script... this demonstrates the use of the new directives @SET and @SCRIPT... The initial script is created using PeopleSoft's Application Designer (the table TEST_TBL is used)... We can then modify the script using SQL*Engine Directives... Along with the standard SQL steps that create the table and indexes we'll also call scripts to create the object synonym and assign the grants we need... The @SCRIPT directive calls the scripts... the @SET directive assigns values to substitution variables contained in the called scripts... This particular example is for Oracle...

But first... it's time for another picture...

Click on the sleeping jaguar for a larger version... this was made with crayola crayons... the spots were a real pain... but they turned out alright... Unlike the subject of this drawing I really don't like to sit around... I'm constantly creating new utilities and functions... be sure to visit SQRTOOLS.COM for more material in the future...

MAIN Script - Creates TABLE, INDEX... then SYNONYM and GRANTS...

*****************************************************************
*                                                               *
*    MODULE: TDSQL99.SQL                                        *
*    AUTHOR: TONY DELIA.                                        *
*      DATE: 12/30/1999.                                        *
*      DESC: SQL*ENGINE SCRIPT - DEMONSTRATE @SET/@SCRIPT.      *
*                                                               *
*****************************************************************
@ON_ERROR=CONTINUE
*****************************************************************
*   Set Static Substitution Variables                           *
*   &&RIGHTS1 = PRIVILEDGES FOR ROLE1                           *
*   &&RIGHTS2 = PRIVILEDGES FOR ROLE2 (SAME AS ROLE1)           *
*   &&RIGHTS3 = PRIVILEDGES FOR ROLE3                           *
*****************************************************************
@SET=&&RIGHTS1=SELECT, UPDATE, INSERT, DELETE
@SET=&&RIGHTS2=&&RIGHTS1
@SET=&&RIGHTS3=SELECT
*****************************************************************
*   SET &&OBJECT  = TABLE NAME                                  *
*   SET &&STEP    = CREATE STEP OF TABLE                        *
*****************************************************************
@SET=&&OBJECT=PS_TEST_TBL
@SET=&&STEP=CREATE_&&OBJECT
*****************************************************************
*   CREATE TABLE                                                *
*****************************************************************
@STEP=&&STEP
CREATE TABLE PS_TEST_TBL etc...
/
*****************************************************************
*   CREATE INDEX (IF TABLE STEP SUCCEEDED)                      *
*****************************************************************
@STEP=INDEX_&&OBJECT
@COND=PASS.&&STEP
CREATE INDEX etc...
/
*****************************************************************
*                                                               *
*  Create SYNONYM using SYNONYM.SQL                             *
*  --------------------------------------                       *
*  &&OBJECT = Table Object                                      *
*  &&STEP   = Object Create Step (@COND=)                       *
*                                                               *
*****************************************************************
@SCRIPT=c:\sql\synonym.sql
*****************************************************************
*                                                               *
*  Grant Priviledges using GRANT.SQL                            *
*  --------------------------------------                       *
*  &&ROLE   = Role to grant priviledges                         *
*  &&RIGHTS = Rights to grant                                   *
*  &&OBJECT = Table Object                                      *
*  &&STEP   = Object Create Step (@COND=)                       *
*                                                               *
*****************************************************************
*  GRANT FOR ROLE1 USING &&RIGHTS1 SETTING                      *
*****************************************************************
@SET=&&ROLE=ROLE1
@SET=&&RIGHTS=&&RIGHTS1
@SCRIPT=c:\sql\grant.sql
*****************************************************************
*  GRANT FOR ROLE2 USING &&RIGHTS2 SETTING                      *
*****************************************************************
@SET=&&ROLE=ROLE2
@SET=&&RIGHTS=&&RIGHTS2
@SCRIPT=c:\sql\grant.sql
*****************************************************************
*  GRANT FOR ROLE3 USING &&RIGHTS3 SETTING                      *
*****************************************************************
@SET=&&ROLE=ROLE3
@SET=&&RIGHTS=&&RIGHTS3
@SCRIPT=c:\sql\grant.sql
*****************************************************************
*  END OF SQL*ENGINE SCRIPT                                     *
*****************************************************************

SYNONYM.SQL (Called by Main script using @SCRIPT)

*****************************************************************
*                                                               *
*    MODULE: SYNONYM.SQL                                        *
*    AUTHOR: TONY DELIA.                                        *
*      DATE: 12/30/1999.                                        *
*      DESC: SQL*ENGINE SCRIPT - CREATE SYNONYM.                *
*                                                               *
*****************************************************************
*                                                               *
*  Required Substitution Variables                              *
*  --------------------------------------                       *
*  &&OBJECT = Table Object                                      *
*  &&STEP   = Object Create Step (@COND=)                       *
*                                                               *
*****************************************************************
@STEP=SYNONYM_&&OBJECT
@COND=PASS.&&STEP
CREATE PUBLIC SYNONYM &&OBJECT
   FOR SYSADM.&&OBJECT
/
*****************************************************************
*  END OF SQL*ENGINE SCRIPT                                     *
*****************************************************************

GRANT.SQL (Called by Main script using @SCRIPT)

*****************************************************************
*                                                               *
*    MODULE: GRANT.SQL                                          *
*    AUTHOR: TONY DELIA.                                        *
*      DATE: 12/30/1999.                                        *
*      DESC: SQL*ENGINE SCRIPT - GRANT PRIVILEDGES.             *
*                                                               *
*****************************************************************
*                                                               *
*  Required Substitution Variables                              *
*  --------------------------------------                       *
*  &&ROLE   = Role to grant priviledges                         *
*  &&RIGHTS = Rights to grant                                   *
*  &&OBJECT = Table Object                                      *
*  &&STEP   = Object Create Step (@COND=)                       *
*                                                               *
*****************************************************************
@STEP=GRANT_&&ROLE_&&OBJECT
@COND=PASS.&&STEP
GRANT &&RIGHTS
   ON SYSADM.&&OBJECT
   TO &&ROLE
/
*****************************************************************
*  END OF SQL*ENGINE SCRIPT                                     *
*****************************************************************

SQR.LOG displays variable substitution (Before/After Resolution):


 *** Variable Substitution Applied to Input *** 
 IN: @SET=&&RIGHTS2=&&RIGHTS1
OUT: @SET=&&RIGHTS2=SELECT, UPDATE, INSERT, DELETE
 
 *** Variable Substitution Applied to Input *** 
 IN: @SET=&&STEP=CREATE_&&OBJECT
OUT: @SET=&&STEP=CREATE_PS_TEST_TBL
 
 *** Variable Substitution Applied to Input *** 
 IN: @STEP=&&STEP
OUT: @STEP=CREATE_PS_TEST_TBL
 
 *** Variable Substitution Applied to Input *** 
 IN: @STEP=INDEX_&&OBJECT
OUT: @STEP=INDEX_PS_TEST_TBL
 
 *** Variable Substitution Applied to Input *** 
 IN: @COND=PASS.&&STEP
OUT: @COND=PASS.CREATE_PS_TEST_TBL
 
 *** Variable Substitution Applied to Input *** 
 IN: @STEP=SYNONYM_&&OBJECT
OUT: @STEP=SYNONYM_PS_TEST_TBL
 
 *** Variable Substitution Applied to Input *** 
 IN: @COND=PASS.&&STEP
OUT: @COND=PASS.CREATE_PS_TEST_TBL
 
 *** Variable Substitution Applied to Input *** 
 IN: CREATE PUBLIC SYNONYM &&OBJECT
OUT: CREATE PUBLIC SYNONYM PS_TEST_TBL
 
 *** Variable Substitution Applied to Input *** 
 IN:    FOR SYSADM.&&OBJECT
OUT:    FOR SYSADM.PS_TEST_TBL
 
 *** Variable Substitution Applied to Input *** 
 IN: @SET=&&RIGHTS=&&RIGHTS1
OUT: @SET=&&RIGHTS=SELECT, UPDATE, INSERT, DELETE
 
 *** Variable Substitution Applied to Input *** 
 IN: @STEP=GRANT_&&ROLE_&&OBJECT
OUT: @STEP=GRANT_ROLE1_PS_TEST_TBL
 
 *** Variable Substitution Applied to Input *** 
 IN: @COND=PASS.&&STEP
OUT: @COND=PASS.CREATE_PS_TEST_TBL
 
 *** Variable Substitution Applied to Input *** 
 IN: GRANT &&RIGHTS
OUT: GRANT SELECT, UPDATE, INSERT, DELETE
 
 *** Variable Substitution Applied to Input *** 
 IN:    ON SYSADM.&&OBJECT
OUT:    ON SYSADM.PS_TEST_TBL
 
 *** Variable Substitution Applied to Input *** 
 IN:    TO &&ROLE
OUT:    TO ROLE1
 
 *** Variable Substitution Applied to Input *** 
 IN: @SET=&&RIGHTS=&&RIGHTS2
OUT: @SET=&&RIGHTS=SELECT, UPDATE, INSERT, DELETE
 
 *** Variable Substitution Applied to Input *** 
 IN: @STEP=GRANT_&&ROLE_&&OBJECT
OUT: @STEP=GRANT_ROLE2_PS_TEST_TBL
 
 *** Variable Substitution Applied to Input *** 
 IN: @COND=PASS.&&STEP
OUT: @COND=PASS.CREATE_PS_TEST_TBL
 
 *** Variable Substitution Applied to Input *** 
 IN: GRANT &&RIGHTS
OUT: GRANT SELECT, UPDATE, INSERT, DELETE
 
 *** Variable Substitution Applied to Input *** 
 IN:    ON SYSADM.&&OBJECT
OUT:    ON SYSADM.PS_TEST_TBL
 
 *** Variable Substitution Applied to Input *** 
 IN:    TO &&ROLE
OUT:    TO ROLE2
 
 *** Variable Substitution Applied to Input *** 
 IN: @SET=&&RIGHTS=&&RIGHTS3
OUT: @SET=&&RIGHTS=SELECT
 
 *** Variable Substitution Applied to Input *** 
 IN: @STEP=GRANT_&&ROLE_&&OBJECT
OUT: @STEP=GRANT_ROLE3_PS_TEST_TBL
 
 *** Variable Substitution Applied to Input *** 
 IN: @COND=PASS.&&STEP
OUT: @COND=PASS.CREATE_PS_TEST_TBL
 
 *** Variable Substitution Applied to Input *** 
 IN: GRANT &&RIGHTS
OUT: GRANT SELECT
 
 *** Variable Substitution Applied to Input *** 
 IN:    ON SYSADM.&&OBJECT
OUT:    ON SYSADM.PS_TEST_TBL
 
 *** Variable Substitution Applied to Input *** 
 IN:    TO &&ROLE
OUT:    TO ROLE3

In the SQR.LOG above the areas highlighted in green are from the SYNONYM.SQL script... the red highlighted areas are from the GRANT.SQL script... the remaining areas show the resolved substitution variables in our MAIN script... The GRANT.SQL script was called 3 times (ROLE1 thru 3) with varying input parameters (&&ROLE/&&RIGHTS)... SQL*Engine is simple... yet at the same time very powerful... Use your imagination for more applications...

Feedback

I would appreciate any feedback you may have on this site. Send mail to tdelia@erols.com or click on the Octopus.

Technical difficulties?

Please report any technical difficulties you may encounter to the address above OR click on the Octopus. Thanks.

PS - Happy Holidays!!!

Tony DeLia - Updated January 2, 2000