Accessing SQL from Programming Languages

A database programmer must have access to a general-purpose programming language for at least two reasons.

• Not all queries can be expressed in SQL, since SQL does not provide the full expressive power of a general-purpose language.
• Non-declarative actions – such as printing a report, interacting with a user, or sending the results of a query to a graphical user interface – cannot be done from within SQL

To access SQL from other programming languages, there are two approaches

• Dynamic SQL
• Embedded SQL

I. Dynamic SQL

A general purpose programming language can connect to and communicate with a database server using a collection of functions or methods.

The dynamic SQL component of SQL allows programs to construct and submit SQL queries at runtime.

The two standards for connecting to an SQL database and perform queries and updates

        1. JDBC (Java Database Connectivity)

        2. ODBC (Open Database Connectivity)

1. Java Database Connectivity (JDBC)

JDBC is an API (Application Programming Interface) for communicating with database systems supporting SQL.

JDBC supports a variety of features for querying and updating data. JDBC also supports metadata retrieval.

The communication with a database can be done as

1. Open a Connection

2. Create a “statement” object

3. Execute queries using statement object to fetch results

4. Close the connection.

2. Open Database Connectivity

The ODBC standard defines an API (Application Programming Interface) that applications can use to open a connection with a database, send queries and updates, and get back the results.

Every database supporting ODBC provides a library that must be linked with the client program. When client program makes an ODBC call, the code in library communicates with the server to carry out the requested action and fetch results.

The first step is to set up a connection with the server. The program opens the database connection by using SQLConnect call. This call takes parameters including connection handle, the server to connect, the user identifier and the password to the database.

Once the connection is established, the program can send SQL commands to the database by using SQLExecDirect.

At the end of the session, the program frees the statement handle, disconnects from the database, and frees up the connection and SQL environment handles.

II. Embedded SQL

It provides a means by which a program can interact with a database server. The SQL statements are identified at compile time using a preprocessor. The preprocessor submits the SQL statements to the database system for pre compilation and optimization then replaces the SQL statements in the program with appropriate code and functions before invoking the programming language compiler.

SQL queries are embedded in a host language. The SQL structures permitted in the host language is called embedded SQL. Host language programs can use embedded SQL syntax to access and update data stored in a database.

EXEC SQL statement is used to identify embedded SQL request to the processor.

EXEC SQL <embedded SQL statement>;

The syntax depends on the host languages.

EXEC  SQL connect to sever user username using password;

This statement establishes connection between the program and database.

To write an embedded SQL query, the declare cursor statement is used.

            declare c cursor for <SQL query>;

Here, ‘c’ is a variable used to identify the query.

EXEC SQL open c;

This statement executes the query and save the results in a temporary relation.

EXEC SQL fetch c into :si,:sn;

This statement fetches tuple in the query result. Repeated calls to fetch statement get successive tuples in the query result.

EXEC SQL close c;

This statement causes the database system to delete the temporary relation that holds the results of the query.