JDBC Java Program Using JDBC TYPE 3 Driver

A Sample Java Program Using JDBC TYPE 3 Driver. This driver is called as Network Protocol Pure Java Driver. This driver would only be the option when DB vendor supplied Type II & IV drivers are not available.

Software: www.idssoftware.com

Server:

a) Install IDSServer software

b) d:\IDSServer\IDSS.exe

(The above said .exe runs as system startup service. IDSS.exe is a server socket program which runs on port number 12.)

c) Notice one more dir called

d:\IDSServer\classes>

jdk11drv.jar, jdk13drv.jar, jdk14drv.jar

Client:a) Client must demand server to download jdk14drv.jar file into client system and the same must be updated in CLASSPATH

[Note: Software installation is not required]

PATH: (only server)

d:\IDSServer

CLASSPATH (server & client)

d:\IDSServer\classes\jdk14drv.jar

Arch:

Driver: ids.driver.IDSDriver

URL: jdbc:ids://abc:12/conn?dsn='oracleSysDSN'

Procedure creation

c:> sqlplus scott/tiger

SQL> cle scr

sql> create or replace procedure emp_sal_proc(eno IN number, sal1 OUT number) IS
BEGIN
SELECT sal INTO sal1 FROM emp WHERE empno=eno;
END;

// Java Program

// ProcExecTest.java

import java.sql.*;
public class ProcExecTest
{
public static void main(String rags[]) throws Exception
{
Class.forName("ids.driver.IDSDriver");
Connection con=DriverManager.getConnection
("jdbc:ids://abc:12/conn?dsn='oracleSysDSN'", "scott", "tiger");
CallableStatement cstmt=con.prepareCall("{call emp_sal_proc(?,?)}");
cstmt.setInt(1, Integer.parseInt(rags[0]));
cstmt.registerOutParameter(2, Types.DOUBLE);
cstmt.execute();
System.out.println(cstmt.getDouble(2));
cstmt.close();
con.close();
}// main()
}// class

Read more...

JDBC Drivers Types

JDBC Drivers are set of classes and interfaces which implements JDBC specification. There are mainly four type of JDBC drivers registered under SUN.

Type 1 - JDBC-ODBC Bridge Driver

Driver which converts JDBC calls into ODBC calls and interact with the ODBC driver is called as DBC-ODBC Bridge Driver. A JDBC-ODBC bridge provides JDBC API access via one or more ODBC drivers, in many cases native database client code must be loaded on each client machine which uses this type of driver. Hence, this kind of driver is generally most appropriate when automatic installation and downloading of a Java technology application is not important.

Type 2 - Native API Partly Java Driver

Driver which converts JDBC call into database specific native call is called as type 2 driver. A native-API partly Java technology-enabled driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.

Type 3 - Network protocol Pure Java Driver

Driver which converts JDBC call into server specific newtwork call and interacts with the server driver is called as Network protocol Driver. A net-protocol fully Java technology-enabled driver translates JDBC API calls into a DBMS-independent net protocol which is then translated to a DBMS protocol by a server. This net server middleware is able to connect all of its Java technology-based clients to many different databases. The specific protocol used depends on the vendor.

In general, this is the most flexible JDBC API alternative. It is likely that all vendors of this solution will provide products suitable for Intranet use. In order for these products to also support Internet access they must handle the additional requirements for security, access through firewalls, etc., that the Web imposes. Several vendors are adding JDBC technology-based drivers to their existing database middleware products.

Type 4 - Native Protocol pure Java Driver

Driver which converts JDBC call into database specific native call is called as type 4 driver. A native-protocol fully Java technology-enabled driver converts JDBC technology calls into the network protocol used by DBMS directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. Since many of these protocols are proprietary the database vendors themselves will be the primary source for this style of driver. Several database vendors have these in progress.

Read more...

JDBC 4.0

Java Database Connectivity 4.0

