1c external data source connection string. External data sources

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:

1. When creating reports and calculations using a data composition system (DCS);
2. To obtain links to information stored in third party sources;
3. To change data stored in tables;
4. 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 imply interference 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:

1. Basic;
2. Data;
3. Functions;
4. 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:

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

Rice. 3

1. 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.

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

To make recording to external sources possible, 1C company added new properties to data tables and fields of external sources:

• For the entire table - property Only reading. ReadOnly = True means that changing the data in this table is impossible;
• For individual table fields - properties Only reading, AllowNull And Fill Value:
  • ReadOnly = True means that changing the data in this field is impossible;
  • 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 using a designer) can use these properties as follows.

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

You can add, change, and delete data in external sources using the built-in language or interactively. The built-in language uses the following table manager methods for this:

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

Accordingly, the objects ExternalDataSourceTableRecordSet And ExternalDataSourceTableObject new methods have appeared 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, if you try 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 remaining fields contain the values ​​that you assigned to them. Therefore the values Null and default values ​​must be assigned to fields explicitly.

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

mFeature = ExternalDataSources.TableIm.shop_feature.CreateObject(); mCharacteristic.id = Code; mCharacteristic.name = Name; mCharacteristic.Write();

Statement Execution Write() will cause the event handler to be called first BeforeRecording, then a physical write to the external source table is performed ( INSERT), then the event handler will be called WhenRecording.

You can do the following with the key field of the external source table. If a key field is changeable, then you “manually” set its value before writing. If changing the key field is prohibited, the platform will independently receive the key in INSERT or immediately after. You can interfere with this process using the method SetLinkNew() before the physical recording (in the event handler BeforeRecording) or immediately after the physical recording (in the event handler WhenRecording).

Changing data

When data changes, the values ​​of all table fields that have ReadOnly = False.

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

If you need to record only some fields, you can specify a list of them directly from the built-in language using the methods SetWritableFields() And GetWrittenFields().

Deleting data

Deleting data directly deletes a row from a database table. In this case, the search for references to the object being deleted is not performed. If such functionality is needed, you can program it yourself in the event handler BeforeDelete().

MFeature = ExternalDataSources.TableIm.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 Transaction Isolation Level:

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

I’ve added examples to the standard article) If I have more time, I’ll add more examples.

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

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

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

Z) 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:

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

While RecordSet.EOF = False Loop
Line = TK.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 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

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 haven't decided yet which is more convenient to use. You decide, and write about your decisions and what prompted you to them.

[you must register to view the link]

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.

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.