Friday, June 25, 2010

Difference between ResultSet TYPE_SCROLL_SENSITIVE and TYPE_SCROLL_INSENSITIVE

Difference between ResultSet TYPE_SCROLL_SENSITIVE and TYPE_SCROLL_INSENSITIVE

If you would like to have a scrollable ResultSet Object then you need to supply constant when you create an object of it. By default it is FORWARD_ONLY, you can traverse forward in order to fetch records from table.

e.g. Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE , ResultSet.CONCUR_READ_ONLY);
Or
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE , ResultSet.CONCUR_READ_ONLY);

Or
Statement stmt = this.con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

Or
Statement stmt = this.con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);


A result set that is TYPE_SCROLL_INSENSITIVE does not reflect changes made while it is still open and one that is TYPE_SCROLL_SENSITIVE does.
Changes will be visible if result set is closed and re-opened.

TYPE_SCROLL_SENSTIVE does not *guarantee* that all changes made to the result set by others will be visible.It depends on a number of factors including the driver implementation (whether it caches the rows or not), the transaction isolation level etc...

Wednesday, June 2, 2010

Why should use Stored Procedures

In software development its very common to interact with Database(s) and Application Layer used to fetch data from DB using any programming language like java, C# conjunction with SQL.
There are two kind of DB languages
1) SQL : Non procedural language, where you can not write your own logic / subroutines.
2) PL/SQL: Procedural language, where you can write business logic and subroutines.

Stored procedures used where we would require the following
* modular programming at DB level
* Faster execution.
* Reduce network traffic.
* Security mechanism.

Advantages:

->You can modify stored procedures independently of the program source code.The application doesn't have to be recompiled when/if the SQL is altered.
->Stored procedures abstract or separate server-side functions from the client-side.
->Stored procedures allow a lot more flexibility offering capabilities such as conditional logic.
->Stored procedures are stored within the databse and run directly in database engine, bandwidth and execution time are reduced. This is because a single stored procedure can execute a complex set of SQL statements.

Disadvantages:

->If we need to modify anything in stored procedure then its definition has to be replaced.
->Any changes in the stored procedure will impact all the places whereever this procedure is used.

How to create :
http://download.oracle.com/docs/html/B16022_01/ch3.htm
Examples in java:
http://www.oracle.com/technology/sample_code/tech/java/jsp/oracle9ijsp.html