Issue with multiple sql queries in single change

[SagarShivgan]

The problem is in trying to run multiple sql queries in single change. If some of them run successfully and one fails, OBEVO throws an exception and fails to mark the change in the deployment table. In the second attempt to deploy, when I correct the failed sql query and try to deploy the change again, I get an exception resulting from prior successfully run queries.

Steps to reproduce the issue -

1. Let us understand by below example. Here I have 2 sql queries in single change. 'Person' table has PK constraint on 'id' column. When I deploy this change then first query runs successfully and it will create entry in Person table. But second query failed due to violation of primary key constraint results in exception and the change not get marked in ARTIFACTDEPLOYMENT table.

   //// CHANGE name="add people" INSERT INTO Person (id, firstName, lastName, addressCountryName) VALUES (1, 'F1', 'L1', 'Japan') GO INSERT INTO Person (id, firstName, lastName, addressCountryName) VALUES (1, 'F2', 'L2', 'United Kingdom') GO

2. Then I have corrected my mistake as shown below and try to deploy this change again. But this time also it throws exception due to violation of primary key constraint.

   //// CHANGE name="add people" INSERT INTO Person (id, firstName, lastName, addressCountryName) VALUES (1, 'F1', 'L1', 'Japan') GO INSERT INTO Person (id, firstName, lastName, addressCountryName) VALUES (2, 'F2', 'L2', 'United Kingdom') GO

I am unable to deploy my valid change. Please help me.

Obevo version - 8.2.1

[mohrezaei]

The solution in this example is somewhat obvious, so you probably have a more complicated real production case. Regardless, the idea may be applicable to your real case. For this example, the following script will work:

//// CHANGE name="add people"
DELETE FROM Person where id = 1
GO
DELETE FROM Person where id = 2
GO
INSERT INTO Person (id, firstName, lastName, addressCountryName)
VALUES (1, 'F1', 'L1', 'Japan')
GO
INSERT INTO Person (id, firstName, lastName, addressCountryName)
VALUES (2, 'F2', 'L2', 'United Kingdom')
GO

Note that the second delete is not necessary, but it's really the whole point here: if you can make your script re-runnable, this situation will not happen. If you really can't do that, one solution would be to break up your scripts across re-runnability boundaries.