You can execute SQL SELECT statements against versioned views to access versioned data.
Read from the Default version
You have two options when running SQL statements against the Default version: you can read the latest or read from a version state you specify.
Read the latest state of the Default version
Versioned views automatically access the current state of the Default version. If you execute SELECT statements against a versioned view, it accesses the current state of Default at the time you execute the statement. If other users are committing edits to the Default version (thereby changing the state that the Default version references), your subsequent queries see the latest state and their edits.
Read a specific version state
If you want to query a specific state of the Default version and don't want the state to change while you are querying, execute the setcurrentversion stored procedure. This procedure validates the supplied version name and sets the corresponding database state internally. If you execute setcurrentversion for the Default version, queries you make against Default always point to the state Default referenced when you executed the setcurrentversion stored procedure.
Execute setcurrentversion from an SQL client. The syntax is as follows:
CALL sde.setcurrentversion('<version_name>',?,?)
You can execute this stored procedure again to return the current state of the versioned table, as needed.
- Be sure there is a versioned view for the versioned feature class or table you want to access.
Beginning with ArcGIS 10.1, versioned views are created when you version data. If your data was registered as versioned prior to 10.1, you can create a versioned view by right-clicking the dataset in the Catalog tree in ArcMap, pointing to Manage, and clicking Enable SQL Access.
- Open a SQL client and call the setcurrentversion stored procedure to set the version to Default.
CALL sde.setcurrentversion('DEFAULT',?,?)The question marks indicate message code output and message output. Message code output and message output are the SQL codes and messages returned after you execute the procedure. You pass in question marks, and the code and message are returned to you.
- Issue a SELECT statement against the versioned view to read versioned data from the geodatabase.
In the following example, the versioned view is sightings_ev:
SELECT ID, SPECIES, REPORTER FROM SIGHTINGS_EV WHERE REPORTER = 'chuck'
Read a version other than Default
You can also execute the setcurrentversion stored procedure to query a version other than Default. This procedure validates the version name you specify and sets the corresponding version state internally. Queries you make against the version always point to the state the version referenced when you executed the setcurrentversion stored procedure.
This procedure can be called again to change to other versions as required and can be called each time the workspace is refreshed to return the current state of the versioned table to the calling application.
The following steps show how to run setcurrentversion to query a specific version other than Default:
- Be sure there is a versioned view for the versioned feature class or table you want to access.
Versioned views are created when you register a table, feature class, or feature dataset as versioned. If your data was versioned prior to ArcGIS 10.1, however, you can create a versioned view by right-clicking the dataset, pointing to Manage, and clicking Enable SQL Access.
- Open a SQL client and call the setcurrentversion stored procedure to set the version you want to query.
In the following example, field_inspections is set as the version to be queried:
CALL sde.setcurrentversion('FIELD_INSPECTIONS',?,?)The question marks indicate message code output and message output. Message code output and message output are the SQL codes and messages returned after you execute the procedure. You pass in question marks, and the code and message are returned to you.
- Issue a SELECT statement against the versioned view to read versioned data from the geodatabase.
In the following example, the versioned view is code_ev:
SELECT owner, site_address, region FROM code_ev WHERE region = 'b'
If you need to return to querying the current state of the Default version, execute the set_default procedure.
CALL sde.set_default;