Announcement
Collapse
No announcement yet.
X
-
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.
-
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 <= 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))
)
)
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
Leave a comment:
-
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.
Leave a comment:
-
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 ...Trigger:
=== 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"
/****** 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
Tags: None
Leave a comment: