DB Commit\Rollback Issues 
Author Message
 DB Commit\Rollback Issues

I am currently writing an application that is heavily database driven
(oracle 817).  One of the things I need to be able to accomplish is
the ability to write to a corresponding LOG table (a table that tracks
each insert\update of a given status table).  Currently, I'm using an
oledb connection, and I write to a table, then immediately after,
write to a corresponding LOG table.  However, unless I explicity
COMMIT after the first query, my second query will not 'find' the
record just inserted.  This behaviour is different than what I am used
to in other languages\db platforms.  Given the following example:

Dim objCommand as DatabaseCommand
Dim objParams As New ParameterCollection

strSQL = "INSERT INTO USERS (USERID, FIRSTNAME, LASTNAME, ADDRESS,
PHONE) VALUES (?,?,?,?,?)
objParams.Add("strUserID",strUserID)
objParams.Add("strFName",strFName)
objParams.Add("strLName",strLName)
objParams.Add("strAddress",strAddress)
objParams.Add("Phone",strPhone)

Try
    intResult = objCommand.ExecuteNonQuery(strSQL, objParams)
Catch
' rollback this query
EndTry

'Now based on insert, write an entry into the log

strSQL = 'INSERT INTO USERS_LOG (USERID, TIMESTAMP,
FIRSTNAME,LASTNAME,ADDRESS,PHONE, CHANGE_MADE_BY)  SELECT
USERID, SYSDATE, FIRSTNAME, LASTNAME, ADDRESS,PHONE,? FROM USERS where
USERID=?)

objParams.Clear()
objParams.Add("changedby",strCurUser)
objParams.Add("strUserID",strUserID)

Try
    intResult = objCommand.ExecuteNonQuery(strSQL, objParams)
Catch
  'rollback all executed queries in this block
EndTry

objCommand.Commit
objCommand.Close

The second query returns 0 from the ExecuteNonQuery because it could
not find the record.  If I add a commit between the first and second
query, it runs fine.
I need to be able to rollback both queries if an error occurs on the
second query.

If I run the same query outside of my developing environment (ie with
SQL Plus or TOAD), it will insert the second record fine, even though
I haven't explicitly forced a commit after the first query.

Is there a problem with OLE that would cause this?  Is there anything
else I can try?  Does anyone see anything wrong with what I'm trying
to do?

Any help would be greatly appreciated.

Thanks in advance.



Mon, 29 Aug 2005 21:53:05 GMT  
 DB Commit\Rollback Issues

Quote:
> I am currently writing an application that is heavily database driven
> (oracle 817).  One of the things I need to be able to accomplish is
> the ability to write to a corresponding LOG table (a table that tracks
> each insert\update of a given status table).  Currently, I'm using an
> oledb connection, and I write to a table, then immediately after,
> write to a corresponding LOG table.  However, unless I explicity
> COMMIT after the first query, my second query will not 'find' the
> record just inserted.  This behaviour is different than what I am used
> to in other languages\db platforms.  Given the following example:

> Dim objCommand as DatabaseCommand
> Dim objParams As New ParameterCollection

. . .

What is a "DatabaseCommand"?

If you cannot see the results from your first query then your second query
is running in a different connection.

Since you aren't using the ADO.NET objects, I don't know how to fix it.

David



Mon, 29 Aug 2005 23:07:44 GMT  
 DB Commit\Rollback Issues
Dave -

Sorry I left out some of the details that probably caused some
confusion.  Anyway, DatabaseCommand is a class that was created to
perform the following tasks: open, close, commit, rollback,
ExecuteNonQuery, ExecuteQuery, and AddParameters.  This is taken from
the 'SYSTEM.DATA.OLEDB' object which include the ADO.net objects you
stated were missing.  
I'll include it for reference.  Can you provide any additional help with
the extra information I've given?

Public Class DatabaseCommand

    Private strDBConnString As String
    Private objDBConnection As OleDbConnection
    Private objDBTransaction As OleDbTransaction
    Private objCommand As OleDbCommand

    Public Sub New()
        'initialize db connection and associated objects
        strDBConnString =
System.Configuration.ConfigurationSettings.AppSettings.Get("DBConnection
String")
        objDBConnection = New OleDbConnection(strDBConnString)
        objCommand = New OleDbCommand()
        objCommand.Connection = objDBConnection
    End Sub

    Public Sub Open()
        objDBConnection.Open()
    End Sub

    Public Sub Close()
        objDBConnection.Close()
    End Sub

    Public Sub BeginTransaction()
        objDBTransaction =
objDBConnection.BeginTransaction(IsolationLevel.Serializable)
        objCommand.Transaction = objDBTransaction
    End Sub

    Public Sub Commit()
        objDBTransaction.Commit()
    End Sub

    Public Sub Rollback()
        objDBTransaction.Rollback()

    End Sub

    Public Function ExecuteQuery(ByVal strSQL As String, Optional ByVal
objParameters As ParameterCollection = Nothing) As DataTable
        Dim objReader As OleDbDataReader
        Dim dtblReturnValue As New DataTable()
        Dim objAdapter As OleDbDataAdapter

        objCommand.CommandText = strSQL

        'add parameters
        objCommand.Parameters.Clear()
        AddParameters(objParameters)

        objAdapter = New OleDbDataAdapter(objCommand)

        objAdapter.Fill(dtblReturnValue)

        objReader = Nothing
        objAdapter = Nothing

        Return dtblReturnValue
    End Function

    Public Function ExecuteNonQuery(ByVal strSQL As String, Optional
ByVal objParameters As ParameterCollection = Nothing) As Integer
        Dim intRowsAffected As Integer
        Dim intIndex As Integer
        'Dim cmd As New OleDb.OleDbCommand()
        'set SQL
        objCommand.CommandText = strSQL

        'add parameters
        objCommand.Parameters.Clear()
        AddParameters(objParameters)

        intRowsAffected = objCommand.ExecuteNonQuery()

        Return intRowsAffected
    End Function

    Private Sub AddParameters(ByVal objParameters As
ParameterCollection)
        Dim intIndex As Integer

        If Not objParameters Is Nothing Then
            'add parameters
            Dim objParameter As OleDbParameter

            For intIndex = 0 To (objParameters.Count - 1)
                objParameter = New
OleDbParameter(objParameters.Item(intIndex).ParameterName,
objParameters.Item(intIndex).Value)
                objCommand.Parameters.Add(objParameter)
            Next
        End If

    End Sub

End Class

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Mon, 29 Aug 2005 23:44:45 GMT  
 DB Commit\Rollback Issues
I'm not sure how the OleDb.NET handles this, and I still don't know which
oledb provider you are using but here's what I would try.

I don't think you should reuse the same command object for multiple queries.

The normal lifecycle of a command object:
create
set sql
set commandtype
add parameters
prepare (if using more than 3 times)
any number of {
    set transaction (depends on the provider, some require explicit
enlistment, some do it for you)
    set parameter values
    execute
    read and close any datadatareaders
    read parameter values

Quote:
}

destroy

My suspicion is that your second command is not getting properly enlisted in
your transaction, and is running in autonomous transactions.  I really may
depend on which of the 3 oledb providers (msora,oraora,odbc) you are using.

In general I would recommend using the ODP.NET native provider from oracle.
If you need to hit SQL as well, use the SQLServer native provider and  rely
on IDBConnection etc provider interfaces and your own coding to provide
interchangability for database backends, not on OleDB.

David



Tue, 30 Aug 2005 01:47:18 GMT  
 DB Commit\Rollback Issues
It does not look like you are running inside of a transaction.  Use
your .Begin Transaction() method of your DB command object before
running queries, etc., then you shouldn't have to commit until the end
and each of the commands will follow one after the other, finding
inserted data.

--
Craig Deelsnyder
Microsoft MVP - ASP/ASP.NET

Quote:

> I am currently writing an application that is heavily database driven
> (oracle 817).  One of the things I need to be able to accomplish is
> the ability to write to a corresponding LOG table (a table that tracks
> each insert\update of a given status table).  Currently, I'm using an
> oledb connection, and I write to a table, then immediately after,
> write to a corresponding LOG table.  However, unless I explicity
> COMMIT after the first query, my second query will not 'find' the
> record just inserted.  This behaviour is different than what I am used
> to in other languages\db platforms.  Given the following example:

> Dim objCommand as DatabaseCommand
> Dim objParams As New ParameterCollection

> strSQL = "INSERT INTO USERS (USERID, FIRSTNAME, LASTNAME, ADDRESS,
> PHONE) VALUES (?,?,?,?,?)
> objParams.Add("strUserID",strUserID)
> objParams.Add("strFName",strFName)
> objParams.Add("strLName",strLName)
> objParams.Add("strAddress",strAddress)
> objParams.Add("Phone",strPhone)

> Try
>     intResult = objCommand.ExecuteNonQuery(strSQL, objParams)
> Catch
> ' rollback this query
> EndTry

> 'Now based on insert, write an entry into the log

> strSQL = 'INSERT INTO USERS_LOG (USERID, TIMESTAMP,
> FIRSTNAME,LASTNAME,ADDRESS,PHONE, CHANGE_MADE_BY)  SELECT
> USERID, SYSDATE, FIRSTNAME, LASTNAME, ADDRESS,PHONE,? FROM USERS where
> USERID=?)

> objParams.Clear()
> objParams.Add("changedby",strCurUser)
> objParams.Add("strUserID",strUserID)

> Try
>     intResult = objCommand.ExecuteNonQuery(strSQL, objParams)
> Catch
>   'rollback all executed queries in this block
> EndTry

> objCommand.Commit
> objCommand.Close

> The second query returns 0 from the ExecuteNonQuery because it could
> not find the record.  If I add a commit between the first and second
> query, it runs fine.
> I need to be able to rollback both queries if an error occurs on the
> second query.

> If I run the same query outside of my developing environment (ie with
> SQL Plus or TOAD), it will insert the second record fine, even though
> I haven't explicitly forced a commit after the first query.

> Is there a problem with OLE that would cause this?  Is there anything
> else I can try?  Does anyone see anything wrong with what I'm trying
> to do?

> Any help would be greatly appreciated.

> Thanks in advance.



Fri, 09 Sep 2005 10:53:02 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. workspace commit/rollback

2. Commit/Rollback spanning procedures

3. Begin-Rollback-Commit Transaction

4. How does .commit and .rollback work?

5. VB4 - Commit or Rollback without BeginTrans. (Error 3034)

6. Commit and Rollback

7. Using commit/rollback with Oracle and RDO

8. BeginTrans Commit RollBack

9. BeginTrans, Commit, RollBack

10. Rollback and Commit with more than 1 form open in VB3

11. Workspace Transaction Begin/Commit/Rollback bug?

12. Commit or Rollback without BeginTrans Error

 

 
Powered by phpBB® Forum Software