1c external data source connection string. External data sources. “Data” tab of the external source properties form

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 there are a lot of “flies in the ointment” poured into this “barrel of honey”. 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

and DB

f) 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

h) 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... 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; 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 * from PH where period >= "" + String(Format(StartDate, "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 >= &StartDate
| 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.
Author.

External data sources 1C - a relatively new metadata object 1C 8.3 and 8.2, with which it is possible to connect to 1C external data sources: SQL tables, Excel, Access, FoxPro (dbf), another 1C database, Oracle, Paradox (db), - and even reading from simple txt/csv files.

This gives many possibilities with other systems. Let's take a closer look.

Setting up external data sources in 1C 8

Setting up external sources is individual for each type of system. But, as a rule, the general part of the setup is the same - this is setting the connection string:

Get 267 video lessons on 1C for free:

If the connection string is specified correctly, the system will prompt you to select the desired table from the database. As a result, we will get a ready-made table in which we can specify the key field (a unique field) and the presentation field (how the record will be reflected in 1C):

Using external data sources in 1C 8.3

External data sources in 1C can be used like other database tables. The platform automatically generates a form for them if one is not specified. It is also possible to use data from external sources in queries.

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 - it won’t work without “dancing with a tambourine”

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.

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

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.

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; 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();
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 >= &StartDate
| And vrDataTable.Period Query.Execute();
TZ = Undefined;

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]

D) But here be VERY CAREFUL.

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";

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... 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” :).

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;

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 >= &StartDate
| 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

Information taken from the site

Work with them in lists in 1C Enterprise mode.

However, in work there is often a situation where part of the data is stored somewhere else.

Online store (usually stores data in an external MySQL/SQL database)
Another base.

To work with such data stored in other databases, special mechanisms need to be developed.

In version 1C 8.2.14, a new one has appeared called 1C External Data Sources, which greatly facilitate the programmer’s work, since:

now there is no need to create special mechanisms for obtaining data
such data can be accessed in the usual way
such data can be viewed in 1C lists.

External data source 1C – external SQL database

Let's say we have a SQL database in which the data we need is stored. Let's try to read data from it using the 1C External Data Source mechanism.

Let's add an external 1C data source. You need to go to the configurator, external data sources are located in the configuration window, at the very bottom of the tree.

1. Connection

Let's add a new external data source 1C, name it arbitrarily.

The database consists of tables. We need to add them inside the added external data source. Right-click on it and select Add Table.

The first time, it will prompt you to provide a connection string. It can be entered manually, or generated by clicking on the “…” button.

In our specific case, we will select “SQL Server” as the driver

Let's fill in the basic parameters for connecting to SQL. The server name can be entered or selected from the list.

1C will connect to SQL and offer to select a specific database from the list.

After this, 1C will display a list of tables in this database and their columns. You need to check the boxes to select the required tables.

Tables and columns will be added. The names will be the same as they are defined in the remote database. In 1C you can rename them (in properties).

Here is an example of the added table:

Here is an example of an added column:

In order for the 1C platform to work with an external table in the same way as it does with 1C directories, you can specify additional parameters in the table:

In the Key Field property, specify one of the columns that will provide a unique identification of the row; if several lines provide uniqueness, then this method does not work (analogous to the Code field)
In the Presentation field property – specify one of the columns that will provide a brief representation of the line (analogous to the Name field)
In the table data type property, specify Object data.

2. View

The connection to the remote base is not made automatically. To connect, you need to select a standard menu.

In the Standard branch there is a special command Managing external data sources, which allows you to specify connection parameters (specific to 1C Enterprise mode) and make a connection.

First you need to specify the parameters for connecting to the database.

When you made the settings in the configurator, it showed you the connection string as a result. You can see it again by clicking Add Table in the configurator again.

Copy the connection string and specify it in 1C Enterprise mode.

After this, you need to make the actual connection.

After the connection is made, it is possible to work with lists.

3. Use in 1C language

The connection can also be made from program code in 1C language.

Connection parameters are specified as follows:
ConnectionParameters = ExternalDataSources.SourceNameConfigurator.GetGeneralConnectionParameters();

ConnectionParameters.AuthenticationStandard = True;
ConnectionParameters.UserName = "sa";
ConnectionParameters.Password = "password";
ConnectionParameters.ConnectionString = “connection string from the configurator”;
ConnectionParameters.DBMS = "MSSQLServer";

ExternalDataSources.SourceNameConfigurator.SetGeneralConnectionParameters(ConnectionParameters);
ExternalDataSources.SourceNameConfigurator.SetUserConnectionParameters(UserName(), Parameters);
ExternalDataSources.SourceNameConfigurator.SetSessionConnectionParameters(Parameters);
ExternalDataSources.SourceNameConfigurator.SetConnection();

You can query data from a database using a regular . An example of a query text for an external source OurExternalSource and tables ExternalSource Table:

CHOOSE
ExternalSourceTable.FieldName
FROM
ExternalDataSource.OurExternalSource.Table.ExternalSourceTable"

External data source 1C - working with Excel file

Let's try another option - working with an Excel file through an external 1C data source.

Let's create a simple Excel file.

Let's add an external source, arbitrarily call it FileExcel. Let's add the table “Sheet1$” to it. As you can easily see, this is the name of the sheet in Excel with the addition of the “$” symbol.

As in the case of SQL, let's add columns. They can be added manually. It is important to ensure that the types of columns you add match, otherwise you may later receive an error like “Data type mismatch.”

For the column you need to specify a name in 1C and a name in the data source.

There is a feature for Excel (error like “Too few parameters. 3 required”):

If the first row of an Excel table contains column names, then you need to simply indicate the name of this column, for example “Code”.
Otherwise, you need to specify the full name with the table name “Sheet1$.Code”, but add “HDR=NO;” in the parameters.

The connection parameters for the Excel file look like this:

XLSX files (Office 2007 and later)
Driver=(Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb));DBQ=D:\FileExcel.xlsx;
XLS files (formerly)
Driver=(Microsoft Excel Driver (*.xls)); DriverID=790; DBQ=D:\FileExcel.xls;
You must specify your own name and path to the file.