External data sources 1s 8.2. External data sources. Example connection string

25.10.2023 -

To work with non-1C 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 technology platform, starting with version 8.2.14.

In this article, let's look at how this object interacts with the Excel file we are used to. For example, there is a need to read data from a *.xls file into a 1C database at some intervals. 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 Excel.

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

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

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

All! This is actually the whole setup. Next, 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 2007 and later)

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

Connection string for XLS and earlier files.

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 ButtonExecutePress(Button)ConnectionParameters = NewConnectionParametersExternalDataSource; ( Connection parameters. ConnectionString= “Driver= ( Microsoft Excel Driver (*. xls) ) ; DriverID= 790 ;

DBQ= D:\fruit. xls;

External Data Sources. Excel. SetGeneralConnectionParameters(ConnectionParameters) ;

External Data Sources. Excel. Establish a connection() ; Request = New Request() ;

Request. Text = "SELECT |

DataTable. Code, |

DataTable. Nomenclature, |

DataTable. Unit, |

DataTable. Quantity | FROM | ExternalDataSource. Excel. Table. DataTable AS DataTable";

TK = Request. Execute ) . Unload() ;

In version 8 of the 1C program, the developers added to the functionality the ability to connect third-party databases and obtain information from them directly from the configurator, without using COM connections and OLE objects. This feature is implemented using a new object - “External Data Sources”

External data sources in 1C can be used in the same way as other tables in the system:

When creating reports and calculations using a data composition system (DCS);
To obtain links to information stored in third party sources;
To change data stored in tables;
When generating requests.

It is important to know that this mechanism is not designed to work with other 1C databases, since the 1C.Enterprise operating model itself does not involve interfering with data at the level of physical tables.

Creating a new source

Adding a new external source to the program occurs in the “Configurator” mode. There is a corresponding branch in the configuration tree (Fig. 1)

You will have to work hard when creating a new source, despite the fact that the form of the new object has only four tabs:

Basic;
Data;
Functions;
Rights.

The first tab has only one interesting parameter - the lock control mode. If you don’t have any questions about blocking data in transactions or the intricacies of parallelizing information flows, you can leave this option in automatic blocking mode. However, such an approach can lead to excessive restrictions (for example, when, instead of a separate record, the program locks the entire physical table, depriving other users of the ability to work with it).

Managed locks, unlike automatic ones, use the transaction mechanism inherent in the program itself, and not in the DBMS, which allows table captures to be transferred to a much lower level.

By setting this parameter to “Automatic and Managed,” we provide the system with the ability to determine which mode to use by directly accessing a similar property for each specific table.

“Data” tab of the external source properties form

The form of the “Data” tab is shown in Fig. 2

Rice. 2

Here we can add external source tables and cubes. There are two ways to add a table:

Manually, then the form for adding a table will open in front of us (Fig. 3);

Rice. 3

Or select from the list of physical source tables (Fig. 4), in which case a special constructor opens in front of us.

Rice. 4

Let's take a closer look at the form for adding a table. The “Name” property is used to uniquely identify an object in the configuration.

The comparison of the metadata object and the final physical table occurs through the “Name in data source” property located on the “Advanced” tab (Fig. 5)

Rice. 5

Next we must determine the type of the table, or rather its objectivity. If the data stored in a structure can be uniquely identified through any one field, the table can be an object one. If the individuality of a record is determined by a set of key fields, the table must have a non-object type.

Comparing such tables with other metadata objects, the following analogy can be given:

Object tables are reference books;
Non-object ones are information registers.

The set of key fields is defined in the next form parameter (“Key Fields”). This field is required; if you leave it blank, saving the configuration will fail.

As can be seen from Fig. 5, some fields and buttons of the form are not editable:

Expression in data source;
Table data type;
Presentation field;
View handlers.

They can be used only after we fill out the table fields, defining their type and assigning identifiers to them (Fig. 6)

Rice. 6

Here you should pay attention to the “Allow Null” parameter; if this checkbox is checked, it is not advisable to use such a field as a key.

Table builder

Perhaps the most important and interesting point in working with external sources is creating a connection string. Its constructor opens if you click the button with three dots next to the “Connection string” parameter.

First of all, we will be asked to decide on the driver that will be used for connection (Fig. 7)

Rice. 7

Incorrect definition of this parameter will not allow you to connect to a third-party infobase. You should also understand that not all drivers specified in the drop-down list can be used to automatically generate a connection string. If the platform generates an error (Fig. 8), then the connection string will have to be entered manually.

Fig.8

Rice. 9

The line itself is a strictly regulated construction.

Example connection string

Let's consider a third-party database created in Microsoft Access and located in the root of drive D. To connect this database, we must use the appropriate driver, but selecting it in the row constructor results in the error Fig. 8.

We'll set up the connection parameters ourselves.

