1. How do you get Column names only for a table (SQL Server)?
Write the Query. -
select name from syscolumns
where id=(select id from sysobjects where name='user_hdr')
order by colid --user_hdr is the table name
2. What are collection pools? What are the advantages?
A connection pool is a cache of database connections that is maintained in memory, so that the connections may be reused
3. What is a data source (DS)?
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.
4. What is the normal procedure followed by a java client to access the db?
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 JDBR URL for your database. (See FAQ on JDBC URL for more information). 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”)
5. How do you handle your own transaction?
Connection Object has a method called setAutocommit(Boolean istrue)
Default is true. Set the Parameter to false , and begin your transaction
6. What are different types of Transaction Isolation Levels?
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. Here 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.
7. What is Metadata and why should I use it?
Metadata ('data about data') is information about one of two things: Database information (java.sql.DatabaseMetaData), or 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
8. What is the advantage of using PreparedStatement?
If we are using PreparedStatement the execution time will be less. The PreparedStatement object contains not just an SQL statement, but the SQL statement that has been precompiled. This means that when the PreparedStatement is executed,the RDBMS can just run the PreparedStatement's Sql statement without having to compile it first.
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.
10. What is cold backup, hot backup, warm backup recovery?
Cold backup (All these files must be backed up at the same time, before the databaseis restarted). Hot backup (official name is ‘online backup') is a backup taken of each tablespace while the database is running and is being accessed by the users.
