www.mimer.com/developer

Principles of Dynamic SQL

---

Dynamic SQL allows you to execute SQL statements placed in a string variable instead of explicitly writing the statements inside a program. This allows SQL statements to be constructed within an application program. These facilities are typically used in interactive environments, where SQL statements are submitted to the application program from the terminal.

An example of when dynamic SQL is needed would be a program for interactive SQL, where any correct SQL statement may be entered at the terminal and processed by the application. Limited dynamic facilities may however be provided by relatively simple application programs.

SQL Statements and Dynamic SQL

The following classes of SQL statements may be submitted to programs using dynamic SQL. Statements excluded from dynamic applications are declarations, diagnostic statements and dynamic SQL statements themselves.

• Access control statements:

  ENTER

  LEAVE

• Data definition statements:

  CREATE

  ALTER

  COMMENT

  DROP

• Security control statements:

  GRANT

  REVOKE

• Transaction control statements:

  SET SESSION

  SET TRANSACTION

  START

  COMMIT

  ROLLBACK

• Data manipulation statements:

  CALL

  SELECT

  SELECTINTO

  INSERT

  UPDATE

  UPDATECURRENT

  DELETE

  DELETECURRENT

• System administration statements:

  CREATE BACKUP

  CREATE INCREMENTAL BACKUP

  ALTERDATABANKRESTORE

  SETDATABASE

  SETDATABANK

  SETSHADOW

  UPDATE STATISTICS

Submitting Statements

Statements may be submitted to dynamic SQL applications in two forms:

• Fully defined statements, written exactly as they would be submitted to interactive SQL. For example:

 GRANT ALL ON HOTEL TO CHARLIE
 
 UPDATE ROOM_STATUS SET STATUS = 'KEY OUT'
 WHERE ROOMNO = 'SKY112'
 
 SELECT * FROM HOTEL
 
 SELECT RESERVATION,SUM(AMOUNT)
 FROM   BILL
 GROUP BY RESERVATION
 

• Statements with "parameter markers", which identify positions where the value of a host variable will be inserted when the statement is executed or the cursor is opened. A parameter marker is represented by a question mark. For example:

 UPDATE ROOM_STATUS SET STATUS = ? WHERE ROOMNO = ?
 
 DELETE FROM BOOK_GUEST WHERE RESERVATION = ?
 
 SELECT HOTELCODE,ON_DATE,FREECOUNT*?
 FROM   FREEROOMS
 WHERE  ROOMTYPE  = ?
   AND  HOTELCODE IN (SELECT HOTELCODE
                      FROM HOTEL
                      WHERE CITY = ?)
 

   Statements submitted with parameter markers are equivalent to normal embedded statements using host variables, except that the statements are defined at run-time. 

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se