nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Explicit commit required for IWExternalDataSource?

Bill Klish

OpenDeploy 6.0.2
DataDeploy module
MySQL DB

I have written a Java class that gets the next sequence number from a table and returns it for insertion into the database. Do I need to execute a commit on the Java Connection object sent in via the DataDeploy engine, or will my changes be committed/rolled back based on the overall deployment?

Thanks,
-Bill

Find more posts tagged with

Comments

Bill Klish

This is frustrating. It appears to be another DataDeploy bug. If I have a problem with my deployment, the external id (sequence number in this case) gets created, the deployment dies, and the connection object passed to my IWExternalDataSource implementation is rolled back. However, if everything goes successfully to the database, that same connection object has no commit executed.

I guess I will have to file yet another support case........

Migrateduser

Hi Bill,

Sorry about the DD issue.

So you're saying that just because you requested a sequence value from the DB in the IWExternalDataSource, your deployment does not commit? DD should be doing the commit in it's regular code path.

Is the feature behaving like this for any database transaction that you're doing in the java class?

Mariam

Bill Klish

Correct. If I get a new sequence number from the db, it shows up in the DD log correctly, but when it shows Committing database.... in the log, the values are all inserted into the other tables, but the sequence column in the table still has the pre-deployment value, causing issues the next time we deploy.

That same connection object is rolled back if there is an issue. For example, I had the wrong dcr field name in my db schema causing the deployment to fail for a non-null value. The sequence was created and visible in the DD log, but, when the deployment failed and it rolled back, the sequence was back to its original pre-deployment value.

It could possibly be that you are not committing or rolling back the connection object you are sending to the IWExternalDataSource interface, but I think you should, to maintain data integrity within the overall deployment.

Migrateduser

Sorry ... bear with me here..

I don't think we call an extra commit for the connection object passed to the EDS.

I'm pretty sure we only establish 1 DB connection. I would think that the EDS doesn't need to have a commit and that the general table commit done later is sufficient. This should be the case if there is truly 1 connection object.

Do you only have 1 value-from callout in your table? Is all that your EDS does is get a sequence value and pass that to the column? As far as the updated number not showing up, that is weird... Sounds like DD isn't passing the value to the column at all. If you just hardcode some value to pass from your class (skip the sequence stuff), does the new value show up?

So if another commit is needed specifically to commit the sequence, that could be something we didn't think of. With ExternalDataSource class you are basically just getting values... then pumping them into the schema. If a value is passed to the column from your EDS, that number should get inserted into the column. If you generated a sequence number and passed that as your callout, i can't see how DD wouldn't see that, unless the EDS feature is not working at all.

But in your case when you are altering something in the DB, it's possible that we need to do another commit. Most use cases, just get a value from a table (no commit needed). Sequences may not have come up before.

It might be good to show me the EDS class and dbschema.... either post or send me an email mtariq@interwoven.com.

Mariam

Bill Klish

I have opened a support case (#1238221) on this but I don't believe we have gotten anywhere yet.

I don't think you are totally following me and have described the EDS functionality incorrectly.

According to the DD admin manual (version 6.1, page 153 in the example java code comments for the conn input parameter) 2 connections are opened to the same database that the deployment is running for. One, for the overall deployment, and one for any operations needed by the EDS class. I am successfully getting the connection correctly in the EDS, it is correctly updating the DB and getting the next sequence number (from MySQL or SQL Server) and returning that back to the tuple to be inserted by the overall deployment.

The problem is this. You never perform a commit on that 2nd connection if the overall deployment of the "real" data is successful. So, let's say the DB is initialized with a sequence value of 1. When I deploy the first DCR, the sequence returned to the tuple is 2. The DD log shows the process committing successfully and if I look in the DB, the DCR data is loaded properly, along with a sequence value of 2 for the sequence column. However, if I look at the sequences table, the sequence value still shows 1, instead of 2.

If I manually add a conn.commit() in my EDS code, the table will read 2. However, as a best practice, we would be possibly committing even if a deployment fails. In this particular instance where we are just grabbing a single value from a table, this probably isn't such a terrible thing, but for data consistency it should be committed with the overall deployment (IMO).

Interestingly, if I have a failure in the overall deployment (purposely leaving a NOT NULL column empty) the 2nd connection object sent into the EDS is rolled back. Using the same example above, if the table had sequence value 1, the value 2 was returned from the EDS, and the DB still shows 1. So, I can assume that either you are still not doing anything with the second connection (most likely) or are rolling back that second connection object. Without seeing the code, hard to say, but I assume you are not doing anything to this second connection object in either case.

I sent a very simplistic example that illustrates the failure using the TeamSite sample book template along with a basic EDS class as part of the case notes.

Let me know if this is still not clear.
-Bill

Migrateduser

Thanks for the clarification Bill. I'm sure we're not rolling back anything with regards to the second connection. The EDS use case was intended to be more of a read operation, not write to the DB. So it's a way for DD to get data to push into a table, but does not affect the tables in anyway. Your use case with the DB sequence is an interesting one, and one that probably wasn't considered. So it sounds like feature request to me.

Mariam

Bill Klish

Fair enough. I will report back on the feature request #. I have submitted the code and dbschema file which reproduces this issue to the support engineer.

Thanks for the help,
-Bill

Migrateduser

FYI,

Bill, I also updated your case!!
ref: Feature: 67649 ~ callout to update index number is not committed in DB.

regards, Steve