"Exploring the Power and Flexibility of Dynamic and Embedded SQL"

 

 

What is SQL?

SQL also referred to as Structured Query Language is capable of doing a lot more than just query a database. The most effective query language that is commercially offered is SQL. It makes use of algebra, calculus constructs in combination. It is capable of defining the structure of the data, modifying the data in the database etc.

SQL consists of different divisions:

• Data-definition language (DDL). Commands to define relational schemas, to delete as well as to modify relational schemas are provided by SQL.

• Interactive data-manipulation language (DML). A query language which depends upon relational algebra as well as tuple relation calculus. Commands for inserting tuples in the database, for deleting tuples, and modifying tuples in the database.

• Transaction control. Commands for specifying the start and end of transactions are also provided by SQL.

 • Embedded and Dynamic SQL. These specify how SQL commands can be embedded within effective languages for programs such as C, C++, Java, etc.

• Integrity. Commands to specify the integrity limitations which must be satisfied by the stored data. Any commands which do not follow the conditions are not allowed.

 • Authorization. Commands to specify authority different schemas.

 

How is the SQL statement processed?

 

 

Dynamic SQL:

Dynamic SQL indicates the construction of SQL statements at runtime, instead of compile time. This means that structure and content of the SQL statement are determined during program execution, based on user input or other runtime conditions.In a dynamic SQL statement, fragments of the query, such as the table name, column names, or filtering criteria, are generated at runtime. This allows for more flexible and adaptable queries that can be customized based on user input, dynamic business logic, or changing requirements.

Fig. Need for Dynamic SQL

Example of Dynamic SQL :

In this example, the SQL statement is generated dynamically using variables and the concatenation operator (+). The person ID value is stored in the @person_id variable, and it is cast to nvarchar to be concatenated with the rest of the command. SET statement assigns the dynamically generated SQL statement to the @sql variable. Finally, EXEC executes the dynamically generated statement using the execsql system stored procedure. Dynamic SQL using SQL is commonly used in stored procedures and other database objects where the SQL statements need to be generated dynamically based on user input or other dynamic factors.

Embedded SQL:

Embedded SQL is a programming technique that allows SQL statements which are a part of program compiled in another programming language. The SQL statements are embedded within the code of the host language, and are executed by a database engine when the program runs.Embedded SQL typically includes special syntax or preprocessor directives to identify SQL statements within the host language code, and to indicate how the SQL should be executed and processed. For example, in a C program using embedded SQL, the SQL statements might be enclosed in special comments or marked with a special prefix, such as "EXEC SQL".

Fig. Processing programs with SQL statements embedded

 

Example of Embedded SQL:

 

In this example, a stored procedure called GetPersonName is created using SQL. The stored procedure takes a parameter called @person_id, which is used to retrieve the person’s name from the table. The EXEC SQL BEGIN DECLARE SECTION and EXEC SQL END DECLARE SECTION directives are used to define the person_names variable, which is used in the command. The SQL statement is then executed using the EXEC SQL SELECT statement, and the result is stored in the person_names variable using the INTO clause. Finally, the stored procedure returns the person ID and person name as a result set. Embedded SQL using SQL is commonly used in stored procedures and other database objects where the SQL statements need to be generated dynamically based on user input or other dynamic factors. It can also help to decrease run time by decreasing quantity of data transferred between the database and the application.

Advantages of Dynamic SQL

●      Flexibility: With dynamic SQL, you can construct SQL statements based on user input, application settings, or other runtime factors. This allows for more flexibility in handling complex queries and varying business requirements.

●      Reusability: Dynamic SQL can be reused in different parts of an application or across different applications. This saves time and effort in developing and maintaining code.

●      Performance: Dynamic SQL can sometimes perform better than static SQL in certain scenarios. For example, if you have a difficult query that involves a huge amount of joins, using dynamic SQL to construct a more optimized query based on the specific data being queried can lead to better performance.

●      Security: Dynamic SQL can help prevent SQL injection attacks by using parameterized queries that separate data from the SQL statement itself. This makes it harder for attackers to insert malicious code into the query.

Disadvantages of Dynamic SQL:

●      Security risks: Dynamic SQL can pose security risks if not properly handled. For example, if user input is not properly sanitized, it could allow attackers to inject malicious SQL code into the query.

●      Performance overhead: Constructing SQL statements at runtime can take more time and resources than executing pre-compiled queries.

●      SQL server compatibility: Dynamic SQL may not be compatible with all SQL servers or database management systems. Different servers may have different rules for handling dynamic SQL statements, which can cause compatibility issues.

Advantages of Embedded SQL:

●      Facilitates remote database access.

●      Integrates authentication services for numerous application areas

●      Gives database transactions an additional security.

●      Prevents logical flaws while carrying out transactions on the database.

Disadvantages of embedded SQL:

●      SQL provides a poor interface  because it provides the appearance that everything is much more difficult than it actually is! The databases' complicated interface makes it tough for users to use them.

●      SQL users do not have total control over databases.

●      Preprocessing of directives is necessary, and there are intricacies that effect other programmes.

Conclusion:

Both embedded SQL and dynamic SQL have numerous applications and can be useful tools for database access in various circumstances. Developers should examine the benefits and drawbacks of each strategy before deciding which one best meets the objectives of their application.Static SQL provides better performance and security as it is pre-compiled and validated by the database server before execution. However, it lacks flexibility as the SQL code cannot be modified at runtime.On the other hand, dynamic SQL offers greater flexibility as SQL code can be generated and executed at runtime. However, it may be vulnerable to SQL injection attacks if not properly sanitized.Overall, it is important to carefully consider the requirements of the application and choose the appropriate type of SQL based on those requirements.

Authors:

●      Sakshi Shewale-27

●      Asif Shikalkar-28

●      Avanish Shilimkar-29

●      Kuldip Solanke-39

●      Swapnil Pawar-48