Interactive SQL suits for
Two add-on techniques are commonly used
Query processing, in general, takes place as depicted in the following picture. A query is first specified. Then it is compiled and executed. After execution the answer of the query is processed within a program loop.
In embedded SQL, SQL-statements can be used mixed with the statements of the used programming language, for example C or Cobol. In order to distinguish the SQL-statements from the statements of the host programming language these statements are marked as belonging to the SQL (EXEC SQL - marks). A pre-compiler is needed to analyze the embedded SQL-statements and to transform them into calls of functions defined in a database interface library. These functions will execute the actual database operations. The output of the pre-compiler is then compiled using the standard compiler of the used programming language.
Database interface libraries are specific to database management systems as well as the pre-compilers. However, the way of how the database operations are embedded in the programs is standardized. Thus it should be possible to change the database management system and just to recompile the program to work with a new dbms. The standard specifies, for example:
An example in C:
/* include the header files */
#include <stdio.h>
#include <string.h>
/* include definitions of SQL communication Area */
#include <sqlca.h>
#define ID_len 20
/* define the variables to provide data for SQL*/
/* EXEC SQL marks statements ment for the pre-compiler */
EXEC SQL BEGIN DECLARE SECTION;
varchar identifier[ID_len], password[ID_len];
varchar searchkey[40];
EXEC SQL END DECLARE SECTION;
/* define variables to reveive data from the database */
struct {
varchar name[40];
varchar lastcontact[20];
} studentrow;
/* define the indicatorvariables. These control the stutus of
answer values - real or null, The value is non-zero in case
of error or exception */
struct {short name_ind, lastcontact_ind; } student_ind;
int student_counter=0;
/* define some auxiliary functions */
void ask_identifier();
void ask_searchkey();
void sql_error();
/* the main program */
main() {
/* Register function sql_error() as the error handler */
EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error \n");
/* get the user id and password */
ask_identifier(&identifier,&password);
/* establish a connection to the database */
/* A colon in front of variable name indicates that the value of
the variable should be used */
EXEC SQL CONNECT :identifier IDENTIFIED BY :password;
/* define the query, the value for variable searchkey is not
evaluated yet
*/
EXEC SQL DECLAEE student_query CURSOR FOR
SELECT rpad(substr(lastname||' '||firstnames,1,40),40) name,
to_char(date_last_used,'DD.MM.YYYY')
FROM STUDENT
WHERE upper(lastname) LIKE upper(:searckey)
ORDER BY name;
/* ask the user to give the search condition */
ask_searchkey(&searchkey);
/* execute the query */
/* the value for variable searchkey is evaluated now */
EXEC SQL OPEN student_search;
/* specify that the control leaves the loop when the are no more
records to process */
EXEC SQL WHENEVER NOT FOUND DO break;
/* processing of the answer records */
for (;;) {
/* activate the next row of the answer */
EXEC SQL FETCH student_search
INTO :studentrow INDICATOR :student_ind;
/* print the retrieved record */
printf(" %s latest contact: %s \n", studentrow.name.arr,
studentrow.latestcontact.arr);
student_counter++;
}
printf("\nNumber of students; %d",student_counter);
/* records have been processed, release the resources */
ECEC SQL CLOSE student_search;
/* just to be sure */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}
void ask_identifier(varchar *username, varchar *userpasswd) {
/* ask the user for user id and password */
/* code not included here */
}
void ask_searchkey(varchar *key) {
/* ask the user for the mask to be compared with the lastname */
/* code not included here */
}
void sql_error(char *msg) {
/* report on error */
char err_msg[512];
int buff_len, msg_len;
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\n%s\n",msg);
/* sqlglm() provides the message text */
buff_len = sizeof(err_msg);
sqlglm(err_msg,&buff_len, &msg_len);
printf("%.*s\n", msg_len,err_msg);
/* Rollback, if database has been modified */
EXEC SQL ROLLBACK RELEASE;
exit(1);
}
|
A kernel concept in database programming is cursor (the result set). Cursor is
Database operations may fail due to various reasons. The program should always check whether the operation succeeded or failed. In the above program this is done using WHENEVER clauses. This can also be done by examining the variable sqlstate (is non-zero if the operation fails).
There are also libraries, for example ODBC (Microsoft Open Database Connection) and JDBC (for Java), that are independent of database management systems. These libraries make it possible to use various database management systems, even within one program. On the programmers point of view these libraries provide a common interface for database programming. However, to use a certain type of database, a driver for that particular type is needed. For example, an Oracle driver is needed in order to use Oracle databases. In the following we outline the use of database in Java programming language using JDBC -API.
JDBC is a class library that provides classes and methods for using a database. The kernel classes of the library are
final String dbDriver="oracle.jdbc.OracleDriver";
final String dbServer= "jdbc:oracle:thin:@bodbacka.cs.helsinki.fi:1521:test";
final String dbUser="scott"; final String dbPassword)"tiger"; try{
Class.forName(dbDriver); // load driver
} catch (ClassNotFoundException e) {
out.println("Couldn't find driver "+dbDriver);
return null;
}
Connection con=null;
try {
con = DriverManager.getConnection(dbServer,dbUser,dbPassword);
} catch (SQLException se) {
out.println("Couldn\'t get connection to "+dbServer+ " for "+ dbUser+"<br>");
out.println(se.getMessage());
}
A connection should be closed (using close-method) when it's no longer needed. Closing a connection releases resources in both the client workstation and in the database server.
A connection instance is needed for creating statements.
String eQuery = "select EMPNO, ENAME, JOB, "+ResultSet provides methods for processing the answer of the query. Boolean function next() activates the next row of the answer table. When it is called the first time it activates the first row. It returns the value 'false' when there are no more rows to activate. Otherwise it returns true.
"MGR, to_char(HIREDATE,'DD.MM.YYYY') HIREDATE, SAL, "+ "COMM, DEPTNO "+
"From EMP "+
"ORDER BY ENAME ";
Statement stmt= con.createStatement(); ResultSet rs= stmt.executeQuery(equery);
Originally ResultSet did not provide any means for proceeding in reverse direction. It could be used only for one way forward pass through the answer rows. However, new versions implement a technique known as scroll cursor. This makes also reverse proceeding possible.
ResultSet provides a collection of data type specific functions, for example, getString, getBoolean, getInt, getDate, etc. to retrieve data from the active row. These functions use either the name of the column or the sequence number of the column as their parameter.
String name= rs.getString("ename"); // column ename
Int pno= rs.getInt(1); // first column
The get-functions are able to perform some data type conversions, for example,
from string to integer or vice versa. If the conversion fails, an exception
(SQLException) is raised. Similar exception is raised also in other error conditions.
Each database operation may result in some type of error. The program should
be constructed so that it catches the exceptions and provides the error messages.
SQL-queries may return NULL-values. Some get-operations, for example getString, return Java nulls for null values, but some return real values, for example, getInt returns a zero (0). Method wasNull may be used for checking whether the returned value was null.
The answer of a query is processed within a loop in a program. The following program prints an employee list as an HTML-table and finds out the biggest employee number.
// list the employees
out.println("<H2>Current employees</H2>");
out.println("<table border=1>"+
"<tr>"+
"<th "+HDC+">NAME</th><th "+HDC+">EMPNO</TH><TH "+HDC+">JOB</th>"+
"<TH "+HDC+">MGR</TH><TH "+HDC+">HIREDATE</TH><TH "+HDC+">SAL</TH><TH "+HDC+
">COMM</TH><TH "+HDC+">DEPTNO"+
"</TH></TR>");
int bigNo=0; // the biggest empno
int curNo=0;
try {
stmt = con.createStatement();
rs = stmt.executeQuery(eQuery);
// list the employees
while(rs.next()) {
out.println("<TR>");
out.println("<TD>"+rs.getString("ENAME")+"</TD>");
curNo= rs.getInt("EMPNO");
if (curNo>bigNo)
bigNo=curNo;
out.println("<TD>"+curNo+"</TD>");
out.println("<TD>"+rs.getString("JOB")+"</TD>");
out.println("<TD>"+rs.getString("MGR")+"</TD>");
out.println("<TD>"+rs.getString("HIREDATE")+"</TD>");
out.println("<TD>"+rs.getString("SAL")+"</TD>");
out.println("<TD>"+rs.getString("COMM")+"</TD>");
out.println("<TD>"+rs.getString("DEPTNO")+"</TD>");
out.println("</TR>");
}
out.println("</table><p><hr>");
} catch (SQLException ex) { /* report about error */ }
Operations that change the contents or the structure of the database are executed using the method Statement.executeUpdate. For example,
stmt.executeUpdate("update emp " +
"set sal= sal + 10000 " +
" where ename= 'Laine'");
gives a small rise of salary to an employee.
Parameterized queries are prepared for use, i.e. compiled to be executed repeatedly. JDBC contains the class PreparedStatement that is a subclass of Statement. This class provides the services for working with parametrized database operations. A parameterized SQL-operation is given as a parameter to the function Connection.prepareStatement that creates an instance of class PreparedStatement.
PreparedStatement pst = con.prepareStatement(
"select ename,hiredate,sal"+
"from emp"+
"where ename like ?" );
A question mark (?) in the SQL-operation indicates a parameter. Parameters must
be assigned values before the operation is executed. PreparedStatement provides
data type specific set-functions for assigning these values (setInt, setString,
setDate, etc). Set-functions have two parameters: the sequence number of the
parameter (question mark) and the value to be assigned for the parameter. For
example,
pst.setString(1,"Smi%;%");
assigns the value 'Smi%' to the first parameter of the query in pst. so that
when executed the query will be
select ename,hiredate,sal
from emp
where ename like 'Smi%'
The operation is executed using executeQuery or executeUpdate methods of PreparedStatetement.
These methods do not require parameters.
It is also possible to use dynamic SQL. This is used , for example, when the programmer does not know what kind of answer is obtained as the result of a query (what are the columns in the answer). To find this out, the programmer may use functions that provide information about the answer (metadata) and then use this information for retrieving data out of the answer.
Oracle PL/SQL is an example of a database programming language. It is based on Ada programming language and provides a subset of Ada functionality. It is intended for coding database procedures. For database operations it provides
for row in query loop ..... end loop;
Currently many database applications are client-server applications. The database resides in one or more database servers and the user interfaces work on different client computers. Computer networks and data communication techniques are used to connect the clients and the servers. Because both the clients and the server have capacity to process data, the workload can be divided between them. Typically the database server takes care of database operations and the clients take care of the user interface and the validation of input data. Information concerning the user interface need not be passed between the clients and the server, only the queries and their answers.
It is also possible to balance the workload between the clients and the server. The designer has to think what tasks to carry out in the server and what in the clients.
If all the processing is done in the server