External data sources 1c 8.2. External data sources. Can only be used in SKD connections

In platform version 8.3.5.1068 (and later), it became possible to add, modify, and delete data in external sources software tools 1C. Examples of this feature are presented in this article.

In order to make recording to external sources possible, 1C has added new properties to data tables and fields of external sources:

For the whole table - property Only reading. ReadOnly = True means that changing the data in this table is not possible;
For individual table fields - properties Only reading, AllowNull And Fill value:
- ReadOnly = True means that changing the data in this field is not possible;
- AllowNull = True means that a value can be written to this field NULL;
- Fill value contains the default value of this field (if one exists).

You (when describing tables manually) or the platform (when creating tables with the designer) can use these properties in the following way.

ReadOnly = True set, for example, for views (view), tables obtained on the basis of an expression (function result) and the like. Data in such tables cannot be changed;
ReadOnly = True specify for fields set automatically ( AUTOINCREMENT), calculated fields, and the like. The data in these fields cannot be changed;
AllowNull = True set for all fields, except for key fields, and those that are described in an external source as NOT NULL;
Fill value fields to set in the case when the standard value of this field is specified in the external source (value DEFAULT).

You can add, modify, and delete data in external sources using the built-in language or interactively. In the built-in language, the following methods of the table manager are used for this:

Create RecordSet()- for non-object tables;
New method CreateObject()- for object tables.

Accordingly, the objects ExternalDataSourceTableRecordSet And ExternalDataSourceTableObject new methods emerged Write() And Delete().

Adding data

When you add data to an external source, you create an object (or recordset), set field values, and write. However, there are some features that are useful to know about.

For example, when trying to set the value of a field that has ReadOnly = True, an error will be thrown. And when writing directly to the database in the expression INSERT such fields will be skipped. The rest of the fields are filled with the values you assigned them. Therefore, the values Null and default values must be assigned to fields explicitly.

id(AllowNull = True);
name(AllowNull = True);

mCharacteristic = ExternalDataSources.IM.Tables.shop_feature.CreateObject(); mCharacteristic.id = Code; mCharacteristic.name = Name; mCharacteristic.Write();

Statement execution Write() will cause the event handler to be called first Before Recording, then a physical write is made to the external source table ( INSERT), then the event handler will be called When Recording.

With the key field of the external source table, you can do the following. If the key field is editable, then you "manually" set its value before writing. If changing the key field is prohibited, then the platform will independently obtain the key in INSERT or immediately after. You can intervene in this process with the method SetReferenceNew() before the physical record (in the event handler Before Recording) or directly after the physical entry (in the event handler When Recording).

Change data

When data is changed, the values of all fields of the table that have ReadOnly = False.

MFeature = ExternalDataSources.IM.Tables.shop_feature.FindByField("id",code); mObject = mCharacteristic.GetObject(); mObject.name = Name; mObject.Write();

If it is necessary to record only some fields, you can specify their list directly from the built-in language using the methods SetWritableFields() And GetWritableFields().

Deleting data

Deleting data directly deletes a row from a database table. At the same time, references to the deleted object are not searched. If such functionality is needed, you can program it yourself in the event handler BeforeDelete().

MFeature = ExternalDataSources.IM.Tables.shop_feature.FindByField("id",Code); mObject = mCharacteristic.GetObject(); mObject.Delete();

Transactions

Reading data from external sources, as before, is performed outside of a transaction, and when writing, the platform opens an implicit transaction. At the same time, you can perform both reading and writing in explicit transactions using object methods ExternalDataSourceManager:

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

Locks

Auto;
Managed;
Automatic and controlled.

as well as the external source table property Isolation LevelTransactions:

In addition, you can independently set the level of locks in the method StartTransaction().

I supplemented the typical article with examples) There will be more time - I will add more examples.

Release 8.2.14.533 - finally a more or less stable version of the 14th release of the platform. Finally, there was a chance to try out a wonderful opportunity - "external data sources".

Why is this opportunity of such interest? Any person who has programmed in 1C is quite familiar with SQL and at least in general terms familiar with the architecture and principles of developing other technological platforms for business applications will tell you with firm certainty what he likes the most in 1C. Of course, the query builder is the most convenient and thoughtful mechanism for writing queries for obtaining data from relational structures that I have personally ever met. And now 1C has given us such a wonderful opportunity to use it not only with 1C, but also with any other tables. Here are just a bunch of "fly in the ointment" poured into this "barrel of honey". Everything in order:

1) Setting up and using - without "dancing with a tambourine" it will not work