Java SE 6 includes several enhancements to the Java Database Connectivity (JDBC) API. These enhancements will be released as JDBC version 4.0. The main objectives of the new JDBC features are to provide a simpler design and better developer experience. This article provides an overview of the JDBC 4.0 enhancements and what benefits they offer to enterprise Java developers. JDBC 4.0 adds some functionality to the core API.

Java developers no longer need to explicitly load JDBC drivers using code like Class.forName() to register a JDBC driver. The DriverManager class takes care of this by automatically locating a suitable driver when the DriverManager.getConnection() method is called. This feature is backward-compatible, so no changes are needed to the existing JDBC code.

Major features of JDBC 4.0:

. Auto-loading of JDBC driver class
. Connection management enhancements
. Support for RowId SQL type
. Scalar Function Support
. DataSet implementation of SQL using Annotations
. SQL exception handling enhancements
. SQL XML support
. Data Type Support
. Support for National Character Set Conversion

There are also other features such as improved support for large objects (BLOB/CLOB) and National Character Set Support.

DataSources. To support the JDBC 4.0 ease of development, Derby introduces new implementations of javax.sql.DataSource.

Autoloading of JDBC drivers. In earlier versions of JDBC, applications had to manually register drivers before requesting Connections. With JDBC 4.0, applications no longer need to issue a Class.forName() on the driver name; instead, the DriverManager will find an appropriate JDBC driver when the application requests a Connection.

SQLExceptions. JDBC 4.0 introduces refined subclasses of SQLException. See Refined subclasses of SQLException.

Wrappers. JDBC 4.0 introduces the concept of wrapped JDBC objects. This is a formal mechanism by which application servers can look for vendor-specific extensions inside standard JDBC objects like Connections, Statements, and ResultSets.

Statement events. With JDBC 4.0, Connection pools can listen for Statement closing and Statement error events. New methods were added to javax.sql.PooledConnection: addStatementEventListener and removeStatementEventListener.

Streaming APIs. JDBC 4.0 adds new overloads of the streaming methods in CallableStatement, PreparedStatement, and ResultSet. These are the setXXX and updateXXX methods which take java.io.InputStream and java.io.Reader arguments. The new overloads allow you to omit the length arguments or to specify long lengths.

New methods. New methods were added to the following interfaces: java.sql.Connection, java.sql.DatabaseMetaData, java.sql.Statement.

Sample Code:

public class Example1 {
public static void main(String[] args) {
...
String dbName = "example1";
String tableName = "stu1";
ds = new EmbeddedDataSource40();
ds.setDatabaseName(dbName);
String connectionURL = "jdbc:derby:"+dbName+";create=true";
try {
con = ds.getConnection();
stmt = con.createStatement();
rs = stmt.executeQuery("select * from "+tableName);
int colCount= rs.getMetaData().getColumnCount();
for (int j=0; j< i =" 0;">Output:

ID NAME COURSE
1001 John Doe Statistics
1002 Jack McDonalds Linear Algebra

Example2

Java class that handles chained exceptions

public class Example2 {
public static void main(String[] args) {
String dbName = "example";
String tableName = "student4";
try {
con = ds.getConnection();
stmt = con.createStatement();
rs = stmt.executeQuery("select * from " + tableName);
} catch (SQLException sx) {
for(Throwable e : sx ) {
System.err.println("Error encountered: " + e);
}
}
finally{
//close connections
}
}
}

I ran the class in Example2.java, specifying student4 as a table name that does not exist in the database. It raised a chained exception in following call.

rs = stmt.executeQuery("select * from " + tableName);

In a real application, you need to catch such chained exceptions, inspect them, and take a suitable action. In this example, however, I will show how to print them to the error console. Here is how to do just that.

for(Throwable e : sx ) {
System.err.println("Error encountered: " + e);
}

Output running the class Example2:

Error encountered: java.sql.SQLSyntaxErrorException:
Table/View 'STUDENT4' does not exist.
Error encountered: java.sql.SQLException:
Table/View 'STUDENT4' does not exist.
Exception in thread "main" java.lang.NullPointerException
at ex.Examlpe2.main(Examlpe2.java:51)

Read more...