Database Programming with Function Calls: SQL/CLI and JDBC

Database Programming with Function Calls: SQL/CLI and JDBC

Embedded SQL (see Section 13.2) is sometimes referred to as a static database programming approach because the query text is written within the program source code and cannot be changed without recompiling or reprocessing the source code. The use of function calls is a more dynamic approach for database programming than embedded SQL. We already saw one dynamic database programming technique—dynamic SQL—in Section 13.2.3. The techniques discussed here provide another approach to dynamic database programming. A library of functions, also known as an application programming interface (API), is used to access the data-base. Although this provides more flexibility because no preprocessor is needed, one drawback is that syntax and other checks on SQL commands have to be done at runtime. Another drawback is that it sometimes requires more complex programming to access query results because the types and numbers of attributes in a query result may not be known in advance.

In this section, we give an overview of two function call interfaces. We first discuss the SQL Call Level Interface (SQL/CLI), which is part of the SQL standard. This was developed as a follow-up to the earlier technique known as ODBC (Open Database Connectivity). We use C as the host language in our SQL/CLI examples. Then we give an overview of JDBC, which is the call function interface for accessing databases from Java. Although it is commonly assumed that JDBC stands for Java Database Connectivity, JDBC is just a registered trademark of Sun Microsystems, not an acronym.

The main advantage of using a function call interface is that it makes it easier to access multiple databases within the same application program, even if they are stored under different DBMS packages. We discuss this further in Section 13.3.2 when we discuss Java database programming with JDBC, although this advantage also applies to database programming with SQL/CLI and ODBC (see Section 13.3.1).

1. Database Programming with SQL/CLI Using C as the Host Language

Before using the function calls in SQL/CLI, it is necessary to install the appropriate library packages on the database server. These packages are obtained from the vendor of the DBMS being used. We now give an overview of how SQL/CLI can be used in a C program. We will illustrate our presentation with the sample program segment CLI1 shown in Figure 13.10.

     Figure 13.10 Program segment CLI1, a C program segment with SQL/CLI.

     //Program CLI1:

     0) #include sqlcli.h ;

     1) void printSal()    {

     SQLHSTMT stmt1 ;

     SQLHDBC con1 ;

     SQLHENV env1 ;

     SQLRETURN ret1, ret2, ret3, ret4 ;

     ret1 = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env1) ;

     if (!ret1) ret2 = SQLAllocHandle(SQL_HANDLE_DBC, env1, &con1) else exit ;

     if (!ret2) ret3 = SQLConnect(con1, "dbs", SQL_NTS, "js", SQL_NTS, "xyz", SQL_NTS) else exit ;

     if (!ret3) ret4 = SQLAllocHandle(SQL_HANDLE_STMT, con1, &stmt1) else exit ;

     SQLPrepare(stmt1, "select Lname, Salary from EMPLOYEE where Ssn = ?", SQL_NTS) ;

     prompt("Enter a Social Security Number: ", ssn) ;

     SQLBindParameter(stmt1, 1, SQL_CHAR, &ssn, 9, &fetchlen1) ;

     ret1 = SQLExecute(stmt1) ;

     if (!ret1) {

     SQLBindCol(stmt1, 1, SQL_CHAR, &lname, 15, &fetchlen1) ;

     SQLBindCol(stmt1, 2, SQL_FLOAT, &salary, 4, &fetchlen2) ;

     ret2 = SQLFetch(stmt1) ;

     if (!ret2) printf(ssn, lname, salary)

     else printf("Social Security Number does not exist: ", ssn) ;

     }

}

When using SQL/CLI, the SQL statements are dynamically created and passed as string parameters in the function calls. Hence, it is necessary to keep track of the information about host program interactions with the database in runtime data structures because the database commands are processed at runtime. The information is kept in four types of records, represented as structs in C data types. An environment record is used as a container to keep track of one or more database connections and to set environment information. A connection record keeps track of the information needed for a particular database connection. A statement record keeps track of the information needed for one SQL statement. A description record keeps track of the information about tuples or parameters—for example, the number of attributes and their types in a tuple, or the number and types of parameters in a function call. This is needed when the programmer does not know this informa-tion about the query when writing the program. In our examples, we assume that the programmer knows the exact query, so we do not show any description records.

Each record is accessible to the program through a C pointer variable—called a handle to the record. The handle is returned when a record is first created. To create a record and return its handle, the following SQL/CLI function is used:

SQLAllocHandle(<handle_type>, <handle_1>, <handle_2>)

In this function, the parameters are as follows:

