COBOL allows SQL queries to be executed both Statically and dynamically. However it’s often confusing that when Static SQL is to be used and when Dynamic SQL. This will be quite clear once you know the exact difference between the two.
Difference Between Static & Dynamic SQL:
SQL is static when we know the SQL Statements in advance i.e. we know what query to perform on which table under what conditions. For e.g. consider a table name “STUDENT” which has various details like Roll No, Name, Address, Mobile No etc.
Below is the query to fetch Name & Address of students whose Roll Nos are below 40.
SELECT NAME, ADDRESS FROM STUDENT WHERE ROLL_NO < 40
In this query we know in advance the details we need to fetch from STUDENT table and we know the selection criteria as well.
Such queries comes under Static SQL. In COBOL, static queries can be written as below:
EXEC SQL SELECT NAME, ADDRESS FROM STUDENT WHERE ROLL_NO < 40 END-EXEC.
Bit of flexibility can be added by using host variables which will allow you to take the value in selection criteria at run time for e.g. from some file or JCL Sysin Card. But that doesn’t make it Dynamic SQLas the query being executed is always same.
SQL is dynamic when we don’t know the exact query in advance and we want to build it at run time. UnlikeStatic SQL the queries are not embedded in the COBOL code, instead queries are stored in the text strings.
See below code snippet to understand it more clearly.
Working-Storage Section. 01 WS-SQL-COMMAND PIC X(50). ... PROCEDURE DIVISION. ... MOVE 'UPDATE STUDENT SET ADDRESS="UNKNOWN" WHERE ROLL_NO=1' TO WS-SQL-COMMAND. EXEC SQL EXECUTE IMMEDIATE :WS-SQL-COMMAND END-EXEC.
Here first we are moving the SQL query to a working storage variable and then executing that query at run-time.
Now in order to understand what EXECUTE IMMEDIATE does,one should be aware of the processing of an SQL Statement.
For Static SQL,
- First the SQL statements are embedded in the COBOL Code.
- Then Pre-compile, Compile and Link the program.
- Next is BIND the program and then execute it.
So you can see clearly that the SQL statements are translated, validated, compiled & linked well before execution.
In case of Dynamic SQL as well same steps follow, only difference comes in the execution of Dynamic SQL statements which includes
- Formatting of Dynamic SQL
- Translation of Dynamic SQL
- Binding of Dynamic SQL
- Processing of Dynamic SQL
So basically the optimized path for execution which is set before execution in case of Static SQL is set during execution in Dynamic SQL.
Now coming back to above example for Dynamic SQL, EXECUTE IMMEDIATE Prepares the SQL statements for validations & translation and then executes those statements.
However EXECUTE IMMEDIATE is not preferred when SELECT query is used as SELECT query can result in multiple rows as output therefore each time same SQL statements will be prepared again and again which can be avoided to increase performance by using PREPARE statement just once and then using EXECUTE statement.
Below code Snippet shows a cursor declaration for SELECT query.
* assign query to variable. MOVE 'SELECT NAME, ADDRESS FROM STUDENT' TO WS-SQL-COMMAND. * prepare SELECT command. EXEC SQL PREPARE Q1 FROM :WS-SQL-COMMAND END-EXEC * declare cursor for SELECT command. EXEC SQL DECLARE C1 CURSOR FOR Q1 END-EXEC * open cursor. EXEC SQL OPEN C1 END-EXEC * fetch data. EXEC SQL FETCH C1 INTO :WS-NAME, :WS-ADDRESS END-EXEC * close cursor. EXEC SQL CLOSE C1 END-EXEC
In this case first the SQL query is prepared once and then using cursor declaration multiple records are fetched from the table STUDENT.
Hope this sums up the basics you need to know before deciding whether to use Static SQL or Dynamic SQL. 🙂