Dynamic SQL and Embedded SQL

SQL is a Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database.

SQL is the standard language for Relational Database Systems. SQL queries can be of two types i.e. Dynamic SQL and Embedded or Static SQL.

 

What is Dynamic SQL?

Dynamic SQL is a programming technique that enables you to create and execute SQL statements dynamically at runtime. The users can execute their own queries in some applications. These statements are compiled at the run-time. These kinds of SQL statements are used where there is a non-uniformity in the data stored in the database. It is more flexible as compared to Embedded SQL and can be used in some flexible applications because the full text of a SQL statement may be unknown at compilation. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.

Since the compilation is done at run-time, the system will know how to access the database at run-time only. So, no proper planning for execution and optimization is required previously. This will reduce the performance of the system. Also, if you are taking the database query from the user at run-time, then there are possibilities that the users might enter some wrong queries and this is very dangerous because here you are dealing with lots of data.

You could think of dynamic SQL as prepared statements in any programming language. With that said, using this technique, we can write down any query, from the simplest possible to the most complex ones. You could use this technique outside SQL to build queries and send them for execution, or inside a stored procedure to build queries based on given parameters. The biggest advantage we have here is flexibility because you can control everything – what shall be in the SELECT and WHERE parts of the query, as well conditions – test variables to include or exclude conditions, adjust values of input variables based on certain criteria, etc.

What is Embedded SQL?

Embedded or Static SQL is those SQL statements that are fixed and can't be changed at runtime in an application. These statements are compiled at the compile-time only. The user knows the path of execution of statements as the SQL statements are there with the user, so the SQL queries can be optimized and can be executed in the best and fastest possible way. The way of accessing the data is predefined and these static SQL statements are generally used on those databases that are uniformly distributed.

Embedded SQL involves the placement of SQL language constructs in procedural language code. Using Pro*Ada, SQL statements are embedded directly in the Ada program, then the source is precompiled. Precompilation translates the Embedded SQL into calls to Pro*Ada runtime library procedures that handle the interaction between the program and the Oracle Server. After precompilation, simply compile the resulting source files using standard, supported Ada compiler, then build the application in the normal way. Pro*Ada supplies all the necessary library procedures. The user must incorporate these into the normal Ada environment library.

These statements are hardcoded in the application, so to build some application in which there is a need for some dynamic or run-time SQL statements, then the Dynamic SQL statements should be used.

Embedding queries sometimes creates problems as they would be clumpy and lead to processing errors if used in a large-scale application that is under production.

Applications of Dynamic SQL

When the format of an SQL statement is unknown before writing or running a program, Dynamic SQL is a good choice.

In cases where Embedded SQL does not support performing any operation, or in cases where the user doesn’t know the exact SQL statements that must be executed by a PL/SQL procedure, Dynamic SQL should be used. These SQL statements may depend on user input, or they may depend on processing work done by the program.

The following are the typical situations where Dynamic SQL can be used.

1. To Execute Dynamic DML Statements

2. To Execute Dynamic Queries

3. To Reference Database Objects that Do Not Exist at Compilation

4. To Optimize Execution Dynamically

5. To Invoke Dynamic PL/SQL Blocks

6. To Perform Dynamic Operations Using Invoker-Rights

 

Applications of Embedded SQL

Embedded SQL comes to the rescue when in industry-level applications there is a need for properly connected systems which could draw data from the database and present it to the user. SQL queries are embedded into high-level languages such that they can easily perform the logic part of the analysis.

Some of the prominent examples of languages with which SQL is embedded are as follows:

• C++

• Java

• Python etc.

Need of Embedded SQL

Embedded SQL gives freedom to use databases as and when required. Once the application developed goes into the production mode several things need to be taken care of. One major aspect is the problem of authorization and fetching and feeding of data into/from the database. With the help of the embedding of queries, the database can be used without creating any bulky code. With the embedded SQL, APIs can be created which can easily fetch and feed data as and when required.

 

Dynamic SQL - Example

EXEC sp_executesql N'SELECT * FROM customer';

It’s pretty obvious that this query is exactly the same as the simple SELECT * FROM customer; query. The sp_executesql procedure takes the SQL string as a parameter and executes it. Since it’s the Unicode string the N prefix is used.

The next thing is to build a query using variables (parameters). PRINT command is used here to show what this query looks like after concatenation.

DECLARE

    @sql NVARCHAR(MAX),

@id NVARCHAR(MAX);

 

-- run query using parameters(s)

SET @id = N'2';

SET @sql = N'SELECT id, customer_name FROM customer WHERE id = ' + @id;

PRINT @sql;

EXEC sp_executesql @sql;

 

If a prepared statement in PHP (or any other language) is written the overall idea and syntax would be close to this example. This leads to the conclusion that statements in a programming language or at the database level could be prepared.

Since concatenating of strings is there, anything can be done here and the only limitation to execute that string is that SQL syntax before execution is OK. 

DECLARE

    @sql NVARCHAR(MAX),

@top NVARCHAR(MAX),

@attributes NVARCHAR(MAX),

@table NVARCHAR(MAX),

@id NVARCHAR(MAX);

 

-- run query using parameters(s)

SET @top = ' TOP 3 ';

SET @attributes = ' * ';

SET @table = ' customer '

SET @id = N'0';

SET @sql = N'SELECT ' + @top + @attributes + N'FROM ' + @table + N' WHERE id > ' + @id;

