Dynamic SQL And Embedded SQL

 Dynamic SQL



  • What is Dynamic SQL?
Dynamic SQL is a programming technique that is used to build SQL statements dynamically at runtime. One can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation.

For example, dynamic SQL allows to create a procedure that operates on a table whose name is not known until runtime.

  • Why need Dynamic SQL?
Dynamic SQL enables us to write programs that reference SQL statements whose full text is not known until runtime. The simple statements which we use are known as static SQL statements.

Static SQL statements do not change from execution to execution. The full text of static SQL statements are known at compilation, which provides the following benefits:

  1. Successful compilation verifies that the SQL statements reference valid database objects.
  2. Successful compilation verifies that the necessary privileges are in place to access the database objects.
  3. Performance of static SQL is generally better than dynamic SQL.
Because of these advantages, you should use dynamic SQL only if you cannot use static SQL to accomplish your goals, or if using static SQL is cumbersome compared to dynamic SQL. 
However, static SQL has limitations that can be overcome with dynamic SQL. You may not always know the full text of the SQL statements that must be executed in a PL/SQL procedure. Your program may accept user input that defines the SQL statements to execute, or your program may need to complete some processing work to determine the correct course of action. In such cases, you should use dynamic SQL.

  • When to use Dynamic SQL?
  1. To Execute Dynamic DML Statements:
You can use dynamic SQL to execute DML statements in which the exact SQL statement is not known until runtime. 

     2. To Execute Statement Not Supported By Static Sql in Pl/SQL:

In PL/SQL, you cannot execute the following types of statements using static SQL:

    • Data definition language (DDL) statements, such as CREATE, DROP, GRANT, and REVOKE
    • Session control language (SCL) statements, such as ALTER SESSION and SET ROLE
    3. To Execute Dynamic Queries:

You can use dynamic SQL to create applications that execute dynamic queries, which are queries whose full text is not known until runtime. Many types of applications need to use dynamic queries, including:

    • Applications that allow users to input or choose query search or sorting criteria at runtime
    • Applications that allow users to input or choose optimizer hints at run time
    • Applications that query a database where the data definitions of tables are constantly changing
    • Applications that query a database where new tables are created often


  • How to Use Dynamic SQL to query in a table?
    1. First, declare two variables, @table for holding the name of the table from which you want to query and @sql for holding the dynamic SQL.
    2. Second, set the value of the @table variable to production.products(this table stores information about product such as name, brand , category, etc).
    3. Third, construct the dynamic SQL by concatenating the SELECT statement with the table name parameter.
    4. Fourth, call the sp_executesql stored procedure by passing the @sql parameter.
Putting The queries together:

DECLARE 
    @table NVARCHAR(128),
    @sql NVARCHAR(MAX);

SET @table = N'production.products';

SET @sql = N'SELECT * FROM ' + @table;

EXEC sp_executesql @sql;


EMBEDDED SQL





  • What is embedded SQL in DBMS?
 When we talk about embedded SQL, it can be understood as small SQL queries put into high-level languages to get meaningful outputs. While embedding SQL queries into other high-level languages, we need to make sure that we have a working database connector on our system.

  By using the connectors we can easily run SQL queries on the existing databases in our RDBMS or we can even create new ones.


  • What is embedded SQL?
When we talk about industry-level applications we need properly connected systems which could draw data from the database and present to the user. In such cases, the embedded SQL comes to our rescue.

We embed SQL queries into high-level languages such that they can easily perform the logic part of our analysis.

Some of the prominent examples of languages with which we embed SQL are as follows:

  • C++
  • Java
  • Python etc.

  • Why do we need embedded SQL?

Embedded SQL gives us the freedom to use databases as and when required. Once the application we develop goes into the production mode several things need to be taken care of.

We need to take care of a thousand things out of which 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, we can easily use the database without creating any bulky code. With the embedded SQL, we can create API’s which can easily fetch and feed data as and when required.

  • How to embed SQL in high level languages?

For using embedded SQL, we need some tools in each high-level language. In some cases, we have inbuilt libraries which provide us with the basic building block.

While in some cases we need to import or use some packages to perform the desired tasks.

For example, in Java, we need a connection class. We first create a connection by using the connection class and further we open the connection bypassing the required parameters to connect with the database.

EXAMPLE: HOW TO CONNECT TO A DATABASE (USING JAVA)

Class.forName("com.mysql.jdbc.Driver");  
Connection connection = DriverManager.getConnection( "jdbc:mysql://localhost:3306/DataFlair","user","root");  
Statement statement = connection.createStatement();
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection( "jdbc:mysql://localhost:3306/DataFlair","user","root");
Statement statement = connection.createStatement();



AUTHORS:
Rishikesh Wani
Shitij Thakur
Ved Raut
Yash Nigade

Comments

Post a Comment