A) Add an external data source - it seems nothing complicated
b) check the box "Select from the list" - it is necessary - this is necessary to check the performance at the beginning and save you from unnecessary troubles
in)
(IMG:http://pics.livejournal.com/comol/pic/0000cr1r.png)
- be sure to press "..." - the connection is ODBC. Not OLEDB as we are all used to, but one level lower

And be here VERY ATTENTIVE.

This is an ODBC driver - if you use the client-server version, it must be on the server. If you're developing on one system and working on another (as is usually the case), make sure you don't get any surprises. A strange recommendation, but choose the oldest or most common driver if you are not particularly concerned about speed and you do not intend to go beyond the limits of the SQL92 standard. This will give you the best compatibility. For example for SQL Server 2008 the best driver there will be SQL Server Native Client 11, but I recommend choosing just SQL Server, otherwise this very native client will have to be installed either on the server or on all client machines (in the case of using the file version), and it will not give much benefit for simple tasks.

E) Standard Server selection dialogs

G) Choose a table and details ... a great opportunity - you can immediately rename it as you like (and details too), while in the properties you will see the names of the data source fields

Parameters = ExternalDataSources.DAX.GetCommonConnectionParameters();
Parameters.AuthenticationStandard = true;
Parameters.UserName = "sa";
Parameters.Password = "pas";
Parameters.ConnectionString = "DRIVER=(SQL Server);SERVER=servet;UID=sa;PWD=;DATABASE=database";
Parameters.DBMS = "MSSQLServer";

ExternalDataSources.DAX.SetCommonConnectionParameters(Parameters);
ExternalDataSources.DAX.SetUserConnectionParameters(UserName(), Parameters);
ExternalDataSources.DAX.SetSessionConnectionParameters(Parameters);

ExternalDataSources.DAX.SetConnection();

Maybe some pieces are not needed, but it works. Need to run code ONCE. After that, it will connect normally ... mysticism of course - why it was necessary is not clear ...

2) Read-only data sources
Yes, miracles do not happen ... but sometimes you want to ....

3) DO NOT USE THEM TOGETHER WITH INTERNAL DATA SOURCES
Personally, this fact killed me on the spot.

But of course, this does not stop experienced people ... what thought came to mind? That's right - temporary tables:

4) DO NOT USE THEM TOGETHER WITH TEMPORARY TABLES

But this no longer looks like technological difficulties, but it looks very much like what they want us to do "so that life does not seem like a paradise" (IMG :) .

5) Can only be used in SKD connections

For those who don't know, it's in the ACS on the "Dataset Links" tab. How often do you use them? Conveniently? Apparently they want to force us to use them more often. But there is a column "Link condition" and "Link parameter". I did not find an example of their use in any typical configuration, in the documentation and in Khrustaleva, too, somehow everything is not transparent. Can anyone explain to me how the "link condition" works. If you write SourceAttribute = ReceiverRevision there, it does not work. Of course, the condition can be written in the "Expression" field - in most cases this is enough ... but somehow it's not very easy.

In total, this problem was previously solved somewhere like this:

Function InitializeDataSource()

DateStart = BuilderSettings.Settings.DataParameters.Items.Value;
DateCon = BuilderSettings.Settings.DataParameters.Items.Value;
If DateCon > "20100101" Then
DateCon = "20100101";
EndIf;

QN = NewQualifiersNumber(15,2);
cs = NewStringQualifiers(255);

ArrayNumber = New Array();
ArrayNumber.Add(Type("Number"));

ArrayString = New Array();
ArrayString.Add(Type("String"));

ArrayData = New Array();
ArrayDate.Add(Type("Date"));

//We will fill in the accounting cost in the table
TypeNumber = NewTypeDescription(ArrayNumber,KN);
StringType = NewTypeDescription(ArrayString, CS);
TypeDate = NewTypeDescription(ArrayDate);

//table to load data from SQL
TK = New ValueTable();
TK.Columns.Add("NomenclatureCode", TypeString);
TK.Columns.Add("Qnty", TypeNumber);
TK.Columns.Add("Period", TypeDate);

TK.Indices.Add("Period");

// Connect to SQL
ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Pwd=;Data Source=;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Reports";
Connection = New COMObject("ADODB.Connection");
Command = New COMObject("ADODB.Command");
RecordSet = New COMObject("ADODB.RecordSet");
Date = "";
Attempt
Connection.Open(ShortLP(ConnectionString));
Command.ActiveConnection = Connection;
Command.CommandText = "Select * from PH where period >= "" + String(Format(DateStart, "DF=yyyyMMdd")) + "" and period RecordSet = Command.Execute();
RecordSet.MoveFirst();
An exception
Return of TK;
End of Attempt;

