This discussion is archived
9 Replies Latest reply: Sep 12, 2011 7:03 AM by 871604 RSS

SELECT, UPDATE, SELECT problem

871604 Newbie
Currently Being Moderated
Hi everyone,

I'm very new to working with Oracle with .net and am a bit stumped by something, so hopefully someone can help me out...
The situation is that I have 3 commands. The first selects a row from a table ('SELECT field1, field2 FROM table1 WHERE field1 = 1'), so field2 = 'foo'. The second command updates that same row in that table ('UPDATE table1 SET field2 = 'bar' WHERE field1 = 1') , then the third performs a reselection ( ('SELECT field1, field2 FROM table1 WHERE field1 = 1'). The problem I have is that the third command brings me back the same results as first, unchanged - even though I can view that row in the database and I KNOW it's changed, and the second command has worked. Field2 still = 'foo'....If I requery and requery a couple of times the data changes eventually.

So here's some code....

DataTable Table = new DataTable();
OracleConnection conn = new OracleConnection("User Id=Database;Password=Password;Data Source=");
conn.Open();
OracleDataAdapter adapter = new OracleDataAdapter("SELECT FIELD1, FIELD2 FROM TABLE1 WHERE FIELD1 = 1, conn);
DataSet set = new DataSet();
adapter.Fill(set);
Table = set.Tables[0];

This is fine, I get what I expect, ie: Field2 = 'FOO'..
So now I do this...

OracleParameter Field2Param = new OracleParameter("Field2", OracleDbType.Char, 64, "BAR", ParameterDirection.Input);

OracleConnection conn = new OracleConnection("User Id=Database;Password=Password;Data Source=");
conn.Open();

OracleCommand cmd = new OracleCommand(insertstr, conn);

OracleTransaction txn = conn.BeginTransaction(IsolationLevel.ReadCommitted);

cmd.Parameters.Add(Field2Param);

cmd.ExecuteNonQuery();

.....this also works, according to the database view.

So then I requery in the same fashion as before.....

DataTable Table = new DataTable();
OracleConnection conn = new OracleConnection("User Id=Database;Password=Password;Data Source=");
conn.Open();
OracleDataAdapter adapter = new OracleDataAdapter("SELECT FIELD1, FIELD2 FROM TABLE1 WHERE FIELD1 = 1, conn);
DataSet set = new DataSet();
adapter.Fill(set);
Table = set.Tables[0];

...but I'm still getting Field2 as 'FOO'.......

please help....
  • 1. Re: SELECT, UPDATE, SELECT problem
    gdarling - oracle Expert
    Currently Being Moderated
    Hi,

    Complete code would help here, as I can't tell where you're ending the explicit transaction and committing the data. Can you reproduce the complaint if you do it all back to back sequentially in a standalone console app with all the code inside MAIN for example?

    Here's my guess though: you're calling con.Open multiple times, so the second and/or 3rd con.Open call is getting a different connection from the pool, rather than the one you were just using, and hasn't committed the transaction yet for it to be visible from other connections.

    Also, if your db is RAC, there's also commit propagation delay that can prevent the data from being visible in other nodes if your code is running in a tight loop and you're connected to a different node.

    Hope it helps,
    Greg

    Edited by: gdarling on Jun 27, 2011 11:25 AM
  • 2. Re: SELECT, UPDATE, SELECT problem
    871604 Newbie
    Currently Being Moderated
    Hey Greg, thanks for the reply and the help, you were absolutely spot on.

    I was figuring I could create and destroy the DB connections as I went along and everything would keep up. Not the case at all.

    Thanks again :)
  • 3. Re: SELECT, UPDATE, SELECT problem
    gdarling - oracle Expert
    Currently Being Moderated
    Just as a parting shot since I can't see your cleanup code, you need to make sure you DISPOSE of the connections, commands etc for best use of resources, rather than just calling CLOSE. Close relies on the garbage collector to clean things up, whereas Dispose lets the connections be returned to the pool immediately.

    Glad to hear your problem is resolved.
    Greg
  • 4. Re: SELECT, UPDATE, SELECT problem
    871604 Newbie
    Currently Being Moderated
    Hmmm. Something still isn't right.
    I'm using VC# 2008 professional, .net 2.0 and Oracle XE.

    I have a webservice project ('DBSvc') with several webmethods used for getting data from Oracle, and when DBSvc is initialised a OracleConnection is created that can be seen by the entire service.
    The 'DBSvc' project has a class called 'UserRecord.cs' which encapsulates the functions needed to retrieve data from the 'User' table in the database.

    In the main project is a class ('GlobalVars') that holds an instance of Service1 called 'm_client', so it's accessible across the entire application project.

    I have a main project with a 'PersonalDetails.aspx' web form which populates some edit controls from a table in the database, and it does this by passing reference to a structure to a web method 'GetUserRecordFromID' in 'DBSvc', which uses the 'UserRecord.cs' class function 'GetRecord' which creates an adapter with the 'DBSvc' connection to fill a dataset and populate a datatable which populates the structure we passed it, which gets passed back to the main application.

    This all works dreamily. The structure has the right data and all is cool.

    The problem arises when I change the data in that structure and pass it back to an 'UpdateUserRecord' WebMethod in my DBSvc, which passes it to a 'UserRecord.cs' UpdateRecord which parameterises the data in the structure, begins a transaction, creates an OracleCommand, adds the parameters and calls ExecuteNonQuery, and then commits it.

    This commit works and the database table row changes instantly.

    But.

    I then go through the process of getting that data back in the manner described above, but the data does not yet seem updated !

    Bear in mind, there's now only one connection (in DBSvc). :(
  • 5. Re: SELECT, UPDATE, SELECT problem
    gdarling - oracle Expert
    Currently Being Moderated
    I'm not too sure what to tell you there, other than "isolate further" :(

    Try this, for example. Both cases work fine for me, what results do you get? Maybe you can break it to reproduce the complaint?

    Greg
    //create table table1(field1 number, field2 varchar2(4000));
    //insert into table1 values(1,'one');
    //commit;
    
    using System;
    using System.Data;
    using Oracle.DataAccess.Client;
    
    class Program
    {
        static string constr = "User Id=scott;Password=tiger;Data Source=orcl;";
        static void Main(string[] args)
        {
            onecon();
            Console.WriteLine("--------------------");
            multiplecons();
        }
    
        private static void onecon()
        {
            DataTable Table = new DataTable();
            OracleConnection conn = new OracleConnection(constr);
            conn.Open();
            OracleDataAdapter adapter = new OracleDataAdapter("SELECT FIELD1, FIELD2 FROM TABLE1 WHERE FIELD1 = 1", conn);
            DataSet set = new DataSet();
            adapter.Fill(set);
            Console.WriteLine(set.Tables[0].Rows[0][1].ToString());
    
            string insertstr = "update table1 set field2 = :1 where field1=1";
            OracleParameter Field2Param = new OracleParameter("Field2", OracleDbType.Char, 64, "BAR", ParameterDirection.Input);
            OracleCommand cmd = new OracleCommand(insertstr, conn);
            OracleTransaction txn = conn.BeginTransaction(IsolationLevel.ReadCommitted);
            cmd.Parameters.Add(Field2Param);
            cmd.ExecuteNonQuery();
            txn.Commit();
    
            OracleDataAdapter adapter2 = new OracleDataAdapter("SELECT FIELD1, FIELD2 FROM TABLE1 WHERE FIELD1 = 1", conn);
            DataSet set2 = new DataSet();
            adapter.Fill(set2);
            Console.WriteLine(set2.Tables[0].Rows[0][1].ToString());
        }
    
        private static void multiplecons()
        {
            DataTable Table = new DataTable();
            OracleConnection conn = new OracleConnection(constr);
            conn.Open();
            OracleDataAdapter adapter = new OracleDataAdapter("SELECT FIELD1, FIELD2 FROM TABLE1 WHERE FIELD1 = 1", conn);
            DataSet set = new DataSet();
            adapter.Fill(set);
            Console.WriteLine(set.Tables[0].Rows[0][1].ToString());
            adapter.Dispose();
            conn.Dispose();
    
            string insertstr = "update table1 set field2 = :1 where field1=1";
            OracleParameter Field2Param = new OracleParameter("Field2", OracleDbType.Char, 64, "differentBAR", ParameterDirection.Input);
            conn = new OracleConnection(constr);
            conn.Open();
            OracleCommand cmd = new OracleCommand(insertstr, conn);
            OracleTransaction txn = conn.BeginTransaction(IsolationLevel.ReadCommitted);
            cmd.Parameters.Add(Field2Param);
            cmd.ExecuteNonQuery();
            txn.Commit();
            cmd.Dispose();
            conn.Dispose();
    
            conn = new OracleConnection(constr);
            conn.Open();
            OracleDataAdapter adapter2 = new OracleDataAdapter("SELECT FIELD1, FIELD2 FROM TABLE1 WHERE FIELD1 = 1", conn);
            DataSet set2 = new DataSet();
            adapter2.Fill(set2);
            Console.WriteLine(set2.Tables[0].Rows[0][1].ToString());
            adapter2.Dispose();
            conn.Dispose();
    
        }
    }
    MY OUTPUT
    ==========
    one
    BAR
    --------------------
    BAR
    differentBAR
    Press any key to continue . . .
  • 6. Re: SELECT, UPDATE, SELECT problem
    871604 Newbie
    Currently Being Moderated
    I'm doing exactly what you're doing only over 2 forms.

    So, I have a gridview in 'projectview.aspx', and a button that redirects the user to a value input page called 'createdesign.aspx', which has a button on it to save the design values to the database, and redirect back to 'projectview.aspx'.

    'projectview.aspx' performs a select to get the records and binds the table to the gridview, which all works nicely until I get redirected from 'createdesign.aspx' after the new record has been inserted. The select statement doesn't pick up the new record, until I refresh a few times, and it magically appears....

    ...hmmmmm, is there a cache in 'projectview.aspx' I don't know about ?
  • 7. Re: SELECT, UPDATE, SELECT problem
    871604 Newbie
    Currently Being Moderated
    Sorry guys, I just marked this question as unanswered, because it's not....
    I messed up, I thought the problem was fixed, but it persists......grrrrr.
    I'm wondering why the database is updated instantly, and I can see the record right there, but it takes several requery attempts before the new data is pulled :(

    Edited by: 868601 on 07-Jul-2011 07:44
  • 8. Re: SELECT, UPDATE, SELECT problem
    gdarling - oracle Expert
    Currently Being Moderated
    The only other suggestion I have other than perhaps opening a SR with support and supplying a testcase, is
    a) enabling ODP tracing so you can make sure the events are occurring in the order you think they are. In particular, committing the transaction.
    b) enabling sqlnet tracing so you can look at the query and results at the network packet level to see if that offers any clues.

    Greg
  • 9. Re: SELECT, UPDATE, SELECT problem
    871604 Newbie
    Currently Being Moderated
    OK, thanks for the useful advice I received. I thought I'd better update you on what the problem actually was.....

    The service Webmethods I'm using were taken from example code and copied/pasted without me actually knowing what was going on (they worked so I left them....come on ! We've ALL done it.....lol).

    Anyway, the CacheDuration was set to 30......and not 0. So of course, the results I was getting from repeated calls to the same function were returning the cached results until the CacheDuration timeout - DUH !

    Needless to say I now get real-time data.

    Thanks again.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points