Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

C# oracle query - Lock, then update, then select

Posted on 2010-08-23
16
Medium Priority
?
1,226 Views
Last Modified: 2012-05-10
I am trying to write a oracle query that will lock a table then update the value by one then select the value.

But how would i write this to activate?

Here is what i have so far:

StringBuilder ticketNumber = new StringBuilder();
                string oradb = "Data Source=whs01_d; User Id=emuuser; Password=";
                string sqlLock = "lock table helpq.qw_keys in exclusive mode ";
                string sqlUpdate = "update helpq.qw_keys set keyvalue=keyvalue+1 where tablename='calls' ";
                string sqlSelect = "select keyvalue "
                                    + "from helpq.qw_keys "
                                    + "where tablename='calls' ";

                OracleConnection conn = new OracleConnection();
                conn.ConnectionString = oradb;

                conn.Open();

                OracleCommand cmdLock = new OracleCommand(sqlLock, conn);
                OracleCommand cmdUpdate = new OracleCommand(sqlUpdate, conn);
                OracleCommand cmdSelect = new OracleCommand(sqlSelect, conn);

                OracleDataReader dr = cmdSelect.ExecuteReader();

                if (dr.HasRows == true)
                {

                    while (dr.Read())
                    {
                        ticketNumber.AppendFormat(dr["keyvalue"].ToString());
                    }

                    return (ticketNumber.ToString());
                }

Open in new window


How do i get the lock to happen then the update? they aren't running.

God Bless!

0
Comment
Question by:benandbecky
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
16 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33503942
I don't see where you are executing the other commands.

something like: cmdLock.ExecuteNonQuery();

also, Why lock the entire table to update a few rows?  Typically in Oracle there's no need to do this but if you still insist, why not a select for update.
0
 

Author Comment

by:benandbecky
ID: 33504085
Can you show me the code how to do this?

Thanks!
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33504095
code to do what?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:benandbecky
ID: 33504108
select for an update
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33504204
Just add 'FOR UPDATE' to the end of the select:

http://psoug.org/reference/select.html
0
 

Author Comment

by:benandbecky
ID: 33504432
hmm its still not working the execute no query didnt work either.

Any more ideas or examples?

Thanks!
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33504483
you added 'for update' to the end of sqlSelect.

Execute the reader to make sure it executes and locks the rows.

Then you did something like:

...
                if (dr.HasRows == true)
                {

                    while (dr.Read())
                    {
                        ticketNumber.AppendFormat(dr["keyvalue"].ToString());
                        cmdUpdate.ExecuteNonQuery();
                    }
...
0
 

Author Comment

by:benandbecky
ID: 33504858
Its still not working, i don't know why not.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33504884
Can you post the complete code as it looks now?
0
 

Author Comment

by:benandbecky
ID: 33504976

StringBuilder ticketNumber = new StringBuilder();
                string oradb = "Data Source=whs01_d; User Id=emuuser; Password=emuuser1";
                string sqlLock = "lock table helpq.qw_keys in exclusive mode " ;
                string sqlUpdate = "update helpq.qw_keys "
                                        + "set keyvalue=keyvalue+1 "
                                        + "where tablename='calls' " ;
                string sqlSelect = "select keyvalue "
                                    + "from helpq.qw_keys "
                                    + "where tablename='calls' "
                                    + "for update " ;

                

                OracleConnection conn = new OracleConnection();
                conn.ConnectionString = oradb;

                conn.Open();

                OracleCommand cmdSelect = new OracleCommand(sqlSelect, conn);
                OracleCommand cmdUpdate = new OracleCommand(sqlUpdate, conn);


                OracleDataReader dr = cmdSelect.ExecuteReader();

                if (dr.HasRows == true)
                {

                    while (dr.Read())
                    {
                        //Class.Connection.CreateCommand(sqlLock, oradb);
                        ticketNumber.AppendFormat(dr["keyvalue"].ToString());
                        cmdUpdate.ExecuteNonQuery();
                    }

                    return (ticketNumber.ToString());
                }
                else
                {
                    ticketNumber.AppendFormat("<FONT COLOR='#FF0000'>Error Creating Ticket Number</FONT></b>");

                    return (ticketNumber.ToString());
                }
            }

Open in new window

0
 

Author Comment

by:benandbecky
ID: 33504984
It just stays processing forever
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 33504991
I created a small test case.  It uses Oracle's ODP.Net not the MSoft provider.  It was tested and works using Oracle 10.2.0.3.

Every execution increments the value by 1.

Given the following table:
--------------------------------
drop table tab1 purge;
create table tab1( tablename varchar2(10), keyvalue number );

insert into  tab1 values('calls',0);
commit;

using System;
using System.Data;

using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

public class Bob
{

	public static void Main(string[] args)
	{

			OracleConnection con = new OracleConnection();
			OracleDataReader dr;

			con = new OracleConnection("User Id=bud;Password=bud;Data Source=bud");

			OracleCommand sqlSelect = new OracleCommand();
			OracleCommand sqlUpdate = new OracleCommand();

			sqlSelect.CommandText = " select keyvalue from tab1 where tablename='calls' for update ";
			sqlUpdate.CommandText = " update tab1 set keyvalue = keyvalue+1 where tablename='calls' ";


			sqlSelect.Connection = con;
			sqlUpdate.Connection = con;

			con.Open();

        	dr = sqlSelect.ExecuteReader();
        	if(dr.Read()) {
        		sqlUpdate.ExecuteNonQuery();
        	}

			con.Close();
			con.Dispose();

	}

}

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33505049
Are you just wanting to generate an incrementing number?  

If there can be gaps,  check out Oracle Sequences.  This is what they are for.

>>It just stays processing forever

I'm wondering if one of your previous attempts still has the row locked and the current run is waiting for it to be released...

I'm also not sure why you why you have the WHILE loop on the data reader.  Can the select statement ever return more than 1 row?
0
 

Author Comment

by:benandbecky
ID: 33505319
How would i parse that value out?

i tried this code:

dr = sqlSelect.ExecuteReader();
                if (dr.Read())
                {
                    sqlUpdate.ExecuteNonQuery();
                }

                ticketNumber2 = int.Parse(dr["keyvalue"].ToString());
                
			    con.Close();
			    con.Dispose();                    
                return (ticketNumber2);
                
            }

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33505741
I'm not at work anymore so I can't provide working code but for a datareader is something like:

ticketNumber2 = dr.GetDecimal(0);
0
 

Author Closing Comment

by:benandbecky
ID: 33510709
Fast and responsive!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question