Editing nonversioned geodatabase data in Informix using SQL

You can use SQL to update, insert data into, and delete data from nonversioned tables in the geodatabase if they do not participate in geodatabase behavior. See What type of data can be edited using SQL? for information on the types of data and geodatabase behavior you cannot edit with SQL.

Note:

If the nonversioned table you want to edit using SQL is enabled for archiving, you must edit the table's archive view rather than the table itself. The view will automatically update certain ArcGIS-maintained fields. See What is an archive view and it's related topics for more information.

All data that is registered with the geodatabase has a system-maintained, unique, not-null ObjectID (Row ID) field. When you use SQL to insert records into nonversioned tables in the geodatabase, you must provide a unique value for the ObjectID. You can use the Next_Row_ID function to insert a value to the ObjectID field using SQL.

This set of instructions describes updating one row at a time in a nonversioned table and inserting an ObjectID. You would most likely write a routine or client program to retrieve ObjectIDs and update your data.

1. Log in to the database from an SQL editor, such as I-SQL.

   Be sure to log in to the database as a user who has permission to edit the data.

2. Query the table_registry table to find the registration ID and owner of the table into which you want to insert a row.

   In this example, the registration ID and owner name for the factories table is returned.

   SELECT owner,registration_id,table_name
    FROM sde.table_registry
    WHERE table_name='factories';
   
   owner    registration_id    table_name
   editor1         7           factories
   

3. Log in to execute the next_row_id function and get the next available row ID value.
4. Execute the next_row_id function. This function is owned by the sde user.

   In this example, editor1 is the table owner, and 7 is the registration ID of the factories table.

   EXECUTE FUNCTION "sde".next_row_id('editor1',7);
   
   ret_code 0
   err_msg
   rowid 18
   
   1 row(s) retrieved.
   

5. Go back to the SQL editor and insert a record to the table.

   INSERT INTO factories
    VALUES (
     18,
     'makem'
     ST_PolyFromText('POLYGON((52 18,66 23,73 9,48 6,52 18),(59 18,67 18,67 13,59 13,59 18))',4326)
    );
   

If the table also contains a GUID or Global ID field, you must provide a unique value for this field as well. See Inserting a value into a Global ID column in Informix using SQL for more information.