Resolved DbCommand.ExecuteNonQuery Never Completes

StoneCodeMonkey

Well-known member
Joined
Apr 17, 2009
Messages
56
Programming Experience
5-10
First things first, here is the code.
VB.NET:
    Public Shared Function ExecuteNonQuery(ByVal ConnectionString As String, ByVal factory As DbProviderFactory, ByVal sql As String) As Integer

        'When this function executes
        'factory = {Oracle.DataAccess.Client.OracleClientFactory}
        'sql = "update downtimearea set area = 'yyy' where downtimearea = '01'"
        Dim rowsAffected As Integer = -1

        'This works.
        Using conn As DbConnection = factory.CreateConnection
            conn.ConnectionString = ConnectionString
            conn.Open()
            If conn.State.Equals(ConnectionState.Open) Then

                Try
                    'This works
                    Dim command As DbCommand = conn.CreateCommand
                    command.CommandText = sql
                    command.CommandType = CommandType.Text

                    '**********This blocks and never returns.
                    rowsAffected = command.ExecuteNonQuery()
                Catch ex As Exception
                    '**********No exception is ever thrown.
                    Debug.Print(ex.Message)
                End Try

            End If
        End Using
        Return rowsAffected
    End Function

I am executing this code against a simple test database. I have a similar function that has the same signature, but returns a DataTable and it works fine. For the life of me I can not figure out why ExecuteNonQuery will not work. I have checked the database and the value is never updated. If I paste the sql into SQLDeveloper it executes just fine and the table is updated as expected.

Any ideas?
 
Last edited:
Hi,

This comment is a bit of a stab in the dark since I have no experience with Oracle and you state that you have already tried this and it works but:-

Is your SQL statement correct "update downtimearea set area = 'yyy' where downtimearea = '01'"

From an SQL server point of view the structure of an sql update statement is:-

Update <TableName> Set <FieldName> = <Value> where <FieldName> = <AnotherValue>

Your SQL statement seems to suggest the structure:-

Update <TableName> Set <FieldName> = <Value> where <TableName> = <AnotherValue>

Notice the use of your table name in the selection element of the query?

Of course you could have a field name which is the same as the table name but just seems a bit strange to me.

Cheers,

Ian
 
Unfortunately there is a column name that is also the table name, so it does add some confusion. Poor schema design.

The SQL statement however is correct. I can execeute it from SQLDeveloper and the table updates as it should. Thanks for the stab.
 
Well I feel silly. The row in the table I was testing against had uncommited updates from my session in SQLDeveloper. It would seem that ODAC will wait as long as necessary for the row to be unlocked.
 
I take it you are sorted now then?

I was just about to post another silly idea for you to try.

If you are sorted then don't worry about it, we have all experienced silly issues that cause us to think there are bigger issues at work.

Cheers,

Ian
 
I take it you are sorted now then?

I was just about to post another silly idea for you to try.

If you are sorted then don't worry about it, we have all experienced silly issues that cause us to think there are bigger issues at work.

Cheers,

Ian

Sorted. Yes. I understand the cause.

However I should have been able to set command.CommandTimeout = n and handle the exception, but even the Timeout will not occur if the table or row is locked. Just need to figure how I want to handle this when it occurs. Its kind of weird, the thread hangs until the lock is remove, but .Net recognizes that the Timeout has been exceeded and throws the exception. I would have thought that "Timeout" would occur at the time interval set regardless of the reason. I suspect that the native call is synchronous and the thread just can't continue until it returns from the native code.

Thanks
 
Hey StoneCodeMonkey, Did you figure out how to handle the situation you described above? I'm running into the same problem and stuck.
 
Back
Top