■                             <handle_type> indicates the type of record being created. The possible val-ues for this parameter are the keywords SQL_HANDLE_ENV,

SQL_HANDLE_DBC, SQL_HANDLE_STMT, or SQL_HANDLE_DESC, for an environment, connection, statement, or description record, respectively.

■                             <handle_1> indicates the container within which the new handle is being created. For example, for a connection record this would be the environment within which the connection is being created, and for a statement record this would be the connection for that statement.

        <handle_2> is the pointer (handle) to the newly created record of type

<handle_type>.

When writing a C program that will include database calls through SQL/CLI, the following are the typical steps that are taken. We illustrate the steps by referring to the example CLI1 in Figure 13.10, which reads a Social Security number of an employee and prints the employee’s last name and salary.

        The library of functions comprising SQL/CLI must be included in the C pro-gram. This is called sqlcli.h, and is included using line 0 in Figure 13.10.

        Declare  handle  variables  of  types  SQLHSTMT, SQLHDBC, SQLHENV, and SQLHDESC for the statements, connections, environments, and descriptions needed in the program, respectively (lines 2 to 4). Also declare variables of type SQLRETURN (line 5) to hold the return codes from the SQL/CLI func-tion calls. A return code of 0 (zero) indicates successful execution of the func-tion call.

        An environment record must be set up in the program using SQLAllocHandle. The function to do this is shown in line 6. Because an environment record is not contained in any other record, the parameter <handle_1> is the NULL handle SQL_NULL_HANDLE (NULL pointer) when creating an environment. The handle (pointer) to the newly created environ-ment record is returned in variable env1 in line 6.

        A connection record is set up in the program using SQLAllocHandle. In line 7, the connection record created has the handle con1 and is contained in the environment env1. A connection is then established in con1 to a particular server database using the SQLConnect function of SQL/CLI (line 8). In our example, the database server name we are connecting to is dbs and the account name and password for login are js and xyz, respectively.

        A statement record is set up in the program using SQLAllocHandle. In line 9, the statement record created has the handle stmt1 and uses the connec-tion con1.

The statement is prepared using the SQL/CLI function SQLPrepare. In line 10, this assigns the SQL statement string (the query in our example) to the statement handle stmt1. The question mark (?) symbol in line 10 represents a statement parameter, which is a value to be determined at runtime—typ-ically by binding it to a C program variable. In general, there could be several parameters in a statement string. They are distinguished by the order of appearance of the question marks in the statement string (the first ? represents parameter 1, the second ? represents parameter 2, and so on). The last parameter in SQLPrepare should give the length of the SQL statement string in bytes, but if we enter the keyword SQL_NTS, this indicates that the string holding the query is a NULL-terminated string so that SQL can calcu-late the string length automatically. This use of SQL_NTS also applies to other string parameters in the function calls in our examples.

7. Before executing the query, any parameters in the query string should be bound to program variables using the SQL/CLI function SQLBindParameter. In Figure 13.10, the parameter (indicated by ?) to the prepared query referenced by stmt1 is bound to the C program variable ssn in line 12. If there are n parameters in the SQL statement, we should have n SQLBindParameter function calls, each with a different parameter position (1, 2, ..., n).

        Following these preparations, we can now execute the SQL statement refer-enced by the handle stmt1 using the function SQLExecute (line 13). Notice that although the query will be executed in line 13, the query results have not yet been assigned to any C program variables.

        In order to determine where the result of the query is returned, one common technique is the bound columns approach. Here, each column in a query result is bound to a C program variable using the SQLBindCol function. The columns are distinguished by their order of appearance in the SQL query. In Figure 13.10 lines 15 and 16, the two columns in the query (Lname and Salary) are bound to the C program variables lname and salary, respectively.

        Finally, in order to retrieve the column values into the C program variables,

the function SQLFetch is used (line 17). This function is similar to the FETCH command of embedded SQL. If a query result has a collection of tuples, each SQLFetch call gets the next tuple and returns its column values into the bound program variables. SQLFetch returns an exception (nonzero) code if there are no more tuples in the query result.

As we can see, using dynamic function calls requires a lot of preparation to set up the SQL statements and to bind statement parameters and query results to the appropriate program variables.

In CLI1 a single tuple is selected by the SQL query. Figure 13.11 shows an example of retrieving multiple tuples. We assume that appropriate C program variables have been declared as in Figure 13.1. The program segment in CLI2 reads (inputs) a department number and then retrieves the employees who work in that department. A loop then iterates over each employee record, one at a time, and prints the employee’s last name and salary.