Driver=(Microsoft Access Driver (*.mdb)) – this is what the first part of the line looks like. In curly braces we defined the driver.

For Excel files it will look like (Microsoft Excel Driver (*.xls)), for Excel files created in an office older than 2003, the driver line will look like (Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)).

Separating this parameter from the next one with a comma, we must enter the address of our storage (in our case DBQ=D:\Database1.mdb).

Adding these two parameters, we get Driver=(Microsoft Access Driver (*.mdb));DBQ=D:\Database1.mdb. By writing this parameter, we get access to the internal structures of this database.

For the “External Source” object, it is not enough just to create it in the configuration; it also needs to be connected in the “Enterprise” mode. This can be done from the menu “All functions”->External sources. When we first enter our table, we need to enter the same connection string in the “Enterprise” mode.

Why is this opportunity of such interest? Any person who has programmed in 1C and is quite familiar with SQL and is at least generally familiar with the architecture and principles of development of other technology platforms for business applications will tell you with firm confidence what he likes most about 1C. Of course, the query builder is the most convenient and thoughtful mechanism for writing queries for retrieving data from relational structures that I have personally ever encountered. And now 1C has given us such a wonderful opportunity to use it not only with 1C, but also with any other tables. It’s just that this “barrel of honey” has a lot of “flies in the ointment”. First things first:

1) Setup and use- without “dancing with a tambourine” it won’t work
a) Add an external data source - it doesn’t seem complicated
b) check the “Select from list” checkbox - mandatory - this is necessary to check the functionality at the beginning and will save you from unnecessary troubles
c) - be sure to click "..." - the connection is ODBC. Not OLEDB as we are all used to, but one level lower

D) But here be VERY CAREFUL.

This is an ODBC driver - if you use the client-server version, it must be on the server. If you are developing on one system and working on another (as is usually the case), make sure there are no surprises waiting for you. A strange recommendation, but choose the oldest or most common driver if you are not particularly concerned about speed and do not intend to go beyond the capabilities of the SQL92 standard. This will give you better compatibility. For example, for SQL Server 2008, the best driver 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 (if using the file version), and the benefit is special for simple he won't give you any tasks.
e) Standard Server selection dialogs

E) I recommend answering “yes” to the question about saving the password, otherwise you won’t be able to start this business.
g) Select the table and details... a wonderful opportunity - you can immediately rename it as you like (and the details too), and in the properties you will see the names of the fields of the data source

Z) And now you launch it, open the query designer - stupidly select all the records from the table and OPA - an error. What to do? If you have a managed interface, look at the service menu, and if a regular one...
I personally used this code:
Code 1C v 8.x Parameters = ExternalDataSources.DAX.GetGeneralConnectionParameters();
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();
Some pieces may not be necessary, but it works.
You need to run the code ONCE. After which it will be normal to connect... mysticism of course - why this was necessary is not clear...

2) Read-only data sources- Yes, miracles don’t happen... but sometimes you want it...

3) THEY CANNOT BE USED TOGETHER WITH INTERNAL DATA SOURCES
Personally, this fact killed me on the spot.

How can this be so... what we were waiting for and already imagined and licked our lips about how we will now combine our data with 1C in one request, collapse it - group it, insert it into the report, but that’s not the case...
But of course this doesn’t stop experienced people... what thought came to mind? That's right - temporary tables:

4) THEY CANNOT BE USED TOGETHER WITH TEMPORARY TABLES

But this no longer looks like technological difficulties, but looks a lot like what they want us to do “so that life doesn’t seem like paradise” :).

5) Can only be used in ACS connections
For those who don’t know, this is in the ACS on the “Data Set Links” tab. Do you use them often? Comfortable? Apparently they want to force us to use them more often. Only there is a column “Communication condition” and “Communication parameter”. I haven’t found an example of their use in any standard configuration, and somehow everything is not transparent in the documentation and in Khrustaleva’s work either. Can anyone explain to me how the "connection condition" works. If you write Source Attributes = Receiver Attributes 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 doesn’t work out very easily.

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

DateStart = SettingsComposer.Settings.DataParameters.Items.Value;
DataCon = SettingsBuilder.Settings.DataParameters.Items.Value;
If DataCon > "20100101" Then
DataCon = "20100101";
endIf;

CN = New QualifiersNumbers(15,2);
KS = 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 = New DescriptionTypes(ArrayNumber, CN);
TypeString = New TypeDescription(ArrayString, KS);
TypeDate = New TypeDescription(ArrayDate);

//table for loading data from SQL
TZ = New ValueTable();
TK.Columns.Add("NomenclatureCode", TypeString);
TK.Columns.Add("Qnty", TypeNumber);
TK.Columns.Add("Period", DateType);

TK.Indices.Add("Period");

//Connect to SQL
Connection String = "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(AbbrLP(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();
Exception
Return of technical specifications;
EndAttempt;

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

