I have a problem when I use stored procedures for save my data, When I have a stored procedure for save data of master table and another for save detail table detail, If I have some error in detail stored procedure this doesn't rollback to transactions of master.

    Dim adapter As IDataAccessAdapter = CreateAdapter(True)
    adapter.StartTransaction(IsolationLevel.ReadCommitted, "TwoUpdates")
    Try
        If Insert(adapter, useSignature) = ISOtrain.Response.Failure Then
            adapter.Rollback()
        End If

        If HasDivisionChanged() Then
            If InsertDivision(adapter, useSignature) = ISOtrain.Response.Success Then
                If DeleteDivision(adapter, useSignature) = ISOtrain.Response.Success Then
                    adapter.Commit()
                Else
                    ' abort, roll back the transaction
                    adapter.Rollback()
                 End If
            Else
                adapter.Rollback()
            End If
        Else
            adapter.Commit()
        End If

    Catch
         adapter.Rollback()
    Finally
        adapter.CloseConnection()
        adapter.Dispose()
    End Try

Hi Lenin,

Please post the code of the InsertDivision method. Also provide more info about version, runtime library version etc. (http://llblgen.com/TinyForum/Messages.aspx?ThreadID=7722)

This is the code for insert in the master table:

    Public Function Update(ByVal inSigAction As System.String, ByVal inSigMeaning As System.String, ByVal inKeyid As System.String, ByVal inKeyuserid As System.String, ByVal inUpdateRevision As System.String, Optional ByVal adapterTemp As IDataAccessAdapter = Nothing) As Integer
        Try
            Dim dbType As StoredProcedureCaller.DatabasesSupported = StoredProcedureCaller.GetDatabaseType()
            If dbType = StoredProcedureCaller.DatabasesSupported.Oracle Then
                If adapterTemp Is Nothing Then
                    Return Oracle.DatabaseSpecific.ActionProcedures.CourseEditUpdate(CourseCode, TrainingType, SubjectCode, GroupType, CourseRev, Description)
                Else
                    Return Oracle.DatabaseSpecific.ActionProcedures.CourseEditUpdate(CourseCode, TrainingType, SubjectCode, GroupType, CourseRev, Description, CType(adapterTemp, ISOtrainDAL.Oracle.DatabaseSpecific.DataAccessAdapter))
                End If
            ElseIf dbType = StoredProcedureCaller.DatabasesSupported.SqlServer Then
                Return Nothing
            Else
                Return Nothing
            End If
        Catch ex As Exception
            LogItem.WriteLog("Courses", "Update", "Error when Update was called. Error: " & ex.Message, "DAL.MyCourseEntity", LogType.Errors)
            Return -2000
        End Try
    End Function

This is the code for insert in detail table of divisions course:

Private Function InsertDivision(ByVal adapterTemp As IDataAccessAdapter, Optional ByVal useSignature As Boolean = False) As Integer Dim dt As DataTable Dim dr As DataRow Dim intResponse As Integer = ISOtrain.Response.Success dt = Division Try For Each dr In dt.Rows If IsDivisionInDataTable(dr.Item(0).ToString, DivisionOld) = False Or Mode = FormMode.CopyRecord Then Dim _myCourseDivisionEntity As New MyCourseDivisionEntity _myCourseDivisionEntity.CourseCode = CourseCode _myCourseDivisionEntity.DivisionCode = dr.Item(0).ToString _myCourseDivisionEntity.ModificationId = "100-01" _myCourseDivisionEntity.ModificationName = "Default User" _myCourseDivisionEntity.TrnCode = GetTrnCode() intResponse = _myCourseDivisionEntity.Insert(adapterTemp) Return intResponse _myCourseDivisionEntity = Nothing End If Next Return intResponse Catch ex As Exception Return ISOtrain.Response.Failure Finally dt = Nothing dr = Nothing End Try End Function

Also provide more info about version, runtime library version etc. (http://llblgen.com/TinyForum/Messages.aspx?ThreadID=7722)

Please can you also provide the generated sql (from SQL profiler or the Oracle equivalent) - can you see the transaction being either commited or rolled back when the insert on the child table fails...?

Thanks

Matt

This is for master table:

CREATE OR REPLACE PROCEDURE SP_COURSE_INSERT ( in_course_code IN courses.course_code%TYPE, in_training_type IN courses.training_type%TYPE, in_subject_code IN courses.subject_code%TYPE, in_group_type IN courses.group_type%TYPE, in_course_rev IN courses.course_rev%TYPE, in_description IN courses.description%TYPE ) AS BEGIN INSERT INTO courses (course_code, training_type, subject_code, group_type, course_rev, description ) VALUES (UPPER (in_course_code), UPPER (in_training_type), UPPER (in_subject_code), UPPER (in_group_type), UPPER (in_course_rev), replace(in_description,CHR(13) || CHR(10),' ') );

EXCEPTION WHEN DUP_VAL_ON_INDEX THEN RAISE DUP_VAL_ON_INDEX; WHEN OTHERS THEN raise_application_error (-20002, 'Unknown error, please contact the system administrator.', FALSE ); END; /

This is the code for detail:

CREATE OR REPLACE PROCEDURE ISOTRAIN.SP_COURSE_DIVISION_INSERT ( in_division_code IN COURSE_DIVISION.DIVISION_CODE%TYPE, in_course_code IN COURSE_DIVISION.course_code%TYPE ) AS BEGIN

INSERT INTO COURSE_DIVISION(
    DIVISION_CODE,
    COURSE_CODE
    )
VALUES (
    UPPER(in_division_code),
    UPPER(in_course_code)
    );

EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        RAISE DUP_VAL_ON_INDEX;
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20002, SQLERRM, FALSE);

END; /

Sorry, I meant the SQL code that actually gets generated and run against the database when you run your code...

This is the generated code of LLBLGen:

For Master:

    Public Shared Function CourseEditUpdate(inCourseCode As System.String, inTrainingType As System.String, inSubjectCode As System.String, inGroupType As System.String, inCourseRev As System.String, inDescription As System.String) As Integer
        Dim parameters() As OracleParameter = New OracleParameter(30 - 1) {}
        parameters(0) = New OracleParameter("IN_COURSE_CODE", OracleType.VarChar, 4000, ParameterDirection.Input, True, 0, 0, "",  DataRowVersion.Current, ConvertNullToDBNull(inCourseCode))
        parameters(1) = New OracleParameter("IN_TRAINING_TYPE", OracleType.VarChar, 4000, ParameterDirection.Input, True, 0, 0, "",  DataRowVersion.Current, ConvertNullToDBNull(inTrainingType))
        parameters(2) = New OracleParameter("IN_SUBJECT_CODE", OracleType.VarChar, 4000, ParameterDirection.Input, True, 0, 0, "",  DataRowVersion.Current, ConvertNullToDBNull(inSubjectCode))
        parameters(3) = New OracleParameter("IN_GROUP_TYPE", OracleType.VarChar, 4000, ParameterDirection.Input, True, 0, 0, "",  DataRowVersion.Current, ConvertNullToDBNull(inGroupType))
        parameters(4) = New OracleParameter("IN_COURSE_REV", OracleType.VarChar, 4000, ParameterDirection.Input, True, 0, 0, "",  DataRowVersion.Current, ConvertNullToDBNull(inCourseRev))
        parameters(5) = New OracleParameter("IN_DESCRIPTION", OracleType.VarChar, 4000, ParameterDirection.Input, True, 0, 0, "",  DataRowVersion.Current, ConvertNullToDBNull(inDescription))
        Dim toReturn As Integer = adapter.CallActionStoredProcedure("ISOTRAIN.SP_COURSE_EDIT_UPDATE", parameters)

        Return toReturn
    End Function

For Detail:

    Public Shared Function CourseDivisionInsert(inDivisionCode As System.String, inCourseCode As System.String, inModificationId As System.String, inModificationName As System.String, inTrnCode As System.String, inSigAction As System.String, inSigMeaning As System.String, inKeyId As System.String, inKeyUserId As System.String, adapter As DataAccessAdapter) As Integer
        Dim parameters() As OracleParameter = New OracleParameter(9 - 1) {}
        parameters(0) = New OracleParameter("IN_DIVISION_CODE", OracleType.VarChar, 4000, ParameterDirection.Input, True, 0, 0, "",  DataRowVersion.Current, ConvertNullToDBNull(inDivisionCode))
        parameters(1) = New OracleParameter("IN_COURSE_CODE", OracleType.VarChar, 4000, ParameterDirection.Input, True, 0, 0, "",  DataRowVersion.Current, ConvertNullToDBNull(inCourseCode))

        Dim toReturn As Integer = adapter.CallActionStoredProcedure("ISOTRAIN.SP_COURSE_DIVISION_INSERT", parameters)

        Return toReturn
    End Function

the code of CallActionStoredProcedure:

    Public Overridable Function CallActionStoredProcedure(storedProcedureToCall As String, parameters As OracleParameter()) As Integer
        Dim Command As New OracleCommand(CreateCorrectStoredProcedureName(storedProcedureToCall))
        Command.Connection = CType(MyBase.GetActiveConnection(), OracleConnection)
        If MyBase.IsTransactionInProgress Then
            CType(Command, IDbCommand).Transaction = CType(MyBase.PhysicalTransaction, OracleTransaction)
        End If
        Command.CommandType = CommandType.StoredProcedure
        Command.CommandTimeout = MyBase.CommandTimeOut

        Dim toReturn As Integer = -1
        Try
            Dim i As Integer
            For i=0 To parameters.Length-1
                If Not parameters(i) Is Nothing Then
                    Command.Parameters.Add(parameters(i))
                End If
            Next i

            MyBase.OpenConnection()
            toReturn = Command.ExecuteNonQuery()
        Finally
            command.Dispose()
            ' clean up a dangling automaticly opened connection if needed.
            If Not (MyBase.KeepConnectionOpen Or MyBase.IsTransactionInProgress) Then
                MyBase.CloseConnection()
            End If
        End Try
        Return toReturn
    End Function

When you run your code (both yours and the LLBLGenerated), it generates SQL statements and sends them to your SQL server. Both MSSQL and Oracle provide tools to capture these SQL statements. It is this SQL that I really need to see - it will tell me exactly what is being generated and sent to the server....

Do you have examples for run Stored Procedures with Transactions? similar to the code that I sent you.

Also provide more info about version, runtime library version etc. (http://llblgen.com/TinyForum/Messages.aspx?ThreadID=7722)

You still haven't answered the above question.

Also I find it too hard to follow your pieces of code, as you haven't posted a solid complete example of code.

Anyway why are youusing SPs to Insert and Update entities, LLBLGen Pro doesn't recommend this. Otherwise why bother using LLBLGen for this particular transaction.

"When you run your code (both yours and the LLBLGenerated), it generates SQL statements and sends them to your SQL server. Both MSSQL and Oracle provide tools to capture these SQL statements. It is this SQL that I really need to see - it will tell me exactly what is being generated and sent to the server...."

Can you help me to generate this code?

You need to use an Oracle equivalent of SQL Profiler - there are several available. This will watch your Oracle server and log the SQL statements that it process. You will be able to filter this log so that it only shows statements generated by you, against the database that you are interested in.

Once you have this trace set up, you need to run the code that is failing, and then save the generated trace to a file, and attach it to a post here.

Although, as Walaa said, you do seem to be using LLBLGen in a very convoluted manner - if you are just calling stored procedures, you may be better off doing this using standard ADO.NET.

Matt

This is:

/* Formatted on 2009/05/05 11:45 (Formatter Plus v4.8. */ INSERT INTO courses (course_code, training_type, subject_code, group_type, course_rev, description ) VALUES (UPPER (:b34), UPPER (:b33), UPPER (:b32), UPPER (:b31), UPPER (:b30), REPLACE (:b29, CHR (13) || CHR (10), ' ') )

 FROM courses
WHERE course_code = UPPER (:b1)

/* Formatted on 2009/05/05 11:44 (Formatter Plus v4.8. */ INSERT INTO course_division (division_code, course_code ) VALUES (UPPER (:b5), UPPER (:b4) )

So there is no sign of a BEGIN TRAN sql statement generated when you start the transaction...?

I am using adapter.StartTransaction(IsolationLevel.ReadCommitted, "TwoUpdates").

Hi Lenin

We are struggling to see what the issue is here. To allow us to investigate further please can you provide us with

a) More info about version, runtime library version etc. http://llblgen.com/TinyForum/Messages.aspx?ThreadID=7722

b) A repro solution that demonstrates the issue. This needs to be in the form of a Visual Studio project that we can open and run so needs to include all code necessary for it to build and run. We would also need a script to allow us to create the required database structure.

