:::: MENU ::::

Posts Categorized / Data Access

  • May 21 / 2008
  • 0
Data Access, dbDigger

What is Open database connectivity (ODBC)

One of the underlying technologies in the Windows operating system is ODBC, which enables Windows-based programs to access a database through a driver. Rather than having a custom interface to each database, something you might very well have to write yourself, you can connect to the database of your choice through a driver.
The concept of ODBC is very similar to the concept of Windows printer drivers, which enables you to write your program without regard for the printer. Individual differences, which DOS programming forced you to address, are conveniently handled by the printer driver. The result is that you spend your time working on the tasks peculiar to your program, not on writing printer drivers. ODBC applies this idea to databases. The visual part of ODBC resides in the control panel.
The Open Database Connectivity (ODBC) interface by Microsoft allows applications to access data in database management systems (DBMS) using SQL as a standard for accessing the data. ODBC permits maximum interoperability, which means a single application can access different DBMS. Application end users can then add ODBC database drivers to link the application to their choice of DBMS.

The ODBC interface defines:
1. A library of ODBC function calls of two types:

  • Core functions that are based on the X/Open and SQL Access Group Call Level Interface specification
  • Extended functions that support additional functionality, including scrollable cursors

2. SQL syntax based on the X/Open and SQL Access Group SQL CAE specification (1992)
3. A standard set of error codes
4. A standard way to connect and logon to a DBMS
5. A standard representation for data types

The ODBC solution for accessing data led to ODBC database drivers, which are dynamic-link libraries on Windows and shared objects on UNIX. These drivers allow an application to gain access to one or more data sources. ODBC provides a standard interface to allow application developers and vendors of database drivers to exchange data between applications and data sources.

The ODBC architecture has four components:

  • Application, which processes and calls ODBC functions to submit SQL statements and retrieve results.
  • Driver Manager, which loads drivers for the application.
  • Driver, which processes ODBC function calls, submits SQL requests to a specific data source, and returns results to the application.
  • Data source, which consists of the data to access and its associated operating system, DBMS, and network platform (if any) used to access the DBMS

Using ODBC, an application developer can develop, compile, and ship an application without targeting a specific DBMS. In this scenario, the application developer does not need to use embedded SQL; therefore, he does not need to recompile the application for each new environment.

You have two choices when deciding how to implement data access:

  • Custom coding to the database’s native interface, such as Oracle’s OCI (Oracle Call Interface)
  • Use third-party ODBC drivers such as DataDirect Technologies’ wire protocol ODBC drivers.

Custom coding data access to the native proprietary interface of each database results in non-portable code. This approach requires you to re-program your application every time you change databases and even database versions. This makes it difficult to maintain the application and is not very cost effective. And, compared to using wire protocol ODBC drivers, there is not even a performance advantage.
DataDirect Technologies ODBC drivers provide the following advantages over other ODBC drivers:

  • DataDirect Technologies offers a Wire Protocol ODBC driver for every major database – Oracle, DB2, SQL Server, Sybase and Informix – on both 32-bit and 64-bit Windows and UNIX platforms. Wire Protocol technology eliminates the need for client libraries, simplifying development and eliminating configuration and support issues.
  • All major database vendors, including Oracle, IBM, Microsoft, Sybase and Progress, embed DataDirect Technologies’ ODBC drivers as part of their strategic product offerings.
  • DataDirect Technologies offers award-winning 24×7 technical support to ensure rapid response to customer concerns (see the SupportLink website for more information.)
  • May 21 / 2008
  • 0
Data Access, dbDigger

Java database connectivity (JDBC)

Call-level interfaces such as JDBC are programming interfaces allowing external access to SQL database manipulation and update commands. They allow the integration of SQL calls into a general programming environment by providing library routines which interface with the database. In particular, Java based JDBC has a rich collection of routines which make such an interface extremely simple and intuitive.
JDBC helps you to write java applications that manage these three programming activities:
  • Connect to a data source, like a database
  • Send queries and update statements to the database
  • Retrieve and process the results received from the database in answer to your query
