How do i use a Oracle transaction defined as

SET TRANSACTION READ ONLY;

with SelfServicing?

Lets say we have the following code.

MyTypedView view = new MyTypedView(); SortExpression sort = new SortExpression(); PredicateExpression filter = new PredicateExpression(); view.Fill(0, sort, true);

I'm not sure why you want to use a Transaction when reading from the database, But anyway I think you need to use IsolationLevel.ReadCommitted

Please check Transactions, SelfServicing

READ ONLY can bee used for reporting because changs after the transaction has started will not be seen http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_10005.htm

ReadCommitted is not the same.

I believe the Oracle .NET provider doesn't support this.

I think 'Snapshot' is the one you should use instead. 'IsolationLevel' on .NET doesn't support ReadOnly otherwise, it's not a valid value.

The funny thing is that OracleConnection says: "IsolationLevel must be ReadCommitted or Serializable"

Nevertheless it can be done with the following code.

Transaction transaction = new Transaction(IsolationLevel.ReadCommitted, "ContractFactory Snapshot Trans"); OracleCommand cmd = new OracleCommand("SET TRANSACTION READ ONLY", (OracleConnection)transaction.ConnectionToUse); cmd.ExecuteNonQuery();

But I'll like to do the same with the LLBL framework.

Transaction opens an OracleTransaction, with passing the isolation level, so it can't be set to something readonly. If you want that, you could manually create an ActionQuery and run it, which is more or less what you are doing right now.

Who do I get access to LLBL OracleConnection whitout creating a transaction?

llblstc wrote:

Who do I get access to LLBL OracleConnection whitout creating a transaction?

The easiest way to do this is by creating a derived class of the Transaction class (which is in the generated code). In there, you've to override CreatePhysicalTransaction. In that override, you do something like:

IDbTransaction toReturn = base.CreatePhysicalTransaction();
ActionQuery query = new ActionQuery(new OracleCommand("SET TRANSACTION READ ONLY", (OracleConnection)toReturn.Connection);
query.Execute();
return toReturn;

Then in your code, instantiate an instance of that derived class instead of Transaction before doing any operations.

Hi Otis,

I'm looking to do something similar with DB2 as below.

If I use a standard DataAccessAdapter then my test query works but when i switch to use the new class (as below) an exception, "No Connection present. Cannot execute command." is thrown after the "query.Execute()" line

Any ideas?

With thanks,

pmax100

public class Db2ReadOnlyAdapter : DataAccessAdapter { protected override IDbTransaction CreateNewPhysicalTransaction() { Console.WriteLine("CreateNewPhysicalTransaction"); IDbTransaction transaction = base.CreateNewPhysicalTransaction(); ActionQuery query = new ActionQuery(new DB2Command("SET TRANSACTION READ ONLY", (DB2Connection)transaction.Connection)); query.Execute();

        return transaction;

        //return base.CreateNewPhysicalTransaction();
    }
}

Your code looks fine, and there should be a connection available, could you please debug this.

Would you please try to change the command passed to: "SET TRANSACTION ISOLATION LEVEL READ ONLY"

Hi Walaa,

I've tried both:

 SET TRANSACTION ISOLATION LEVEL READ ONLY

and

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED READ ONLY

but still get the same problem

Also for reference, in the caller code I set the derived adapter up as follows:

 Db2ReadOnlyAdapter adapter = new Db2ReadOnlyAdapter();  
 adapter.StartTransaction(IsolationLevel.ReadUncommitted, "TransForRead");

I can't see much wrong with that either!

pmax100

Hi again,

This is a debug trace overview..

adapter.StartTransaction(IsolationLevel.ReadUncommitted, "TransForRead");

Db2ReadOnlyAdapter->StartTransaction
    base.StartTransaction(isolationLevelToUse, name);

Db2ReadOnlyAdapter->OpenConnection
    base.OpenConnection

Db2ReadOnlyAdapter->CreateNewPhysicalTransaction
    ActionQuery
    Exception Thrown

I added in a few overrides to track the sequence of events taht are fired.

pmax100

Please post the exact exception message and stack trace.

Of course...

the Exception is:

[System.InvalidOperationException] {"No Connection present. Cannot execute command."}

Source: "SD.LLBLGen.Pro.ORMSupportClasses.NET20"

Stack Trace: at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute() at JD.WC.Queries.Db2ReadOnlyAdapter.CreateNewPhysicalTransaction() in C:\\WC\\Queries\\JD.WC.Queries\\Db2ReadOnlyAdapter.cs:line 23 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.StartTransaction(IsolationLevel isolationLevelToUse, String name) at JD.WC.Queries.Db2ReadOnlyAdapter.StartTransaction(IsolationLevel isolationLevelToUse, String name) in C:\\WC\\Queries\\JD.WC.Queries\\Db2ReadOnlyAdapter.cs:line 39 at OrderTest.Program.DoExport() in C:\\WC\\TestApp\\Program.cs:line 29"

I'm using the latest version of LLBLGen (9/10/09) v2.6 with the IBM DB2 driver v9.0.0.2

Use: ActionQuery query = new ActionQuery((DB2Connection)transaction.Connection), new DB2Command("SET TRANSACTION READ ONLY");

the connection in the command isn't used, you've to specify the connection separately.

Hi,

I had to make a couple of bracket corrections to get this to compile:

ActionQuery query = new ActionQuery((DB2Connection)transaction.Connection, new DB2Command("SET TRANSACTION READ ONLY"));

but I still received an exception...

"An exception was caught during the execution of an action query: Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."

With Inner Exception...

"Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized."} [System.InvalidOperationException]: {"Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized."} Data: {System.Collections.ListDictionaryInternal} HelpLink: null InnerException: null Message: "Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized." Source: "IBM.Data.DB2" StackTrace: " at IBM.Data.DB2.DB2Connection.a(String A_0, DB2Transaction A_1)\r\n at IBM.Data.DB2.DB2Command.a(String A_0)\r\n at IBM.Data.DB2.DB2Command.g()\r\n at IBM.Data.DB2.DB2Command.ExecuteNonQuery()\r\n at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute()" TargetSite: {IBM.Data.DB2.DB2Transaction a(System.String, IBM.Data.DB2.DB2Transaction)}