While RecordSet.EOF = False Loop
String = TK.Add();
String.NomenclatureCode = RecordSet.Fields(RecordSet.Fields.Item(1).Name).Value;
String.Qnty = RecordSet.Fields(RecordSet.Fields.Item(12).Name).Value;
String.Period = RecordSet.Fields(RecordSet.Fields.Item(13).Name).Value;
RecordSet.MoveNext();
EndCycle;

Request = New Request;
Query.TempTableManager = VrTable;
Query.Text = "Here is a query involving a temp table";

Result = Request.Run();
Return Result;

EndFunctions

OuterSet = InitializeDataSource();
DataSet = New Structure();
DataSet.Insert("SQL Table", ExternalSet);
GenericReports.Generate GenericReport(ThisObject, Result, Decryption Data, OutputToReportForm,Dataset);

Actually, there are not many lines of code and they are quite standard ... in this case, you can use the full functionality of the query builder, and give only the DATA COMPOSITION function in the ACS

But, of course, it looks a little not so beautiful ... and uploading to the table of values \u200b\u200bevery time you need to write code and check if you made a mistake in the name of the details ... otherwise what we were given in 1C looks somehow half-hearted. I have not yet decided which is more convenient to use. You decide, and write about your decisions and what pushed you to make them.

[you need to register to view the link]

To work with non-1s data, for example: a SQL database, an Access database, an Excel file, and many others, a mechanism (object) called External Data Sources was introduced into the technological platform, starting from version 8.2.14.

In this article, let's look at how it interacts given object with the usual Excel file. For example, there is a need to read data from a *.xls file into the 1C database with some periodicity. If earlier we needed to initialize using Com objects, then create a table of values, read data line by line, now these manipulations simply disappear. When using an External Data Source, we can query and retrieve data instantly.

Let's say we have an Excel workbook with the following content. From which we need to read data.

First of all, in the configurator mode, add a new data source and call it also Excel.

Now we need to add data, then a table with the fields that we will have in 1C. Let's create them in the same way as the columns in the file are named. To do this, go to the created object and in the Data tab, click add. Here I strongly recommend that you create it manually, since quite often there are problems when 1s-ina tries to automatically configure itself.

Let's assign the name of our table to DataTable. After that, go to the Data tab and create fields (similar to a file).

In the properties of each field, you must specify the Name, Type and Name in the data source (identical to the file).
We assign a Name in the data source. Here we specify the name of the sheet in *. xls with "$" at the end. We have List1$.
Be sure to specify the Key Fields. Here, from the database theory, it is necessary to specify the field by which the string will be completely unique.
Representation field - specify the field that will provide a brief representation of the string, in our case the Nomenclature.

Everything! That's actually the whole setup. Then you just need to specify the connection string in any procedure, and after connecting, you can work with the data source.

Connection string for XLSX files (Office version 2007 and later)

Driver=(Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb));DBQ=D:\FileExcel.xlsx;

Connection string for XLS files and earlier.

Driver=(Microsoft Excel Driver (*.xls)); DriverID=790; DBQ=D:\FileExcel.xls;

Let's create a new external processing that will connect and report the connection.

Procedure ButtonPerformPress(Button) ConnectionParameters = New ConnectionParametersExternalDataSource; Connection Options. ConnectionString="Driver=( Microsoft Excel Driver (*. xls) ) ; DriverID=790 ; DBQ= D:\fruit. xls; "; ExternalDataSources. Excel. SetGeneralConnectionParameters(ConnectionParameters) ; ExternalDataSources. Excel. Establish a connection() ; Request = New Request() ; Inquiry. Text = "SELECT | DataTable. Code, | DataTable. Nomenclature, | DataTable. Unitism, | DataTable. Quantity | FROM | ExternalDataSource. Excel. Table. DataTable AS DataTable"; TK = Request. Run ( ) . Unload() ; Notify("Connected"); EndProcedure

If we set a breakpoint and see what we have in the TK, then we will see the following:

P.S. In the very Excel file there must not be any blank lines before the field names, otherwise an odbc error will occur. sqlstate 07002 (excel odbc driver too few parameters).

1) Setup and use- without "dancing with a tambourine" will not work
a) Add an external data source - it seems nothing complicated
b) check the box "Select from the list" - it is necessary - this is necessary to check the performance at the beginning and save you from unnecessary troubles
c) - be sure to press "..." - the connection is ODBC. Not OLEDB as we are all used to, but one level lower

D) Be VERY CAREFUL here.

This is an ODBC driver - if you use the client-server version, it must be on the server. If you're developing on one system and working on another (as is usually the case), make sure you don't get any surprises. A strange recommendation, but choose the oldest or most common driver if you are not particularly concerned about speed and you do not intend to go beyond the limits of the SQL92 standard. This will give you the best compatibility. For example, for SQL Server 2008, the best driver would be SQL Server Native Client 11, but I recommend choosing just SQL Server, otherwise this very native client will have to be installed either on the server or on all client machines (in the case of using the file version), and the gain is special for simple he won't give you a job.
e) Standard Server selection dialogs

E) I recommend answering “yes” to the question of saving the password, otherwise it will not work to start this business.
g) Choose a table and details... a great opportunity - you can immediately rename it as you like (and the details too), while in the properties you will see the names of the data source fields

H) And now you run, open the query designer - you stupidly select all the records from the table and the OPA - an error. What to do? If you have a managed interface, look in the service menu, and if you have a normal one...
I personally used this code:
Code 1C v 8.x Parameters = ExternalDataSources.DAX.GetCommonConnectionParameters();
Parameters.AuthenticationStandard = true;
Parameters.UserName = "sa";
Parameters.Password = "pas";
Parameters.ConnectionString = "DRIVER=(SQL Server);SERVER=servet;UID=sa;PWD=;DATABASE=database";
Parameters.DBMS = "MSSQLServer";

ExternalDataSources.DAX.SetConnection();
Maybe some pieces are not needed, but it works.
You need to execute the code ONCE. After that, it will connect normally ... mysticism of course - why it was necessary is not clear ...

2) Read-only data sources- Yes, miracles do not happen ... but sometimes you want to ....

3) DO NOT USE THEM TOGETHER WITH INTERNAL DATA SOURCES
Personally, this fact killed me on the spot.

How is it .... what we have been waiting for and have already imagined and licked how we now in one request will combine our data with 1C, turn it off - group it, insert it into the report, but it wasn’t there ...
But of course, this does not stop experienced people ... what thought came to mind? That's right - temporary tables:

4) DO NOT USE THEM TOGETHER WITH TEMPORARY TABLES

But this no longer looks like technological difficulties, but it looks very much like what they want us to do "so that life does not seem like a paradise" :).

5) Can only be used in SKD connections
For those who don't know, it's in the ACS on the "Dataset Links" tab. How often do you use them? Conveniently? Apparently they want to force us to use them more often. But there is a column "Link condition" and "Link parameter". I did not find an example of their use in any typical configuration, in the documentation and in Khrustaleva, too, somehow everything is not transparent. Can anyone explain to me how the "link condition" works. If you write SourceAttribute = ReceiverRevision there, it does not work. Of course, the condition can be written in the "Expression" field - in most cases this is enough ... but somehow it's not very easy.

In total, this problem was previously solved somewhere like this:
Code 1C v 8.x Function InitializeDataSource()

DateStart = BuilderSettings.Settings.DataParameters.Items.Value;
DateCon = BuilderSettings.Settings.DataParameters.Items.Value;
If DateCon > "20100101" Then
DateCon = "20100101";
EndIf;

QN = NewQualifiersNumber(15,2);
cs = NewStringQualifiers(255);

ArrayNumber = New Array();
ArrayNumber.Add(Type("Number"));

ArrayString = New Array();
ArrayString.Add(Type("String"));

ArrayData = New Array();
ArrayDate.Add(Type("Date"));

//We will fill in the accounting cost in the table
TypeNumber = NewTypeDescription(ArrayNumber,KN);
StringType = NewTypeDescription(ArrayString, CS);
TypeDate = NewTypeDescription(ArrayDate);

//table to load data from SQL
TK = New ValueTable();
TK.Columns.Add("NomenclatureCode", TypeString);
TK.Columns.Add("Qnty", TypeNumber);
TK.Columns.Add("Period", TypeDate);

TK.Indices.Add("Period");

// Connect to SQL
ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Pwd=;Data Source=;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Reports";
Connection = New COMObject("ADODB.Connection");
Command = New COMObject("ADODB.Command");
RecordSet = New COMObject("ADODB.RecordSet");
Date = "";
Attempt
Connection.Open(ShortLP(ConnectionString));
Command.ActiveConnection = Connection;
Command.CommandText = "S_elect * fr om PH wh ere period >= "" + String(Format(DateStart, "DF=yyyyMMdd")) + "" and period<= "" + Строка(Формат(ДатаКон, "ДФ=ггггММдд")) + """;
RecordSet = Command.Execute();
RecordSet.MoveFirst();
An exception
Return of TK;
End of Attempt;

Request = New Request;
Query.TempTableManager = VrTable;
Query.Text = "Here is a query involving a temp table";

Result = Request.Run();
Return Result;

EndFunctions

Information taken from the site