Embedded SQL, Dynamic SQL, and SQLJ

Embedded SQL

Embedded SQL is a method that combines SQL with a high−level programming language's features. It enables programmers to put SQL statements right into the source code files used to set up an application. Database operations may be carried out effortlessly by developers by adding SQL statements to the application code. The source code files having embedded SQL statements should be preprocessed before compilation because of the issue of interpretation of SQL statements by the high−level programming languages in embedded SQL. The terms EXEC SQL and END_EXEC must be used before and after each SQL statement in the source code file. In embedded SQL, host variables play a key role. These variables serve as an intermediary for data transfer between the application and the database. There are two different kinds of host variables: input host variables that provide data to the database and output host variables that receive that data.

Example

This example shows the embedded code written in C++, retrieving the customer id, and name from the database.

Input

Student

id    roll_no	 name	 address
1      21	 monu	  gonda

Example

int main(){
/* connecting to database */
EXEC SQL CONNECT student;

/* declaring variables */
EXEC SQL BEGIN DECLARE SECTION;
int id;
int roll_no;
char name[10];
char address[30];
EXEC SQL END DECLARE SECTION;

/* set up error processing */
EXEC SQL WHENEVER SQLWARNING DO display_warning();
EXEC SQL WHENEVER SQLERROR STOP;
EXEC SQL WHENEVER NOT FOUND GOTO lbl_no_records;

/* Execute the sql query */
EXEC SQL SELECT * FROM STUDENT WHERE STUDENT_ID = :STD_ID;

/* Display result */
printf("id: %d
", id);
printf("name: %d
", name);
exit();

Output

id:1
name:monu

Advantages of Embedded SQL

Optimized Performance: Since embedded SQL allows developers to have control over the SQL statements, they can optimize the queries for better performance. By analyzing the database schema and query execution plans, developers can fine−tune the SQL statements to ensure efficient data retrieval and manipulation.

Tight Integration: Embedded SQL enables seamless integration of SQL operations within the application code. This integration allows developers to leverage the full power of SQL while using the high−level programming language for other application logic. It eliminates the need for separate SQL scripts or external files, making the code more manageable and maintainable.

Data Consistency: By embedding SQL statements within the application code, developers can ensure data consistency throughout the application. Changes made to the database structure or table schema can be reflected in the embedded SQL code, reducing the chances of data inconsistencies.

Database Security: Embedded SQL allows developers to incorporate authentication and security measures directly into the application. This integration enables the implementation of fine−grained access controls, ensuring that only authorized users can perform database operations

Dynamic SQL

Dynamic SQL involves the creation and execution of SQL statements at runtime. Dynamic SQL allows developers to generate SQL statements dynamically based on runtime conditions or user input. By combining changeable data, conditions, and dynamic database or column names, developers may quickly construct SQL queries using dynamic SQL. Because of its adaptability, dynamic SQL is a good choice when the SQL statements need to change in response to evolving needs or user inputs. Dynamic SQL queries are built at execution time so the system chooses how to access the database and conduct the SQL queries. Performance could be affected as a result of this lack of preparation because the system must create an execution plan on the spot. Dynamic SQL, however, provides outstanding adaptability and versatility.

Steps to use Dynamic SQL

Step 1: Declare two variables

DECLARE 
@var1 NVARCHAR(MAX), 
@var2 NVARCHAR(MAX);

Step 2: Set the value of the first variable as table_name

SET @var1 = N'table_name';

Step 3: Select statement is added to table_name to create dynamic SQL

SET @var2= N'SELECT * FROM ' + @var1;

Step 4: Use the second variable to run the sp_executesql

EXEC sp_executesql @var2;

Example

This example will show how the data of a table named 'student' is selected using a dynamic process.

Input Table

Student

Id 	  Name	     City
1	  Monu	     Gonda
2	  Aman	     Lucknow
3	  Naman	     Dehradun

Example

DECLARE
@var1 NVARCHAR(100),@var2 NVARCHAR(120);
SET @var1 = N'student';
SET @var2 = N'SELECT * FROM' + @var1;
EXEC sp_executesql @var2;

Output

Student

Id 	  Name	     City
1	  Monu	     Gonda
2	  Aman	     Lucknow
3	  Naman	     Dehradun

Advantages of Dynamic SQL

Flexibility: Dynamic SQL provides unparalleled flexibility as it allows developers to build SQL statements dynamically based on runtime conditions or user input. This flexibility enables developers to create dynamic queries, adapt to changing requirements, and handle complex scenarios.

Conditional Queries: Dynamic SQL is particularly useful when dealing with conditional queries. By building SQL statements dynamically, developers can incorporate conditions into the queries, such as dynamic WHERE clauses or varying column selections based on runtime conditions.

Table and Column Name Flexibility: Dynamic SQL allows developers to work with dynamic table and column names. This flexibility is beneficial when dealing with scenarios where the table or column names are not known or need to be determined at runtime.

Database Administration: Dynamic SQL can be useful for database administrators (DBAs) when performing tasks such as data migration, schema changes, or automated maintenance operations. Dynamic SQL allows DBAs to generate and execute SQL statements on the fly, adapting to the specific requirements of the task at hand.

SQLJ

SQLJ is an industry standard for embedding SQL statements in Java programs. SQLJ provides a way to integrate SQL with Java similar to how embedded SQL combines SQL with high−level languages.SQLJ acts as a simpler and easier−to−use alternative to JDBC (Java Database Connectivity). Developers may use the power of SQL for database operations while gaining access to Java's object−oriented characteristics by writing SQL statements directly within Java code. Developers must utilize an SQLJ translator to use SQLJ, which transforms SQL statements into Java code that can be run using the JDBC interface. SQLJ also requires importing specific classes, such as java.sql, for database connectivity.

Example

This example will show how the SQLJ query is written to insert a value into the database.

#sql [ctx] {
  INSERT INTO employee
    (ID,  NAME, AGE, SALARY)
  VALUES
    (:id, :name, :age, :salary)
};

Advantages of SQLJ

Type Safety: SQLJ provides enhanced type safety compared to traditional JDBC. The SQLJ translator performs static type checking, ensuring that SQL statements are syntactically correct and compatible with the underlying database schema. This reduces the chances of runtime errors caused by incorrect SQL statements

Simplified Syntax: SQLJ simplifies the process of embedding SQL statements in Java code. The #sql syntax allows developers to write SQL statements directly within the Java code, making it easier to understand and maintain the code. The SQLJ translator converts these SQL statements into Java code that can be executed through JDBC.

Integration with Java Objects: SQLJ seamlessly integrates with Java objects, allowing developers to work with database data using Java classes and objects. This integration enables developers to leverage Java's object−oriented features for data manipulation and processing.

Enhanced Performance: SQLJ can provide performance improvements compared to traditional JDBC in certain scenarios. The static type checking performed by the SQLJ translator allows for better optimization and query execution plans. Additionally, SQLJ simplifies the process of binding Java objects to SQL data, reducing the overhead of data conversion.

Conclusion

This article consists of three parts. First is embedded SQL which combines high−level languages with SQL. The advantages of embedded SQL are optimized performance, tight integration, data consistency, and database security. Second is dynamic SQL which generates and execute SQL statement at runtime. The advantages of dynamic SQL are flexibility, conditional queries, table and column name flexibility, and database administration. Third is SQLJ which embeds SQL statements in Java programs using SQLJ translator. Advantages are type safety, simplified syntax, integration with Java objects, and enhanced performance.