Starting from version 8.3.5, the platform has implemented the ability to write data to tables of external data sources. This article discusses this functionality, which expands the existing possibilities for integrating configuration with third-party systems.
Applicability
The article discusses platform 1C 8.3.5. The material is also relevant for the current releases of the platform.
Writing to external data sources in "1C: Enterprise 8"
In the 8.2.14 platform, a new object was added to the metadata tree - external data sources. The purpose of this mechanism is to receive data from external databases in relation to 1C.
With the releases of new platform releases, external data sources have evolved, for example, it became possible to place them in temporary tables, perform joins with ordinary tables.
In this article, we will look at what tools the developer has for writing to external data sources.
All actions were performed on the Microsoft SQL Server 2008 R2 DBMS.
In the 8.3.4 platform, the ability to use the functions described in external data sources was implemented.
This feature allows you to run stored procedures on the SQL Server side, and with their help access data from external sources, including writing.
Let's look at an example. Let's create a new database with the name kursy_test using SQL Management Studio. All further experiments will be performed on it.
In this database, we will create the nomenklatura table, for this we will write a script with the following content:
USE [kursy_test]
GO
CREATE TABLE [dbo]. [nomenklatura] (
[id] [int] NOT NULL,
[description] [nvarchar] (150) NOT NULL,
[price] [numeric] (15, 2) NULL,
CONSTRAINT [PK_id] PRIMARY KEY ([id])
GO
As a result of execution, a table with the following structure will be created:
Now we need to create two stored procedures, with the help of which data modification will be performed.
We'll call the first procedure insert_nomenklatura. It will be designed to add a new row to the table. The script for its creation will look like this:
USE [kursy_test]
GO
CREATE PROCEDURE insert_nomenklatura
@id int,
@description nvarchar (150),
@price numeric (15, 2)
AS
BEGIN
INSERT INTO [kursy_test] .dbo. [nomenklatura] ([id], [description], [price])
VALUES (@id, @description, @price)
END
GO
The second procedure, named update_nomenklatura, will update an existing record in the table. To create it, the following script was run:
In Object Browser, the created table and the two stored procedures look like this:
We have completed all the preparatory steps on the Microsoft SQL Server side, now we go to the 1C: Enterprise 8 configurator.
Create a new external data source named Nomenklatura. When creating a new table in this data source, we specify the following parameters for connecting to the data source:
We will indicate the following connection string:
Driver = (SQL Server); Server = (local); Database = kursy_test; User Id = sa; Password =;
If the sa user has a password, it must be specified in the Password parameter of the connection string.
If all connection parameters are specified correctly, when you click on the OK button, a window will open containing the tables available in the data source:
We mark the fields of the table that are of interest to us. When the button is pressed Ready the dbo_nomenklatura table will be created in the external data source Nomenklatura:
Now we switch to the “Functions” tab of our data source.
Using a similar wizard, we add two stored procedures:
We get the following appearance of the "Functions" tab:
Now let's program the user interface to work with an external data source.
In the list form on the command panel, place the “Add” button with the following handler:
& OnClient
Procedure Add ()
Alert = New Description Alerts("OpenTableCompletion", ThisObject);
OpenForm (
"ExternalDataSource.Nomenklatura.Table.dbo_nomenklatura.ObjectForm",
,
,
,
,
,
Alert, ModeOpenWindowForms.LockAllInterface);
End of Procedure
& OnClient
Save Procedure (Command)
Save on Server();
EndProcedure & OnServer
Procedure Save on Server()
If Object.Ref.Empty() Then
ExternalDataSources.Nomenklatura.dbo_insert_nomenklatura
Otherwise
ExternalDataSources.Nomenklatura.dbo_update_nomenklatura
(Object.id, Object.description, Object.price);
EndIf;
End of Procedure
In enterprise mode, the list form looks like this:
The shape of the object is shown below:
Click on the image to enlarge.
Thus, using stored procedures, we have implemented writing to external data sources.
A new feature has appeared in the 8.3.5 platform - writing to external data sources directly, bypassing the mechanism of stored procedures discussed above.
The data can be edited both programmatically and interactively. And for our example, you won't have to resort to configuration.
In the command bars and in the "More" menu, you can see standard buttons such as "New", "Copy", "Modify", etc.
Click on the image to enlarge.
And in the form of the object there were buttons "Write" and "Write and close":
As you can see, now working with external sources is similar to working with directories, documents, etc.
Consider what changes have been made at the metadata level to make it possible to write to external data sources.
A new property has been added to the data table Only reading(type is boolean).
If this property is set to True, the platform cannot write data to this table.
The data table field now has the following properties:
- Only reading(type - boolean) - is it possible to change the data in this field;
- AllowNull(type - boolean) - whether it is possible to store the NULL value in this field.
Property Only reading should be set to True for auto-changing database fields, auto-generated key fields, calculated fields, etc.
You can add, modify, and delete data in external sources using the built-in language.
To do this, the objects ExternalDataSourceTableRecordSet and ExternalDataSourceTableObject new methods have been implemented Write () and Delete().
Let's look at an example of programmatically adding a new record for an external data source discussed above.
& OnClient
Procedure CreateProgrammatically(Command )
CreateProgrammaticallyOnServer();
EndProcedure & OnServer
Procedure CreateProgrammaticallyOnServer()
WritableObject=
ExternalDataSources.Nomenklatura.Tables.dbo_nomenklatura.CreateObject();
WritableObject.id=
5
;
WritableObject.description= "Wardrobe";
WritableObject.price=
5000
;
WritableObject.();
End of Procedure
In the object module of the table of an external data source, you can now set event handlers for write events, such as Before Recording (), When Recording () etc.:
In this article, two options for writing data to external data sources were considered - using stored procedures and using the new mechanisms of the 8.3.5 platform.
Thus, the platform now implements mechanisms for full integration with external applications.
In version 8.3.6, the functionality described above has been expanded with the help of new special methods GetMutableFields () and SetMutableFields (). With the help of them, it is possible to perform a write operation in those fields of the VIEW table that are marked in the configurator as read-only. Thanks to this, it became possible to implement such a scenario in which writing to individual fields of the TID table is possible only when it is necessary in accordance with the business logic of the application.
In version 8.3.7, the mechanism was improved, which determines which specific fields of the view tables can contain NULL values. Up to this point, all view tables could take on this value. This change is due to an increase in the speed of sorting in dynamic lists by these fields.
In revision 8.3.8, it is finally possible to determine if an external data source is in a transactional state. This functionality is introduced by the new method ExternalDataSourceManager.TransactionActive ()
In conclusion, we note that for external data sources, as shown above, data tables from the classical relational model are described. The platform, on the other hand, uses a different paradigm for working with data, offering the developer a certain set of types of applied objects (directories, documents, registers, etc.). That is why the system, when working with tables of external data sources, does not support most of the functionality that is inherent in its "native" objects. Therefore, it is recommended to refrain from developing any business logic, taking into account the use of VID tables, if this is not related to the tasks of integration with existing systems. Or, to put it simply, you should try to avoid storing data that is actively used in your application in some other table of the external system if it is not used by this system.
In the next article we will consider the logical continuation of the use of external data sources technology in the 1C: Enterprise system.
To work with data not 1c, for example: SQL database, Access database, 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 this object interacts with our familiar Excel file. For example, there is a need to read data from a * .xls file into the 1C database with some frequency. If earlier we needed to initialize using Com objects, then create a table of values, read the data line by line, now the manipulation data simply disappears. When using an External Data Source, we can make inquiries and receive 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 are named in the file. To do this, go to the created object and click Add in the Data tab. Here I strongly recommend creating it manually, as there are often problems when 1c-in tries to automatically configure itself.
We will assign the name of our table to DataTable. After that, go to the Data tab and create fields (similar to the 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 indicate the name of the sheet in *. xls with "$" appended at the end. We have Sheet1 $.
Be sure to indicate the Key Fields. Here, from the theory of the database, it is necessary to indicate the field by which will ensure the complete uniqueness of the string.
Presentation field - we indicate the field that will provide a short representation of the string, in our case the Nomenclature.
Everything! This is actually the whole setting. 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 2007 and higher)
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 Execute Button Pressing (Button) ConnectionParameters = New ExternalDataSourceConnectionParameters; Connection parameters. 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 | Data Table. Code, | Data Table. Nomenclature, | Data Table. Edizm, | Data Table. Quantity | FROM | ExternalDataSource. Excel. Table. Data Table AS Data Table "; TK = Request. Execute ( ). Unload (); Report ("Connected"); End of Procedure
If we set a breakpoint and see what we have in the TK, we will see the following:
P.S. In the Excel file itself, there should not be any blank lines before the names of the fields, otherwise an odbc error will occur. sqlstate 07002 (excel odbc driver too few parameters).
Work with them in lists in 1C Enterprise mode.
However, in work, a situation often arises when 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, which is stored in other databases, it is necessary to develop special mechanisms.
In version 1C 8.2.14, a new one has appeared called External data sources 1C, which greatly facilitate the work of the programmer, since:
- now there is no need to create special mechanisms for receiving data
- such data can be accessed with the usual
- such data can be viewed in the 1C lists.
- In the Key field property, specify one of the columns that will provide a unique identification of the row; if uniqueness is provided by several lines, then this method does not work (analogue of the Code field)
- In the Presentation field property, specify one of the columns that will provide a short representation of the string (analogous to the Name field)
- In the Table data type property, specify Object data.
- If the first line of the Excel spreadsheet contains the names of the columns, then you just need to specify 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.
- XLSX files (Office 2007 and higher)
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 source 1C - external SQL database
Let's say we have a SQL database that stores the data we need. Let's try to read data from it using the 1C External Data Source mechanism.
Let's add an external data source 1C. 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, let's name it arbitrarily.
The database is made up 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 for a connection string. It can be entered manually, or generated, for which you need to click on the "..." button.
In our particular case, we will select "SQL Server" as a 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 that, 1C will display a list of tables in this database and their columns. It is necessary to select the required tables with checkmarks.
Tables and columns will be added. The names will be as defined in the remote database. In 1C you can rename them (in properties).
Here is an example of the added table:
Here's 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 reference books, additional parameters can be specified in the table:
2. Viewing
The connection to the remote base is not made automatically. To connect, you need to select a typical menu.
In the Standard branch there is a special command Manage external data sources, which allows you to specify connection parameters (your own for 1C Enterprise mode) and connect.
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 that, you need to make the actual connection.
After the connection is made, it is possible to work with the lists.
3. Use in 1C language
Connection can also be made from the program code in the 1C language.
Connection parameters are specified as follows:
ConnectionParameters = ExternalDataSources.SourceNameConfigurator.GetGeneralConnectionParameters ();
ConnectionParameters.AuthenticationStandard = True;
Connection Parameters.UserName = "sa";
Connection Parameters.Password = "password";
Connection Parameters.ConnectionString = "connection string from the configurator";
Connection Parameters.DBMS = "MSSQLServer";
ExternalDataSources.SourceNameConfigurator.SetGeneralConnectionParameters (ConnectionParameters);
ExternalDataSources.SourceNameConfigurator.SetUserConnectionParameters (UserName (), Parameters);
ExternalDataSources.SourceNameConfigurator.SetSessionConnectionParameters (Parameters);
ExternalDataSources.SourceNameConfigurator.InstallConnection ();
You can query data from the database using the usual one. Example query text for external source OurExternalSource and ExternalSourceTable tables:
SELECT
ExternalSourceTable.FieldName
FROM
ExternalDataSource.OurExternalSource.Table.ExternalSourceTable "
External data source 1C - working with an 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, let's call it FileExcel. Let's add the "Sheet1 $" table to it. As you can easily notice, this is the name of the sheet in Excel with the addition of the "$" symbol.
As with SQL, let's add columns. They can be added manually. It is important to monitor the correspondence of the types of added columns, otherwise you may get an error of the form "Inconsistency of data types" later.
For the column, you need to specify the name in 1C and the name in the data source.
For Excel, there is a feature (an error like "Too few parameters. Requires 3"):
The connection parameters for the Excel file look like this:
Release 8.2.14.533 is finally a more or less stable version of the 14th release of the platform. Finally, an opportunity presented itself to try out a wonderful opportunity - "external data sources".
Why is this opportunity so interesting? Anyone who has programmed in 1C at the same time is quite familiar with SQL and at least in general terms is familiar with the architecture and principles of developing other technology platforms for business applications with firm confidence will tell you what he likes most in 1C. Of course, the query builder is the most convenient and sophisticated mechanism for writing queries to get data from relational structures that I have personally ever come across. And now 1C has provided us with 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 - like nothing complicated
b) put a tick "Select from the list" - it is necessary - this is necessary to check the performance already at the beginning and will save you from unnecessary troubles
v)
(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 below
Be here VERY CAREFUL.
This is an ODBC driver - if you are using the client-server version, it must be on the server. If you are developing on one system, and the working version on another (as is usually the case) make sure that you are not in for surprises. Strange recommendation, but choose the oldest or most general driver if you are not particularly concerned about speed and you do not intend to go beyond the capabilities of the SQL92 standard. This will give you the best 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 (in the case of using the file version), and special gain for simple he will not give tasks.
E) Standard Server selection dialogs
G) Select a table and details ... a great opportunity - you can immediately rename it as you like (and details too), while in the properties you will display the names of the fields of the data source
H) And now you start, open the query designer - you stupidly select all records from the table and OPA - an error. What to do? If you have a managed interface, take a look at the service menu, and if it's a regular one ...
I personally used the following 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.SetGeneralConnectionParameters (Parameters);
ExternalDataSources.DAX.SetUserConnectionParameters (UserName (), Parameters);
ExternalDataSources.DAX.SetSessionConnectionParameters (Parameters);
ExternalDataSources.DAX.InstallConnection ();
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 needed is not clear ...
2) Read-only data sources
Yes, miracles do not happen ... but sometimes you want so much ...
3) CANNOT BE USED TOGETHER WITH INTERNAL DATA SOURCES
This fact killed me personally.
How is it ... then what they were waiting for and already presented and licked their lips as we now in one request will combine our data with 1C, we will collapse - we will group it, insert it into the report, but it was not there ...
But naturally experienced people this does not stop ... what thought came to mind? That's right - temporary tables:
4) DO NOT USE THEM TOGETHER WITH TIME TABLES
But this does not look like technological difficulties, but very much looks like what they want us to do "so that life does not seem like paradise" (IMG :).
5) Can only be used in ACS connections
For those who do not know - this is in the ACS on the "Dataset Links" tab. Do you use them often? Comfortable? Apparently they want to force us to use them more often. Only 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's, everything is somehow not transparent either. Someone can explain to me how the "link condition" works. If you write there Source Props = Receiver Revision, it does not work. Of course, the condition can be written in the "Expression" field - in most cases this is enough ... but somehow it is not very easy to get it.
In total, this task was previously solved somewhere like this:
Function InitializeDataSource ()
StartDate = SettingsComposer.Settings.DataParameters.Elements.Value;
DateCon = SettingsComposer.Settings.DataParameters.Elements.Value;
If DateCon> "20100101" Then
DateCon = "20100101";
EndIf;
CC = New QualifiersNumbers (15.2);
KS = New String Qualifiers (255);
ArrayNumber = New Array ();
ArrayNumber.Add (Type ("Number"));
ArrayString = New Array ();
ArrayString.Add (Type ("String"));
ArrayDate = New Array ();
ArrayDate.Add (Type ("Date"));
// In the table we will fill in the accounting cost
TypeNumber = New Description of Types (ArrayNumber, CN);
TypeString = NewDescription of Types (ArrayString, KS);
TypeDate = NewDateDescription (ArrayDate);
// table for loading data from SQL
TK = New ValuesTable ();
TZ.Kolonki.Add ("NomenclatureCode", StringType);
TK.Columns.Add ("Qnty", TypeNumber);
TK.Kolonki.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 (SocrLP (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 TK;
End of Attempts;
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 ();
End of Cycle;
Request = New Request ();
VRTable = NewTemporaryTablesManager ();
Request.SetParameter ("vrTableData", TK);
Request.SetParameter ("StartDate", StartDate);
Request.SetParameter ("DateCon", DateCon);
Request.Text = "SELECT
| vpDataTable.NomenclatureCode,
| vrDataTable.Qnty,
| vpDataTable.Period
| POST Data Table
| FROM
| & vrDataTable AS vrDataTable
| WHERE
| timeDataTable.Period> = & StartDate
| AND vrDataTable.Period Request.Run ();
TK = Undefined;
Request = New Request;
Query.TemporaryTablesManager = VRTable;
Query.Text = "Here is a query involving a verbal table";
Result = Query.Run ();
Refund Result;
EndFunction
OuterSet = InitializeDataSource ();
Dataset = New Structure ();
DataSet.Insert ("SQL Table", ExternalSet);
Typical Reports. Generate a Typical Report (ThisObject, Result, Decoding Data, Output To Report Form, Dataset);
Actually, there are not many lines of code and they are quite standard ... at the same time, you can use the full functionality of the query designer, and give only the DATA LINK function in the ACS
But it looks a bit of course not so beautiful ... and the unloading into the table of values every time you need to write the code and check if you made a mistake in the name of the details ... but what we were given in 1C looks somehow half-hearted. I have not yet decided what 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]
