SQL on the iSeries – Part III (Dynamic SQL)

Posted on by thejwal

The examples we considered in the previous part of this post were all Static SQL statements. The clauses and conditions which needed to be used were fixed at the time of the program creation.

However situations arise wherein the filtration and grouping clauses can be determined only at run time. In such cases, the SQL statements must be formed during runt time, i.e. dynamically. This is called Dynamic SQL.

The program below shows an example where an SQL statement is assembled during run time and executed using the ‘EXECUTE IMMEDIATE’ command.

/Free
  D MySQL  S           512
  MySQL = 'DELETE FROM EMPPF WHERE EMPNUM =' + %EditC(100:'X');
 
  Exec SQL EXECUTE IMMEDIATE :MYSQL;
  
  If SQLCOD < *Zero;
    Exsr ErrSbr;
  EndIf;

  *Inlr = *On;
   Return;

  BegSR ErrSbr;
    Dsply 'Error occurred while deleting';
   EndSR;

/End-Free

Each time an sql statement is executed using the EXECUTE IMMEDIATE, the SQL engine needs to analyse and prepare a plan which has some overhead associated with it.

So if the same statement is executed immediately a number of times, the overhead associated might impact performance.

If the same statement is executed more than once, the SQL plan to execute it can be prepared once and then the plan can be used multiple times. This is more efficient than executing the same SQL multiple times using EXECUTE IMMEDIATE. The below example shows how to prepare a statement before execution.

/Free
  D MySQL  S           512
  MySQL = 'DELETE FROM EMPPF WHERE EMPNUM =' + %EditC(100:'X');
 
  Exec SQL PREPARE STMT1 FROM :MYSQL;
  Exec SQL EXECUTE STMT1;

  If SQLCOD < *Zero;
    Exsr ErrSbr;
  EndIf;

  *Inlr = *On;
   Return;

  BegSR ErrSbr;
    Dsply 'Error occurred while deleting';
   EndSR;

/End-Free

In the above example, the filtration criteria worked fine as the literal being compared is a number. However if the literal being compared to a character, it must be surrounded by quotes. The literal would have to be surrounded by quotes as below.

/Free
  D MySQL  S           512
  D Quote  C              CONST('''')

 MySQL = 'DELETE FROM EMPPF WHERE EMPNAME = ' + QUOTE + EMPNAME + QUOTE;
 
 Exec SQL PREPARE STMT1 FROM :MYSQL;
 Exec SQL EXECUTE STMT1;

 If SQLCOD < *Zero;
 Exsr ErrSbr;
 EndIf;

 *Inlr = *On;
 Return;

 BegSR ErrSbr;
 Dsply 'Error occurred while deleting';
 EndSR;

/End-Free

With this, we conclude this part of the post. In the next part, we look at processing multiple records selected by a query – called as result set.

Leave a comment