1. What is the query used to display all tables names in SQL Server (Query analyzer)?
----------------------------------------------------------------------------------
2. How many types of JDBC Drivers are present and what are they?
Answer: There are 4 types of JDBC Drivers:
Type 1: JDBC-ODBC Bridge Driver
Type 2: Native API Partly Java Driver
Type 3: Network protocol Driver
Type 4: JDBC Net pure Java Driver
----------------------------------------------------------------------------------
3. What is the fastest type of JDBC driver?
Answer: In general, all things being equal, you can assume that the more your request and response change hands, the slower it will be. This means that Type 1 and Type 3 drivers will be slower than Type 2 drivers (the database calls make atleast 3 translations), and Type 4 drivers are the fastest (only one translation).
----------------------------------------------------------------------------------
4. What Class.forName will do while loading drivers?
Answer: It is used to create an instance of a driver and register it with the DriverManager. When you have loaded a driver, it is available for making a connection with a DBMS.
----------------------------------------------------------------------------------
5. How to Retrieve Warnings?
Answer: SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an application, as exceptions do; they simply alert the user that something did not happen as planned. A warning can be reported on a Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a ResultSet object. Each of these classes has a getWarnings method, which you must invoke in order to see the first warning reported on the calling object.
E.g.
SQLWarning warning = stmt.getWarnings();
if (warning != null) {
while (warning != null) {
System.out.println("Message: " + warning.getMessage());
System.out.println("SQLState: " + warning.getSQLState());
System.out.print("Vendor error code: ");
System.out.println(warning.getErrorCode());
warning = warning.getNextWarning();
}}
----------------------------------------------------------------------------------
6. What are stored procedures? How is it useful?
Answer: A stored procedure is a set of statements/commands which reside in the database. The stored procedure is precompiled and saves the database the effort of parsing and compiling sql statements everytime a query is run. Each Database has it's own stored procedure language, usually a variant of C with a SQL preproceesor. Newer versions of databases support writing stored procs in Java and Perl too. Before the advent of 3-tier/n-tier architecture it was pretty common for stored procedures to implement the business logic. The biggest advantage is of course speed. Stored procs are also useful when you want to do Batch updates/exports/houseKeeping kind of stuff on the db. The overhead of a JDBC Connection may be significant in these cases.
----------------------------------------------------------------------------------
7. How to call a Stored Procedure from JDBC?
Answer: The first step is to create a CallableStatement object. As with Statement an and PreparedStatement objects, this is done with an open Connection object. A CallableStatement object contains a call to a stored procedure.
E.g.
CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();
----------------------------------------------------------------------------------
8. Is the JDBC-ODBC Bridge multi-threaded?
Answer: 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 multithreading.
----------------------------------------------------------------------------------
9. Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection?
Answer No. You can open only one Statement object per connection when you are using the JDBC-ODBC Bridge.
----------------------------------------------------------------------------------
10. What is cold backup, hot backup, warm backup recovery?
Answer:
Cold backup - All these files must be backed up at the same time, before the database is restarted.
Hot backup/Online backup- It is a backup taken of each table-space while the database is running and is being accessed by the users.
----------------------------------------------------------------------------------
11. When we will Denormalize data?
Answer: Data denormalization is reverse procedure, carried out purely for reasons of improving performance.It maybe efficient for a high throughput system to replicate data for certain data. The goal of normalization is to remove the following dangerous features from the database:
-Duplication of data -Inconsistent data -Ambiguous data
Normalization starts off as a mechanical process where you just follow the rules but these rules can lead to a ridiculous end result. The final design does need to be adjusted by an intelligent hand.
----------------------------------------------------------------------------------
12. What is the advantage of using PreparedStatement?
Answer: If we are using PreparedStatement the execution time will be less.The PreparedStatement object contains a precompiled SQL statement!This means that when the PreparedStatement is executed,the RDBMS can just run the PreparedStatement's Sql statement without having to compile it first.
----------------------------------------------------------------------------------
13. What is a "dirty read"?
Answer: Quite often in database processing, we come across the situation wherein one transaction can change a value, and a second transaction can read this value before the original change has been committed or rolled back. This is known as a dirty read scenario because there is always the possibility that the first transaction may rollback the change, resulting in the second transaction having read an invalid value. While you can easily command a database to disallow dirty reads, this usually degrades the performance of your application due to the increased locking overhead. Disallowing dirty reads also leads to decreased system concurrency.----------------------------------------------------------------------------------
14. What is Metadata and why should I use it?Answer: Metadata ('data about data') contains information about one of these two things:
- Database information (java.sql.DatabaseMetaData),
- Information about a specific ResultSet (java.sql.ResultSetMetaData).
Use DatabaseMetaData to find information about your database, such as its capabilities and structure. Use ResultSetMetaData to find information about the results of an SQL query, such as size and types of columns.
----------------------------------------------------------------------------------
15. Different types of Transaction Isolation Levels?
Answer: The isolation level describes the degree to which the data being updated is visible to other transactions. This is important when two transactions are trying to read the same row of a table.Imagine two transactions A and B:
Three types of inconsistencies can occur:
- Dirty-read: A has changed a row, but has not committed the changes. B reads the uncommitted data but his view of the data may be wrong if A rolls back his changes and updates his own changes to the database.
- Non-repeatable read: B performs a read, but A modifies or deletes that data later. If B reads the same row again, he will get different data.
- Phantoms: A does a query on a set of rows to perform an operation. B modifies the table such that a query of A would have given a different result. The table may be inconsistent.
TRANSACTION_READ_UNCOMMITTED : DIRTY READS, NON-REPEATABLE
READ AND PHANTOMS CAN OCCUR.
TRANSACTION_READ_COMMITTED : DIRTY READS ARE PREVENTED,
NON-REPEATABLE READ AND PHANTOMS CAN OCCUR.
TRANSACTION_REPEATABLE_READ : DIRTY READS , NON-REPEATABLE
READ ARE PREVENTED AND PHANTOMS CAN OCCUR.
TRANSACTION_SERIALIZABLE : DIRTY READS, NON-REPEATABLE READ
AND PHANTOMS ARE PREVENTED.
----------------------------------------------------------------------------------
16. What is 2 phase commit?
Answer: A 2-phase commit is an algorithm used to ensure the integrity of a committing transaction. In Phase 1, the transaction coordinator contacts potential participants in the transaction. The participants all agree to make the results of the transaction permanent but do not do so immediately. The participants log information to disk to ensure they can complete Phase 2. If all the participants agree to commit, the coordinator logs that agreement and the outcome is decided. The recording of this agreement in the log ends Phase 1. In Phase 2, the coordinator informs each participant of the decision, and they permanently update their resources.
-----------------------------------------------------------------------------------
17. How do you handle your own transaction ?
Answer: Connection Object has a method called setAutocommit ( Default Boolean is true) Just set the Parameter to false , and begin your transaction.
----------------------------------------------------------------------------------
18. What is the normal procedure followed by a java client to access the database?
Answer: The database connection is created in 3 steps:
1.Find a proper database URL
2.Load the database driver
3.Ask the Java DriverManager class to open a connection to your database.
In java code, the steps are realized in code as follows:
1.Create a properly formatted JDBC URL for your database. A JDBC URL has the form jdbc:someSubProtocol://myDatabaseServer/theDatabaseName
2.Class.forName("my.database.driver");
3.Connection conn=DriverManager.getConnection("a.JDBC.URL","databaseLogin","databasePassword")
----------------------------------------------------------------------------------
19. What is a data source?
Answer: A DataSource class brings another level of abstraction than directly using a connection object. Data source can be referenced by JNDI. Data Source may point to RDBMS, file System , any DBMS etc.
----------------------------------------------------------------------------------
20. What are connection pools? What are the advantages?
Answer A connection pool is a cache of database connections that is maintained in memory, so that the connections may be reused.
----------------------------------------------------------------------------------
21. How do you get Column names only for a table (SQL Server)? Write the Query.
Answer: select name from syscolumns where id=(select id from sysobjects where name='user_hdr') order by colid --user_hdr is the table name
----------------------------------------------------------------------------------
22. What is the difference between cached rowset, jdbrowset and webrowset?
Answer: A CachedRowSet is a disconnected, serializable, scrollable container for tabular data. The primary purpose of the CachedRowSet class is to provide a representation of a JDBC ResultSet that can be passed between different components of a distributed application. For example, a CachedResultSet can be used to send the result of a query executed by an EJB component running in a server environment over a network to a client running in a web browser. A second use for CachedRowSets is to provide scrolling and updating for ResultSets that don't provide these capabilities themselves. Finally, a CachedRowSet can be used to provide Java applications with access to tabular data in an environment such as a thin client or PDA, where it would be inappropriate to use a JDBC driver due to resource limitations or security considerations. The CachedRowSet class provides a means to "get rows in" and "get changed rows out" without the need to implement the full JDBC API.
A JdbcRowSet is a connected rowset that wraps a ResultSet object. The main use of JdbcRowSet is to wrap a ResultSet and make it appear as a JavaBeans component.
The WebRowSet class extends CachedRowSet with the ability to write out the state of the the RowSet as an XML document. The format of the XML document is described by the DTD 'RowSet.dtd'.
----------------------------------------------------------------------------------
23. Is thin driver provided by Oracle a type 4 driver?
Answer YES
----------------------------------------------------------------------------------
24. What are different types of isolation levels in JDBC and explain where you can use them?
Answer:
- If the application needs only committed records, then TRANSACTION_READ_COMMITED isolation is the good choice.
- If the application needs to read a row exclusively till you finish your work, then TRANSACTION_REPEATABLE_READ is the best choice.
- If the application needs to control all of the transaction problems(dirty read, phantom read and unrepeatable read), you can choose TRANSACTION_SERIALIZABLE for maximum safety.
-If the application don't have to deal with concurrent transactions, then the best choice is TRANSACTION_NONE to improve performance.
- If the application is searching for records from the database then you can easily choose TRANSACTION_READ_UNCOMMITED because you need not worry about other programmes that are inserting records at the same time. It improves performance.
----------------------------------------------------------------------------------
25. What is the difference between Statement, PreparedStatement and CallableStatement?
Answer: Statement is used for static SQL statement with no input and output parameters, PreparedStatement is used for dynamic SQL statement with input parameters and CallableStatement is used for dynamic SQL satement with both input and output parameters.
But PreparedStatement and CallableStatement can be used for static SQL statements as well. CallableStatement is mainly meant for stored procedures. PreparedStatement gives better performance when compared to Statement because it is pre-parsed and pre-compiled by the database once for the first time and then onwards it reuses the parsed and compiled statement. But we loose java portability and we have to depend up on database specific stored procedures.
-----------------------------------------------------------------------------------
26. Who implements the methods of JDBC (java.sql.*) interfaces?
e.g.resultSet.next() is a method in ResultSet interface and Sun doesn't provide any implementation for this method. Who actually provides the implementation?
Answer: Its the JDBC Driver vendor. Java provides the specifications for the interfaces and its the vendor who has to implement those methods.
-----------------------------------------------------------------------------------
27. Consider the following code:
Class.forName("mypackage.MyClass");
Connection con = DriverManager.getConnection("some connection string");
What Exceptions can be thrown in the above statements?
Answer: The above statements can throw any or all of the following exceptions:
LinkageError - if the linkage fails
ExceptionInInitializerError - if the initialization provoked by this method fails
ClassNotFoundException - if the class cannot be located
SQLException - if a database access error occurs
-----------------------------------------------------------------------------------
28. What actually does Class.forName("mypackage.MyDriver"); method do?
Answer: Class.forName("..."); initializes the provided class and returns the Class object associated
with the class or interface with the given string name. For example, the following code fragment returns the runtime Class descriptor for the class named java.lang.Thread:
Class t = Class.forName("java.lang.Thread")
-----------------------------------------------------------------------------------
29. If your SQL gets truncated in the process of execution, How would you know how much of data is trnasfered and how much of data is left over?
Answer: This can be known using the class DataTruncation. DataTruncation is an exception that reports a DataTruncation warning (on reads) or throws a DataTruncation exception (on writes) when JDBC unexpectedly truncates a data value. So all we should do is write our code using the getDataSize() and getTransferSize() methods of this class in our catch block trapping this SQLException.
The getDataSize() returns the number of bytes of data that should have been transferred while the getTransferSize() method returns the number of bytes of data actually transferred. The SQLstate for a DataTruncation is 01004.