SELECT @sql AS query;

EXEC sp_executesql @sql;

Parameters for different parts of the query limit/top, list of attributes, table name, and id are used. Since the variable (@sql) passed to the sp_executesql procedure is the textual variable all standard string operations can be done with it.

This time, instead of the PRINT command, the SELECT @sql AS query; statement is used to test the contents of the @sql variable. This is a good practice during working with dynamic SQL and want to be sure that SQL syntax is correct before executing it.

Embedded SQL - Example

Although the SQL statements can be embedded in any general purpose programming language, still just take an example in C language so that a clear picture can be drawn. Here just an interactive SQL statement is taken to see how it can be embedded in C language. 

Increase the salary of teacher by 10% who are B.Tech

update teacher set salary=1.1*salary where qualification=’B.Tech’; The embedded SQL program for above written SQL statement will be:

main()

{

                exec sql include sqlca;

                exec sql declare table teacher (tid char(6) not null,tname char(20),sex char(1),age number(3),qualification char(7),salary number(7),city varchar(15));

                //Display a message to user

                printf("updating teacher salary who are B.Techn");

                //this code executes the SQL statement

                exec sql update teacher set salary=1.1*salary where qualification='B.Tech';

                printf(update done");

              

                exit();

}

 

Explanation

1. The embedded SQL statement can be written in any case (lower or upper). Although the convention of that programming language in which we are embedding the SQL statements should be followed. E.g., COBOL and FORTRAN are written in upper case so, the SQL statements are also written in upper case, while in C, the SQL statements are written in lower case as shown in the above program.

2. Each embedded SQL statement begins with an introducer which indicates that it is a SQL statement. For most of the programming languages, EXEC SQL is used as an introducer.

3. Each embedded SQL statement ends with a terminator. There can be different terminators for different programming languages. For example, there is END EXEC for COBOL and a semicolon (;) for C.

4. The DECLARE TABLE statement is used to declare a table. With the use of DECLARE TABLE statement the program specifies the column and data type explicitly.

5. When SQL statement is typed, there are chances of error. This error is displayed by the interactive SQL program which prompts to type a new statement. There can be two types of errors: compile time and runtime.

 

Advantages of Dynamic SQL

Predicate Optimization: The real benefit of dynamic SQL is that the execution plans generated for each invocation of the query will be optimized for the predicates that are actually being used at that moment. The main issue with the static SQL solutions, aside from maintainability, was that the additional predicates confused the query optimizer, causing it to create inefficient plans. Dynamic SQL gets around this issue by not including anything extra in the query.

Single Query Plan Caching: For every stored procedure there is one cached query plan and an additional ad hoc plan caches for each invocation of the stored procedure (this can be verified using the view sys.dm_exec_cached_plans). This means that every time a new argument is passed to the stored procedure, a compilation occurs, which is clearly going to kill performance. The dynamic query is not being parameterized and is therefore producing duplicate query plans for different arguments.

 

Disadvantages of Dynamic SQL

Speed: Dynamic SQL tends to be slower than embedded SQL, as SQL Server must generate an execution plan every time at runtime. 

Permissions: Dynamic SQL requires the users to have direct access permissions on all accessed objects like tables and views. Generally, users are given access to the stored procedures which reference that tables, but not directly on the tables. In this case, dynamic SQL will not work.

Syntax: One distinct advantage of writing stored T-SQL procedures is that you get a syntax check directly. With dynamic SQL, a trivial syntax error may not show up until run time. Even if you test your code carefully, there may be some query or some variation of a query, that is only run in odd cases and not covered in your test suite.

 

Advantages of Embedded SQL

Small footprint database: As embedded SQL uses an UltraLite database engine compiled specifically for each application, the footprint is generally smaller than when using an UltraLite component, especially for a small number of tables. For a large number of tables, this benefit is lost.

High performance: Combining the high performance of C and C++ applications with the optimization of the generated code, including data access plans, makes embedded SQL a good choice for high-performance application development.

Extensive SQL support: With embedded SQL you can use a wide range of SQL in your applications.

 

Disadvantages of Embedded SQL

Knowledge of C or C++ required: If you are not familiar with C or C++ programming, you may wish to use one of the other UltraLite interfaces. UltraLite components provide interfaces from several popular programming languages and tools.

Complex development model: The use of a reference database to hold the UltraLite database schema, together with the need to preprocess your source code files, makes the embedded SQL development process complex. The UltraLite components provide a much simpler development process.

SQL must be specified at design time: Only SQL statements defined at compile time can be included in your application. The UltraLite components allow dynamic use of SQL statements.

The choice of development model is guided by the needs of your particular project, and by the programming skills and experience available.

Dynamic SQL	Embedded SQL
In Dynamic SQL, how a database will be accessed, can be determined only at run time.	In Embedded SQL, database access procedure is predetermined in the statement.
Dynamic SQL statements are less efficient.	Embedded SQL statements are faster and more efficient.
Dynamic SQL statements are compiled at run time.	Embedded SQL statements are compiled at compile time.
Dynamic SQL is highly flexible.	Embedded SQL is less flexible.

 

Conclusion

If the requirement is to make a flexible application Dynamic SQL can be used, but the users must be professional and trained. Otherwise, embedded or static SQL will be more efficient as compared to Dynamic SQL.