We have implemented the ability to add, modify and delete data in external sources. We need immediately to mention that we have created this mechanism to solve the problems of integration with other applications. Do not try to use it for replacing the «platform» mechanism of data storage in 1C applied solution.
You can edit the data both in software and interactively. In most cases, you do not need to program anything. Everything will work interactively, automatically, as well as, for example, when adding, deleting or modifying the catalogs and documents.

If you look «inside», in order to write in external sources, we have added new properties for data tables and external source fields:

• For entire table – property ReadOnly. ReadOnly = True means that the data can not be modified in this table;
• For individual table fields – properties ReadOnly, AllowedNull and FillingValue:
  • ReadOnly = True means that the data can not modified in this field;
  • AllowedNull = True means that the value NULL can be written in this field;
  • FillingValue contains the standard value of this field (if exists).

You (when describing tables manually) or platform (when creating tables in wizard) can use these properties as follows:

• Set ReadOnly = True, for example, for the views, tables obtained based on the expression (function result) and similar. The data in these tables can not be modified;
• Indicate ReadOnly = True for the fields that are set automatically (AUTOINCREMENT), calculated fields and similar. The data can not be modified in these fields;
• Set AllowedNull = True for all the fields, except the key fields and those that are described in external source as NOT NULL;
• Set FillingValue of the field only in case when the standard value of this field (DEFAULT value) is indicated in external source.

You can add, modify and delete data in external sources using the script or interactively. The script uses for this the following methods of table manager:

• CreateRecordSet() – for non-object tables;
• New method CreateObject() – for object tables.

Thereafter, the objects ExternalDataSourceTableRecordSet and ExternalDataSourceTableObject have new methods Write() and Delete().

Adding data

When you add data to an external source, you create an object (or record set), set the values of fields and save. In this case, there are some features which are useful to know.

For example, when you try to set the value of field which has ReadOnly = True, an error will occur. And with direct saving into the database these fields will be skipped in the expression INSERT. The rest of fields are written with the values that you have assigned to them. Therefore, the values Null and default values must be assigned explicitly.

Next is a small example. It appends data to the object table ExtTable which has three fields:

• col1 (ReadOnly = True);
• col2 (AllowedNull = True);
• col3 (AllowedNull = True).

Execution of operator Write() will lead to the fact that firstly the event handler BeforeWrite will be called, then a physical saving in external source table will be performed (INSERT), then the event handler OnWrite.

You can handle a key field of external source table as follows. If no key field is available to change, then you «manually» set its value before writing. If the change of key field is denied, then the platform will receive by itself the key in INSERT or immediately after it. You can intervene in this process using the method SetNewObjectRef() before physical writing (in the event handler BeforeWrite) or immediately after physical writing (in the event handler OnWrite).

Data modification

When changing data, the values of all table fields are updated which have ReadOnly = False.

If you need to write only some fields, you can specify their list directly from script using the methods SetFieldsToWrite() and GetFieldsToWrite().

Data deletion

When deleting data, a direct removal of row from database table is performed. In this case, the search of references to deleted object is not performed. If you need this functionality, you can program it yourself in the event handler BeforeDelete().

Interactive data modification

You can add, delete or change the data of external sources interactively, in the form. In this case, the logic of work will be similar to that described above. The difference is that during the interactive modification it becomes necessary for optimistic data locking.

If the external source table has a field which contains a data version, the platform executes an optimistic locking in the same way as when working with «native» data. Before writing it is checked that the data version, contained in RAM, is equal to the data version contained in external source. If it is not the case – an error is displayed. If the versions do not match – the data are written.

And if the external source table has no field which contains the data version, the optimistic locking is implemented as follows.

When opening the form, the data are read completely and saved on the server. On writing, the data are read from external source again and compared with those that are stored on the server. If there are differences – an error is displayed. If there are no differences, the form data (that are changed by the user) are combined with data saved on the server and written to external source.

Transactions

The data are read from external sources, as before, outside transaction, and on writing the platform opens an implicit transaction. In this case, you can perform both reading and writing in explicit transactions using the methods of object ExternalDataSourceManager:

• StartTransaction();
• CommitTransaction();
• CancelTransaction().

Locks

To manage the locks, we recommend to use the property of external source DataLockControlMode:

• Automatic;
• Managed;
• Automatic and managed.

as well as the property of external source table TransactionIsolationLevel:

In addition, you can set yourself the lock level in the method StartTransaction().