Figure 13.11

Program segment CLI2, a C program segment that uses SQL/CLI for a query with a collection of tuples in its result.

//Program Segment CLI2:

     #include sqlcli.h ;

     void printDepartmentEmps() {

     SQLHSTMT stmt1 ;

     SQLHDBC con1 ;

     SQLHENV env1 ;

     SQLRETURN ret1, ret2, ret3, ret4 ;

     ret1 = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env1) ;

     if (!ret1) ret2 = SQLAllocHandle(SQL_HANDLE_DBC, env1, &con1) else exit ;

     if (!ret2) ret3 = SQLConnect(con1, "dbs", SQL_NTS, "js", SQL_NTS, "xyz", SQL_NTS) else exit ;

     if (!ret3) ret4 = SQLAllocHandle(SQL_HANDLE_STMT, con1, &stmt1) else exit ;

     SQLPrepare(stmt1, "select Lname, Salary from EMPLOYEE where Dno = ?",

SQL_NTS) ;

     prompt("Enter the Department Number: ", dno) ;

     SQLBindParameter(stmt1, 1, SQL_INTEGER, &dno, 4, &fetchlen1) ;

     ret1 = SQLExecute(stmt1) ;

     if (!ret1) {

     SQLBindCol(stmt1, 1, SQL_CHAR, &lname, 15, &fetchlen1) ;

     SQLBindCol(stmt1, 2, SQL_FLOAT, &salary, 4, &fetchlen2) ;

     ret2 = SQLFetch(stmt1) ;

     while (!ret2) {

     printf(lname, salary) ;

     ret2 = SQLFetch(stmt1) ;

     }

     }

}

2. JDBC: SQL Function Calls for Java Programming

We now turn our attention to how SQL can be called from the Java object-oriented programming language. The function libraries for this access are known as JDBC. The Java programming language was designed to be platform independent—that is, a program should be able to run on any type of computer system that has a Java interpreter installed. Because of this portability, many RDBMS vendors provide JDBC drivers so that it is possible to access their systems via Java programs. A JDBC driver is basically an implementation of the function calls specified in the JDBC application programming interface (API) for a particular vendor’s RDBMS. Hence, a Java program with JDBC function calls can access any RDBMS that has a JDBC driver available.

Because Java is object-oriented, its function libraries are implemented as classes. Before being able to process JDBC function calls with Java, it is necessary to import the JDBC class libraries, which are called java.sql.*. These can be downloaded and installed via the Web.

JDBC is designed to allow a single Java program to connect to several different data-bases. These are sometimes called the data sources accessed by the Java program. These data sources could be stored using RDBMSs from different vendors and could reside on different machines. Hence, different data source accesses within the same Java program may require JDBC drivers from different vendors. To achieve this flex-ibility, a special JDBC class called the driver manager class is employed, which keeps track of the installed drivers. A driver should be registered with the driver manager before it is used. The operations (methods) of the driver manager class include getDriver, registerDriver, and deregisterDriver. These can be used to add and remove drivers dynamically. Other functions set up and close connections to data sources, as we will see.

To load a JDBC driver explicitly, the generic Java function for loading a class can be used. For example, to load the JDBC driver for the Oracle RDBMS, the following command can be used:

Class.forName("oracle.jdbc.driver.OracleDriver")

This will register the driver with the driver manager and make it available to the program. It is also possible to load and register the driver(s) needed in the command line that runs the program, for example, by including the following in the command line:

-Djdbc.drivers = oracle.jdbc.driver

The following are typical steps that are taken when writing a Java application pro-gram with database access through JDBC function calls. We illustrate the steps by referring to the example JDBC1 in Figure 13.12, which reads a Social Security num-ber of an employee and prints the employee’s last name and salary.

        The JDBC library of classes must be imported into the Java program. These classes are called java.sql.*, and can be imported using line 1 in Figure 13.12. Any additional Java class libraries needed by the program must also be imported.

        Load the JDBC driver as discussed previously (lines 4 to 7). The Java exception in line 5 occurs if the driver is not loaded successfully.

Create appropriate variables as needed in the Java program (lines 8 and 9).

Figure 13.12

Program segment JDBC1, a Java program segment with JDBC.

//Program JDBC1:

     import java.io.* ;

     import java.sql.*