So from this I deduced that I needed to assign the transaction to the command as follows:

DB2Command cmd = new DB2Command("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED READ ONLY");
cmd.Transaction = (DB2Transaction)transaction;
ActionQuery query = new ActionQuery((DB2Connection)transaction.Connection,cmd); 
query.Execute();

This now does seem to get as far as the DB2 server but I now have a different (more DB2 specific) exception perhaps indicating that the SET command is incorrect or being executed in the wrong place ? ....

Exception:

"An exception was caught during the execution of an action query: ERROR [42601] [IBM][DB2/LINUX] SQL0104N An unexpected token \"TRANSACTION\" was found following \"SET \". Expected tokens may include: \"JOIN <joined_table>\". SQLSTATE=42601\r\n. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."

InnerException:

"ERROR [42601] [IBM][DB2/LINUX] SQL0104N An unexpected token \"TRANSACTION\" was found following \"SET \". Expected tokens may include: \"JOIN <joined_table>\". SQLSTATE=42601\r\n"} [IBM.Data.DB2.DB2Exception]: {"ERROR [42601] [IBM][DB2/LINUX] SQL0104N An unexpected token \"TRANSACTION\" was found following \"SET \". Expected tokens may include: \"JOIN <joined_table>\". SQLSTATE=42601\r\n"} Data: {System.Collections.ListDictionaryInternal} HelpLink: null InnerException: null Message: "ERROR [42601] [IBM][DB2/LINUX] SQL0104N An unexpected token \"TRANSACTION\" was found following \"SET \". Expected tokens may include: \"JOIN <joined_table>\". SQLSTATE=42601\r\n" Source: "IBM.Data.DB2" StackTrace: " at IBM.Data.DB2.DB2Connection.a(IntPtr A_0, x A_1, s A_2)\r\n at IBM.Data.DB2.DB2Command.g()\r\n at IBM.Data.DB2.DB2Command.ExecuteNonQuery()\r\n at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute()" TargetSite: {Void a(IntPtr, x, s)}

I also tried changing the DB2 command to:

"SET TRANSACTION ISOLATION LEVEL READ ONLY"

and also

"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED READ ONLY"

but still got the same exception message!

pmax100

This command does seem to work though...

"SET CURRENT ISOLATION READ UNCOMMITTED"

but as the isolation level is set via the DataAdapter class (as below) I don't think it acheives anything!

         adapter.StartTransaction(IsolationLevel.ReadUncommitted, "TransForRead");

What I'm trying to acheive is to ensure that all select/read only queries somehow replicate the "for read only with ur" DB2 command

This is to ensure that the impact on the database in terms of record locking is minimal. (Our dba has insisted that I somehow use or replicate this syntax but i'd quite like to use LLBLGEN for this!)

As I wish to use the data for reporting purposes over longer time periods (ie. days) and real time activity is of no real importance!

Sorry for the crappy code I posted, I should have checked the brackets and also should have suggested to call 'WireTransaction' as well.

that said, I think it goes wrong because when you create a physical ado.net transaction object, the ado.net provider will send a BEGIN TRANSACTIOn ... etc. statement with the isolation level, so doing that again is likely not going to work.

I'm not sure if this is even supported through ado.net, the only other option I can think of is to try the isolation level 'snapshot' but that has to be supported by the db2 ado.net provider, and I'm not sure if that provider supports readonly transactions.

Hi Otis,

No problem, i just really appreciate your guidance and advice - the odd syntax issue keeps me on my toes!

I have done some digging and there is mention of a connection property in ODBC and IBM CLI drivers that might do the trick...

SQL_ATTR_ACCESS_MODE with a value of SQL_MODE_READ_ONLY

I've no idea how to go about setting this via LLBLGEN though.

Another option would be to somehow append a suffix on the select SQL that LLBL generates, i.e. "for read only with ur" .

• do you know if there are any options available within the LLBL framework for doing this?

I'm now looking at using value projections (or data readers) to return data sets in the form of generic lists.

thanks again,

pmax100

Another option would be to somehow append a suffix on the select SQL that LLBL generates, i.e. "for read only with ur" .

• do you know if there are any options available within the LLBL framework for doing this?

Try this: Drive from the DataAccessAdapter and override the CreateSelectDQ(), in there you should call the base method, then append your code to the query before returning it.

Thanks Walaa,

I think your suggestion has done the trick...

protected override IRetrievalQuery CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, long maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, bool allowDuplicates, IGroupByCollection groupByClause, int pageNumber, int pageSize) { IRetrievalQuery query= base.CreateSelectDQ(fieldsToFetch, persistenceInfoObjects, filter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates, groupByClause, pageNumber, pageSize); IBM.Data.DB2.DB2Command db2Command= (DB2Command)query.Command; db2Command.CommandText += " FOR READ ONLY WITH UR"; return query; }