Request = New Request();
VrTable = New TemporaryTableManager();
Query.SetParameter("vrDataTable", TK);
Query.SetParameter("Start Date", Start Date);
Request.SetParameter("DataCon", DateCon);
Query.Text = "SELECT
| vrDataTable.NomenclatureCode,
| vrDataTable.Qnty,
| vrDataTable.Period
|Place DataTable
|FROM
| &vDataTable AS vrDataTable
|WHERE
| vrDataTable.Period >= &DateStart
| And vrDataTable.Period<= &ДатаКон";
Request.Run();
TZ = Undefined;

Request = New Request;
Query.TemporaryTableManager = VrTable;
Query.Text = "Here is a query involving a variable table";

Result = Query.Run();
Return Result;

EndFunction

ExternalSet = InitializeDataSource();
DataSet = new Structure();
DataSet.Insert("SQL Table", ExternalSet);
TypicalReports.GenerateTypicalReport(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 designer, and give only the DATA COMPOSITION function to the ACS

But it certainly doesn’t look as nice... and every time you need to write code to upload values into a table and check whether you made a mistake in the name of the details... and 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 prompted you to them.

Information taken from the site

Release 8.2.14.533 is finally a more or less stable version of the 14th release of the platform. Finally, the opportunity presented itself to try out a wonderful opportunity - “external data sources”.

1) Setting up and using - without “dancing with a tambourine” it won’t work

A) Add an external data source - it doesn’t seem complicated
b) check the “Select from list” checkbox - mandatory - this is necessary to check the functionality at the beginning and will save you from unnecessary troubles
V)
(IMG:http://pics.livejournal.com/comol/pic/0000cr1r.png)
- be sure to click "..." - the connection is ODBC. Not OLEDB as we are all used to, but one level lower

But be here VERY CAREFUL.

E) Standard Server selection dialogs

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

Parameters = ExternalDataSources.DAX.GetGeneralConnectionParameters();
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();

Some pieces may not be necessary, but it works. You need to run the code ONCE. After which it will be normal to connect... mysticism of course - why this was necessary is not clear...

2) Read-only data sources
Yes, miracles don’t happen... but sometimes you want it that way....

3) THEY CANNOT BE USED TOGETHER WITH INTERNAL DATA SOURCES
Personally, this fact killed me on the spot.

How can this be... what we were waiting for and already imagined and licked our lips about how we will now combine our data with 1C in one request, collapse it - group it, insert it into the report, but that’s not the case...

But of course this doesn’t stop experienced people... what thought came to mind? That's right - temporary tables:

4) THEY CANNOT BE USED TOGETHER WITH TEMPORARY TABLES

But this no longer looks like technological difficulties, but looks a lot like what they want us to do “so that life doesn’t seem like paradise” (IMG:).

5) Can only be used in ACS connections

For those who don’t know, this is in the ACS on the “Data Set Links” tab. Do you use them often? Comfortable? Apparently they want to force us to use them more often. Only there is a column “Communication condition” and “Communication parameter”. I haven’t found an example of their use in any standard configuration, and somehow everything is not transparent in the documentation and in Khrustaleva’s work either. Can anyone explain to me how the "connection condition" works. If you write Source Attributes = Receiver Attributes 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 doesn’t work out very easily.

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

Function InitializeDataSource()

DateStart = SettingsComposer.Settings.DataParameters.Items.Value;
DataCon = SettingsBuilder.Settings.DataParameters.Items.Value;
If DataCon > "20100101" Then
DataCon = "20100101";
endIf;

CN = New QualifiersNumbers(15,2);
KS = 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 = New DescriptionTypes(ArrayNumber, CN);
TypeString = New TypeDescription(ArrayString, KS);
TypeDate = New TypeDescription(ArrayDate);

//table for loading data from SQL
TZ = New ValueTable();
TK.Columns.Add("NomenclatureCode", TypeString);
TK.Columns.Add("Qnty", TypeNumber);
TK.Columns.Add("Period", DateType);

TK.Indices.Add("Period");

//Connect to SQL
Connection String = "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(AbbrLP(ConnectionString));
Command.ActiveConnection = Connection;
Command.CommandText = "Select * from PH where period >= "" + String(Format(DateStart, "DF=yyyyMMdd")) + "" and period RecordSet = Command.Execute();
RecordSet.MoveFirst();
Exception
Return of technical specifications;
EndAttempt;

Request = New Request;
Query.TemporaryTableManager = VrTable;
Query.Text = "Here is a query involving a variable table";

Result = Query.Run();
Return Result;

EndFunction

[you must register to view the link]

External data sources 1s 8.2. External data sources. Example connection string

Request. Text = "SELECT |

DataTable. Quantity | FROM | ExternalDataSource. Excel. Table. DataTable AS DataTable";

Creating a new source

“Data” tab of the external source properties form

Table builder

Example connection string

Recent Entries