Writing Data into an Arcsde Geodatabase ?

To understand SDE.

The ESRI Spatial Database Engine (SDE) is not a data format as commonly (and mistakenly) perceived.

In fact, like Safe’s Feature Manipulation Engine (FME), SDE is an Advanced Programming Interface (API) and is a form of middleware.

SDE was built in the 1990s to fill a gap in that common Enterprise Databases from: Oracle, Microsoft, Informix, Sybase etc. were not “spatial” (with the exception of Oracle).  There was no easy way in these non-spatial databases to read/write to these databases consistently and store the data in a way that made GIS applications easily be able interface with them and have relatively high performance through background requests for Eg. Retrieve all rows where the spatial object is within a certain display coordinate bounds, have spatial indices etc.  So in the mid 1990s, ESRI built ArcSDE (by purchasing the first version from a different developer and then expanding it) to fill this market gap, and allow them to market ArcGIS as something that could be deployed onto numerous different backend database systems and these applications use the same set of ArcSDE commands to read and write data to then, with ArcSDE taking care of the specific, typically SQL based, backend statements needing to be executed, and by default stored spatial geometry in a specific ESRI encoded BLOB binary object (again, Oracle was an exception being an early adopter of some early GIS conventions and already had their own spatial field data type and setting up SDE on Oracle you had a choice of using just a plain ESRI encoded BLOB or the Oracle binary encoded data type).

Fast forward to 2024, and virtually all main database systems natively support spatial data by storing it in Open Geospatial Consortium (OGC) based data standards, and similarly provide a fairly comprehensive set of native API spatial commands, typically through a SQL API, to query and write data, without needing SDE.

So now, typically most database DBAs no longer store spatial data in the older ESRI format BLOBs, and even Oracle eventually gave its users to not have to use their own flavor of spatial data type as well, and later versions of SDE similarly were adapted to be able also read/write to the native forms of spatial fields now readily available right through to Microsoft being one of the last to roll out their own spatial data type fields with SQL Server 2008.

Similarly, FME no longer “needs” to read/write data to these databases using the SDE API anymore.  This can be done directly through sending Spatial SQL commands directly to the database and cut-out SDE altogether, with FME designing its native Writers, Eg. SQL Server Spatial, to directly parse Features within the Writers to the native SQL commands required using Eg. CREATE TABLE … , DROP … , INSERT… , UPDATE … DELETE … etc.  This is often faster than reading/writing using  SDE as with SDE it needs to be separately loaded into memory, an ArcGIS licence checked out, and FME/GIS Application needs to parse a set of SDE commands, SDE to in turn parse this into backend database commands, execute them, interpret the message/warnings/errors generated in the backend into equivalent SDE messages/warnings/errors and pass these back to the calling application, which is all layers of extra overhead that slows it down.

SDE is recommended though when needing to interface with specific higher level ESRI objects Eg. Domain objects, or when you need SDE to constrain Eg. edits to not write data in a way that causes problems in the ArcGIS suite of software (for example ArcGIS can fail if write Points and Lines into the one backend table, even though this is permitted under OGC standards and other non-ArcGIS applications can handle this)

So knowing why SDE exists and what it does: Does FME need to do anything “special” before writing via the SDE interface?

Not really….SDE provides standard commands for manipulating spatial data to any application, like FME, that calls it.  FME provides it the spatial data to write, and specifies arguments as to which transaction format to run it in Eg. Called SDE writing with Versioned Data Transactions that in the backend will cause SDE to write data separately into delta version tables.

There are constraints though from ESRI’s 1980s based views on how spatial data should be stored Eg. Can’t store Points and Lines in the same table, and there should only be one Spatial data field per table, and should not have simple features stored as Aggregates etc. even though this flexibiltiy is acceptable in the wider GIS standards world.  The user then before writing data via SDE has to ensure that the Features being written conform to the ESRI spatial data specifications as FME will not filter for this before writing.

As for “compression”? Data storage compression is handled in the backend, and not SDE/FME, and is already partially compressed by feature data being encoded into a usually data efficient binary object by either FME or SDE before transmitting it for writing across the network interface.  There is then further encoding/compression of the data by the network interface itself as for any network traffic since that is how modern networks work and are able to transmit and receive the very high bandwidths of data they not able to do in the Gigabit/Terabit world we live in.

I will say though that SDE is very intolerant of network latency or “ping” as some would call it due to its design as being very “chatty” across the network between the computer executing SDE commands it checking the database return values/messages at every step so delays in these network packets of SDE generated messages can slow down SDE based reads/writes considerably vs a plainer native SQL client type interface that won’t ask for any data messages and passively after sending its requested Inserts, Updates, Deletes etc. just waits for the underlying database to return any messages, if any.  Hence latency can be a high bottle-neck to SDE data reads/writes (and why in my often high latency environment I avoid SDE where possible in Eg. FME Read/Writes)

@bwn  Thanks for ur explanation .

i have tried today to write into specific version by fme feature writer ,i want only to move point to specific geometry but i got error .

which parameters should i consider here ,to solve my error ?

I want only to update geometry of point to locate at other location .

feature class name : the table  that i want to update my point in it . 

warning : 

it is written ,Global id is not be editable 

object_id ist not editable 

but anyway ,i did not change any attributes 

Error :

Error running translation 

Other error 

when I changed preserve no to yes then I got this error 

Could not unregister GlobalID column 'GLOBALID' in table .The error number from ArcObjects is: '-2147212936'. The error message from ArcObjects is: {Unregister GlobalID column is not supported for versioned or replicated data.}

i am only testing with only only point that i have changed geometry in workspace and try to write again into specific version of sde .

could you advice me , how could I fix the problem ?

Thanks in advance .

Fme 2021

@bwn i got error running translation .when I write into version of sde ,transaction edit session .

Do you have any suggestion ? 

That’s very bad advice to give.

If you’re dealing with a versioned or archived feature class, you must write using SDE as the SDE interface manages the versioning and archiving of the data. If you write to the base table of these feature classes you can break the feature class resulting in data loss.

Generally, you’re fine to read using SQL, but if you’re reading a versioned FC make sure you’re not reading the base table and instead reading the view.

Writing should always be done using the SDE writer, especially so if you are not the one managing/maintaining/administrating SDE as configurations might change (ie implementing versioning). If you were writing directly to the SQL table and it was changed to versioning, you would have no indication that this change had been made. Your process would therefore be writing directly to the base tables and not via the A/D tables. At least if you’re using the SDE writer, the process will likely fail as the writing method will need to be updated

@jokerfme The easiest way to think of SDE is just a GDB on a database.

You’ll need to change your transaction type to match how the FeatureClass is configured
https://docs.safe.com/fme/html/FME-Form-Documentation/FME-ReadersWriters/geodatabase/GEODATABASE_SDE_writer.htm

Agreed you do need to be careful, and read the ESRI manuals, but we write into Versioned + Archived data all the time using SQL.  ESRI supports this through Versioned Views.

https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/overview-edit-versioned-data-with-sql.htm

Thanks a lot 

@hkingsbury 

Thanks  a lot 

but I did not get the reason  the following sentence :

If you’re reading a versioned FC make sure you’re not reading the base table and instead reading the view.

What is the difference between reading from a base table or a view?
Since I will write into a versioned feature class .

Should I write the output to the view or the base table ?