What is Dynamic SQL?
Dynamic SQL is a programming technique that accepts and executes SQL statements “On The Fly” at runtime. It adds flexibility and functionality to your applications. Dynamic SQL statements are not embedded in your source program. Instead they are stored in character strings input to or built by the program at runtime.
Why Dynamic SQL?
Most database applications do a specific job. For example, a simple program might prompt the user for an employee number, then UPDATE rows in the EMP and DEPT tables. In this case, you know the makeup of the UPDATE statement at pre-compile time. That is, you know which tables might be changed, the constraints defined for each table and column, which columns might be updated, and the data type of each column.
However, some applications must accept (or build) and process a variety of SQL statements at runtime. For example, a general-purpose report writer must build different SELECT statements for the various reports it generates. In this case, the statement’s makeup is unknown until run time. Such statements can, and probably will, change from execution to execution. Dynamic SQL is used in this situation. Another important criterion is the execution time. In some cases the Dynamic SQL can fetch data from different tables using same SQL
Advantages and Disadvantages of Dynamic SQL
Programs that accept and process dynamically defined SQL statements are more versatile than those using static embedded SQL statements. For example, your program might simply prompt users for a search condition to be used in the WHERE clause of a SELECT, UPDATE, or DELETE statement. A more complex program might allow users to choose from menus listing SQL operations, table and view names, column names, and so on.
The fact that the SQL statements can be dynamically changed, can be utilized to eliminate redundant code. This is applicable in a situation where records from a table can be selected based on the difference selection criteria based on some input parameters. This would typically get translated into two select statements separated by if statement or case statement. If the processing required for each of the records is very complex, duplicating it may result in lower maintenability of the code. This can be avoided using the dynamic SQL. Thus, dynamic SQL lets you write highly flexible applications.
However, some dynamic queries require complex coding, the use of special data structures, and more runtime processing.You might find the coding difficult, unless you fully understand dynamic SQL concepts and methods.
In practice, static SQL will meet most of your programming needs. Use dynamic SQL only if you need its open-ended flexibility. Dynamic SQL can be used in some cases where one or more of the following is unknown at pre-compile time:
· text of the SQL statement (commands, clauses, and so on)
· the number of pseudo variables
· the data types of pseudo variables
· references to database objects such as fields, indexes, tables
But it depends on programmer what he emphasizes on: efficiency or flexibility and also on the scenario.
How Dynamic SQL Statements Are Processed
1. Typically, application programs may prompt a user for the values of pseudo variables used in the statement.
2. Then statements are parsed; that is, examined to make sure that it follows syntax rules and refers to valid database objects.
3. Next, pseudo variables are bind to the SQL statement. Binding means passing the addresses of pseudo variables in the SQL statement to BaaN so that BaaN can read or write their values.
4. Then the SQL statements are executed .The SQL statement can be executed repeatedly using new values for the pseudo variables.
5. Then each record is fetched one by one from the table. These fetched records can be processed further.
To use dynamic SQL in baan following functions are available:
1) SQL.PARSE Used to form a query
2) SQL .SELECT.BIND Used to bind a pseudo variable (in select statement) with program variable.
3) SQL.WHERE.BIND Used to bind a pseudo variable (in where statement) with program variable.
4) SQL.EXEC To initialize the query.
5) SQL.FETCH To execute the query.
6) SQL.BREAK To stop the execution of the query.
7) SQL.CLOSE To delete all internal information of the query.