Standard odata interface.
Print (Ctrl+P)
The second part can be seen
General information
In platform version 8.3.5.1068 , published in September 2015, a mechanism for integrating 1C with external programs through technology appeared REST interface. The platform uses the OData protocol as the access protocol. It is an open web protocol for querying and updating data. It allows you to manipulate data using HTTP commands as requests. Replies in version 8.3.5.1068 could only be received in the format Atom/XML . However, starting from release 8.3.8.1652 in August 2017, a second option for getting data in JSON format (JavaScript Object Notation) has appeared. . Compared to XML, it is easy for humans to read and takes up less space. In addition, all browsers have built-in tools for working with JSON.
Working with the OData protocol on the 1C: Enterprise platform can be found in the book 1C: Developer's Guide in Chapter 17 Internet Service Mechanisms, paragraph 17.2.1 Standard OData interface. You can also look at examples of extending OData protocol support,
Use advantage REST interface. concludes that in order to access system data from external application no code modification required applied solution(for example, if the applied solution is supported). To gain this access, you must publish the application to the web server in a special way and specify which configuration objects will be used in this way. After that, third-party systems can access your application using HTTP requests.
The publication of the standard OData interface is performed using the publish dialog on the web server (Administration - Publish to web server) and described in the book 1C: Enterprise 8.3. "Administrator's Guide".
Important! In order for configuration objects to become available through the standard OData interface, you must enable this using the global context method Set Composition of StandardODataInterface().
The mechanism for setting the composition of objects available through the standard OData interface can be done as external processing. This does not require modification of the applied solution.
To interact with an external REST web server from 1C:Enterprise, the tools available in the platform for working with HTTP are used: objects HTTPConnection, HTTPRequest and HTTPResponse.
In this series of articles, I will show examples of typical operations using the corresponding HTTP method;
- Data Acquisition - Method GET;
- Object Creation - Method POST;
- Data update: method PATCH- in this case, you can specify only those properties that need to be updated; method PUT– in this case, it is necessary to specify all the properties of the entity;
- Deleting data - method DELETE.
1. Examples of data acquisition. HTTP method GET
The server will be a database published on the web server with the name webbuh(Demo-base “Accounting of the Enterprise 3.0”). I will use the JSON format as the data exchange format. More information about working with JSON is written in the documentation available. To receive data from the server using the GET HTTP method, you need to create an object Reading JSON to sequentially read JSON data from a file or string. To organize sequential recording of objects and texts on the server using the POST PATCH PUT HTTP method, you need to create an object JSON entry. Note that the DELETE method does not require JSON.
As an illustration of streaming reading and writing JSON when accessing the REST interface, I will call the following general purpose custom function CallHTTPMethodOnServer :
&On server // <Описание функции>// // Parameters: //To receive from the server in JSON format, when accessing the REST interface of the application, you need to specify in the resource address $format=json. Or specify MIME type application/json in the title:
headers = new match(); Headers.Insert("Content-Type", "application/json") ; ResourceAddress =" webbuh/odata/standard.odata/ ?$format=json" HTTP Request = New HTTPRequest(ResourceAddress, Headers);Global context feature ReadJSON(ReadJSON, True )
- If the value of the second parameter is set to True , reading the object JSON will be done in Conformity.If set to False , objects will be read into an object of type Structure.
- When deserializing JSON objects into a struct, be aware of the struct key requirements. If the deserialization of an object finds a property name that is not valid for a structure key, an exception will be thrown.
1. 1 Configuring HTTP connection parameters
To organize the client part of interaction with an external REST web server, I created a client configuration based on BSP from scratch. On this configuration, I created a directory for setting connection parameters (see Fig. 1)
Fig 1 Reference manual for configuring HTTP connection parameters to external IB through the rest interface
After pressing the button Check server response a procedure is called by which the client will try to receive the server's response. Program code procedure is written below:
&OnClient Procedure CheckConnection(Command) Address = Object.ServerAddress; User = Object.User; Password = Object.Password; BaseName = Object.Name; Port = ? (Object.Port<>0,Object.Port,80); HTTPConnection = New HTTPConnection(Address, Port, User, Password); ResourceAddress = BaseName + "/odata/standard.odata/ $metadata "; //Call custom function Response Structure= B callHTTPMethodOnServer("GET", HTTPConnection,ResourceAddress) ; If Response Structure <> Undefined Then General PurposeClientServer.InformUser("Status Code "+Response Structure.StatusCode); end if; EndProcedureThe purpose of this procedure is service check and whether the user entered the connection parameters correctly. To do this, just make a GET request:
HTTPConnection.CallHTTPMethod( "GET", RequestHTTP) ;
using resource address:
Resource Address
=BaseName+ “/odata/standard.odata/
“;
You can also check the operation of the service in the browser using
URL http://host/WebBuh/odata/standard.odata. As a result of such a query, only a list of entities is obtained. For a complete description of the standard OData interface (list of available entities, their attributes and functions in the form of XML-
document.) you need to make a GET request using the parameter $metadata.
URL http://host/WebBuh/odata/standard.odata/$metadata. A detailed description of the document can be found at http://www.odata.org/documentation/ (in English).
You can receive answers in the format Atom/XML or JSON. Response status codes by HTTP protocol you can see the answers in the ranges:
- 100-199 – informational responses indicating that the client's request has been accepted and is being processed.
- 200-299 – means that the client's request was processed successfully.
- 300-399 means that the request failed and the client needs to take some action to satisfy the request.
- 400-499 - informs about errors on the side of the client application. These codes may also indicate that additional information is required from the client.
- 500-599 - Informs about an error on the server side, indicates that the server has encountered an error and will probably not be able to fulfill the client's request.
1.2 Searching for an object by ID
The following function is designed to search for a directory or document by a unique identifier on the server. If the object is found, then the function returns the string value of the identifier (Ref_Key) , otherwise it returns undefined. The following parameters are passed to the function:
- HTTPConnection – Object of type HTTPConnection
- PublishName - The name of the published server database
- Element - entity identifier of the object, for example, Catalog_Organizations or Document_ - directory of the Organization.
- Identifier - The identifier of the object to be looked up on the server, for example, Organization.UniqueIdentifier()
Parameter Resource Address is used to access the REST service. To check the operation of the service, you can specify the resource in the browser in this way
http://(WebServerAddress)/(PubName)/odata/standard.odata/(Item)?(Parameters) ,where
- WebServer Address– The address of the web server where the service is published, for example Localhost
- NamePublications– name of the infobase specified when publishing the solution
- /odata/standard.odata/ – Sign of access to the standard OData interface
- Element – resource identifier or predefined resources. For example, Catalog_Account(guid'value').
- Parameters– resource parameters. Used, for example, for selection, in the accepted for HTTP requests: ?key=value&key2=value2
1.3 Searching for an object by search fields
The following user-defined function is designed to search for an object by search fields in the case when the object is by identification number. Function object string Ref_Key-an identification number.
&AtServer Function P searchObjectBySearchFields(HTTPConnection,PublicationName, Element,SearchFields) Condition = "" ; For Each Key Value From Search Field Loop Condition = Condition + KeyValue.Key+"eq""+ KeyValue.Value+ "" and "; EndCycle; RequestText =Lion(Condition, StrLength(Condition)-5); // remove the last 5 characters Resource Address= PublicationName+ "/odata/standard.odata/" +Element+ "?$filter=" + RequestText+ "&$format=json& $select=Ref_Key" ; // Call my custom function Response Structure= callHTTPMethodOnServer( "GET",HTTPConnection,ResourceAddress); If Response Structure .StatusCode >= 400 Then //General PurposeClientServer.InformUser(Element+ "Error"+Response Structure.StatusCode); //General PurposeClientServer.NotifyUser(ResponseStructure.ServerResponseUndecrypted); return undefined; EndIf; Match = Response Structure. ResponseServer a; Array = Match["value" ]; If Array = Undefined Then Return Match ["Ref_Key" ] Otherwise Return Array ["Ref_Key" ]; EndIf; EndFunctionsAs seen from the body of the procedure P SearchObjectByFieldsSearch, selection starts with keyword$filterin the resource address. Formal parameterSearch fields -this is a correspondence that contains the names and values of the attributes.
Note that the name of the details is sometimes not obvious. It must be remembered that for directories:
- code - code,
- Description
- DeletionMark - deletion mark,
- IsFolder - a sign of a group,
- Parent_Key is the parent.
- If the attribute is of a reference type, add the _Key suffix to its name, for example, Contractor_Key.
For documents:
- Number – document number,
- Date - date of the document.
Logical selection operations
- eq - Equal to; /Catalog_Cities?$filter=Name eq ‘Main’;
- ne - Not equal; /Catalog_Cities?$filter=Name ne ‘Perm’;
- gt - more; /Catalog_Products?$filter= Price gt 10;
- ge - Greater than or equal to; /Catalog_Products?$filter=Price ge 10;
- lt - less; /Catalog_Products?$filter=Price lt 10;
- le - Less than or equal to; /Catalog_Products?$filter=Price le 10;
- or - Logical OR; /Catalog_ Products ?$filter= Price lt 10 or Price gt 100;
- and - Logical AND; / Catalog _Products?$ filter =Price g t 10 and Price l t 100;
- not - Negation; /Catalog_ Products ?$filter=not (Price eq 10);
Note also that the value of the actual parameter Element(or entity)) which I pass to the function formed according to the following rule:
PrefixName_ConfigurationObjectName_SuffixName.
Using the standard OData interface, you can access following objects (Name Prefix):
- Directory - Catalog;
- Document - Document;
- Document Journal - DocumentJournal;
- Constant - Constant;
- Exchange plan - ExchangePlan;
- Chart of accounts - ChartOfAccounts
- Calculation type plan - ChartOfCalculationTypes;
- Chart of types of characteristics - ChartOfCharacteristicTypes;
- Information register - InformationRegister;
- Accumulation Register - AccumulationRegister;
- Calculation Register - CalculationRegister;
- Accounting register - AccountingRegister;
- Business process - BusinessProcess;
- Task - Task.
ConfigurationObjectName- property "Name" of the configuration object as it is set in the configurator.
Name suffix- needed to specify the resource name, optional, can take the following values:
- The name of the tabular part of the object;
- The name of the object's virtual table;
- RowType - row of the tabular part of the object;
- RecordType is a single register entry.
Resource Access Options
After forming the resource name, you need to define the parameters for accessing the resource, for example, ?$filter= Meaning &$format=json& $select= Ref_Key ,
- $filter- selection when receiving data
- $format- specifies the format of the returned data,
- $select- enumeration of entity properties that will be included in the query result;
- $metadata- returns a description of the standard OData interface (used without specifying a name suffix, an example is in one of the images above);
- $top- limiting the number of returned records;
- $skip- removes the specified number of records from the query result;
- $count- returns the number of records in the query selection;
- $inlinecount=allpage(=none)- adds information about the number of records to the query result
- $orderby=<Реквизит1>asc,<Реквизит2>desc- sort query result
- allowOnly- only allowed (used without the "$" sign).
1.4 Get an array of information register entries
Let's look at an example of obtaining an array of records of the register of information on the full name of individuals, for example, the history of changing the full name individual
NamePublications ="WebBuh"; Element = "InformationRegister_Name of Individuals"; Period = Undefined ; ReferenceType Data= New Struct(); D DataReferenceType.Insert("PhysicalPerson",PhysicalPerson_Key); DataNonReferenceType= New Struct(); DataNonReferenceType.Insert("PhysicalPerson_Type", "StandardODATA.Catalog_PhysicalPersons") Array = GetRegisterRecordSetDetails(HTTPConnection, PublicationName, Item, Period, DimensionsReferenceType, Measurements of Non-ReferenceType)The body of the function GetRegisterRecordSet, which is called in this example, is shown below.
In 1C:Enterprise, starting from version 8.3.5, the platform can automatically generate a REST interface for the entire configuration. It works on the OData protocol and it allows you to work with directories, documents and registers through a web server. In general, the speed of receiving data is several orders of magnitude faster than via COM or files, which is good news.
Different queries are used for different operations:
- GET - used to get data;
- POST - used to create objects;
- PATCH - modification of an existing object;
- DELETE - deleting an object.
Prefixes are used to access various objects:
- Directory - Catalog;
- Document - Document;
- Document Journal - DocumentJournal;
- Constant - Constant;
- Exchange plan - ExchangePlan;
- Chart of accounts - ChartOfAccounts
- Calculation type plan - ChartOfCalculationTypes;
- Chart of types of characteristics - ChartOfCharacteristicTypes;
- Information register - InformationRegister;
- Accumulation Register - AccumulationRegister;
- Calculation Register - CalculationRegister;
- Accounting register - AccountingRegister;
- Business process - BusinessProcess;
- Task - Task.
Using the ODATA protocol, you can also use the built-in methods of objects by performing POST requests.
- For a document, Post() and Unpost();
- For a task - ExecuteTask();
- For a business process - Start();
- For information register - SliceLast() and SliceFirst();
- For the accumulation register and accounting register - Balance(), Turnovers() and BalanceAndTurnovers();
- For calculation register – ScheduleData(), ActualActionPeriod(),<ИмяПерерасчета>() and Base<Имя базового регистра расчета>().
To start working with this interface, you need to publish it. This is done through the configurator menu "Administration" - "Publish on a web server", check the box "Publish the standard OData interface" and click "Publish".
After publication, the operation of the protocol can be checked at http://<ИмяСервера>/<ИмяБазы>/odata/standard.odata. In response, we must receive the entire composition of the published tables. It should look like the picture below.
If, after publishing the REST interface, the composition of the published data is empty, then you need to use the SetStandardODataInterfaceComposition method. At the input it has 1 parameter of the “array” type. You need to add the metadata that you want to publish to the array.
Composition = new Array;
Composition.Add(Metadata.Catalogs.Accounts);
SetCompositionStandardInterfaceOData(Composition);
http://<ИмяСервера>/<ИмяБазы><ИмяСправочника>
You can use the following options when querying:
select - in this parameter we specify the fields we need;
format - set the format in which we want to receive the response (XML or JSON), the default is XML
odata - if we do not need a metadata description in the response, then we write “odata=nometadata”
filter - here we indicate the selections.
As I wrote above, we can get a response in two XML or JSON formats, XML is used by default. In order to get data in JSON format, you need to add “?$format=application/json” to the URL.
http://<ИмяСервера>/<ИмяБазы>/odata/standard.odata/Catalog_<ИмяСправочника>?$format=application/json
Basically, we need to get a specific element of the directory, and not all of its entries. To do this, we use the magic word “filter”.
http://<ИмяСервера>/<ИмяБазы>/odata/standard.odata/Catalog_<ИмяСправочника>?$format=application/json&$filter=Ref_Key eq guid'UID'
If you have noticed, the URL contains two parameters $format and $filter, they can be placed in any order, the main thing is that the first parameter should be preceded by the sign “ ? “, and before the second “ & “. The logic here is this, we indicate the address of the directory in the first part, and the parameters in the second. These parts are separated by the sign “ ? “, but the parameters themselves are separated by the sign “ & “. Schematically, it looks like this
AddressTables ? $Parameter1=Parameter1 Value & $Parameter2=Parameter2 Value
Overview of OData
OData is a web based API for accessing and manipulating data. It is similar to the mini-ODBC and JDBC APIs, but is specifically targeted at the Internet. More specifically, OData allows clients to construct URIs to name an entity set, filter the entities that the set contains, and trace relationships with related entities and collections of entities. For more information, see Introduction OData: Data Access for the Web, the cloud, mobile devices, and more. mobile devices etc.).
Figure 1. Exposing a database to the web using a general purpose OData provider
The diagram in Figure 1 shows how a resource such as a database can be exposed to the Internet through a general purpose OData provider. The OData syntax allows Web browsers to manipulate the data in the above database for various manipulations (create, update, delete, query).
Figure 2. Conceptual Schema Definition Language (CSDL)
Figure 2 shows the CSDL (Conceptual Schema Definition Language) schema. CSDL is an optional tool to help consuming applications understand the structure of the exposed data. CSDL is like metadata in JDBC and ODBC, it helps client applications understand what exactly they are accessing.
There are thousands of web-based APIs on the Internet. OData is one example of such an API. For more information on web based APIs, visit the website: Programmable Web. Standardizing such interfaces will improve consolidation in this important technology area and help innovation keep pace with market demands, in particular to support new data constructs and open data initiatives.
OData in an OASIS organization
A draft charter for OData has been submitted to the Organization for the Advancement of Structured Information Standards (OASIS). Below is a snippet of text from that document.
The work will be focused on achieving the following goals.
- Creation of HTTP-based RESTful data services, the ability to identify resources using URI identifiers (Uniform Resource Identifier) and define resources using an abstract data model, the ability to publish and edit by web clients using simple HTTP messages.
- Displaying and retrieving information from various sources, including but not limited to relational databases, file systems, content management systems and traditional websites.
The OData specification documents submitted to the OASIS organization, as well as the draft charter, are available at the following links.
In addition to the OData specifications, the following four documents with OData extensions have been submitted to the OASIS organization.
These documents, with extensions, are intended to initiate work on the proposed OASIS OData Technical Committee. On the OASIS website, you can read full text OData charters and extension documents.
OData and IBM Products
The following IBM products support OData.
- A product to support access by various clients.
- DB2 and Informix products are able to use OData through Microsoft visual studio. More detailed information on this topic is contained.
Examples
This section provides some simple OData examples that access the Netflix service.
OData and other web based APIs
There are currently thousands of web based APIs in use. Popular APIs in this category include map services. As of June 2012, the Programmable Web has listed over 6,000 web-based APIs (see note titled: API Business Models Take Center Stage). A recent study by Vordel found that "half of enterprises are adopting APIs to build new business channels", with 25% of these interfaces being developed specifically for mobile applications.
One area of interest is how OData relates to activities in the Linked Data API area. Working group The W3C Linked Data Platform (LDP) Working Group and the OASIS OData Technical Committee seek to specify REST-type APIs for retrieving and manipulating data on the web using various models data. The LDP platform is based on a data model specified by Microsoft and originates from Peter Chen's Entity Relationship Model formulated in 1976 (Peter Chen). The EDM model is still used in the design relational databases data. The EDM data model is based on the transformation of information into entities and relationships. This model uses domain-specific values, such as social security numbers, invoice numbers, item numbers, to refer to resources and their properties, and to link information.
The EDM presents information in a way that is familiar to many data developers. This greatly facilitates the understanding and use of this model by developers. RDF allows you to link data around the world and supports the formation of inferences through logical inference, i.e., allows you to derive new facts from existing information using universal identifiers, associated definitions and properties.
Building adapters between heterogeneous web-based APIs is entirely possible. For example, a prototype adapter for linking solution workitems obtained through a Linked Data API called OSLC (Open Services for Life Cycle Collaboration), and Microsoft documents Sharepoints obtained through OData can be found at the following link: http://wiki.eclipse.org/Lyo/SharepointAdapter .
As the market expands, new APIs appear. While their capabilities may overlap to varying degrees, there is no doubt that developers will continue to innovate in this promising technology area as new use cases are identified.
Thanks
I express my deep gratitude for the feedback and suggestions to my colleagues: Elizabeth Cleary, Andrew Eisenberg, Diane Jordan, Arnaud Le Hors.
Let's imagine for a moment that we have infobases on the 1C:Enterprise 8 platform, with the data of which we need to work regularly. But, unfortunately, we do not have the opportunity to make any changes to their configuration. Perhaps this (in the presence of a full-fledged platform); or free "1C: UNF for Ukraine. Micro"; or because of the benefits automatic update we do not want to withdraw support; or the service company named the cost of its services, which did not receive approval from the management, and there is no own "1C programmer" ...
And in such conditions, you are given the task of integrating this base and some external system. What to do and what options do we have? It is possible to supplement me in the comments, but so far I see exactly 5 ways:
- Access to the database through the family COM objects(V83.ComConnector and earlier). Limitation: The platform must be installed on Windows.
- Direct Access to database tables. Here is an example for DBMS. . Limitation: Prevent direct access to data in license agreement; instability of the received access; changing the data can lead to a violation of the logical integrity of the database.
- Starting with platform version 8.3.5, it became possible to provide access to data through automatic REST interface based on the OData v.3.0 protocol. Limitation: You need to install the web server and the web server extension module from the platform delivery.
- Starting from platform version 8.3.6 extension mechanism, which allows you to "fasten" new functionality without making changes to the main configuration. Among the new functionality are WEB and HTTP services that are interesting to us. Starting from platform version 8.3.11, the possibility of expanding the structure of database tables (adding new details for storing service data for integration purposes) has become available. Limitation: it is necessary to have a programmer who will develop the extension and will monitor its performance during updates; for services, you need to install a web server and a web server extension module from the platform delivery.
- We can refuse "instant access" and then we can use triggering external processing using a parameter command line/execute. In such a scenario, you can schedule some processing to run regularly, which will check the external resource for instructions to execute and place the results of its work there. You can also independently launch the 1C client application to work out your commands, if you have access to the OS in which the base is located. Limitation: it is necessary to have a programmer who will create the processing; the presence of a time lag in the reaction of the system to the value of the interval between launches in the scheduler or to the start time of the client application.
Thus, among the 5 options, the fastest and easiest way for the administrator is automatic access via the oData protocol. The same option is cross-platform.
And the option with the oData protocol is less expensive in terms of developing a link with the 1C base. The fact is that Microsoft is pushing it hard. In addition to releasing the OData SDK for .NET, AJAX, PHP, Java, JavaScript, WebOS, and Objective-C development, the company has implemented this protocol in its popular products: Excel, PowerPoint, SharePoint, MsSQL, and more. Thus, you do not need to create your own version of CommerceML and parse XML and JSON texts both on your side and on the side of the 1C base, as if you were implementing your own WEB and HTTP services. When using OData, you will immediately have ready-made libraries for obtaining or modifying data for use in your external system, while on the side of the 1C base everything will happen automatically.
Has a minute already passed?
We can exhale - we no longer have any restrictions and we can do any integration we want! I specifically limited our imagination at the beginning, so as not to painfully list all the options from the well-known book "1C: Enterprise Integration Technologies" ISBN 978-5-9677-1462-7 by D.I. Goncharov. and Khrustaleva E.Yu. (and even more so, do not waste time listing the weaknesses of these options). You can take my word for it here or you can start a discussion in the comments, but the OData option is still the most attractive one.
For those who are interested in the topic, please go to the official website of the protocol - www.odata.org. Once again, I would like to point out that, despite the fact that current version protocol is already 4.0 and it was standardized by the OASIS consortium on March 17, 2014, but the 1C:Enterprise 8 platform still uses a more early version 3.0. By the way, do not forget to look into the protocol ecosystem section and admire the mention of our 1C:Enterprise, which is the first in the list :))
Required settings
As I mentioned, you must have a web server. Starting from version 8.4, the server part of the platform will already have its own web server, but for now we need to use third-party ones - IIS or Apache. How to set everything up is well described in the yellow booklet for the administrator. But if you like pictures and other people's experience, then I pulled it in the search: , and of course :) I can not vouch for the completeness and relevance of the data provided in these articles and I am not familiar with the authors at all. If you have any problems, then read the Administrator's Guide for your version of the platform - everything you need is there.
After you already have a web server installed and it no longer crashes at startup due to problems with expanding access to 1C (let's guess - you installed 32-bit Apache and a 64-bit platform), there is very little left. In the configurator, go to the "Administration" menu and click on the "Publish on a web server ..." command. In the window that appears, you must specify the following important points:
- The name of your database in the "Name" field, by which the web server will provide access to it (if you do not want problems, then do not write in Cyrillic);
- Specify the web server that is installed on this computer(if you have as many as two web servers installed, then there will be a choice in the drop-down list);
- The path to the publishing directory that your chosen web server should be able to access;
- And most importantly - the "Publish standard OData interface" checkbox!
After clicking on the "Publish" button, a default.vrd file (an XML file that contains the data that you made in the publishing settings) will be created at the path specified in the settings, and an entry about your publication with that name will be added to the web server config what you specified. After publishing, the web server should be restarted.
A few notes on publishing. If you have several databases to publish, then each of them must have its own directory. Immediately think about the user who is going to access the database - he must have the necessary roles and a name in Latin letters without special characters (if you want to fight with encodings in the future, you can make a name in Russian with spaces). If you are working on Windows and you have UAC enabled, then you should run the configurator as an administrator before publishing. If you work on Linux - hold on, we believe in you! :)
Here's what a working default.vrd looks like with the OData interface published:
As you can see from the publication file, the web server absolutely does not care where the infobase is physically located, the path to it is written in the same way as you write it in the list of bases in the initial window. The main thing is that the access extension be installed on the same computer with the web server and the location of the database be physically accessible over the network for the web server service with the rights to change. There may also be problems with obtaining a license and you will need to delve into the nethasp.ini file, but that's all standard procedures and for normal platform installation.
After you have completed the publication and restarted the server, we can check the results in the browser. To do this, you need to specify the name of the web server, then the name of the database from the publication, then the path /odata/standard.odata/ . You should be prompted for a password and you will see something similar to this:
To demonstrate the capabilities of the technology discussed in the article, I took the configuration "Trade Management (basic)", edition 10.3", in which the compatibility mode is set to "Version 8.2.13" and therefore all metadata is immediately available via the REST interface, and the function call SetCompositionStandardInterfaceOData() to control composition availability is disabled.
If you have a modified 10.3 trade that you set the compatibility mode to "Version 8.3.5" or higher, or if you have more modern configurations, then all metadata will be hidden by default and you will need to use special processing to indicate the visibility of the required data. In order not to waste time creating your own processing, you can use my work, which is suitable for both regular and managed interfaces (see attachments).
Grumbling...
To be honest, I did not really understand this idea with a custom list of metadata. Some kind of half solution. If the goal was to increase security, then why is it immediately given for permitted data full access to editing and deleting, and not to fine-tune the rights with the ability to provide read-only? Of course, you can solve the issue of rights with the help of a specially configured role, but this is already an unnecessary modification of the applied solution, and even in this case, I will not let anyone into the database via the REST interface except for the server scripts of my site (back-end), which will decide what give and what not.
If they wanted to minimize the volume transmitted information, then an even more dubious decision - they look at the metadata only a few times to study the functionality and then work with the data. It would be logical to do optimizations in the data? After all, it would be possible to limit the list of fields requested from objects, and it is especially important to limit the information disclosed by the $ expand parameter, which pulls the entire associated object, while we only need one or two fields from it.
And what's next?
I think that we can congratulate you - everything is set up and working for you! What are the next steps? It all depends on why you needed to organize access to the database - to exchange information with a corporate website, to operate a mobile application, to connect with corporate software...
If you want to do something like a user account on the site with the provision of a history of mutual settlements, current prices, taking into account personal discounts, order entry and other goodies, then we have several ready-made libraries on the official website at our service. There is even a whole OpenUI5 framework from SAP, which allows you to create a full-fledged business application based on the database obtained via the OData protocol. In general, expanse for a competent JS programmer.
A word of caution about "bicycles"
Probably, after reading the last phrase, your eyes could light up - "To hell with the standard 1C web client! With such capabilities, I will create my own interface to the database with blackjack and a pleasant company." But if I were you, I would not be in a hurry to compete in this field with 1C, where experienced web developers have been creating what we all see after the fact for several years. Do you really want to repeat the sad glory of the "Dominican project"?
The creators may object to me and - the risk is worth the effort and almost ready-for-commercial products have already been created. Oknosoft employees here often like to boast that there is a queue of customers for years to come for their development. And I am sincerely happy for them - the guys have found their niche. But according to their articles, I got the impression that their metadata.js complex is a web analogy of the once popular DISCo Extensions for Pocket Computers program, which made it possible for ordinary 1C programmers to write applications for smartphones. And where is this program now? 1C itself gave the same functionality for developing mobile solutions (and even richer, and for more platforms) for free as part of the delivery of its platform.
From personal experience. At some point, after my remark "that this cannot be done in the 1C web client," my former company decided to create its own alternative web interface. We hired several JavaScript developers, took some then-popular interface library with beautiful grids and charts as a basis for the front-end, and chose node.js for the server. I implemented a SOAP data access interface for this project (there were no HTTP services back then) and we got a beautiful form of order lists in a couple of weeks. I don’t know exactly what happened there in development, but after several months of a heroic battle against an all-out attack of bugs, the project was closed and the developers said goodbye. But the programmers of the 1C company at that very time did not sit idly by, but released a number of updates, after which it became a little more pleasant to use the standard web client.
Let's say that we are not competent JS programmers, but we want to hang some list on the site. I opened an article with a list of the most popular libraries for creating tables today and found in it a simple jsGrid library just for our case. We study their site, take an example of their code for using OData and paste the path to our data there.
To be completely lazy and not program anything for our example, I need to request a full-fledged table without the need to load string representations by links. In typical UT10.3, the choice is not very rich in this regard, and therefore I will take the Counterparties directory.
Now the most interesting. But how to form a query string for reading the data we need? It's simple! We look at our path to the OData root (for me it is) how this directory is called correctly - Catalog_Contractors. Next, open the address in a new window http://localhost/DemoTrdBase/odata/standard.odata/Catalog_Contractorswe will see the contents of the entire directory in XML format. But for our example, we need JSON, and therefore we need to add a parameter to the string:$format=json - it will work http://localhost/DemoTrdBase/odata/standard.odata/Catalog_Contractors? $format=json . So, we are not interested in groups and let's remove them using the filter parameter:$filter=IsFolder eq false - it will work http://localhost/DemoTrdBase/odata/standard.odata/Catalog_Contractors? $format=json & $filter=IsFolder eq false . The block of parameters, as you have already noticed, begins with the symbol "?", and the parameters themselves are connected to each other by the symbol "&". For a complete list of available options and features, see the platform documentation.
We put the resulting file in the directory that is configured as the root in your web server. This is necessary so that the "domains" of the page and the requested data match, otherwise you will get an endless update indicator and an error in the logs: "
No "Access-Control-Allow-Origin" header is present on the requested resource. Origin "null" is therefore not allowed access. The response had HTTP status code 401. "
Literally a few minutes of the time spent and we already have a decent sign, which is even automatically marked up on pages. Only the authentication request looks a little awkward, and in order not to enter the login and password each time, I registered them immediately in the access line. The final file turned out to be quite simple and everyone can easily repeat it, but just in case, I will also place it in attachments.
How about no programming?
Sites, mobile applications, message bus and other words for any IT specialist sound cool, but do not find a response in the hearts of high bosses. Their main working tool is Excel, for which they burn with irrational love even if they have a powerful reporting subsystem from their 1C databases. And at this moment, we remember that it was Microsoft that came up with the OData standard and implemented it in their programs starting with Office 2010. In office programs we can either just view tables with data, or use query mechanisms to get more interesting information at a time without having to join tables from different sheets.
For example, we are interested in debtors. In UT10, we can get them from the virtual table of balances of the accumulation register Mutual settlements with counterparties by imposing a filter so that the amount of debt is greater than zero (otherwise these are advances). I will not set a date, as I am interested in up-to-date data. For my base it will be the following link: http://localhost/DemoTrdBase/odata/standard.odata/AccumulationRegister_Calculations with counterparties/Balance?$filter=AmountBalance gt 0
After reviewing the result, we can notice that we do not have views for counterparties, but only keys for the counterparty directory table. Therefore, we will also request these auxiliary data. To do this, use the link without filters: http://localhost/DemoTrdBase/odata/standard.odata/Catalog_Contractors
And now a simple sequence of steps:
- Open Excel (I have version 2016; if you have 2010 or 2013, the menu may be slightly different)
- We follow the navigation path: "Data" / "Create a query" / "From other sources" / "From the ODATA channel".
- We indicate our link to the register of mutual settlements
- On the authorization form, select the third option "Basic" and specify the login / password. Click "Connect".
- In the query editor window that opens, instead of the name "Request1" we will give something more interesting for us - "Settlements"
- In the center of the query editor window we see the "List" column, where each line contains the word "Record". We can either use the "To Table" context command or click the appropriate button in the editor's "Transform" menu. Answer the question "OK".
- Now the column is called "Column1" and next to it there is a button with arrows in different directions. Click on it. The description of the existing columns will be pulled from the server. Clear all the checkboxes and leave them only on the columns "Account_Key" and "AmountControlBalance". Click on "OK" and a table will appear with two selected columns with the data we need.
- Outside of our attention were such dimensions as the organization, the contract and the deal, but they were still requested. As a result, we now have counterparty lines with different amounts. They can be collapsed using grouping. To do this, either from the menu or from the context, call the "Group by" command. In the grouping fields, leave only the counterparty, and delete the field with the amount. Name the new column "Debt", in the operation select "Amount", and in the column specify the field of our amount. Click on "OK" and as a result we get a little less entries.
- Now we need to decrypt counterparties. To do this, in the left panel "Requests", where there is already a current request "Settlements", call context menu and select "New Query" / "Other Sources" / "ODATA Feed". In the window that appears, specify the path to the counterparty directory. Next, specify the authorization login/password again, click "OK" in the preview and name the new request "Accounts".
- Let's return to the "Settlements" query (click on the name on the query panel).
- Now let's join our two queries. To do this, call the command "Combine" / "Combine queries" in the main menu of the editor. In the window of the designer of the union there will be our table of mutual settlements. In the center, in the drop-down box, select the query "Accounts". The type of connection remains the one that is by default (left outer). Next, click to select the columns for the join condition. We agree with everything that is offered to us below.
- After the merger, we got a new column "Accounts" with a familiar button with multidirectional arrows. We click on this button and select the columns we are interested in. For interest, select "NameFull" and "Parent" (group). The "Parent" column also invites us to open up - select the "Description" field in it (the usual name of the directory).
- Let's make it beautiful. We can delete the first column with the counterparty key. Let's name the group column "Group", give the name "Counterparties" to the column with the name of the counterparty, and move the debt column to the end of the resulting table.
- Now we can click on home button editor - "Close and load".
- You can then use the downloaded table as data for a PivotTable or PivotChart. Or, when creating these new objects, we specify our request "Mutual settlements" as the data source.
But, as they say, it is better to see once than hear a hundred times. I tried to record the same sequence of actions on video. And I immediately warn you that when you repeat it, it will be a little different - authorization will be requested, as I mentioned above. Just at the time of recording the video, Excel already remembered my username and password.
Results
Hope my article was helpful. I reported the existence and benefits new technology platforms. Also explained in detail the access settings in information base using the OData protocol and gave several examples of practical use.
In order to avoid prejudices that OData access can only be used for a managed interface on latest versions platform, as a training example, I chose the demo configuration database "Trade Management (basic), edition 10.3" in compatibility mode 8.2. Even with a database of this configuration, with just a few clicks of the mouse, you can get up-to-date data on debts in an Excel workbook, and it would be just as easy to get up-to-date stock balances, sales data and other useful information.
P.S. The continuation of this article, which discusses the operations of creating and modifying data, is published at
The ability to automatically generate an OData REST interface for the entire application solution was implemented. Thus, we have the opportunity to provide full access third party application to the 1C database in just a couple of clicks.
This mechanism is designed to solve several common tasks:
- Uploading / loading data to / from the application solution;
- Integration with Internet sites (online stores);
- Increasing the functionality of the applied solution without changing the configuration;
- Integration with others corporate systems(sometimes without additional programming).
The OData REST interface can be used to exchange data between 1C databases, but since other, more convenient mechanisms already exist for this, the main purpose of the OData REST interface is seen in integration with third-party systems.
And this is really convenient, considering that OData clients exist on all major platforms, the corresponding libraries can be downloaded.
In this article, I will try to explain in detail what the OData REST interface is and how it can be used.
Publishing an OData REST interface
To use the OData interface, you need to publish it, and for this we need a web server - Apache 2.2 or IIS (starting from the version also Apache 2.4). Then, you need to go to the menu "Administration" -> "Publishing on a web server ...".
In the window that opens, fill in the required fields and click "Publish":
After that, you will need to determine the composition of the OData interface, i.e. indicate which configuration objects are included in it and which are not (initially, there is not a single object in the composition).
You can do it like this:
&AtServer Procedure SetODataAtServer() tArray = New Array; tArray.Add(Metadata.Catalogs.Products); SetStandardInterface CompositionOData(tArray); EndProcedure
&On server Procedure SetODataOnServer() tArray= New Array; tArray. Add(Metadata. Directories. Products) ; Set StandardInterface Composition OData(tArray) ; EndProcedure |
