70) What is JDBC?
Ans: JDBC is a set of Java API for executing SQL statements. This API consists of a set of classes and interfaces
to enable programs to write pure Java Database applications.
71) What are drivers available?
Ans: a) JDBC-ODBC Bridge driver
b) Native API Partly-Java driver
c) JDBC-Net Pure Java driver
d) Native-Protocol Pure Java driver
72) What is the difference between JDBC and ODBC?
Ans: a) ODBC is for Microsoft and JDBC is for Java applications.
b) ODBC can’t be directly used with Java because it uses a C interface.
c) ODBC makes use of pointers which have been removed totally from Java.
d) ODBC mixes simple and advanced features together and has complex options for simple queries. But JDBC is designed to keep things simple while allowing advanced capabilities when required.
e) ODBC requires manual installation of the ODBC driver manager and driver on all client machines. JDBC drivers are written in Java and JDBC code is automatically installable, secure, and portable on all platforms.
f) JDBC API is a natural Java interface and is built on ODBC. JDBC retains some of the basic features of ODBC.
73) What are the types of JDBC Driver Models and explain them?
Ans: There are two types of JDBC Driver Models and they are:
a) Two tier model and b) Three tier modelTwo tier model: In this model, Java applications interact directly with the database. A JDBC driver is required to communicate with the particular database management system that is being accessed. SQL statements are sent to the database and the results are given to user. This model is referred to as client/server configuration where user is the client and the machine that has the database is called as the server.
Three tier model: A middle tier is introduced in this model. The functions of this model are:
a) Collection of SQL statements from the client and handing it over to the database,
b) Receiving results from database to the client and
c) Maintaining control over accessing and updating of the above.
74) What are the steps involved for making a connection with a database or how do you connect to a database?
Ans: a) Loading the driver : To load the driver, Class.forName( ) method is used.
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
When the driver is loaded, it registers itself with the java.sql.DriverManager class as an available database driver.
b) Making a connection with database : To open a connection to a given database, DriverManager.getConnection( ) method is used.
Connection con = DriverManager.getConnection (“jdbc:odbc:somedb”, “user”, “password”);
c) Executing SQL statements : To execute a SQL query, java.sql.statements class is used.
createStatement( ) method of Connection to obtain a new Statement object.
Statement stmt = con.createStatement( );
A query that returns data can be executed using the executeQuery( ) method of Statement. This method
executes the statement and returns a java.sql.ResultSet that encapsulates the retrieved data:
ResultSet rs = stmt.executeQuery(“SELECT * FROM some table”);
d) Process the results : ResultSet returns one row at a time. Next( ) method of ResultSet object can be called to move to the next row. The getString( ) and getObject( ) methods are used for retrieving column values:
while(rs.next( ) ) {
String event = rs.getString(“event”);
Object count = (Integer) rs.getObject(“count”);
75) What type of driver did you use in project?
Ans: JDBC-ODBC Bridge driver (is a driver that uses native(C language) libraries and makes calls to an existing
ODBC driver to access a database engine).
76) What are the types of statements in JDBC?
Ans: Statement -- To be used createStatement() method for executing single SQL statement
PreparedStatement -- To be used preparedStatement() method for executing same SQL statement over and
over
CallableStatement -- To be used prepareCall( ) method for multiple SQL statements over and over
77) What is stored procedure?
Ans: Stored procedure is a group of SQL statements that forms a logical unit and performs a particular task. Stored Procedures are used to encapsulate a set of operations or queries to execute on database. Stored procedures can be compiled and executed with different parameters and results and may have any combination of input/output parameters.
78) How to create and call stored procedures?
Ans: To create stored procedures: Create procedure procedurename (specify in, out and in out parameters)
BEGIN
Any multiple SQL statement;
END;
To call stored procedures:
CallableStatement csmt = con.prepareCall(“{call procedure name(?,?)}”);
csmt.registerOutParameter(column no., data type);
csmt.setInt(column no., column name)
csmt.execute( );
14. Stored procedures can be called by Callable Statement.
20. The method for precompiled SQL Statement in JDBC is prepareStatement().
46.BorderLayout is the default layout of Dialog object.
47.executeQuery() returns ResultSet.
Database - JDBC (java.sql)
Connecting to a Database
This example uses the JDBC-ODBC bridge to connect to a database called “mydatabase”.
try {
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
String url = “jdbc:odbc:mydatabase”;
Connection con = DriverManager.getConnection(
url, “login”, “password”);
} catch (ClassNotFoundException e) {
} catch (SQLException e) {
}
Creating a Table
This example creates a table called “mytable” with three columns: COL_A which holds strings, COL_B which holds integers, and COL_C which holds floating point numbers.
try {
Statement stmt = con.createStatement();
stmt.executeUpdate(“CREATE TABLE mytable (
COL_A VARCHAR(100), COL_B INTEGER, COL_C FLOAT)”);
} catch (SQLException e) {
}
Entering a New Row into a Table
This example enters a row containing a string, an integer, and a floating point number into the table called “mytable”.
try {
Statement stmt = connection.createStatement();
stmt.executeUpdate(“INSERT INTO mytable
VALUES (‘Patrick Chan’, 123, 1.23)”);
connection.close();
} catch (SQLException e) {
}
Getting All Rows from a Table
This example retrieves all the rows from a table called “mytable”. A row in “mytable” consists of a string, integer, and floating point number.
try {
Statement stmt = connection.createStatement();
// Get data using colunm names.
ResultSet rs = stmt.executeQuery(
“SELECT * FROM mytable”);
while (rs.next()) {
String s = rs.getString(“COL_A”);
int i = rs.getInt(“COL_B”);
float f = rs.getFloat(“COL_C”);
process(s, i, f);
}
// Get data using colunm numbers.
rs = stmt.executeQuery(
“SELECT * FROM mytable”);
while (rs.next()) {
String s = rs.getString(1);
int i = rs.getInt(2);
float f = rs.getFloat(3);
process(s, i, f);
}
} catch (SQLException e) {
}
Getting Particular Rows from a Table
This example retrieves all rows from a table called “mytable” whose column COL_A equals “Patrick Chan”. A row in “mytable” consists of a string, integer, and floating point number.
try {
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(
“SELECT * FROM mytable WHERE COL_A = ‘Patrick Chan’”);
rs.next();
String s = rs.getString(“COL_A”);
int i = rs.getInt(“COL_B”);
float f = rs.getFloat(“COL_C”);
process(s, i, f);
} catch (SQLException e) {
}
Updating a Row of Data in a Table
This example updates a row in a table called “mytable”. In particular, for all rows whose column COL_B equals 123, column COL_A is set to “John Doe”.
try {
Statement stmt = connection.createStatement();
int numUpdated = stmt.executeUpdate(
“UPDATE mytable SET COL_A = ‘John Doe’
WHERE COL_B = 123”);
connection.close();
} catch (SQLException e) {
}
Using a Prepared Statement
A prepared statement should be used in cases where a particular SQL statement is used frequently. The prepared statement is more expensive to set up but executes faster than a statement. This example demonstrates a prepared statement for getting all rows from a table called “mytable” whose column COL_A equals “Patrick Chan”. This example also demonstrates a prepared statement for updating data in the table. In particular, for all rows whose column COL_B equals 123, column COL_A is set to “John Doe”.
try {
// Retrieving rows from the database.
PreparedStatement stmt = connection.prepareStatement(
“SELECT * FROM mytable WHERE COL_A = ?”);
int colunm = 1;
stmt.setString(colunm, “Patrick Chan”);
ResultSet rs = stmt.executeQuery();
// Updating the database.
stmt = connection.prepareStatement(
“UPDATE mytable SET COL_A = ? WHERE COL_B = ?”);
colunm = 1;
stmt.setString(colunm, “John Doe”);
colunm = 2;
stmt.setInt(colunm, 123);
int numUpdated = stmt.executeUpdate();
} catch (SQLException e) {
}
1. What’s the JDBC 2.0 API?
The JDBC 2.0 API is the latest update of the JDBC API. It contains many new features, including scrollable result sets and the new SQL:1999 (formerly SQL 3) data types. There are two parts to the JDBC 2.0 API: the JDBC 2.0 core API (the java.sql package), which is included in the JavaTM 2 SDK, Standard Edition the JDBC 2.0 Optional Package API (the javax.sql package), which is available separately or as part of the Java 2 SDK, Enterprise Edition
2. Does the JDBC-ODBC Bridge support the new features in the JDBC 2.0 API?
No, the JDBC-ODBC Bridge that is included in the Java 2 Platform initial release does not support the new features in the JDBC 2.0 API. However, Sun and Merant are working to produce a new version of the Bridge that does support the new features. Note that we do not recommend using the Bridge except for experimental purposes or when you have no other driver available.
3. Can the JDBC-ODBC Bridge be used with applets?
Use of the JDBC-ODBC bridge from an untrusted applet running in a browser, such as Netscape Navigator, isn’t allowed. The JDBC-ODBC bridge doesn’t allow untrusted code to call it for security reasons. This is good because it means that an untrusted applet that is downloaded by the browser can’t circumvent Java security by calling ODBC. Remember that ODBC is native code, so once ODBC is called, the Java programming language can’t guarantee that a security violation won’t occur. On the other hand, Pure Java JDBC drivers work well with applets. They are fully downloadable and do not require any client-side configuration.
Finally, we would like to note that it is possible to use the JDBC-ODBC bridge with applets that will be run in appletviewer since appletviewer assumes that applets are trusted. It is also possible to use the JDBC-ODBC bridge with applets that are run in the HotJavaTM browser (available from Java Software), since HotJava provides an option to turn off applet security. In general, it is dangerous to turn applet security off, but it may be appropriate in certain controlled situations, such as for applets that will only be used in a secure intranet environment. Remember to exercise caution if you choose this option, and use an all-Java JDBC driver whenever possible to avoid security problems.
4. How do I start debugging problems related to the JDBC API?
A good way to find out what JDBC calls are doing is to enable JDBC tracing. The JDBC trace contains a detailed listing of the activity occurring in the system that is related to JDBC operations.
If you use the DriverManager facility to establish your database connection, you use the DriverManager.setLogWriter method to enable tracing of JDBC operations. If you use a DataSource object to get a connection, you use the DataSource.setLogWriter method to enable tracing. (For pooled connections, you use the ConnectionPoolDataSource.setLogWriter method, and for connections that can participate in distributed transactions, you use the XADataSource.setLogWriter method.)
5. How can I use the JDBC API to access a desktop database like Microsoft Access over the network?
Most desktop databases currently require a JDBC solution that uses ODBC underneath. This is because the vendors of these database products haven’t implemented all-Java JDBC drivers.
The best approach is to use a commercial JDBC driver that supports ODBC and the database you want to use. See the JDBC drivers page for a list of available JDBC drivers.
The JDBC-ODBC bridge from Sun’s Java Software does not provide network access to desktop databases by itself. The JDBC-ODBC bridge loads ODBC as a local DLL, and typical ODBC drivers for desktop databases like Access aren’t networked. The JDBC-ODBC bridge can be used together with the RMI-JDBC bridge , however, to access a desktop database like Access over the net. This RMI-JDBC-ODBC solution is free.
6. Does the JDK include the JDBC API and the JDBC-ODBC Bridge?
Yes, the JDK 1.1 and the Java 2 SDK, Standard Edition (formerly known as the JDK 1.2), contain both the JDBC API and the JDBC-ODBC Bridge. The Java 2 SDK, Standard Edition, contains the JDBC 2.0 core API, which is the latest version. It does not include the JDBC 2.0 Optional Package, which is part of the Java 2 SDK, Enterprise Edition, or which you can download separately.
Note that the version of the JDBC API and the JDBC-ODBC Bridge provided for separate download on the JDBC download page are only for use with the JDK 1.0.2.
7. What JDBC technology-enabled drivers are available?
See our web page on JDBC technology-enabled drivers for a current listing.
8. What documentation is available for the JDBC API?
See the JDBC technology home page for links to information about JDBC technology. This page links to information about features and benefits, a list of new features, a section on getting started, online tutorials, a section on driver requirements, and other information in addition to the specifications and javadoc documentation.
9. Are there any ODBC drivers that do not work with the JDBC-ODBC Bridge?
Most ODBC 2.0 drivers should work with the Bridge. Since there is some variation in functionality between ODBC drivers, the functionality of the bridge may be affected. The bridge works with popular PC databases, such as Microsoft Access and FoxPro.
10. Does the JDBC-ODBC Bridge work with Microsoft J++?
No, J++ does not support the JDBC-ODBC bridge since it doesn’t implement the Java Native Interface (JNI). Any all-Java JDBC driver should work with J++, however.
11. What causes the “No suitable driver” error?
“No suitable driver” is an error that usually occurs during a call to the DriverManager.getConnection method. The cause can be failing to load the appropriate JDBC drivers before calling the getConnection method, or it can be specifying an invalid JDBC URL—one that isn’t recognized by your JDBC driver. Your best bet is to check the documentation for your JDBC driver or contact your JDBC driver vendor if you suspect that the URL you are specifying is not being recognized by your JDBC driver.
In addition, when you are using the JDBC-ODBC Bridge, this error can occur if one or more the the shared libraries needed by the Bridge cannot be loaded. If you think this is the cause, check your configuration to be sure that the shared libraries are accessible to the Bridge.
12. Why isn’t the java.sql.DriverManager class being found?
This problem can be caused by running a JDBC applet in a browser that supports the JDK 1.0.2, such as Netscape Navigator 3.0. The JDK 1.0.2 does not contain the JDBC API, so the DriverManager class typically isn’t found by the Java virtual machine running in the browser.
Here’s a solution that doesn’t require any additional configuration of your web clients. Remember that classes in the java.* packages cannot be downloaded by most browsers for security reasons. Because of this, many vendors of all-Java JDBC drivers supply versions of the java.sql.* classes that have been renamed to jdbc.sql.*, along with a version of their driver that uses these modified classes. If you import jdbc.sql.* in your applet code instead of java.sql.*, and add the jdbc.sql.* classes provided by your JDBC driver vendor to your applet’s codebase, then all of the JDBC classes needed by the applet can be downloaded by the browser at run time, including the DriverManager class.
This solution will allow your applet to work in any client browser that supports the JDK 1.0.2. Your applet will also work in browsers that support the JDK 1.1, although you may want to switch to the JDK 1.1 classes for performance reasons. Also, keep in mind that the solution outlined here is just an example and that other solutions are possible.
13. Why doesn’t calling the method Class.forName load my JDBC driver?
There is a bug in the JDK 1.1.x that can cause the method Class.forName to fail. A workaround is to explicitly call the method DriverManager.registerDriver(new YourDriverClass()). The exact problem in the JDK is a race condition in the class loader that prevents the static section of code in the driver class from executing and registering the driver with the DriverManager.
14. Why do the java.sql and java.math packages fail to download java.* packages? Is there a workaround?
For security reasons, browsers will not download java.* packages. In order to use the JDBC API with browsers that have not been upgraded to JDK1.1 or beyond, we recommend that the java.sql and java.math packages be renamed jdbc.sql and jdbc.math. Most vendors supplying JDBC technology-enabled drivers that are written purely in the Java programming language already provide versions of these renamed packages. When JDK 1.1 support has been added to your browser, you should convert your applets back to the java.* package names.
15. Why is the precision of java.math.BigDecimal limited to 18 digits in the JDK 1.0.2 add-on version of the JDBC API?
In JDK 1.1, java.math.BigInteger is implemented in C. It supports a precision of thousands of digits. The same is true for BigDecigmal.
The version of BigInteger provided with the JDK 1.0.2 add-on version of the JDBC API is a simplified version written in the Java programming language, and it is limited to 18 digits. Because the implementation of BigDecimal is based on BigInteger, it also is limited to this precision.
In the JDBC 2.0 API, you can use a new version of the method ResultSet.getBigDecimal that does not take a scale parameter and returns a BigDecimal with full precision.
16. Can the JDBC API be added to JDK 1.0.2?
Yes. Download the JDBC 1.22 API from the JDBC download page and follow the installation instructions in the release notes.
If you are using any version of the JDK from 1.1 on, the JDBC API is already included, and you should not download the JDBC 1.22 API.
17. How do I retrieve a whole row of data at once, instead of calling an individual ResultSet.getXXX method for each column?
The ResultSet.getXXX methods are the only way to retrieve data from a ResultSet object, which means that you have to make a method call for each column of a row. It is unlikely that this is the cause of a performance problem, however, because it is difficult to see how a column could be fetched without at least the cost of a function call in any scenario. We welcome input from developers on this issue.
18. Why does the ODBC driver manager return ‘Data source name not found and no default driver specified Vendor: 0’
This type of error occurs during an attempt to connect to a database with the bridge. First, note that the error is coming from the ODBC driver manager. This indicates that the bridge-which is a normal ODBC client-has successfully called ODBC, so the problem isn’t due to native libraries not being present. In this case, it appears that the error is due to the fact that an ODBC DSN (data source name) needs to be configured on the client machine. Developers often forget to do this, thinking that the bridge will magically find the DSN they configured on their remote server machine
19. Are all the required JDBC drivers to establish connectivity to my database part of the JDK?
No. There aren’t any JDBC technology-enabled drivers bundled with the JDK 1.1.x or Java 2 Platform releases other than the JDBC-ODBC Bridge. So, developers need to get a driver and install it before they can connect to a database. We are considering bundling JDBC technology- enabled drivers in the future.
20. Is the JDBC-ODBC Bridge multi-threaded?
No. The JDBC-ODBC Bridge does not support concurrent access from different threads. The JDBC-ODBC Bridge uses synchronized methods to serialize all of the calls that it makes to ODBC. Multi-threaded Java programs may use the Bridge, but they won’t get the advantages of multi-threading. In addition, deadlocks can occur between locks held in the database and the semaphore used by the Bridge. We are thinking about removing the synchronized methods in the future. They were added originally to make things simple for folks writing Java programs that use a single-threaded ODBC driver.
21. Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection?
No. You can open only one Statement object per connection when you are using the JDBC-ODBC Bridge.
22. Does the JDBC-ODBC Bridge developed by Merant and Sun support result sets that contain Japanese Characters (DBCS)?
Yes, but we haven’t tested this ourselves. The version of the Bridge in the Java 2 SDK, Standard Edition, and Java 2 SDK, Enterprise Edition, also supports a new charSet Connection property for specifying the character encoding used by the underlying DBMS.
23. Why can’t I invoke the ResultSet methods afterLast and beforeFirst when the method next works?
You are probably using a driver implemented for the JDBC 1.0 API. You need to upgrade to a JDBC 2.0 driver that implements scrollable result sets. Also be sure that your code has created scrollable result sets and that the DBMS you are using supports them.
24. How can I retrieve a String or other objeOracle’s JDBC Drivers
Oracle provides both Type 2 and Type 4 drivers. All Oracle JDBC drivers support the full JDBC specification, but in addition, they support the extended capabilities of the Oracle database. For example, the JDBC specification doesn’t support LOB data, but the Oracle OCI8 JDBC driver does. Oracle’s implementation of the Type 2 JDBC driver is referred to as the Oracle “OCI driver,” and the version of this driver that supports an Oracle 7 database is the OCI7 driver and the OCI8 supports Oracle 8. These drivers are platform specific; for example, the Windows NT and Windows 95 version of the driver (oci805jdbc.dll.) is implemented as a dynamic link library (DLL) in C. As mentioned previously, Type 2 drivers may require client code. In the case of the OCI8 driver, the clients must have Oracle’s Net*8 and all other dependent files loaded.
A common way to implement Oracle OCI drivers is to use Oracle Application Server with the JWeb cartridge on the middle tier and deploy the client presentation logic as an applet; the interaction with the Oracle database is conducted from the middle tier only, with just the results sent to the client applet as pure HTML or Java and HTML. All Oracle drivers are compliant with the Java Development Kit JDK 1.0 and 1.1.x and support the JDBC 1.22 standard. In addition, all Oracle JDBC drivers support data types such as RAW and LONG RAW, ROWID, and RECURSOR, which are supported in Oracle databases but not part of the JDBC standard. Oracle’s drivers also support execution of PL/SQL stored procedures and anonymous blocks (for dynamic execution), and
include capabilities such as row pre-fetching, execution batching, and defining query columns to
reduce the network round trips to Oracle database. In addition, the OCI driver for Oracle8 supports oracle data types CLOB, BLOB, NCLOB, and BFILE. The screenshot shows an example of one of the classes in the Oracle JDBC, as part of the class hierarchy from which it descends, as displayed in Oracle’s JDeveloper integrated development environment. As you can see, the OraclePreparedStatement class inherits from the java.sql.PreparedStatement class, which in turn inherits from the java.sql.Statement.
Oracle also provides a Type 4 JDBC driver, referred to as the Oracle “thin” driver. This driver includes its own implementation of a TCP/IP version of Oracle’s Net8 written entirely in Java, so it is platform independent, can be downloaded to a browser at runtime, and does not require any Oracle software on the client side. This driver requires a TCP/IP listener on the server side, and the client connection string uses the TCP/IP port address, not the TNSNAMES entry for the database name.
1.Add JDBC classes to your Java application or applet class by adding the following statement to your Java source code:
import java.sql.*;
To use the extended capabilities of the Oracle database, you must also import the Oracle JDBC driver. The statement in Java source looks like this:
import oracle.JDBC.driver.*
2.Load the JDBC driver by including the following statement in your class.
Class.forName(“oracle.JDBC.driver.OracleDriver”);
You can load the driver from your class-initialization routine.
3.Obtain a connection to an Oracle database by calling the getConnection() method of the JDBC DriverManager class. When you call this method you need to specify the connection information for the database in the form of a URL. The form the URL will take depends on the driver used. For example, to use the pure Java Type 4 Oracle driver (the thin driver) to connect to an Oracle7 database, the URL would read:
jdbc:oracle:thin@database_name:port_no:SID
To connect to an Oracle8 database using the OCI driver, the URL would be more like:
jdbc:oracle:oci8@database_name
To specify the database for use with an OCI driver, you can use either a SQL*Net name- value pair, or, if you’re using an Oracle Name server, you can use the name from the tnsname.ora file. (Both of these strings would conclude with the logon information as well—specifically the user name and password—but we’ve eliminated that from this example.)
The preliminary driver and database-connection issues now taken care of, there are still several other things your Java source code must include in order for the compiled code to submit queries to the database and process results.
4.Create a Statement object by calling the createStatement() method of the Connection object you created in the previous step. The following statement creates a Statement object stmt:
Statement stmt = conn.createStatement ();
5.Once the Statement object exists (in code), the application can then include code to execute a SQL query by calling the executeQuery() method of the Statement object.The executeQuery() method returns the result of the query in the ResultSet object. The following statement executes a query :
ResultSet rset = stmt.executeQuery (SELECT ename from emp where empno = 7900);
6.Finally, call the next() method of a ResultObject to retrieve a row and display it. Use a loop if the query returns more then one row from the database. For example, the following statements get the name of an employee from the ResultSet object and display it in the java.awt text control placed on the GUI.
rset.next();
enameTxtb.setText = ((String)rset.getString(1));
16
JDBC (java database conctivity)
1. What is JDBC ? what are its advantages ?
A. It is an API .The latest version of jdbc api is (3.0).
The JDBC 3.0 API is divided into two packages:
(1) java.sql and (2) javax.sql.
Both packages are included in the J2SE and J2EE platforms.
advantages:
The JDBC API can be used to interact with multiple data sources in a distributed, heterogenous environment.
It can connect to any of the database from java language.
It can switch over to any backend database without changing java code or by minute changes.
2. How many JDBC Drivers are there ? what are they?
A. There are 4 types of JDBC drivers.
a. JDBC-ODBC Bridge Driver(Type-1 driver)
b. Native API Partly Java Driver(Type-2 driver)
c. Net protocol pure Java Driver(Type-3 driver)
d. Native protocol Pure Java Driver(Type-4 driver)
3. Explain about JDBC-ODBC driver(Type-1) ? When this type of driver is used ?
A. In this mechanism the flow of execution will be
Java code(JDBC API)<------>JDBC-ODBC bridge driver<------->ODBC API<------->ODBC Layer<-------->DataBase
This type of JDBC Drivers provides a bridge between JDBC API and ODBC API.
This Bridge(JDBC-ODBC bridge) translates standard JDBC calls to Corresponding ODBC Calls, and
send them to ODBC database via ODBC Libraries.
The JDBC API is based on ODBC API.
ODBC(Open Database Connectivity)is Microsoft’s API for Database drivers.
ODBC is based on X/Open Call Level Interface(CLI)specification for database access.
The URL and class to be loaded for this type of driver are
Class :- sun.jdbc.odbc.JdbcOdbcDriver
URL :- jdbc:odbc:dsnname
4. Explain about Type-2 driver ? When this type of driver is used ?
A. The Drivers which are written in Native code will come into this category
In this mechanism the flow of Execution will be
java code(JDBC API)<------>Type-2 driver(jdbc driver)<------->Native API(vendor specific)<------->DataBase
When database call is made using JDBC,the driver translates the request into vendor-specific API calls.
The database will process the requet and sends the results back through the Native API ,which will
forward them back to the JDBC dirver. The JDBC driver will format the results to conform to the JDBC
standard and return them to the application.
5. Explain about Type-3 driver ? When this type of driver is used ?
A. In this mechanism the flow of Execution will be
java code(JDBC API)<------>JDBC driver<------->JDBC driver server<-------->Native driver<------->DataBase
The Java Client Application sends the calls to the Intermediate data access server(jdbc driver server)
The middle tier then handles the requet using other driver(Type-II or Type-IV drivers) to complete the request.
6. Explain about Type-4 driver ? When this type of driver is used ?
A. This is a pure java driver(alternative to Type-II drivers).
In this mechanism the flow of Execution will be
java code(JDBC API)<------>Type-4 driver(jdbc driver)<------->DataBase
These type of drivers convert the JDBC API calls to direct network calls using
vendor specific networking protocal by making direct socket connection with
database.
examples of this type of drivers are
1.Tabular Data Stream for Sybase
2.Oracle Thin jdbc driver for Oracle
7. What are the Advantages & DisAdvantages of Type-2 ,Type-4 Drivers over JDBC-ODBC bridge driver(Type-1)?
A. Type-2 & Type-4 are given
8. Which Driver is preferable for using JDBC API in Applets?
A. Type-4 Drivers.
9.Write the Syntax of URL to get connection ? Explain?
A.Syntax:- jdbc:<subprotocal>:<subname>
jdbc -----> is a protocal .This is only allowed protocal in JDBC.
<subprotocal> ----> The subprotocal is used to identify a database driver,or the
name of the database connectivity mechanism, choosen by the database driver providers.
<subname> -------> The syntax of the subname is driver specific. The driver may choose any syntax appropriate for its implementation
ex: jdbc:odbc:dsn
jdbc:oracle:oci8:@ database name.
jdbc:orale:thin:@ database name:port number:SID
10.How do u Load a driver ?
A. Using Driver Class.forName(java.lang.String driverclass) or registerDriver(Driver driver) .
11.what are the types of resultsets in JDBC3.0 ?How you can retrieve information of resultset?
A. ScrollableResultSet and ResultSet.We can retrieve information of resultset by using java.sql.ResultSetMetaData interface.You can get the instance by calling the method getMetaData() on ResulSet object.
12.write the steps to Connect database?
A. Class.forName(The class name of a spasific driver);
Connection c=DriverManager.getConnection(url of a spasific driver,user name,password);
Statement s=c.createStatement();
(or)
PreparedStatement p=c.prepareStatement();
(or)
CallableStatement cal=c.prpareCall();
Depending upon the requirement.
13.Can java objects be stored in database? how?
A.Yes.We can store java objects, BY using setObject(),setBlob() and setClob() methods in PreparedStatement
14.what do u mean by isolation level?
A. Isolation means that the business logic can proceed without
consideration for the other activities of the system.
15.How do u set the isolation level?
A. By using setTransactionIsolation(int level) in java.sql.Connection interface.
level MEANS:-
static final int TRANSACTION_READ_UNCOMMITTED //cannot prevent any reads.
static final int TRANSACTION_READ_COMMITTED //prevents dirty reads
static final int TRANSACTION_REPEATABLE_READ //prevents dirty reads & non-repeatable read.
static final int TRANSACTION_SERIALIZABLE //prevents dirty reads , non-repeatable read & phantom read.
These are the static final fields in java.sql.Connection interface.
16. what is a dirty read?
A. A Dirty read allows a row changed by one transaction to be
read by another transaction before any change in the row
have been committed.
This problem can be solved by setting the transaction isolation
level to TRANSACTION_READ_COMMITTED
17. what is a non-repeatable read ?
A. A non-repeatable read is where one transaction reads a row, a second
transaction alters or deletes the row, and the first transaction
re-reads the row,getting different values the second time.
This problem can be solved by setting the transaction isolation
level to TRANSACTION_REPEATABLE_READ
18. what is phantom read?
A. A phantom read is where one transaction reads all rows that satisfy a WHERE condition,a second transaction inserts a row that satisfies that WHERE condition,and the first transaction re-reads for the same condition,retrieving the additional ‘phantom’ row in the second read This problem can be solved by setting the transaction isolation level to TRANSACTION_SERIALIZABLE
19.What is the difference between java.sql.Statement &
java.sql.PreparedStatement ?
A. write the appropriate situations to use these statements?
20.How to retrieve the information about the database ?
A.we can retrieve the info about the database by using inerface java.sql.DatabaseMetaData we can get this object by using getMetaData() method in Connection interface.
21.what are the Different types of exceptions in jdbc?
A. BatchUpdateException
DataTruncation
SQLException
SQLWarning
22.How to execute no of queries at one go?
A. By using a batchUpdate’s (ie throw addBAtch() and executeBatch()) in java.sql.Statement interface,or by using procedures.
23. what are the advantages of connection pool.
A. Performance
24. In which interface the methods commit() & rollback() are defined ?
A. java.sql.Connection interface
25. How to store images in database?
A. Using binary streams (ie getBinaryStream() ,setBinaryStream()). But it is not visable in database ,it is stored in form of bytes ,to make it visable we have to use any one frontend tool.
26.How to check null value in JDBC?
A. By using the method wasNull() in ResultSet ,it returns boolean value.
Returns whether the last column read had a value of SQL NULL.
Note that you must first call one of the getXXX methods on a column to try to read its value and then call the method wasNull to see if the value read was SQL NULL.
27.Give one Example of static Synchronized method in JDBC API?
A. getConnection() method in DriverManager class.Which is used to get object of Connection interface.
28.What is a Connection?
A. Connection is an interface which is used to make a connection between client and Database (ie opening a session with a particular database).
29.what is the difference between execute() ,executeUpdate() and executeQuery() ? where we will use them?
A. execute() method returns a boolean value (ie if the given query returns a resutset then it returns true else false),so depending upon the return value we can get the ResultSet object (getResultset())or we can know how many rows have bean affected by our query (getUpdateCount()).That is we can use this method for Fetching queries and Non-Fetching queries.Fetching queries are the queries which are used to fetch the records from database (ie which returns resutset) ex: Select * from emp.
Non-Fetching queries are the queries which are used to update,insert,create or delete the records from database
ex: update emp set sal=10000 where empno=7809.
executeUpdate() method is used for nonfetching queries.which returns int value.
executeQuery() method is used for fetching queries which returns ResulSet object ,Which contains methods to fetch the values.
30.How is jndi useful for Database connection?
ct type without creating a new object each time?
Creating and garbage collecting potentially large numbers of objects (millions) unnecessarily can really hurt performance. It may be better to provide a way to retrieve data like strings using the JDBC API without always allocating a new object.
We are studying this issue to see if it is an area in which the JDBC API should be improved. Stay tuned, and please send us any comments you have on this question.
25. There is a method getColumnCount in the JDBC API. Is there a similar method to find the number of rows in a result set?
No, but it is easy to find the number of rows. If you are using a scrollable result set, rs, you can call the methods rs.last and then rs.getRow to find out how many rows rs has. If the result is not scrollable, you can either count the rows by iterating through the result set or get the number of rows by submitting a query with a COUNT column in the SELECT clause.
26. I would like to download the JDBC-ODBC Bridge for the Java 2 SDK, Standard Edition (formerly JDK 1.2). I’m a beginner with the JDBC API, and I would like to start with the Bridge. How do I do it?
The JDBC-ODBC Bridge is bundled with the Java 2 SDK, Standard Edition, so there is no need to download it separately.
27. If I use the JDBC API, do I have to use ODBC underneath?
No, this is just one of many possible solutions. We recommend using a pure Java JDBC technology-enabled driver, type 3 or 4, in order to get all of the benefits of the Java programming language and the JDBC API.
28. Once I have the Java 2 SDK, Standard Edition, from Sun, what else do I need to connect to a database?
You still need to get and install a JDBC technology-enabled driver that supports the database that you are using. There are many drivers available from a variety of sources. You can also try using the JDBC-ODBC Bridge if you have ODBC connectivity set up already. The Bridge comes with the Java 2 SDK, Standard Edition, and Enterprise Edition, and it doesn’t require any extra setup itself. The Bridge is a normal ODBC client. Note, however, that you should use the JDBC-ODBC Bridge only for experimental prototyping or when you have no other driver available.
16
What is a JDBC Driver?
A JDBC driver is the set of classes that implement the JDBC interfaces for a particular database.
There are four different types of JDBC driver: A Type 1 driver is a JDBC-ODBC bridge driver; this type of driver enables a client to connect to an ODBC database via Java calls and JDBC—neither the database nor middle tier need to be Java compliant.However, ODBC binary code must be installed on each client machine that uses this driver.
A Type 2 driver converts JDBC calls into calls for a specific database. This driver is referred to as a native-API, partly Java driver. As with the Type 1 driver, some binary code may be required on the client machine, which means this type of driver is not suitable for downloading over a network to a client.
A Type 3 driver is a JDBC-Net pure Java driver, which translates JDBC calls into a database -independent net protocol. Vendors of database middleware products can implement this type of driver into their products to provide interoperability with the greatest number of database servers.
Finally, a Type 4 driver, or, native protocol, pure Java driver converts JDBC calls into the network protocol used by the database directly. A Type 4 driver requires no client software, so it’s ideal for deployment to browsers at runtime. Each of these driver types has its own optimal usage scenarios, and will affect the way you deploy a given Java application.
For example, because Type 4 drivers are 100% Java, use Java sockets to connect to the database, and require no client-side data access code, they are ideal for applets or other download situations inside a firewall.
Ans: JDBC is a set of Java API for executing SQL statements. This API consists of a set of classes and interfaces
to enable programs to write pure Java Database applications.
71) What are drivers available?
Ans: a) JDBC-ODBC Bridge driver
b) Native API Partly-Java driver
c) JDBC-Net Pure Java driver
d) Native-Protocol Pure Java driver
72) What is the difference between JDBC and ODBC?
Ans: a) ODBC is for Microsoft and JDBC is for Java applications.
b) ODBC can’t be directly used with Java because it uses a C interface.
c) ODBC makes use of pointers which have been removed totally from Java.
d) ODBC mixes simple and advanced features together and has complex options for simple queries. But JDBC is designed to keep things simple while allowing advanced capabilities when required.
e) ODBC requires manual installation of the ODBC driver manager and driver on all client machines. JDBC drivers are written in Java and JDBC code is automatically installable, secure, and portable on all platforms.
f) JDBC API is a natural Java interface and is built on ODBC. JDBC retains some of the basic features of ODBC.
73) What are the types of JDBC Driver Models and explain them?
Ans: There are two types of JDBC Driver Models and they are:
a) Two tier model and b) Three tier modelTwo tier model: In this model, Java applications interact directly with the database. A JDBC driver is required to communicate with the particular database management system that is being accessed. SQL statements are sent to the database and the results are given to user. This model is referred to as client/server configuration where user is the client and the machine that has the database is called as the server.
Three tier model: A middle tier is introduced in this model. The functions of this model are:
a) Collection of SQL statements from the client and handing it over to the database,
b) Receiving results from database to the client and
c) Maintaining control over accessing and updating of the above.
74) What are the steps involved for making a connection with a database or how do you connect to a database?
Ans: a) Loading the driver : To load the driver, Class.forName( ) method is used.
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
When the driver is loaded, it registers itself with the java.sql.DriverManager class as an available database driver.
b) Making a connection with database : To open a connection to a given database, DriverManager.getConnection( ) method is used.
Connection con = DriverManager.getConnection (“jdbc:odbc:somedb”, “user”, “password”);
c) Executing SQL statements : To execute a SQL query, java.sql.statements class is used.
createStatement( ) method of Connection to obtain a new Statement object.
Statement stmt = con.createStatement( );
A query that returns data can be executed using the executeQuery( ) method of Statement. This method
executes the statement and returns a java.sql.ResultSet that encapsulates the retrieved data:
ResultSet rs = stmt.executeQuery(“SELECT * FROM some table”);
d) Process the results : ResultSet returns one row at a time. Next( ) method of ResultSet object can be called to move to the next row. The getString( ) and getObject( ) methods are used for retrieving column values:
while(rs.next( ) ) {
String event = rs.getString(“event”);
Object count = (Integer) rs.getObject(“count”);
75) What type of driver did you use in project?
Ans: JDBC-ODBC Bridge driver (is a driver that uses native(C language) libraries and makes calls to an existing
ODBC driver to access a database engine).
76) What are the types of statements in JDBC?
Ans: Statement -- To be used createStatement() method for executing single SQL statement
PreparedStatement -- To be used preparedStatement() method for executing same SQL statement over and
over
CallableStatement -- To be used prepareCall( ) method for multiple SQL statements over and over
77) What is stored procedure?
Ans: Stored procedure is a group of SQL statements that forms a logical unit and performs a particular task. Stored Procedures are used to encapsulate a set of operations or queries to execute on database. Stored procedures can be compiled and executed with different parameters and results and may have any combination of input/output parameters.
78) How to create and call stored procedures?
Ans: To create stored procedures: Create procedure procedurename (specify in, out and in out parameters)
BEGIN
Any multiple SQL statement;
END;
To call stored procedures:
CallableStatement csmt = con.prepareCall(“{call procedure name(?,?)}”);
csmt.registerOutParameter(column no., data type);
csmt.setInt(column no., column name)
csmt.execute( );
14. Stored procedures can be called by Callable Statement.
20. The method for precompiled SQL Statement in JDBC is prepareStatement().
46.BorderLayout is the default layout of Dialog object.
47.executeQuery() returns ResultSet.
Database - JDBC (java.sql)
Connecting to a Database
This example uses the JDBC-ODBC bridge to connect to a database called “mydatabase”.
try {
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
String url = “jdbc:odbc:mydatabase”;
Connection con = DriverManager.getConnection(
url, “login”, “password”);
} catch (ClassNotFoundException e) {
} catch (SQLException e) {
}
Creating a Table
This example creates a table called “mytable” with three columns: COL_A which holds strings, COL_B which holds integers, and COL_C which holds floating point numbers.
try {
Statement stmt = con.createStatement();
stmt.executeUpdate(“CREATE TABLE mytable (
COL_A VARCHAR(100), COL_B INTEGER, COL_C FLOAT)”);
} catch (SQLException e) {
}
Entering a New Row into a Table
This example enters a row containing a string, an integer, and a floating point number into the table called “mytable”.
try {
Statement stmt = connection.createStatement();
stmt.executeUpdate(“INSERT INTO mytable
VALUES (‘Patrick Chan’, 123, 1.23)”);
connection.close();
} catch (SQLException e) {
}
Getting All Rows from a Table
This example retrieves all the rows from a table called “mytable”. A row in “mytable” consists of a string, integer, and floating point number.
try {
Statement stmt = connection.createStatement();
// Get data using colunm names.
ResultSet rs = stmt.executeQuery(
“SELECT * FROM mytable”);
while (rs.next()) {
String s = rs.getString(“COL_A”);
int i = rs.getInt(“COL_B”);
float f = rs.getFloat(“COL_C”);
process(s, i, f);
}
// Get data using colunm numbers.
rs = stmt.executeQuery(
“SELECT * FROM mytable”);
while (rs.next()) {
String s = rs.getString(1);
int i = rs.getInt(2);
float f = rs.getFloat(3);
process(s, i, f);
}
} catch (SQLException e) {
}
Getting Particular Rows from a Table
This example retrieves all rows from a table called “mytable” whose column COL_A equals “Patrick Chan”. A row in “mytable” consists of a string, integer, and floating point number.
try {
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(
“SELECT * FROM mytable WHERE COL_A = ‘Patrick Chan’”);
rs.next();
String s = rs.getString(“COL_A”);
int i = rs.getInt(“COL_B”);
float f = rs.getFloat(“COL_C”);
process(s, i, f);
} catch (SQLException e) {
}
Updating a Row of Data in a Table
This example updates a row in a table called “mytable”. In particular, for all rows whose column COL_B equals 123, column COL_A is set to “John Doe”.
try {
Statement stmt = connection.createStatement();
int numUpdated = stmt.executeUpdate(
“UPDATE mytable SET COL_A = ‘John Doe’
WHERE COL_B = 123”);
connection.close();
} catch (SQLException e) {
}
Using a Prepared Statement
A prepared statement should be used in cases where a particular SQL statement is used frequently. The prepared statement is more expensive to set up but executes faster than a statement. This example demonstrates a prepared statement for getting all rows from a table called “mytable” whose column COL_A equals “Patrick Chan”. This example also demonstrates a prepared statement for updating data in the table. In particular, for all rows whose column COL_B equals 123, column COL_A is set to “John Doe”.
try {
// Retrieving rows from the database.
PreparedStatement stmt = connection.prepareStatement(
“SELECT * FROM mytable WHERE COL_A = ?”);
int colunm = 1;
stmt.setString(colunm, “Patrick Chan”);
ResultSet rs = stmt.executeQuery();
// Updating the database.
stmt = connection.prepareStatement(
“UPDATE mytable SET COL_A = ? WHERE COL_B = ?”);
colunm = 1;
stmt.setString(colunm, “John Doe”);
colunm = 2;
stmt.setInt(colunm, 123);
int numUpdated = stmt.executeUpdate();
} catch (SQLException e) {
}
1. What’s the JDBC 2.0 API?
The JDBC 2.0 API is the latest update of the JDBC API. It contains many new features, including scrollable result sets and the new SQL:1999 (formerly SQL 3) data types. There are two parts to the JDBC 2.0 API: the JDBC 2.0 core API (the java.sql package), which is included in the JavaTM 2 SDK, Standard Edition the JDBC 2.0 Optional Package API (the javax.sql package), which is available separately or as part of the Java 2 SDK, Enterprise Edition
2. Does the JDBC-ODBC Bridge support the new features in the JDBC 2.0 API?
No, the JDBC-ODBC Bridge that is included in the Java 2 Platform initial release does not support the new features in the JDBC 2.0 API. However, Sun and Merant are working to produce a new version of the Bridge that does support the new features. Note that we do not recommend using the Bridge except for experimental purposes or when you have no other driver available.
3. Can the JDBC-ODBC Bridge be used with applets?
Use of the JDBC-ODBC bridge from an untrusted applet running in a browser, such as Netscape Navigator, isn’t allowed. The JDBC-ODBC bridge doesn’t allow untrusted code to call it for security reasons. This is good because it means that an untrusted applet that is downloaded by the browser can’t circumvent Java security by calling ODBC. Remember that ODBC is native code, so once ODBC is called, the Java programming language can’t guarantee that a security violation won’t occur. On the other hand, Pure Java JDBC drivers work well with applets. They are fully downloadable and do not require any client-side configuration.
Finally, we would like to note that it is possible to use the JDBC-ODBC bridge with applets that will be run in appletviewer since appletviewer assumes that applets are trusted. It is also possible to use the JDBC-ODBC bridge with applets that are run in the HotJavaTM browser (available from Java Software), since HotJava provides an option to turn off applet security. In general, it is dangerous to turn applet security off, but it may be appropriate in certain controlled situations, such as for applets that will only be used in a secure intranet environment. Remember to exercise caution if you choose this option, and use an all-Java JDBC driver whenever possible to avoid security problems.
4. How do I start debugging problems related to the JDBC API?
A good way to find out what JDBC calls are doing is to enable JDBC tracing. The JDBC trace contains a detailed listing of the activity occurring in the system that is related to JDBC operations.
If you use the DriverManager facility to establish your database connection, you use the DriverManager.setLogWriter method to enable tracing of JDBC operations. If you use a DataSource object to get a connection, you use the DataSource.setLogWriter method to enable tracing. (For pooled connections, you use the ConnectionPoolDataSource.setLogWriter method, and for connections that can participate in distributed transactions, you use the XADataSource.setLogWriter method.)
5. How can I use the JDBC API to access a desktop database like Microsoft Access over the network?
Most desktop databases currently require a JDBC solution that uses ODBC underneath. This is because the vendors of these database products haven’t implemented all-Java JDBC drivers.
The best approach is to use a commercial JDBC driver that supports ODBC and the database you want to use. See the JDBC drivers page for a list of available JDBC drivers.
The JDBC-ODBC bridge from Sun’s Java Software does not provide network access to desktop databases by itself. The JDBC-ODBC bridge loads ODBC as a local DLL, and typical ODBC drivers for desktop databases like Access aren’t networked. The JDBC-ODBC bridge can be used together with the RMI-JDBC bridge , however, to access a desktop database like Access over the net. This RMI-JDBC-ODBC solution is free.
6. Does the JDK include the JDBC API and the JDBC-ODBC Bridge?
Yes, the JDK 1.1 and the Java 2 SDK, Standard Edition (formerly known as the JDK 1.2), contain both the JDBC API and the JDBC-ODBC Bridge. The Java 2 SDK, Standard Edition, contains the JDBC 2.0 core API, which is the latest version. It does not include the JDBC 2.0 Optional Package, which is part of the Java 2 SDK, Enterprise Edition, or which you can download separately.
Note that the version of the JDBC API and the JDBC-ODBC Bridge provided for separate download on the JDBC download page are only for use with the JDK 1.0.2.
7. What JDBC technology-enabled drivers are available?
See our web page on JDBC technology-enabled drivers for a current listing.
8. What documentation is available for the JDBC API?
See the JDBC technology home page for links to information about JDBC technology. This page links to information about features and benefits, a list of new features, a section on getting started, online tutorials, a section on driver requirements, and other information in addition to the specifications and javadoc documentation.
9. Are there any ODBC drivers that do not work with the JDBC-ODBC Bridge?
Most ODBC 2.0 drivers should work with the Bridge. Since there is some variation in functionality between ODBC drivers, the functionality of the bridge may be affected. The bridge works with popular PC databases, such as Microsoft Access and FoxPro.
10. Does the JDBC-ODBC Bridge work with Microsoft J++?
No, J++ does not support the JDBC-ODBC bridge since it doesn’t implement the Java Native Interface (JNI). Any all-Java JDBC driver should work with J++, however.
11. What causes the “No suitable driver” error?
“No suitable driver” is an error that usually occurs during a call to the DriverManager.getConnection method. The cause can be failing to load the appropriate JDBC drivers before calling the getConnection method, or it can be specifying an invalid JDBC URL—one that isn’t recognized by your JDBC driver. Your best bet is to check the documentation for your JDBC driver or contact your JDBC driver vendor if you suspect that the URL you are specifying is not being recognized by your JDBC driver.
In addition, when you are using the JDBC-ODBC Bridge, this error can occur if one or more the the shared libraries needed by the Bridge cannot be loaded. If you think this is the cause, check your configuration to be sure that the shared libraries are accessible to the Bridge.
12. Why isn’t the java.sql.DriverManager class being found?
This problem can be caused by running a JDBC applet in a browser that supports the JDK 1.0.2, such as Netscape Navigator 3.0. The JDK 1.0.2 does not contain the JDBC API, so the DriverManager class typically isn’t found by the Java virtual machine running in the browser.
Here’s a solution that doesn’t require any additional configuration of your web clients. Remember that classes in the java.* packages cannot be downloaded by most browsers for security reasons. Because of this, many vendors of all-Java JDBC drivers supply versions of the java.sql.* classes that have been renamed to jdbc.sql.*, along with a version of their driver that uses these modified classes. If you import jdbc.sql.* in your applet code instead of java.sql.*, and add the jdbc.sql.* classes provided by your JDBC driver vendor to your applet’s codebase, then all of the JDBC classes needed by the applet can be downloaded by the browser at run time, including the DriverManager class.
This solution will allow your applet to work in any client browser that supports the JDK 1.0.2. Your applet will also work in browsers that support the JDK 1.1, although you may want to switch to the JDK 1.1 classes for performance reasons. Also, keep in mind that the solution outlined here is just an example and that other solutions are possible.
13. Why doesn’t calling the method Class.forName load my JDBC driver?
There is a bug in the JDK 1.1.x that can cause the method Class.forName to fail. A workaround is to explicitly call the method DriverManager.registerDriver(new YourDriverClass()). The exact problem in the JDK is a race condition in the class loader that prevents the static section of code in the driver class from executing and registering the driver with the DriverManager.
14. Why do the java.sql and java.math packages fail to download java.* packages? Is there a workaround?
For security reasons, browsers will not download java.* packages. In order to use the JDBC API with browsers that have not been upgraded to JDK1.1 or beyond, we recommend that the java.sql and java.math packages be renamed jdbc.sql and jdbc.math. Most vendors supplying JDBC technology-enabled drivers that are written purely in the Java programming language already provide versions of these renamed packages. When JDK 1.1 support has been added to your browser, you should convert your applets back to the java.* package names.
15. Why is the precision of java.math.BigDecimal limited to 18 digits in the JDK 1.0.2 add-on version of the JDBC API?
In JDK 1.1, java.math.BigInteger is implemented in C. It supports a precision of thousands of digits. The same is true for BigDecigmal.
The version of BigInteger provided with the JDK 1.0.2 add-on version of the JDBC API is a simplified version written in the Java programming language, and it is limited to 18 digits. Because the implementation of BigDecimal is based on BigInteger, it also is limited to this precision.
In the JDBC 2.0 API, you can use a new version of the method ResultSet.getBigDecimal that does not take a scale parameter and returns a BigDecimal with full precision.
16. Can the JDBC API be added to JDK 1.0.2?
Yes. Download the JDBC 1.22 API from the JDBC download page and follow the installation instructions in the release notes.
If you are using any version of the JDK from 1.1 on, the JDBC API is already included, and you should not download the JDBC 1.22 API.
17. How do I retrieve a whole row of data at once, instead of calling an individual ResultSet.getXXX method for each column?
The ResultSet.getXXX methods are the only way to retrieve data from a ResultSet object, which means that you have to make a method call for each column of a row. It is unlikely that this is the cause of a performance problem, however, because it is difficult to see how a column could be fetched without at least the cost of a function call in any scenario. We welcome input from developers on this issue.
18. Why does the ODBC driver manager return ‘Data source name not found and no default driver specified Vendor: 0’
This type of error occurs during an attempt to connect to a database with the bridge. First, note that the error is coming from the ODBC driver manager. This indicates that the bridge-which is a normal ODBC client-has successfully called ODBC, so the problem isn’t due to native libraries not being present. In this case, it appears that the error is due to the fact that an ODBC DSN (data source name) needs to be configured on the client machine. Developers often forget to do this, thinking that the bridge will magically find the DSN they configured on their remote server machine
19. Are all the required JDBC drivers to establish connectivity to my database part of the JDK?
No. There aren’t any JDBC technology-enabled drivers bundled with the JDK 1.1.x or Java 2 Platform releases other than the JDBC-ODBC Bridge. So, developers need to get a driver and install it before they can connect to a database. We are considering bundling JDBC technology- enabled drivers in the future.
20. Is the JDBC-ODBC Bridge multi-threaded?
No. The JDBC-ODBC Bridge does not support concurrent access from different threads. The JDBC-ODBC Bridge uses synchronized methods to serialize all of the calls that it makes to ODBC. Multi-threaded Java programs may use the Bridge, but they won’t get the advantages of multi-threading. In addition, deadlocks can occur between locks held in the database and the semaphore used by the Bridge. We are thinking about removing the synchronized methods in the future. They were added originally to make things simple for folks writing Java programs that use a single-threaded ODBC driver.
21. Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection?
No. You can open only one Statement object per connection when you are using the JDBC-ODBC Bridge.
22. Does the JDBC-ODBC Bridge developed by Merant and Sun support result sets that contain Japanese Characters (DBCS)?
Yes, but we haven’t tested this ourselves. The version of the Bridge in the Java 2 SDK, Standard Edition, and Java 2 SDK, Enterprise Edition, also supports a new charSet Connection property for specifying the character encoding used by the underlying DBMS.
23. Why can’t I invoke the ResultSet methods afterLast and beforeFirst when the method next works?
You are probably using a driver implemented for the JDBC 1.0 API. You need to upgrade to a JDBC 2.0 driver that implements scrollable result sets. Also be sure that your code has created scrollable result sets and that the DBMS you are using supports them.
24. How can I retrieve a String or other objeOracle’s JDBC Drivers
Oracle provides both Type 2 and Type 4 drivers. All Oracle JDBC drivers support the full JDBC specification, but in addition, they support the extended capabilities of the Oracle database. For example, the JDBC specification doesn’t support LOB data, but the Oracle OCI8 JDBC driver does. Oracle’s implementation of the Type 2 JDBC driver is referred to as the Oracle “OCI driver,” and the version of this driver that supports an Oracle 7 database is the OCI7 driver and the OCI8 supports Oracle 8. These drivers are platform specific; for example, the Windows NT and Windows 95 version of the driver (oci805jdbc.dll.) is implemented as a dynamic link library (DLL) in C. As mentioned previously, Type 2 drivers may require client code. In the case of the OCI8 driver, the clients must have Oracle’s Net*8 and all other dependent files loaded.
A common way to implement Oracle OCI drivers is to use Oracle Application Server with the JWeb cartridge on the middle tier and deploy the client presentation logic as an applet; the interaction with the Oracle database is conducted from the middle tier only, with just the results sent to the client applet as pure HTML or Java and HTML. All Oracle drivers are compliant with the Java Development Kit JDK 1.0 and 1.1.x and support the JDBC 1.22 standard. In addition, all Oracle JDBC drivers support data types such as RAW and LONG RAW, ROWID, and RECURSOR, which are supported in Oracle databases but not part of the JDBC standard. Oracle’s drivers also support execution of PL/SQL stored procedures and anonymous blocks (for dynamic execution), and
include capabilities such as row pre-fetching, execution batching, and defining query columns to
reduce the network round trips to Oracle database. In addition, the OCI driver for Oracle8 supports oracle data types CLOB, BLOB, NCLOB, and BFILE. The screenshot shows an example of one of the classes in the Oracle JDBC, as part of the class hierarchy from which it descends, as displayed in Oracle’s JDeveloper integrated development environment. As you can see, the OraclePreparedStatement class inherits from the java.sql.PreparedStatement class, which in turn inherits from the java.sql.Statement.
Oracle also provides a Type 4 JDBC driver, referred to as the Oracle “thin” driver. This driver includes its own implementation of a TCP/IP version of Oracle’s Net8 written entirely in Java, so it is platform independent, can be downloaded to a browser at runtime, and does not require any Oracle software on the client side. This driver requires a TCP/IP listener on the server side, and the client connection string uses the TCP/IP port address, not the TNSNAMES entry for the database name.
1.Add JDBC classes to your Java application or applet class by adding the following statement to your Java source code:
import java.sql.*;
To use the extended capabilities of the Oracle database, you must also import the Oracle JDBC driver. The statement in Java source looks like this:
import oracle.JDBC.driver.*
2.Load the JDBC driver by including the following statement in your class.
Class.forName(“oracle.JDBC.driver.OracleDriver”);
You can load the driver from your class-initialization routine.
3.Obtain a connection to an Oracle database by calling the getConnection() method of the JDBC DriverManager class. When you call this method you need to specify the connection information for the database in the form of a URL. The form the URL will take depends on the driver used. For example, to use the pure Java Type 4 Oracle driver (the thin driver) to connect to an Oracle7 database, the URL would read:
jdbc:oracle:thin@database_name:port_no:SID
To connect to an Oracle8 database using the OCI driver, the URL would be more like:
jdbc:oracle:oci8@database_name
To specify the database for use with an OCI driver, you can use either a SQL*Net name- value pair, or, if you’re using an Oracle Name server, you can use the name from the tnsname.ora file. (Both of these strings would conclude with the logon information as well—specifically the user name and password—but we’ve eliminated that from this example.)
The preliminary driver and database-connection issues now taken care of, there are still several other things your Java source code must include in order for the compiled code to submit queries to the database and process results.
4.Create a Statement object by calling the createStatement() method of the Connection object you created in the previous step. The following statement creates a Statement object stmt:
Statement stmt = conn.createStatement ();
5.Once the Statement object exists (in code), the application can then include code to execute a SQL query by calling the executeQuery() method of the Statement object.The executeQuery() method returns the result of the query in the ResultSet object. The following statement executes a query :
ResultSet rset = stmt.executeQuery (SELECT ename from emp where empno = 7900);
6.Finally, call the next() method of a ResultObject to retrieve a row and display it. Use a loop if the query returns more then one row from the database. For example, the following statements get the name of an employee from the ResultSet object and display it in the java.awt text control placed on the GUI.
rset.next();
enameTxtb.setText = ((String)rset.getString(1));
16
JDBC (java database conctivity)
1. What is JDBC ? what are its advantages ?
A. It is an API .The latest version of jdbc api is (3.0).
The JDBC 3.0 API is divided into two packages:
(1) java.sql and (2) javax.sql.
Both packages are included in the J2SE and J2EE platforms.
advantages:
The JDBC API can be used to interact with multiple data sources in a distributed, heterogenous environment.
It can connect to any of the database from java language.
It can switch over to any backend database without changing java code or by minute changes.
2. How many JDBC Drivers are there ? what are they?
A. There are 4 types of JDBC drivers.
a. JDBC-ODBC Bridge Driver(Type-1 driver)
b. Native API Partly Java Driver(Type-2 driver)
c. Net protocol pure Java Driver(Type-3 driver)
d. Native protocol Pure Java Driver(Type-4 driver)
3. Explain about JDBC-ODBC driver(Type-1) ? When this type of driver is used ?
A. In this mechanism the flow of execution will be
Java code(JDBC API)<------>JDBC-ODBC bridge driver<------->ODBC API<------->ODBC Layer<-------->DataBase
This type of JDBC Drivers provides a bridge between JDBC API and ODBC API.
This Bridge(JDBC-ODBC bridge) translates standard JDBC calls to Corresponding ODBC Calls, and
send them to ODBC database via ODBC Libraries.
The JDBC API is based on ODBC API.
ODBC(Open Database Connectivity)is Microsoft’s API for Database drivers.
ODBC is based on X/Open Call Level Interface(CLI)specification for database access.
The URL and class to be loaded for this type of driver are
Class :- sun.jdbc.odbc.JdbcOdbcDriver
URL :- jdbc:odbc:dsnname
4. Explain about Type-2 driver ? When this type of driver is used ?
A. The Drivers which are written in Native code will come into this category
In this mechanism the flow of Execution will be
java code(JDBC API)<------>Type-2 driver(jdbc driver)<------->Native API(vendor specific)<------->DataBase
When database call is made using JDBC,the driver translates the request into vendor-specific API calls.
The database will process the requet and sends the results back through the Native API ,which will
forward them back to the JDBC dirver. The JDBC driver will format the results to conform to the JDBC
standard and return them to the application.
5. Explain about Type-3 driver ? When this type of driver is used ?
A. In this mechanism the flow of Execution will be
java code(JDBC API)<------>JDBC driver<------->JDBC driver server<-------->Native driver<------->DataBase
The Java Client Application sends the calls to the Intermediate data access server(jdbc driver server)
The middle tier then handles the requet using other driver(Type-II or Type-IV drivers) to complete the request.
6. Explain about Type-4 driver ? When this type of driver is used ?
A. This is a pure java driver(alternative to Type-II drivers).
In this mechanism the flow of Execution will be
java code(JDBC API)<------>Type-4 driver(jdbc driver)<------->DataBase
These type of drivers convert the JDBC API calls to direct network calls using
vendor specific networking protocal by making direct socket connection with
database.
examples of this type of drivers are
1.Tabular Data Stream for Sybase
2.Oracle Thin jdbc driver for Oracle
7. What are the Advantages & DisAdvantages of Type-2 ,Type-4 Drivers over JDBC-ODBC bridge driver(Type-1)?
A. Type-2 & Type-4 are given
8. Which Driver is preferable for using JDBC API in Applets?
A. Type-4 Drivers.
9.Write the Syntax of URL to get connection ? Explain?
A.Syntax:- jdbc:<subprotocal>:<subname>
jdbc -----> is a protocal .This is only allowed protocal in JDBC.
<subprotocal> ----> The subprotocal is used to identify a database driver,or the
name of the database connectivity mechanism, choosen by the database driver providers.
<subname> -------> The syntax of the subname is driver specific. The driver may choose any syntax appropriate for its implementation
ex: jdbc:odbc:dsn
jdbc:oracle:oci8:@ database name.
jdbc:orale:thin:@ database name:port number:SID
10.How do u Load a driver ?
A. Using Driver Class.forName(java.lang.String driverclass) or registerDriver(Driver driver) .
11.what are the types of resultsets in JDBC3.0 ?How you can retrieve information of resultset?
A. ScrollableResultSet and ResultSet.We can retrieve information of resultset by using java.sql.ResultSetMetaData interface.You can get the instance by calling the method getMetaData() on ResulSet object.
12.write the steps to Connect database?
A. Class.forName(The class name of a spasific driver);
Connection c=DriverManager.getConnection(url of a spasific driver,user name,password);
Statement s=c.createStatement();
(or)
PreparedStatement p=c.prepareStatement();
(or)
CallableStatement cal=c.prpareCall();
Depending upon the requirement.
13.Can java objects be stored in database? how?
A.Yes.We can store java objects, BY using setObject(),setBlob() and setClob() methods in PreparedStatement
14.what do u mean by isolation level?
A. Isolation means that the business logic can proceed without
consideration for the other activities of the system.
15.How do u set the isolation level?
A. By using setTransactionIsolation(int level) in java.sql.Connection interface.
level MEANS:-
static final int TRANSACTION_READ_UNCOMMITTED //cannot prevent any reads.
static final int TRANSACTION_READ_COMMITTED //prevents dirty reads
static final int TRANSACTION_REPEATABLE_READ //prevents dirty reads & non-repeatable read.
static final int TRANSACTION_SERIALIZABLE //prevents dirty reads , non-repeatable read & phantom read.
These are the static final fields in java.sql.Connection interface.
16. what is a dirty read?
A. A Dirty read allows a row changed by one transaction to be
read by another transaction before any change in the row
have been committed.
This problem can be solved by setting the transaction isolation
level to TRANSACTION_READ_COMMITTED
17. what is a non-repeatable read ?
A. A non-repeatable read is where one transaction reads a row, a second
transaction alters or deletes the row, and the first transaction
re-reads the row,getting different values the second time.
This problem can be solved by setting the transaction isolation
level to TRANSACTION_REPEATABLE_READ
18. what is phantom read?
A. A phantom read is where one transaction reads all rows that satisfy a WHERE condition,a second transaction inserts a row that satisfies that WHERE condition,and the first transaction re-reads for the same condition,retrieving the additional ‘phantom’ row in the second read This problem can be solved by setting the transaction isolation level to TRANSACTION_SERIALIZABLE
19.What is the difference between java.sql.Statement &
java.sql.PreparedStatement ?
A. write the appropriate situations to use these statements?
20.How to retrieve the information about the database ?
A.we can retrieve the info about the database by using inerface java.sql.DatabaseMetaData we can get this object by using getMetaData() method in Connection interface.
21.what are the Different types of exceptions in jdbc?
A. BatchUpdateException
DataTruncation
SQLException
SQLWarning
22.How to execute no of queries at one go?
A. By using a batchUpdate’s (ie throw addBAtch() and executeBatch()) in java.sql.Statement interface,or by using procedures.
23. what are the advantages of connection pool.
A. Performance
24. In which interface the methods commit() & rollback() are defined ?
A. java.sql.Connection interface
25. How to store images in database?
A. Using binary streams (ie getBinaryStream() ,setBinaryStream()). But it is not visable in database ,it is stored in form of bytes ,to make it visable we have to use any one frontend tool.
26.How to check null value in JDBC?
A. By using the method wasNull() in ResultSet ,it returns boolean value.
Returns whether the last column read had a value of SQL NULL.
Note that you must first call one of the getXXX methods on a column to try to read its value and then call the method wasNull to see if the value read was SQL NULL.
27.Give one Example of static Synchronized method in JDBC API?
A. getConnection() method in DriverManager class.Which is used to get object of Connection interface.
28.What is a Connection?
A. Connection is an interface which is used to make a connection between client and Database (ie opening a session with a particular database).
29.what is the difference between execute() ,executeUpdate() and executeQuery() ? where we will use them?
A. execute() method returns a boolean value (ie if the given query returns a resutset then it returns true else false),so depending upon the return value we can get the ResultSet object (getResultset())or we can know how many rows have bean affected by our query (getUpdateCount()).That is we can use this method for Fetching queries and Non-Fetching queries.Fetching queries are the queries which are used to fetch the records from database (ie which returns resutset) ex: Select * from emp.
Non-Fetching queries are the queries which are used to update,insert,create or delete the records from database
ex: update emp set sal=10000 where empno=7809.
executeUpdate() method is used for nonfetching queries.which returns int value.
executeQuery() method is used for fetching queries which returns ResulSet object ,Which contains methods to fetch the values.
30.How is jndi useful for Database connection?
ct type without creating a new object each time?
Creating and garbage collecting potentially large numbers of objects (millions) unnecessarily can really hurt performance. It may be better to provide a way to retrieve data like strings using the JDBC API without always allocating a new object.
We are studying this issue to see if it is an area in which the JDBC API should be improved. Stay tuned, and please send us any comments you have on this question.
25. There is a method getColumnCount in the JDBC API. Is there a similar method to find the number of rows in a result set?
No, but it is easy to find the number of rows. If you are using a scrollable result set, rs, you can call the methods rs.last and then rs.getRow to find out how many rows rs has. If the result is not scrollable, you can either count the rows by iterating through the result set or get the number of rows by submitting a query with a COUNT column in the SELECT clause.
26. I would like to download the JDBC-ODBC Bridge for the Java 2 SDK, Standard Edition (formerly JDK 1.2). I’m a beginner with the JDBC API, and I would like to start with the Bridge. How do I do it?
The JDBC-ODBC Bridge is bundled with the Java 2 SDK, Standard Edition, so there is no need to download it separately.
27. If I use the JDBC API, do I have to use ODBC underneath?
No, this is just one of many possible solutions. We recommend using a pure Java JDBC technology-enabled driver, type 3 or 4, in order to get all of the benefits of the Java programming language and the JDBC API.
28. Once I have the Java 2 SDK, Standard Edition, from Sun, what else do I need to connect to a database?
You still need to get and install a JDBC technology-enabled driver that supports the database that you are using. There are many drivers available from a variety of sources. You can also try using the JDBC-ODBC Bridge if you have ODBC connectivity set up already. The Bridge comes with the Java 2 SDK, Standard Edition, and Enterprise Edition, and it doesn’t require any extra setup itself. The Bridge is a normal ODBC client. Note, however, that you should use the JDBC-ODBC Bridge only for experimental prototyping or when you have no other driver available.
16
What is a JDBC Driver?
A JDBC driver is the set of classes that implement the JDBC interfaces for a particular database.
There are four different types of JDBC driver: A Type 1 driver is a JDBC-ODBC bridge driver; this type of driver enables a client to connect to an ODBC database via Java calls and JDBC—neither the database nor middle tier need to be Java compliant.However, ODBC binary code must be installed on each client machine that uses this driver.
A Type 2 driver converts JDBC calls into calls for a specific database. This driver is referred to as a native-API, partly Java driver. As with the Type 1 driver, some binary code may be required on the client machine, which means this type of driver is not suitable for downloading over a network to a client.
A Type 3 driver is a JDBC-Net pure Java driver, which translates JDBC calls into a database -independent net protocol. Vendors of database middleware products can implement this type of driver into their products to provide interoperability with the greatest number of database servers.
Finally, a Type 4 driver, or, native protocol, pure Java driver converts JDBC calls into the network protocol used by the database directly. A Type 4 driver requires no client software, so it’s ideal for deployment to browsers at runtime. Each of these driver types has its own optimal usage scenarios, and will affect the way you deploy a given Java application.
For example, because Type 4 drivers are 100% Java, use Java sockets to connect to the database, and require no client-side data access code, they are ideal for applets or other download situations inside a firewall.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.