Thanks

Matt

recientemente encontramos problemas cuando se ejecutaban Transacciones con LLBLGen, a raíz de esto se tuvieron que aplicar unos parchos de Microsoft (asp .net 2008 ) para corregir la situación. Aplicar los tres parchos en el siguiente orden:

NDP20SP2-KB958481-x86.exe NDP30SP2-KB958483-x86.exe NDP35SP1-KB958484-x86.exe

Es importante que apliquen los parchos o de lo contrario confrontarán problemas con los Rollbacks al hacer Transacciones.

lenino wrote:

recientemente encontramos problemas cuando se ejecutaban Transacciones con LLBLGen, a raíz de esto se tuvieron que aplicar unos parchos de Microsoft (asp .net 2008 ) para corregir la situación. Aplicar los tres parchos en el siguiente orden:

NDP20SP2-KB958481-x86.exe NDP30SP2-KB958483-x86.exe NDP35SP1-KB958484-x86.exe

Es importante que apliquen los parchos o de lo contrario confrontarán problemas con los Rollbacks al hacer Transacciones.

Gracias Lenin por la información.

In English:

lenino wrote:

Recently we found problems with LLBLGen and Transactions. So we applied some patches (Asp.Net 2008 ) to fix the issue. You have to apply the patches in the following order:

NDP20SP2-KB958481-x86.exe NDP30SP2-KB958483-x86.exe NDP35SP1-KB958484-x86.exe

It's important to apply the patches or you will encounter problems with Rollbacks.

I presume these patches are available through Microsoft's PSS?

Yes

http://www.microsoft.com/downloads/details.aspx?familyid=6C095BBA-6100-4EC9-9C54-6450B0212565&displaylang=en

The three files are 1. .NET Framework 2.0 Service Pack 2 - NDP20SP2-KB958481-x86.exe 2. .NET Framework 3.0 Service Pack 2 - NDP30SP2-KB958483-x86.exe 3. .NET Framework 3.5 Service Pack 1 - NDP35SP1-KB958484-x86.exe

Matt