SQLContainer - Unable to delete from database.

Flow
1

I am trying to utilize the SQLContainer add-on and perform a simple database delete from a MySQL database. I have tried to remove the item from the container and commit the changes, however, the item remains in the database. I have even tried this by passing in the item id of the last item in the container to ensure it was not a problem with the Item id paramater I was passing.

Any help is much appreciated!

		container = connectionPool.getProviderContainer();
		container.removeItem(container.lastItemId());
		try {
			
			container.commit();
		}
2

Double check that there aren’t any NULL values. Think there was an issue about that.

Also if your VERSION column is not NULL if using that.

All I can think of with the limited info

Dana

3

That got it! I did not specify a version column. I changed this value to my primary key which is an auto-incremented primary key.

Is there any need to create a new column for version or will what I am doing satisfice?

Thanks so much!

4

Hi,

using the primary key column as version column will work just fine, but you will lose optimistic locking. If you would use a dedicated version column, you could update its value (or better yet, have the database do it for you) on each change of the row. This way you could detect situations where some other user has updated the row in between of you reading it and changing it. After the detection you could e.g. notify the user attempting to modify the row and load the most recent state.

-Tepi

5

Thanks Tepi. That makes sense. I will incorporate that change as the application scales…currently, unique users will be reading/writing to only their private data store. Really cleared things up…