Oracle read only transaction in VB .NET?

[ashley.ward]

I am attempting to write a program with VB 2005 Express Edition which
accesses an Oracle 9 database and dumps the results of three SELECT
queries into a spreadsheet file once every hour.

Modifications will be being made to the database whilst this program is
running. I would like the three queries to each give data relating to
the same point in time, despite the fact they actually run in sequence.

In Oracle's sqlplus tool, I can use "SET TRANSACTION READ ONLY" to give
transaction-level read consistency. Oracle describe it this way: "All
subsequent queries in that transaction only see changes committed
before the transaction began. Read-only transactions are useful for
reports that run multiple queries against one or more tables while
other users update these same tables".

This seems to be exactly what I need. I'm thinking that the structure
I want is something like the below (I think the detail of the SELECT
queries doesn't matter):

SET TRANSACTION READ ONLY;
SELECT * FROM A;
SELECT * FROM B;
SELECT * FROM C;
COMMIT;

I've tested this with sqlplus, and if I modify the tables whilst the
transaction is in progress, the results show the state as it was when
the transaction began :) (Conversely, if I don't use a transaction,
intermediate modifications between the queries do have an effect.)

Unfortunately I can't get anything similar to work in Visual Basic
..NET. I have tried adding code something like the following before
some code that does two test queries with a pause in between, but the
SET TRANSACTION READ ONLY seems to have no effect. (Apologies for not
providing full code here.)

dbConnection.Open()
dbCommand = dbConnection.CreateCommand()
dbCommand.CommandText = "SET TRANSACTION READ ONLY"
dbCommand.ExecuteNonQuery()

I assume the SET TRANSACTION READ ONLY is being optimised away, or
perhaps changes made to the connection in this way aren't persistent.

I have also tried something along the lines of:

dbConnection.Open()
dbTransaction = dbConnection.BeginTransaction(IsolationLevel.Snaps hot)
dbCommand.Transaction = dbTransaction

because the description of IsolationLevel.Snapshot seems to be closest
to what I want. But with this, I get "Invalid IsolationLevel
parameter: must be ReadCommitted or Serializable." I assume
IsolationLevel.Snapshot just isn't implemented in
System.Data.OracleClient :(

Am I approaching this in the wrong way? Perhaps I need to work at a
lower level, eg ODBC?

I am a beginner at VB.NET. However I have a lot of experience in C,
Java, Apache+PHP+mysql and some experience in VB6.

Any help much appreciated.

Ashley.

[tommaso.gastaldi]

Hi ashley,

have you made sure you send the 5 commands in sequence - without
closing
the connection - between a command and the following one?

-tom

as*********@gmail.com ha scritto:

I am attempting to write a program with VB 2005 Express Edition which
accesses an Oracle 9 database and dumps the results of three SELECT
queries into a spreadsheet file once every hour.

Modifications will be being made to the database whilst this program is
running. I would like the three queries to each give data relating to
the same point in time, despite the fact they actually run in sequence.

In Oracle's sqlplus tool, I can use "SET TRANSACTION READ ONLY" to give
transaction-level read consistency. Oracle describe it this way: "All
subsequent queries in that transaction only see changes committed
before the transaction began. Read-only transactions are useful for
reports that run multiple queries against one or more tables while
other users update these same tables".

This seems to be exactly what I need. I'm thinking that the structure
I want is something like the below (I think the detail of the SELECT
queries doesn't matter):

SET TRANSACTION READ ONLY;
SELECT * FROM A;
SELECT * FROM B;
SELECT * FROM C;
COMMIT;

I've tested this with sqlplus, and if I modify the tables whilst the
transaction is in progress, the results show the state as it was when
the transaction began :) (Conversely, if I don't use a transaction,
intermediate modifications between the queries do have an effect.)

Unfortunately I can't get anything similar to work in Visual Basic
.NET. I have tried adding code something like the following before
some code that does two test queries with a pause in between, but the
SET TRANSACTION READ ONLY seems to have no effect. (Apologies for not
providing full code here.)

dbConnection.Open()
dbCommand = dbConnection.CreateCommand()
dbCommand.CommandText = "SET TRANSACTION READ ONLY"
dbCommand.ExecuteNonQuery()

I assume the SET TRANSACTION READ ONLY is being optimised away, or
perhaps changes made to the connection in this way aren't persistent.

I have also tried something along the lines of:

dbConnection.Open()
dbTransaction = dbConnection.BeginTransaction(IsolationLevel.Snaps hot)
dbCommand.Transaction = dbTransaction

because the description of IsolationLevel.Snapshot seems to be closest
to what I want. But with this, I get "Invalid IsolationLevel
parameter: must be ReadCommitted or Serializable." I assume
IsolationLevel.Snapshot just isn't implemented in
System.Data.OracleClient :(

Am I approaching this in the wrong way? Perhaps I need to work at a
lower level, eg ODBC?

I am a beginner at VB.NET. However I have a lot of experience in C,
Java, Apache+PHP+mysql and some experience in VB6.

Any help much appreciated.

Ashley.

[ashley.ward]

to**************@uniroma1.it wrote:

have you made sure you send the 5 commands in sequence - without closing
the connection - between a command and the following one?

Hi tom...

Thanks for the reply. I don't think I've closed the connection. Here
is the complete code (this is a Console application, by the way):

Imports System.Data.OracleClient ' for this to work, need to have
System.Data.OracleClient listed as a reference in the project
properties...

Module Module1

Sub Main()

Dim dbConnectionString As String = "Password=ASHLEY;User
ID=ASHLEY;Data Source=HAGRID"
Dim queryString As String = "SELECT * FROM TEST"

Dim dbConnection As OracleConnection
Dim dbCommand As OracleCommand
Dim dbDataReader As OracleDataReader

Try
dbConnection = New OracleConnection(dbConnectionString)
dbConnection.Open()

dbCommand = dbConnection.CreateCommand()
dbCommand.CommandText = "SET TRANSACTION READ ONLY NAME
'PDI_HSD'"
dbCommand.ExecuteNonQuery()

dbCommand.CommandText = queryString

Console.WriteLine("First query...")
dbDataReader = dbCommand.ExecuteReader()
Do While dbDataReader.Read()
Console.WriteLine(dbDataReader(0))
Loop
dbDataReader.Close()

Console.WriteLine("Change the database table contents using
an external tool, then press return.")
Console.ReadKey()

Console.WriteLine("Second query...")
dbDataReader = dbCommand.ExecuteReader()
Do While dbDataReader.Read()
Console.WriteLine(dbDataReader(0))
Loop
dbDataReader.Close()

Console.WriteLine("Expecting the two queries to give the
same results, despite the external modification.")
Console.WriteLine("Press return to finish.")
Console.ReadKey()

dbConnection.Close()

Catch ex As Exception
Console.WriteLine(ex.Message)
End Try

End Sub

End Module

Hopefully the prompts make it clear how I have been testing this. When
I test it here, the change I make with an external tool when prompted
is shown in the second query: so it seems to me that the SET
TRANSACTION READ ONLY has had no effect.

Any more help appreciated.

Ashley.

[tommaso.gastaldi]

Hi, ashley

as first suggestion, irrelevant to your problem, I would move some
clean up in a finally block to avoid leaving it open in case of
exceptions

Catch ex As Exception
Console.WriteLine(ex.Message)
Finally

If Not dbDataReader Is Nothing Then dbDataReader.Close()
dbConnection.Close()

End Try

back to the problem I do not have here Oracle. I will make some
hypotheses to be tested.

Is it possible that the external tool does a COMMIT which resets the
SET TRANSACTION READ ... ?

You could test if that is the case doing some change to the table
programmatically.

Let me know if this takes somewhere....

-tom

PS
I also have a curiosity: have you measured any performance improvement
by using an
OracleConnection instead of an OleDbConnection ?
as*********@gmail.com ha scritto:

to**************@uniroma1.it wrote:

have you made sure you send the 5 commands in sequence - without closing
the connection - between a command and the following one?

Hi tom...

Thanks for the reply. I don't think I've closed the connection. Here
is the complete code (this is a Console application, by the way):

Imports System.Data.OracleClient ' for this to work, need to have
System.Data.OracleClient listed as a reference in the project
properties...

Module Module1

Sub Main()

Dim dbConnectionString As String = "Password=ASHLEY;User
ID=ASHLEY;Data Source=HAGRID"
Dim queryString As String = "SELECT * FROM TEST"

Dim dbConnection As OracleConnection
Dim dbCommand As OracleCommand
Dim dbDataReader As OracleDataReader

Try
dbConnection = New OracleConnection(dbConnectionString)
dbConnection.Open()

dbCommand = dbConnection.CreateCommand()
dbCommand.CommandText = "SET TRANSACTION READ ONLY NAME
'PDI_HSD'"
dbCommand.ExecuteNonQuery()

dbCommand.CommandText = queryString

Console.WriteLine("First query...")
dbDataReader = dbCommand.ExecuteReader()
Do While dbDataReader.Read()
Console.WriteLine(dbDataReader(0))
Loop
dbDataReader.Close()

Console.WriteLine("Change the database table contents using
an external tool, then press return.")
Console.ReadKey()

Console.WriteLine("Second query...")
dbDataReader = dbCommand.ExecuteReader()
Do While dbDataReader.Read()
Console.WriteLine(dbDataReader(0))
Loop
dbDataReader.Close()

Console.WriteLine("Expecting the two queries to give the
same results, despite the external modification.")
Console.WriteLine("Press return to finish.")
Console.ReadKey()

dbConnection.Close()

Catch ex As Exception
Console.WriteLine(ex.Message)
End Try

End Sub

End Module

Hopefully the prompts make it clear how I have been testing this. When
I test it here, the change I make with an external tool when prompted
is shown in the second query: so it seems to me that the SET
TRANSACTION READ ONLY has had no effect.

Any more help appreciated.

Ashley.

[ashley.ward]

Hi tom -- thanks for the reply.

to**************@uniroma1.it wrote:

as first suggestion, irrelevant to your problem, I would move some
clean up in a finally block to avoid leaving it open in case of
exceptions
Yes, a good idea -- thanks.
back to the problem I do not have here Oracle. I will make some
hypotheses to be tested.

Is it possible that the external tool does a COMMIT which resets the
SET TRANSACTION READ ... ?
Yes, the external tool does do a COMMIT. But that shouldn't reset the
SET TRANSACTION READ ONLY. When I test manually (substituting an
sqlplus session for the VB program), any changes I make in the external
tool don't show up in the sqlplus session after the SET TRANSACTION,
COMMIT or not.
I also have a curiosity: have you measured any performance improvement
by using an
OracleConnection instead of an OleDbConnection ?

I haven't tried OleDbConnection yet.

Ashley.

[tommaso.gastaldi]

Ok. So let's try to determine who is responsible for the Reset of SET
TRANSACTION READ ... (perhaps ExecuteReader does it) let's put the
execute reader and close in a position where they cannot reset
and see if anything changes:

dbConnection.Open()

dbDataReader1 = dbCommand1.ExecuteReader()
dbDataReader2 = dbCommand2.ExecuteReader()

"SET TRANSACTION READ ONLY NAME " here

Do While dbDataReader1.Read()
Console.WriteLine(dbDataReader(0))
Loop

'manual changes here

Do While dbDataReader2.Read()
Console.WriteLine(dbDataReader(0))
Loop
'test if equal

dbDataReader1.Close()
dbDataReader2.Close()

dbConnection.Close()

Once you have determined which is the resetting instruction you con
possibly adjust statements in a better way.

Let me know...

-tom

as*********@gmail.com ha scritto:

Hi tom -- thanks for the reply.

to**************@uniroma1.it wrote:

as first suggestion, irrelevant to your problem, I would move some
clean up in a finally block to avoid leaving it open in case of
exceptions

Yes, a good idea -- thanks.

back to the problem I do not have here Oracle. I will make some
hypotheses to be tested.

Is it possible that the external tool does a COMMIT which resets the
SET TRANSACTION READ ... ?

Yes, the external tool does do a COMMIT. But that shouldn't reset the
SET TRANSACTION READ ONLY. When I test manually (substituting an
sqlplus session for the VB program), any changes I make in the external
tool don't show up in the sqlplus session after the SET TRANSACTION,
COMMIT or not.

I also have a curiosity: have you measured any performance improvement
by using an
OracleConnection instead of an OleDbConnection ?

I haven't tried OleDbConnection yet.

Ashley.