Direct DBMS sql queries and ArcSDE geodatabase

Question

i want to make updates from Arcobjects on several tables stored on a ArcSDE 10.1 versionned geodatabase based on Postgresql 9.0.5 , none of these update require spatial data , and making these updates through ArcSDE is not easy, but i think to make a direct Postgresql connection to make these sql updates.

Question : what are the consequences and the problems that could happen from this queries , knowing that i wanna change just attribute data , actually i'm using just the DEFAULT version of the geodatabase, making several versions could make a problem toward the use of direct sql queries ?

Answer 1

It is possible to use SQL to issue UPDATE and SELECT commands. Generally speaking it is safe to do however there are a number of things to bear in mind:

1. Do not perform an UPDATE operation on any field in the database that is used for unique IDs such as OBJECTID as this will mess up the data consistency in your Geodatabase
2. You should not perform an SQL edit to DEFAULT version if you work in a multi-user environment. Editing the DEFAULT version places an exclusive lock on the data which will block your other users from accessing the Geodatabase until you release the lock
3. Do not attempt to edit the geometry types in SQL
4. Know your data model, do you have relationships that a Feature Class relies on for functionality such as annotation classes or topology

So, with the above in mind it is straight forward to do editing in SQL on a versioned Geodatabase. There are a set of functions in the database that will help with ensuring integrity of the Geodatabase. To edit in PostgreSQL you can do the following:

# Use sdetable command to make a multi-versioned view
sdetable -o create_mv_view -T viewname -t tablename -i service -D database -u username -p password

# In psql you can use the following commands
# Set the current version for queries and edits
SELECT sde.sde_set_current_version(<version_name>);

# Start an edit session (indicated by the 1)
SELECT sde.sde_edit_version(<version_name>, 1);

# Issue any INSERT, UPDATE, or DELETE commands here

# Close the edit session (indicated by the 2)
SELECT sde.sde_edit_version(<version_name>, 2)

Best practice is to avoid editing DEFAULT in SQL so you should create a version for the batch of edits you want to do using the command SELECT sde.sde_create_version(<version_name>) then once your edits are complete you should use ArcGIS desktop to reconcile the edits, then delete the version with SELECT sde.sde_delete_version(<version_name>).

The Esri web help has some good information on doing this safely and is worth a quick read. The process sounds long winded but its actually pretty quick once you know what you are doing. Good luck!