...

     class getEmpInfo {

     public static void main (String args []) throws SQLException, IOException {

     try { Class.forName("oracle.jdbc.driver.OracleDriver")

     } catch (ClassNotFoundException x) {

     System.out.println ("Driver could not be loaded") ;

     }

     String dbacct, passwrd, ssn, lname ;

     Double salary ;

     dbacct = readentry("Enter database account:") ;

     passwrd = readentry("Enter password:") ;

     Connection conn = DriverManager.getConnection

     ("jdbc:oracle:oci8:" + dbacct + "/" + passwrd) ;

     String stmt1 = "select Lname, Salary from EMPLOYEE where Ssn = ?" ;

     PreparedStatement p = conn.prepareStatement(stmt1) ;

     ssn = readentry("Enter a Social Security Number: ") ;

     p.clearParameters() ;

     p.setString(1, ssn) ;

     ResultSet r = p.executeQuery() ;

     while (r.next()) {

     lname = r.getString(1) ;

     salary = r.getDouble(2) ;

system.out.printline(lname + salary) ;

}}

}

Statement objects. In line 14 in Figure 13.12, a query string with a single parameter—indicated by the ? symbol—is created in the string variable stmt1. In line 15, an object p of type PreparedStatement is created based on the query string in stmt1 and using the connection object conn. In general, the programmer should use PreparedStatement objects if a query is to be executed multiple times, since it would be prepared, checked, and com-piled only once, thus saving this cost for the additional executions of the query.

        Setting the statement parameters. The question mark (?) symbol in line 14 represents a statement parameter, which is a value to be determined at run-time, typically by binding it to a Java program variable. In general, there could be several parameters, distinguished by the order of appearance of the question marks within the statement string (first ? represents parameter 1, second ? represents parameter 2, and so on), as we discussed previously.

        Before executing a PreparedStatement query, any parameters should be bound to program variables. Depending on the type of the parameter, differ-ent functions such as setString, setInteger, setDouble, and so on are applied to the PreparedStatement object to set its parameters. The appropriate function should be used to correspond to the data type of the param-eter being set. In Figure 13.12, the parameter (indicated by ?) in object p is bound to the Java program variable ssn in line 18. The function setString is used because ssn is a string variable. If there are n parameters in the SQL statement, we should have n set... functions, each with a different param-eter position (1, 2, ..., n). Generally, it is advisable to clear all parameters before setting any new values (line 17).

        Following these preparations, we can now execute the SQL statement referenced by the object p using the function executeQuery (line 19). There is a generic function execute in JDBC, plus two specialized functions: executeUpdate and executeQuery. executeUpdate is used for SQL insert, delete, or update statements, and returns an integer value indicating the number of tuples that were affected. executeQuery is used for SQL retrieval statements, and returns an object of type ResultSet, which we dis-cuss next.

        The ResultSet object. In line 19, the result of the query is returned in an object r of type ResultSet. This resembles a two-dimensional array or a table, where the tuples are the rows and the attributes returned are the columns. A ResultSet object is similar to a cursor in embedded SQL and an iterator in SQLJ. In our example, when the query is executed, r refers to a tuple before the first tuple in the query result. The r.next() function (line 20) moves to the next tuple (row) in the ResultSet object and returns NULL if there are no more objects. This is used to control the looping. The pro-

grammer can refer to the attributes in the current tuple using various get... functions that depend on the type of each attribute (for example, getString, getInteger, getDouble, and so on). The programmer can either use the attribute positions (1, 2) or the actual attribute names ("Lname", "Salary") with the get... functions. In our examples, we used the positional notation in lines 21 and 22.

In general, the programmer can check for SQL exceptions after each JDBC function call. We did not do this to simplify the examples.

Notice that JDBC does not distinguish between queries that return single tuples and those that return multiple tuples, unlike some of the other techniques. This is justifiable because a single tuple result set is just a special case.

In example JDBC1, a single tuple is selected by the SQL query, so the loop in lines 20 to 24 is executed at most once. The example shown in Figure 13.13 illustrates the retrieval of multiple tuples. The program segment in JDBC2 reads (inputs) a department number and then retrieves the employees who work in that depart-ment. A loop then iterates over each employee record, one at a time, and prints the employee’s last name and salary. This example also illustrates how we can execute a query directly, without having to prepare it as in the previous example. This tech-nique is preferred for queries that will be executed only once, since it is simpler to program. In line 17 of Figure 13.13, the programmer creates a Statement object (instead of PreparedStatement, as in the previous example) without associating it with a particular query string. The query string q is passed to the statement object s when it is executed in line 18.

This concludes our brief introduction to JDBC. The interested reader is referred to the Web site http://java.sun.com/docs/books/tutorial/jdbc/, which contains many further details about JDBC.