Editing Versioned Geodatabases using SQL

Introduction

In the below examples we will demonstrate issuing SQL Commands on the back-end SQL Server and Oracle database of an ArcSDE Geodatabase. The SQL commands issued in the following examples are ways to speed up versioned writes with ArcSDE Geodatabases.

Issuing SQL commands to the back-end database of an ArcSDE Geodatabase is risky behavior and can cause data corruption/loss. By bypassing the ArcSDE side of the Geodatabase, we are preventing the safeguards that are established by ArcGIS from protecting the data housed in the database. As such, we do not recommend issuing SQL commands to the back-end database. If you choose to do so, be very careful.

Requirements

• ArcGIS Installed + License
• ArcSDE Geodatabase

Step-by-step Instructions

Before we begin, load the supplied data into your ArcSDE Geodatabase. Once you have loaded the data into the Geodatabase, please register the database as versioned. Make note of the table name and the version you are going to use for editing. For more information regarding the versioning process please see this Esri article.

SQL Server Option

Delete all rows(Truncates) from the versioned table/view.

Note: The reason we cannot do an explicit truncate is that the versioned table we are interacting with is, in fact, a view. Views are unable to be truncated. As such, we have to use a different command to perform the truncate.

1. Open 'SQLServerSDEVersion.fmw' or Add a SQLCreator to the workspace, connect to the back-end database of your ArcSDE Geodatabase. In this example, we connected to Microsoft SQL Server Spatial.

2. Add the following SQL code into the SQLCreator

FME_SQL_DELIMITER ; /*The following code will set a version to edit, as well as start and stop an edit    session and will delete all rows from the set versioned table.   Should be noted that  all edits will need to be reconcoled+posted in ArcGIS, cannot  be done in FME. Note: Change the 'sdeuser.TrentVersionArc' to the version you intend to edit. */
USE geodatabase104; -- points to database name
EXEC sde.set_current_version 'sdeuser.TrentVersionArc'; -- Sets version to access
EXEC sde.edit_version 'sdeuser.TrentVersionArc', 1; -- opens edit session on version
BEGIN TRANSACTION [Tran1]
BEGIN TRY
    DELETE FROM sdeuser.SampleData_SQLVersion_evw --attempts a truncate of table
    COMMIT TRANSACTION [Tran1] -- if successful will commit the transaction
END TRY
BEGIN CATCH -- if error occurs, will rollback the transaction
    ROLLBACK TRANSACTION [Tran1]
END CATCH
EXEC sde.edit_version 'sdeuser.TrentVersionArc', 2; -- ends the edit session

3. Run your translation. If the translation is successful, check the base table and the specific version with in ArcGIS.

Oracle Option 

1. Open 'OracleArcSDEVersion.fmw' or Add an ArcSDE Reader - Connect to your ArcSDE instance and table.

2. Add a SQLExecutor connecting to the back-end database of your ArcSDE Geodatabase. In this example, we connected to an Oracle Non-spatial instance, which is in fact, the ArcSDE Geodatabase.

In this SQLExecutor, we will issue the following SQL Commands:

FME_SQL_DELIMITER /
BEGIN     
  sde.version_util.set_current_version('C129979_VersionTest');
  sde.version_user_ddl.edit_version('C129979_VersionTest', 1);
END;
/

The above SQL statement will set the version we would like to work on, as well as open an edit session using that version.

3. Add a second SQLExecutor, connect to the same database as previously used. This is
where the updates will be applied using the following SQL code:

FME_SQL_DELIMITER /
BEGIN 
  UPDATE C129979_Trent_evw -- Updates the table based on ObjectID field.
  SET "SUBZONE" = 'Test'
  WHERE "OBJECTID" = @Value(OBJECTID);
  COMMIT; -- commits the feature if successful.
EXCEPTION
  WHEN OTHERS THEN -- Will rollback the translation if the update fails.
    ROLLBACK;
END;
/

4. Add a Sampler transformer. The reason for this is due to only sending one feature to the SQLExecutor that will end the edit session on the versioned table. As the SQLExecutor will execute for each feature, with ending the Edit session after the first feature, we do not need more than one feature being sent. Set the parameters as follows

5. Add a FeatureHolder transformer. This is used merely to prevent the last SQLExecutor from executing when it receives a feature. The reason for preventing this is due to if the last SQL statement is executed, it will end the edit session, as such, you will end up with one feature being updated per translation.

6. Add a third SQLExecutor, again connect to the same database. Here we will end our Edit Session, using the following SQL code.

FME_SQL_DELIMITER /
BEGIN
  sde.version_user_ddl.edit_version('C129979_VersionTest', 2);
END;
/

7. Run your translation. If the translation is successful, check the base table along with the specific version with in ArcGIS.

Results

SQL Server Base table after Truncating version.

SQL Server Specific version after Truncate

Oracle Base Table after updating version.

Oracle Updated versioned Table looking at version we just updated.

Data Attribution

The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.