The JDBC 1.0 API provided the basic framework for data access, consisting primarily of the following interfaces and classes:
  • Driver
  • DriverManager
  • Connection
  • Statement
  • PreparedStatement
  • CallableStatement
  • ResultSet
  • DatabaseMetaData
  • ResultSetMetaData
  • Types
The following simple code fragment gives a simple example of these three steps:
Connection con = DriverManager.getConnection
( “jdbc:myDriver:wombat”, “myLogin”,”myPassword”);Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(“SELECT a, b, c FROM Table1”);
while (rs.next()) {
int x = rs.getInt(“a”);
String s = rs.getString(“b”);
float f = rs.getFloat(“c”);

JDBC includes four components:
1. The JDBC API: The JDBC™ API provides programmatic access to relational data from the Java™ programming language. Using the JDBC API, applications can execute SQL statements, retrieve results, and propagate changes back to an underlying data source. The JDBC API can also interact with multiple data sources in a distributed, heterogeneous environment. The JDBC API is part of the Java platform, which includes the Java™ Standard Edition (Java™ SE ) and the Java™ Enterprise Edition (Java™ EE). The JDBC 4.0 API is divided into two packages: java.sql and javax.sql. Both packages are included in the Java SE and Java EE platforms.
2. JDBC Driver Manager: The JDBC DriverManager class defines objects which can connect Java applications to a JDBC driver. DriverManager has traditionally been the backbone of the JDBC architecture. It is quite small and simple. The Standard Extension packages javax.naming and javax.sql let you use a DataSource object registered with a Java Naming and Directory Interface™ (JNDI) naming service to establish a connection with a data source. You can use either connecting mechanism, but using a DataSource object is recommended whenever possible.
3. JDBC Test Suite: The JDBC driver test suite helps you to determine that JDBC drivers will run your program. These tests are not comprehensive or exhaustive, but they do exercise many of the important features in the JDBC API.
4. JDBC-ODBC Bridge: The Java Software bridge provides JDBC access via ODBC drivers. Note that you need to load ODBC binary code onto each client machine that uses this driver. As a result, the ODBC driver is most appropriate on a corporate network where client installations are not a major problem, or for application server code written in Java in a three-tier architecture.
Here is an easy way of visualizing what happens in a call level interface: You are writing a normal Java program. Somewhere in the program, you need to interact with a database. Using standard library routines, you open a connection to the database. You then use JDBC to send your SQL code to the database, and process the results that are returned. When you are done, you close the connection. Such an approach has to be contrasted with the precompilation route taken with Embedded SQL. The latter has a precompilation step, where the embedded SQL code is converted to the host language code (C/C++). Call-level interfaces do not require precompilation and thus avoid some of the problems of Embedded SQL. The result is increased portability and a cleaner client-server relationship.
The JDBC API supports both two-tier and three-tier processing models for database access.
In the two-tier model, a Java applet or application talks directly to the data source. This requires a JDBC driver that can communicate with the particular data source being accessed. A user’s commands are delivered to the database or other data source, and the results of those statements are sent back to the user. The data source may be located on another machine to which the user is connected via a network. This is referred to as a client/server configuration, with the user’s machine as the client, and the machine housing the data source as the server. The network can be an intranet, which, for example, connects employees within a corporation, or it can be the Internet.
In the three-tier model, commands are sent to a “middle tier” of services, which then sends the commands to the data source. The data source processes the commands and sends the results back to the middle tier, which then sends them to the user. MIS directors find the three-tier model very attractive because the middle tier makes it possible to maintain control over access and the kinds of updates that can be made to corporate data. Another advantage is that it simplifies the deployment of applications. Finally, in many cases, the three-tier architecture can provide performance advantages.
Result Sets and Cursors
The rows that satisfy the conditions of a query are called the result set. The number of rows returned in a result set can be zero, one, or many. A user can access the data in a result set one row at a time, and a cursor provides the means to do that. A cursor can be thought of as a pointer into a file that contains the rows of the result set, and that pointer has the ability to keep track of which row is currently being accessed. A cursor allows a user to process each row of a result set from top to bottom and consequently may be used for iterative processing. Most DBMSs create a cursor automatically when a result set is generated. Earlier JDBC API versions added new capabilities for a result set’s cursor, allowing it to move both forward and backward and also allowing it to move to a specified row or to a row whose position is relative to another row.
Stored Procedures
A stored procedure is a group of SQL statements that can be called by name. In other words, it is executable code, a mini-program, that performs a particular task that can be invoked the same way one can call a function or method. Traditionally, stored procedures have been written in a DBMS-specific programming language. The latest generation of database products allows stored procedures to be written using the Java programming language and the JDBC API. Stored procedures written in the Java programming language are bytecode portable between DBMSs. Once a stored procedure is written, it can be used and reused because a DBMS that supports stored procedures will, as its name implies, store it in the database. The following code is an example of how to create a very simple stored procedure using the Java programming language. Note that the stored procedure is just a static Java method that contains normal JDBC code. It accepts two input parameters and uses them to change an employee’s car number.
import java.sql.*;
public class UpdateCar {
public static void UpdateCarNum(int carNo, int empNo)
throws SQLException {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DriverManager.getConnection(“jdbc:default:connection”);
pstmt = con.prepareStatement(
pstmt.setInt(1, carNo);
pstmt.setInt(2, empNo);
finally {
if (pstmt != null) pstmt.close();
A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. Stored procedures are used to encapsulate a set of operations or queries to execute on a database server. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters.
This simple stored procedure has no parameters. Even though most stored procedures do something more complex than this example, it serves to illustrate some basic points about them. As previously stated, the syntax for defining a stored procedure is different for each DBMS. For example, some use begin . . . end , or other keywords to indicate the beginning and ending of the procedure definition. In some DBMSs, the following SQL statement creates a stored procedure:
create procedure SHOW_SUPPLIERS
order by SUP_NAME
The following code puts the SQL statement into a string and assigns it to the variable createProcedure, which we will use later:
String createProcedure = “create procedure SHOW_SUPPLIERS ” +
“as ” +
“order by SUP_NAME”;
The following code fragment uses the Connection object con to create a Statement object, which is used to send the SQL statement creating the stored procedure to the database:
Statement stmt = con.createStatement();
The procedure SHOW_SUPPLIERS is compiled and stored in the database as a database object that can be called, similar to the way you would call a method.
Calling a Stored Procedure from JDBC. JDBC allows you to call a database stored procedure from an application written in the Java programming language. The first step is to create a CallableStatement object. As with Statement and PreparedStatement objects, this is done with an open Connection object. A callableStatement object contains a call to a stored procedure; it does not contain the stored procedure itself. The first line of code below creates a call to the stored procedure SHOW_SUPPLIERS using the connection con. The part that is enclosed in curly braces is the escape syntax for stored procedures. When the driver encounters “{call SHOW_SUPPLIERS}”, it will translate this escape syntax into the native SQL used by the database to call the stored procedure named SHOW_SUPPLIERS.
CallableStatement cs = con.prepareCall(“{call SHOW_SUPPLIERS}”);
ResultSet rs = cs.executeQuery();
The ResultSet rs will be similar to the following:
Acme, Inc.Colombian
Acme, Inc.Colombian_Decaf
Superior CoffeeFrench_Roast
Superior CoffeeFrench_Roast_Decaf
The High GroundEspresso
Note that the method used to execute cs is executeQuery because cs calls a stored procedure that contains one query and thus produces one result set. If the procedure had contained one update or one DDL statement, the method executeUpdate would have been the one to use. It is sometimes the case, however, that a stored procedure contains more than one SQL statement, in which case it will produce more than one result set, more than one update count, or some combination of result sets and update counts. In this case, where there are multiple results, the method execute should be used to execute the CallableStatement .
The class CallableStatement is a subclass of PreparedStatement, so a CallableStatement object can take input parameters just as a PreparedStatement object can. In addition, a CallableStatement object can take output parameters, or parameters that are for both input and output. INOUT parameters and the method execute are used rarely.
Metadata in jdbc
JDBC provides the interface DatabaseMetaData, which a driver writer must implement so that its methods return information about the driver and/or DBMS for which the driver is written. For example, a large number of methods return whether or not the driver supports a particular functionality. This interface gives users and tools a standardized way to get metadata. In general, developers writing tools and drivers are the ones most likely to be concerned with metadata.
Types of Drivers
There are many possible implementations of JDBC drivers. These implementations are categorized as follows:
  • Type 1 – drivers that implement the JDBC API as a mapping to another data access API, such as ODBC. Drivers of this type are generally dependent on a native library, which limits their portability. The JDBC-ODBC Bridge driver is an example of a Type 1 driver.
  • Type 2 – drivers that are written partly in the Java programming language and partly in native code. These drivers use a native client library specific to the data source to which they connect. Again, because of the native code, their portability is limited.
  • Type 3 – drivers that use a pure Java client and communicate with a middleware server using a database-independent protocol. The middleware server then communicates the client requests to the data source.
  • Type 4 – drivers that are pure Java and implement the network protocol for a specific data source. The client connects directly to the data source.
Establishing A Connection
This involves two steps:
1. Load the vendor specific driver
To ensure portability and code reuse, the API was designed to be as independent of the version or the vendor of a database as possible. Since different DBMS have different behavior, we need to tell the driver manager which DBMS we wish to use, so that it can invoke the correct driver.
An Oracle driver is loaded using the following code snippet:
SQL Server driver is loaded with following code
Class.forName( “sun.jdbc.odbc.JdbcOdbcDriver” )
2 Make the connection
Once the driver is loaded and ready for a connection to be made, you may create an instance of a Connection object using:
String url = “jdbc:odbc:CMS”;
con = DriverManager.getConnection(url,username, password);
The first string is the URL for the database including the protocol (jdbc), the vendor (odbc), The username and password are your username and password, the same as you would enter into SQL Server to access your account.
That’s it! The connection returned in the last step is an open connection which we will use to pass SQL statements to the database. In this code snippet, con is an open connection, and we will use it below.
Creating JDBC Statements
A JDBC Statement object is used to send your SQL statements to the DBMS, and should not to be confused with an SQL statement. A JDBC Statement object is associated with an open connection, and not any single SQL Statement. You can think of a JDBC Statement object as a channel sitting on a connection, and passing one or more of your SQL statements (which you ask it to execute) to the DBMS.
An active connection is needed to create a Statement object. The following code snippet, using our Connection object con, does it for you:
Statement stmt = con.createStatement() ;
At this point, a Statement object exists, but it does not have an SQL statement to pass on to the DBMS. We learn how to do that in a following section.
Creating JDBC PreparedStatement
The PreparedStatement object is a close relative of the Statement object. Both accomplish roughly the same thing: running SQL statements. PreparedStatement, however, allows you to precompile your SQL and run it repeatedly, adjusting specific parameters as necessary. Since processing SQL strings is a large part of a database’s overhead, getting compilation out of the way at the start can significantly improve performance. With proper use, it can also simplify otherwise tedious database tasks. Sometimes, it is more convenient or more efficient to use a PreparedStatement object for sending SQL statements to the DBMS. The main feature which distinguishes it from its superclass Statement, is that unlike Statement, it is given an SQL statement right when it is created. This SQL statement is then sent to the DBMS right away, where it is compiled. Thus, in effect, a PreparedStatement is associated as a channel with a connection and a compiled SQL statement.
The advantage offered is that if you need to use the same, or similar query with different parameters multiple times, the statement can be compiled and optimized by the DBMS just once. Contrast this with a use of a normal Statement where each use of the same SQL statement requires a compilation all over again.
PreparedStatements are also created with a Connection method. The following snippet shows how to create a parameterized SQL statement with three input parameters:
PreparedStatement prepareUpdatePrice = con.prepareStatement(
“UPDATE Sells SET price = ? WHERE bar = ? AND beer = ?”);
Before we can execute a PreparedStatement, we need to supply values for the parameters. This can be done by calling one of the setXXX methods defined in the class PreparedStatement. Most often used methods are setInt, setFloat, setDouble, setString etc.
You can set these values before each execution of the prepared statement.
Continuing the above example, we would write:
prepareUpdatePrice.setInt(1, 3);
prepareUpdatePrice.setString(2, “Bar Of Foo”);
prepareUpdatePrice.setString(3, “BudLite”);
Executing CREATE/INSERT/UPDATE Statements
Executing SQL statements in JDBC varies depending on the “intention” of the SQL statement. DDL (data definition language) statements such as table creation and table alteration statements, as well as statements to update the table contents, are all executed using the method executeUpdate. These commands change the state of the database, hence the name of the method contains “Update”.
The following snippet has examples of executeUpdate statements.
Statement stmt = con.createStatement();
stmt.executeUpdate(“CREATE TABLE Sells ” +
“(bar VARCHAR2(40), beer VARCHAR2(40), price REAL)” );
stmt.executeUpdate(“INSERT INTO Sells ” +
“VALUES (‘Bar Of Foo’, ‘BudLite’, 2.00)” );
String sqlString = “CREATE TABLE Bars ” +
“(name VARCHAR2(40), address VARCHAR2(80), license INT)” ;
When executeUpdate is used to call DDL statements, the return value is always zero, while data modification statement executions will return a value greater than or equal to zero, which is the number of tuples affected in the relation.
Executing SELECT Statements
As opposed to the previous section statements, a query is expected to return a set of tuples as the result, and not change the state of the database. Not surprisingly, there is a corresponding method called executeQuery, which returns its results as a ResultSet object:
String bar, beer ; float price ;
ResultSet rs = stmt.executeQuery(“SELECT * FROM Sells”);
while ( rs.next() ) {
bar = rs.getString(“bar”);
beer = rs.getString(“beer”);
price = rs.getFloat(“price”);
System.out.println(bar + ” sells ” + beer + ” for ” + price + “
The bag of tuples resulting from the query are contained in the variable rs which is an instance of ResultSet. A set is of not much use to us unless we can access each row and the attributes in each row. The ResultSet provides a cursor to us, which can be used to access each row in turn. The cursor is initially set just before the first row. Each invocation of the method next causes it to move to the next row, if one exists and return true, or return false if there is no remaining row. We can use the getXXX method of the appropriate type to retrieve the attributes of a row. In the previous example, we used getString and getFloat methods to access the column values. Notice
that we provided the name of the column whose value is desired as a parameter to the method. Also note that the VARCHAR2 type bar, beer have been converted to Java String, and the REAL to Java float.
Equivalently, we could have specified the column number instead of the column name, with the same result. Thus the relevant statements would be:
bar = rs.getString(1);
price = rs.getFloat(3);
beer = rs.getString(2);
While working with a PreparedStatement, we would execute a query by first plugging in the values of the parameters, and then invoking the executeQuery on it.
ResultSet rs = prepareUpdatePrice.executeQuery() ;
Accessing ResultSet
JDBC also offers you a number of methods to find out where you are in the result set using getRow, isFirst, isBeforeFirst, isLast, isAfterLast.
There are means to make scroll-able cursors allow free access of any row in the result set. By default, cursors scroll forward only and are read only. When creating a Statement for a Connection, you can change the type of ResultSet to a more flexible scrolling or updatable model:
Statement stmt = con.createStatement(
ResultSet rs = stmt.executeQuery(“SELECT * FROM Sells”);
The different options for types are TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, and TYPE_SCROLL_SENSITIVE. You can choose whether the cursor is read-only or updatable using the options CONCUR_READ_ONLY, and CONCUR_UPDATABLE. With the default cursor, you can scroll forward using rs.next().
With scroll-able cursors you have more options:
rs.absolute(3); // moves to the third tuple
rs.previous(); // moves back one tuple (tuple 2)
rs.relative(2); // moves forward two tuples (tuple 4)
rs.relative(-3); // moves back three tuples (tuple 1)
JDBC allows SQL statements to be grouped together into a single transaction. Thus, we can ensure the ACID properties using JDBC transactional features. Transaction control is performed by the Connection object. When a connection is created, by default it is in the auto-commit mode. This means that each individual SQL statement is treated as a transaction by itself, and will be committed as soon as it’s execution finished. (This is not exactly precise, but we can gloss over this subtlety for most purposes).
We can turn off auto-commit mode for an active connection with :
con.setAutoCommit(false) ;
and turn it on again with :
con.setAutoCommit(true) ;
Once auto-commit is off, no SQL statements will be committed (that is, the database will not be permanently updated) until you have explicitly told it to commit by invoking the commit() method: con.commit() ;
At any point before commit, we may invoke rollback() to rollback the transaction, and restore values to the last commit point (before the attempted updates).
Here is an example which ties these ideas together:
Statement stmt = con.createStatement();
stmt.executeUpdate(“INSERT INTO Sells VALUES(‘Bar Of Foo’,
‘BudLite’, 1.00)” );
stmt.executeUpdate(“INSERT INTO Sells VALUES(‘Bar Of Joe’,
‘Miller’, 2.00)” );
We can also set transaction isolation levels as desired. For example, we can set the transaction isolation level to TRANSACTION_READ_COMMITTED, which will not allow a value to be accessed until after it has been committed, and forbid dirty reads. There are five such values for isolation levels provided in the Connection interface. By default, the isolation level is serializable. JDBC allows us to find out the transaction isolation level the database is set to (using the Connection method getTransactionIsolation) and set the appropriate level (using the Connection method setTransactionIsolation method).
Usually rollback will be used in combination with Java’s exception handling ability to recover from (un)predictable errors. Such a combination provides an excellent and easy mechanism for handling data integrity. We study error handling using JDBC in the next section.
Handling Errors with Exceptions
Any JDBC object that encounters an error serious enough to halt execution throws a SQLException. For example, database connection errors, malformed SQL statements, and insufficient database privileges all throw SQLException objects.
The SQLException class extends the normal java.lang.Exception class and defines an additional method called getNextException( ). This allows JDBC classes to chain a series of SQLException objects together. SQLException also defines the getSQLState( ) and getErrorCode( ) methods to provide additional information about an error. The value returned by getSQLState( ) is one of the ANSI-92 SQL state codes. An extremely conscientious application might have a catch block that looks something like this:
try {
// Actual database code
catch (SQLException e) {
while(e != null) {
System.out.println(“nSQL Exception:”);
System.out.println(e.getMessage( ));
System.out.println(“ANSI-92 SQL State: ” + e.getSQLState( ));
System.out.println(“Vendor Error Code: ” + e.getErrorCode( ));
e = e.getNextException( );
SQL Warnings
JDBC classes also have the option of generating (but not throwing) a SQLWarning exception when something is not quite right, but at the same time, not sufficiently serious to warrant halting the entire program. For example, attempting to set a transaction isolation mode that is not supported by the underlying database might generate a warning rather than an exception. Remember, exactly what qualifies as a warning condition varies by database.
SQLWarning encapsulates the same information as SQLException and is used in a similar fashion. However, unlike SQLException objects, which are caught in try/catch blocks, warnings are retrieved using the getWarnings( ) methods of the Connection, Statement, ResultSet, CallableStatement, and PreparedStatement interfaces. SQLWarning implements the getMessage( ), getSQLState( ), and getErrorCode( ) methods in the same manner as SQLException.
JDBC give access to such information by providing two levels of error conditions:
SQLException and SQLWarning.
SQLExceptions are Java exceptions which, if not handled, will terminate the application. SQLWarnings are subclasses of SQLException, but they represent nonfatal errors or unexpected conditions, and as such, can be ignored.
In Java, statements which are expected to “throw” an exception or a warning are enclosed in a try block. If a statement in the try block throws an exception or a warning, it can be “caught” in one of the corresponding catch statements.
Each catch statement specifies which exceptions it is ready to “catch”.
Consult us to explore the Databases. Contact us