Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Issue with multiple sql queries in single change #297

Open
SagarShivgan opened this issue Jan 31, 2022 · 1 comment
Open

Issue with multiple sql queries in single change #297

SagarShivgan opened this issue Jan 31, 2022 · 1 comment

Comments

@SagarShivgan
Copy link

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
Copy link

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants