Accessing SQL from Programming Language

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, we can use:

• Dynamic SQL: JDBC and ODBC - A general-purpose program can connect to and communicate with a database server using a collection of functions
• Embedded SQL - provide a means by which a program can interact with a database server. The SQL statements are translated at compile time into function calls. At runtime, these function calls connect to the database using an API that provides dynamic SQL facilities

Ø  Dynamic SQL

ODBC (Open Database Connectivity) and JDBC (Java Database Connectivity) serve as APIs for a program to interact with a database server.

ODBC

ODBC works with C, C++, C# and Visual Basic (other APIs such as ADO.NET sit on top of ODBC).

ODBC is the standard for application programs communicating with a database server.

The API will:

·         open a connection with a database

·         send queries and updates

·         get back results

ODBC can be used with applications such as GUIs, spreadsheets etc.

JDBC

JDBC works with Java. Along with supporting various features for querying and updating data, and for retrieving query results, JDBC also supports metadata retrieval i.e. retrieving information about the database such as relations present in the database and the names and types of relation attributes.

JDBC connects with the database as follows:

·         open a connection

·         create a “Statement” object

·         execute queries using the Statement object to send queries and fetch results

·         exception mechanism to handle errors

 

Ø  Embedded SQL

Embedded SQL refers to embedding SQL queries in another language.

SQL can be embedded in various languages including C, Java and Cobol.

A language into which SQL queries are embedded is referred to as a host language, and the SQL structures permitted in the host language comprise embedded SQL.

The EXEC SQL statement is used to identify embedded SQL request to the preprocessor:

            EXEC SQL <embedded SQL statement> END_EXEC

Before executing any SQL statements, the program must first connect to the database. This is done using:

EXEC-SQL connect to server user user-name using password;

Here, server identifies the server to which a connection is to be established.

Variables of the host language can be used within embedded SQL statements. They are preceded by a colon (:) to distinguish from SQL variables (e.g., :credit_amount ).

Variables used as above must be declared within DECLARE section. The syntax for declaring the variables, follows the usual host language syntax.

            EXEC SQL BEGIN DECLARE SECTION

 int credit-amount ;

EXEC SQL END DECLARE SECTION;

·         To write an embedded SQL query, the statemenmt

declare c cursor for statement < SQL query>.

        is used. The variable c is used to identify the query

·         The open statement for our example is as follows:

 EXEC SQL open c ;

This statement causes the database system to execute the query and to save the results within a temporary relation.

·         The fetch statement causes the values of one tuple in the query result to be placed on host language variables.

EXEC SQL fetch c into :si, :sn;

      Repeated calls to fetch get successive tuples in the query result.

·         The close statement causes the database system to delete the temporary relation that holds the result of the query.

EXEC SQL close c ;