Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    insteadOfInsertUpdate db table trigger incompatibility

    SmartClient Version: v12.0p_2018-11-30/PowerEdition Deployment (built 2018-11-30)

    I have installed an InsteadOfInsertUpdate style trigger on a database table that validates the inserted/update fields before actually updating the data. There is an interaction with smartGWT logic that I don't understand. Though I can insert/update the statement generated by smartGWT by hand, the web app gives me the exception included bellow. Oddly, you can see where the insert had been done at one point as it echos the inserted record's PK. When all is said and done, that record does not exist in the database and a rollback log item is seen. A pop up is generated by smartGWT saying that a result set was created. What is smartGWT looking for in the insert/update process to keep the roll back from occurring?

    Following are some more details if that helps.

    Thanks in advance

    Eric


    smartGWT generated SQL:

    INSERT INTO Test_Detail_Sticky_Waiver (DetailSubType, DetailType, FK_Test_Session_Start, FK_user_creator, Rationale, State, Test_Name) VALUES ('Run()', 'Test', 53336, 28403, 'Testing 1', 'Error', 'CSP_MEM_1003')

    error log:
    === 2019-06-17 13:08:19,109 [-330] INFO SQLDriver - [builtinApplication.Test_Detail_Sticky_Waiver_add] Executing SQL query on 'QA_BEER_LOCAL' using connection '246721895': INSERT INTO Test_Detail_Sticky_Waiver (DetailSubType, DetailType, FK_Test_Session_Start, FK_user_creator, Rationale, State, Test_Name) VALUES ('Run()', 'Test', 53336, 28403, 'sadfgvbsdfgb', 'Error', 'CSP_MEM_1003')
    === 2019-06-17 13:08:19,183 [-330] DEBUG SQLDriver - [builtinApplication.Test_Detail_Sticky_Waiver_add] Found 1 sequence(s)/autoGenerated PK field(s): [PK_Test_Detail_Sticky_Waiver]
    === 2019-06-17 13:08:19,183 [-330] DEBUG SQLDriver - [builtinApplication.Test_Detail_Sticky_Waiver_add] Discovered zero or one generated key via JDBC: {PK_Test_Detail_Sticky_Waiver=77}
    === 2019-06-17 13:08:19,183 [-330] DEBUG SQLDriver - [builtinApplication.Test_Detail_Sticky_Waiver_add] FAILED to execute SQL update in 'QA_BEER_LOCAL' using connection'246721895'
    === 2019-06-17 13:08:19,183 [-330] DEBUG DSRequest - freeOnExecute is false for request of type add on DataSource Test_Detail_Sticky_Waiver - not freeing resources!
    === 2019-06-17 13:08:19,184 [-330] WARN RequestContext - dsRequest.execute() failed:
    com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:228)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:563)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:479)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7344)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2713)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:224)
    ... etc ...
    === 2019-06-17 13:08:19,184 [-330] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
    === 2019-06-17 13:08:19,184 [-330] DEBUG SQLTransaction - Rolling back QA_BEER_LOCAL transaction "246721895"
    === 2019-06-17 13:08:19,187 [-330] DEBUG RPCManager - non-DMI response, dropExtraFields: false
    === 2019-06-17 13:08:19,187 [-330] DEBUG SQLTransaction - getConnection() looked for transactional connection for QA_BEER_LOCAL: hashcode "246721895"
    === 2019-06-17 13:08:19,187 [-330] WARN SQLTransaction - Ending QA_BEER_LOCAL transaction "246721895"
    Trigger:

    /****** Object: Trigger [dbo].[Test_Detail_Sticky_Waiver_InsteadOfInsertUpdate] Script Date: 6/18/2019 5:54:18 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO



    CREATE TRIGGER [dbo].[Test_Detail_Sticky_Waiver_InsteadOfInsertUpdate]
    ON [dbo].[Test_Detail_Sticky_Waiver]
    INSTEAD OF INSERT, UPDATE
    AS
    BEGIN
    SET NOCOUNT ON
    DECLARE @nRows bigint,
    @action varchar(4),
    @timeNow dateTime = getDate(),
    @debug tinyint = 1

    IF EXISTS (SELECT * FROM INSERTED)
    BEGIN
    IF EXISTS (SELECT * FROM DELETED)
    SET @action = 'U' -- update
    ELSE
    SET @action = 'I' --insert
    END


    IF @action = 'I' or @action= 'U'
    BEGIN

    -- find existances of existing records having time overlaps with ones being inserted or updated.
    SELECT tt.FK_Test_Session_Start -- we don't care what is selected. We just want to know if something exists
    FROM inserted ii
    INNER JOIN Test_Detail_Sticky_Waiver tt ON tt.Test_Name = ii.Test_Name AND tt.State = ii.State AND tt.DetailType = ii.DetailType AND tt.DetailSubType = ii.DetailSubType
    JOIN Test_Session tt_start ON tt.FK_Test_Session_Start = tt_start.PK_Test_Session
    JOIN Test_Session ii_start ON ii.FK_Test_Session_Start = ii_start.PK_Test_Session
    LEFT OUTER JOIN Test_Session tt_end ON tt.FK_Test_Session_End = tt_end.PK_Test_Session
    LEFT OUTER JOIN Test_Session ii_end ON ii.FK_Test_Session_End = ii_end.PK_Test_Session
    WHERE
    ii.PK_Test_Detail_Sticky_Waiver != tt.PK_Test_Detail_Sticky_Waiver

    AND tt_start.FK_Test_Session_Stream = ii_start.FK_Test_Session_Stream
    AND tt_start.FK_Test_Bench = ii_start.FK_Test_Bench

    -- find time overlaps with current wivers
    AND ( -- The start if the inserted reocrd lies with in an existing waiver
    (tt_start.CT_on_create <= ii_start.CT_on_create AND ii_start.CT_on_create < ISNULL(tt_end.CT_on_create, @timeNow) )
    OR
    -- The end of the inserted record lies with in an existing waiver
    (tt_start.CT_on_create < ISNULL(ii_end.CT_on_create, @timeNow) AND ISNULL(ii_end.CT_on_create, @timeNow) < ISNULL(tt_end.CT_on_create, @timeNow +1))
    )


    SELECT @nRows = @@Rowcount
    IF @debug > 0
    PRINT '[Test_Detail_Sticky_Waiver_InsteadOfInsertUpdate] Attempting action:' + @action + ' with number of conflict rows:'
    + CAST(@nRows as varchar(4))

    If @nRows = 0
    GOTO CompleteTransaction

    RAISERROR (N'A sticky waiver being inserted or updated overlaps in time with one or more existing waivers of the same stream, test name, State(Type), DetailType(Checker), and DetailSubType(Feature)',
    10, -- Severity,
    1 -- State,
    )
    ROLLBACK TRANSACTION
    GOTO EndTransaction

    END -- IF @action = 'I' or @action= 'U'


    CompleteTransaction:

    IF @action = 'I'
    INSERT INTO Test_Detail_Sticky_Waiver
    --OUTPUT inserted.*
    SELECT Inserted.Test_Name
    ,Inserted.FK_Test_Session_Start
    ,Inserted.FK_Test_Session_End
    ,Inserted.State
    ,Inserted.DetailType
    ,Inserted.DetailSubType
    ,Inserted.Rationale
    ,Inserted.FK_user_creator
    ,Inserted.FK_user_unwaiver
    ,Inserted.CT_on_create
    ,Inserted.CT_on_unwaive
    FROM Inserted


    IF @action = 'U'
    UPDATE Test_Detail_Sticky_Waiver
    SET Test_Name = Inserted.Test_Name
    ,FK_Test_Session_Start = Inserted.FK_Test_Session_Start
    ,FK_Test_Session_End = Inserted.FK_Test_Session_End
    ,State = Inserted.State
    ,DetailType = Inserted.DetailType
    ,DetailSubType = Inserted.DetailSubType
    ,Rationale = Inserted.Rationale
    ,FK_user_creator = Inserted.FK_user_creator
    ,FK_user_unwaiver = Inserted.FK_user_unwaiver
    ,CT_on_create = Inserted.CT_on_create
    ,CT_on_unwaive = Inserted.CT_on_unwaive
    --OUTPUT inserted.*
    FROM Inserted, Test_Detail_Sticky_Waiver
    WHERE Inserted.PK_Test_Detail_Sticky_Waiver = Test_Detail_Sticky_Waiver.PK_Test_Detail_Sticky_Waiver


    EndTransaction:

    END


    #2
    Take a look at the stack trace: SQLServer is throwing that error, not SmartGWT. There's no way for us to recover once that error has been thrown, as a response is never provided by the database here, we only get the exception.

    As far as what's going on, we are doing standard JDBC usage of executeUpdate(), and it looks like Microsoft's JDBC driver just doesn't handle this kind of trigger being used for an update.

    If you can't figure out how to adjust your trigger to make Microsoft's driver stop crashing with executeUpdate(), you could try issuing your INSERT as a fetch via direct JDBC. This would obviously be hacking around an MS driver bug, and we're only suggesting it because the exception they are throwing says that they returned a ResultSet, which is what you'd expect from a fetch.

    This sample shows how to do your own JDBC calls in the midst of a SmartGWT-managed transaction.

    Comment


      #3
      Thanks for the driver observation!

      For anyone who bumps into this thread, I was able to solve it with two trigger code changes. The first was to ensure that there were no 'naked' select/insert/update statements other the one doing the actual intended table change. By that I mean I was able to do a query to find conflicting records, however I stuffed the results into a variable. That seemed to fix the drivers confusion as it probably obscured the intermediate results. Second, I was able to get the error message I wanted back through the software layers by using THROW, not RAISE.

      Here is a working example of a the technique:

      CREATE TRIGGER [dbo].[Test_Detail_Sticky_Waiver_InsteadOfInsertUpdate]
      ON [dbo].[Test_Detail_Sticky_Waiver]
      INSTEAD OF INSERT, UPDATE
      AS
      BEGIN
      SET NOCOUNT ON
      DECLARE @action varchar(6),
      @timeNow dateTime = getDate(),
      @firstConflict varchar(100),
      @debug tinyint = 0

      IF EXISTS (SELECT * FROM INSERTED)
      BEGIN

      IF EXISTS (SELECT * FROM DELETED)
      SET @action = 'update'
      ELSE
      SET @action = 'insert'
      END

      IF @action = 'insert' or @action= 'update'
      BEGIN

      -- find the first record having time overlaps with ones being inserted or updated.
      set @firstConflict = (
      SELECT TOP 1 'PK=' + CAST(tt.PK_Test_Detail_Sticky_Waiver as varchar(10)) + ' starting at session ' + tt_start.SessionID + ' ' + CAST(tt_start.CT_on_create as varchar(20))

      FROM inserted ii
      INNER JOIN Test_Detail_Sticky_Waiver tt ON tt.Test_Name = ii.Test_Name AND tt.State = ii.State AND tt.DetailType = ii.DetailType AND tt.DetailSubType = ii.DetailSubType
      JOIN Test_Session tt_start ON tt.FK_Test_Session_Start = tt_start.PK_Test_Session
      JOIN Test_Session ii_start ON ii.FK_Test_Session_Start = ii_start.PK_Test_Session
      LEFT OUTER JOIN Test_Session_Stream ii_stream ON ii_stream.PK_Test_Session_Stream = ii_start.FK_Test_Session_Stream
      LEFT OUTER JOIN Test_Session tt_end ON tt.FK_Test_Session_End = tt_end.PK_Test_Session
      LEFT OUTER JOIN Test_Session ii_end ON ii.FK_Test_Session_End = ii_end.PK_Test_Session
      WHERE
      -- When updating a record (say by moving it's start of end time slightly) we create the query such that it doesn't detect it overlaps with it's original record still in the DB.
      ii.PK_Test_Detail_Sticky_Waiver != tt.PK_Test_Detail_Sticky_Waiver

      AND tt_start.FK_Test_Session_Stream = ii_start.FK_Test_Session_Stream
      AND tt_start.FK_Test_Bench = ii_start.FK_Test_Bench

      -- find time overlaps with current wivers
      AND ( -- The start of the inserted reocrd lies with in an existing waiver
      (tt_start.CT_on_create &lt;= ii_start.CT_on_create AND ii_start.CT_on_create &lt; ISNULL(tt_end.CT_on_create, @timeNow) )
      OR
      -- The end of the inserted record lies with in an existing waiver
      (tt_start.CT_on_create &lt; ISNULL(ii_end.CT_on_create, @timeNow) AND ISNULL(ii_end.CT_on_create, @timeNow) &lt; ISNULL(tt_end.CT_on_create, @timeNow +1))
      )
      )

      If @firstConflict IS NULL
      GOTO CompleteTransaction

      ROLLBACK TRANSACTION;

      DECLARE @msg varchar(256) = 'A sticky wavier being ' + @action + 'ed overlaps existing waiver(s). The first being ' + @firstConflict ;
      THROW 51000, @msg, 1;

      GOTO EndTransaction

      END -- IF @action = 'insert' or @action= 'update'


      CompleteTransaction:

      IF @action = 'insert'
      INSERT INTO Test_Detail_Sticky_Waiver
      --OUTPUT inserted.*
      SELECT Inserted.Test_Name
      ,Inserted.FK_Test_Session_Start
      ,Inserted.FK_Test_Session_End
      ,Inserted.State
      ,Inserted.DetailType
      ,Inserted.DetailSubType
      ,Inserted.Rationale
      ,Inserted.FK_user_creator
      ,Inserted.FK_user_unwaiver
      ,Inserted.CT_on_create
      ,Inserted.CT_on_unwaive
      FROM Inserted

      IF @action = 'update'
      UPDATE Test_Detail_Sticky_Waiver
      SET Test_Name = Inserted.Test_Name
      ,FK_Test_Session_Start = Inserted.FK_Test_Session_Start
      ,FK_Test_Session_End = Inserted.FK_Test_Session_End
      ,State = Inserted.State
      ,DetailType = Inserted.DetailType
      ,DetailSubType = Inserted.DetailSubType
      ,Rationale = Inserted.Rationale
      ,FK_user_creator = Inserted.FK_user_creator
      ,FK_user_unwaiver = Inserted.FK_user_unwaiver
      ,CT_on_create = Inserted.CT_on_create
      ,CT_on_unwaive = Inserted.CT_on_unwaive
      --OUTPUT inserted.*
      FROM Inserted, Test_Detail_Sticky_Waiver
      WHERE Inserted.PK_Test_Detail_Sticky_Waiver = Test_Detail_Sticky_Waiver.PK_Test_Detail_Sticky_Waiver


      EndTransaction:

      END

      Comment


        #4
        Thanks for posting this solution! Likely to help someone with a similar issue, and the techniques may apply to writing workable stored procedures for other database types too.

        Comment

        Working...
        X