How to: Use Resultset Locking to Update LOBs Using ODP.NET
Date: 11-Oct-2004
Objective
After reading this how-to document you should be able to:
Prerequisite
The reader is assumed to be familiar with MS Visual Studio.NET, and also possess an understanding of the rudimentaries of ODP.NET and databases.
Introduction
ODP.NET provides an easy and optimal way to access and manipulate Lobs ODP.NET provides three types of LOB objects for manipulating the LOB data namely the OracleBlob, OracleClob and OracleBfile. For updating Lobs using these objects, two requirements need to be met. Firstly, a transaction must be started before selecting the LOB column. Next, the rows in which the columns reside must be locked, on a row-by-row basis or as a part of the entire resultset.
This document demonstrates how to update LOB data using resultset locking i.e. locking the entire resultset. The update is done through an OracleCommand object. The LOB data is bound as a parameter to a SQL statement. After establishing a database connection successfully, a database table with a CLOB column is created. The table is populated with initial CLOB data. An OracleTransaction is started and the rows to be updated are retrieved from the database. The complete Resultset is locked using "SELECT ..FOR UPDATE" clause. The retrieved CLOB object is obtained in an OracleClob type object using OracleDataReader for update. Using OracleClob.append method the existing CLOB data is modified. After making changes to the OracleClob object the transaction is committed. Using a read method, the new, updated data is displayed from the database on console using OracleDataReader. Using OracleClob.erase the modified data is erased. Then, using OracleClob.write the old data is rewritten to the clob object.
Value Proposition
Once a transaction is started, an application can use any one of the following three levels of locking - these locks can be placed appropriately to avoid any dirty 'write's while updating LOBs:
- No Locking: No locking minimizes data contention, however, it's also the most dangerous since it cannot avoid dirty 'write's.
- Row Level Locking: Row locking allows particular rows of the ResultSet to be locked. Though this is much safer than the 'no locking' mechanism, it does not completely prevent dirty writes. On another note, row locking offers less data contention than ResultSet locking if the ResultSet consists of more than one row.
- ResultSet Locking: locking the ResultSet prevents dirty writes during the lifetime of the lock on the ResultSet. However, data contention can be an issue if the lock is established for a long time on a large amount of rows.
Requirements
Create the Database Objects
This How-to document uses the multimedia_tab table. Connect to the database as any user, for eg. scott/tiger using SQL*Plus and run the following commands:
DROP TABLE multimedia_tab;
CREATE TABLE multimedia_tab(thekey NUMBER(4) PRIMARY KEY, story CLOB, sound BLOB);
INSERT INTO multimedia_tab values(1,'This is a long story. Once upon a time ...',
'656667686970717273747576777879808182838485');
commit;
|
|
Code Walk-Through
Include Required Namespaces: It is worthwhile to add references of the namespaces in the 'general declarations' section of the .cs or .vb file, to avoid qualifying their usage later in the script:
| C# |
using System;
using System.Text;
using Oracle.DataAccess.Types;
using Oracle.DataAccess.Client;
|
| Visual Basic .NET |
Imports System
Imports System.Text
Imports Oracle.DataAccess.Types
Imports Oracle.DataAccess.Client
|
1. Establish a connection to the database:
| C# |
// Step 1
// Establish connection using ODP.NET
// NOTE: Modify User Id, Password, Data Source
// as per your database setup
string connectStr = "User Id=Scott;Password=tiger;Data Source=orcl9i";
OracleConnection connection = new OracleConnection(connectStr);
connection.Open();
Console.WriteLine("connected to database");
Console.WriteLine(" ");
|
| Visual Basic .NET |
' Step 1
' Establish connection using ODP.NET
' NOTE: Modify User Id, Password, Data Source
' as per your database setup
Dim connectStr As String = "User Id=Scott;Password=tiger;Data Source=orcl9i"
' Initialize connection
Dim connection As OracleConnection = New OracleConnection(connectStr)
connection.Open()
Console.WriteLine("connected to database")
Console.WriteLine(" ")
|
2. Declare the required Oracle objects:
| C# |
// Step 2
// Declare Oracle objects
OracleTransaction txn;
OracleCommand cmd = new OracleCommand("",connection);
OracleDataReader reader;
OracleClob clob;
|
| Visual Basic .NET |
'Step 2
' Declare Oracle objects
Dim txn As OracleTransaction
Dim cmd As OracleCommand = New OracleCommand("", connection)
Dim reader As OracleDataReader
Dim clob As OracleClob
|
3. For any LOB update, an Oracle transaction is required. So, begin the transaction :
| C# |
// Step 3
// Start a transaction
txn = connection.BeginTransaction();
|
| Visual Basic .NET |
' Step 3
' Start a transaction
txn = connection.BeginTransaction()
|
4. Use the FOR UPDATE clause to lock the complete Resultset for update. Then execute the statement:
| C# |
// Step 4
// Lock the result set using the "FOR UPDATE" clause
cmd.CommandText = "SELECT story FROM multimedia_tab FOR UPDATE";
reader = cmd.ExecuteReader();
|
| Visual Basic .NET |
' Step 4
' Lock the result set using the "FOR UPDATE" clause
cmd.CommandText = "SELECT story FROM multimedia_tab FOR UPDATE"
reader = cmd.ExecuteReader()
|
5. Read data into an Oracle LOB object:
| C# |
// Step 5
// Read data from an OracleDataReader using a proper typed
// accessor to an Oracle LOB object
reader.Read();
clob = reader.GetOracleClob(0);
Console.WriteLine("Old Data: {0}", clob.Value);
Console.WriteLine(" ");
|
| Visual Basic .NET |
' Step 5
' Read data from an OracleDataReader using a proper typed
' accessor to an Oracle LOB object
reader.Read()
clob = reader.GetOracleClob(0)
Console.WriteLine("Old Data: {0}", clob.Value)
Console.WriteLine(" ")
|
6. Modify and then commit the data in the current instance of OracleCLOB. Committing the transaction automatically releases the lock:
| C# |
// Step 6
// Modify the CLOB column of the row
string ending = " The end.";
// Append CLOB data to the current OracleCLOB instance
clob.Append(ending.ToCharArray(), 0, ending.Length);
// Release the lock
txn.Commit();
Console.Write("Updated to new data:");
|
| Visual Basic .NET |
' Step 6
' Modify the CLOB column of the row
Dim ending As String = " The end."
' Append CLOB data to the current OracleCLOB instance
clob.Append(ending.ToCharArray(), 0, ending.Length)
' Release the lock
txn.Commit()
Console.Write("Updated to new data:")
|
7. Fetch the modified data from the database and display it on the console:
| C# |
// Step 7
// Fetch the modified data from the database
// Lock the complete resultset using the "FOR UPDATE" clause
cmd.CommandText = "SELECT story FROM multimedia_tab FOR UPDATE";
reader = cmd.ExecuteReader();
reader.Read();
clob = reader.GetOracleClob(0);
Console.WriteLine(clob.Value);
Console.WriteLine(" ");
|
| Visual Basic .NET |
' Step 7
' Fetch the modified data from the database
' Lock the complete resultset using the "FOR UPDATE" clause
cmd.CommandText = "SELECT story FROM multimedia_tab FOR UPDATE"
reader = cmd.ExecuteReader()
reader.Read()
clob = reader.GetOracleClob(0)
Console.WriteLine(clob.Value)
Console.WriteLine(" ")
|
8. Reset the current instance of CLOB object to the old data:
| C# |
// Step 8
// Reset data with the old value
// Start an Oracle transaction
txn = connection.BeginTransaction();
// Clear the contents of the current instance
// of OracleCLOB object
clob.Erase();
// Re-write a string to the old value
StringBuilder blr1 = new StringBuilder();
blr1.Append("'This is a long story. Once upon a time ...',");
String oldData = blr1.ToString();
|
| Visual Basic .NET |
' Step 8
' Reset data with the old value
' Start an Oracle transaction
txn = connection.BeginTransaction()
' Clear the contents of the current instance
' of OracleCLOB object
clob.Erase()
' Re-write a string to the old value
Dim blr1 As StringBuilder = New StringBuilder()
blr1.Append("'This is a long story. Once upon a time ...',")
Dim oldData As String = blr1.ToString()
|
9. Committing the transaction automatically releases the lock. The old data is then displayed on the console:
| C# |
// Step 9
// Write the byte array into the OracleCLOB object
clob.Write(oldData.ToCharArray(),0, oldData.Length);
// Update the data and release the lock
txn.Commit();
Console.WriteLine("Old Data again: {0}", clob.Value);
|
| Visual Basic .NET |
' Step 9
' Write the byte array into the OracleCLOB object
clob.Write(oldData.ToCharArray(), 0, oldData.Length)
' Update the data and release the lock
txn.Commit()
Console.WriteLine("Old Data again: {0}", clob.Value)
|
Set up and Run the How-to
1. Open Visual Studio.NET.
2. Create a Console Application Project:
| C# |
Create a Console Application Project in C#. Class1.cs is added to the project by default. |
| Visual Basic .NET |
Create a Console Application Project in Visual Basic .NET. Module1.vb is added to the project by default. |
3. Make sure that your project contains references to the System, Oracle.DataAccess and System.Data namespaces. Add references to these namespaces if they do not exist.
4.Copy the code:
| C# |
Using Solution Explorer open Class1.cs. For complete listing of code for this How-to article in C# click here. Copy the code and overwrite the contents of Class1.cs. |
| Visual Basic .NET |
Using Solution Explorer open Module1.vb. For the complete listing of code for this How-to article in VB.NET click here. Copy the code and overwrite the contents of Module1.vb. |
5. Modify the User Id, Password and Data Source as per your database setup in Step 1 of the code.
6.To compile and run this application press Ctrl+F5. This displays the old and new data as shown in Figure 1.1:
Figure 1.1 - Screenshot of output
Resources
Please enter your comments on this How-To in the OTN Sample Code Discussion Forum. |
Bookmark
