1C:Enterprise 8.3. Developer Guide. Contents
EXTERNAL DATA SOURCES
When an infobase based on 1C:Enterprise is used, you may want to get information from external data bases (both relational and analytical) and use this information in 1C:Enterprise in a number of ways (for reports, calculations etc). An ExternalDataSources configuration object is used to solve these tasks in 1C:Enterprise. An external data source may include tables from a relational data source and cubes from an analytical data source. An application may contain any number of connected external data sources. Below, we take a closer look at working with all types of external data sources.
23.1. WORKING WITH RELATIONAL EXTERNAL DATA SOURCES
23.1.1. Overview
An object that describes an external data source connected to a relational database consists of tables, and each table consists of fields. Table data may reference other tables: for instance, a value in a table field may identify records from another table. The object that describes an external data source may be used as follows:
as a query data source;
as a data source in the data composition system;
as a data source for dynamic lists;
they can be parts of common attributes (see page 1-204);
table records can be displayed in 1C:Enterprise managed forms (use of ordinary forms for external data source objects is not supported);
external data source tables can act as infobase attribute types;
access rights and access restrictions can be applied to external data source tables (and fields);
access to tables and fields is available in 1C:Enterprise script;
an external data source table can be included in subsystems (see page 1-173);
an external data source table can be included in functional options (page 1-211);
characteristics can be created for an external data source table (see page 1-309).
The ODBC engine is used to get access to external data sources. Data from external data sources are read only.
IMPORTANT!
The external data source mechanism should not be used to access 1C:Enterprise databases, since the 1C:Enterprise data model is not designed to work with data at DBMS storage physical structure level.
1C:Enterprise abilities are thoroughly considered when the following DBMS systems are used as an external data source:
Microsoft SQL Server
IBM DB2
PostgreSQL
Oracle Database
NOTE
When working with an external data source, errors may occur if the Database management system type property of the external data source connection parameters contain a value that does not match the system that is actually used.
When other DBMS are used, external data source functions depend on a specific DBMS. The following query language expressions and functions for such data sources are transformed into a DBMS query with the help of ODBC Escape
Sequences (http://msdn.microsoft.com/en-us/library/windows/desktop/ms715364(v=vs.85).aspx): SUBSTRING, YEAR, QUARTER, MONTH, DAYOFYEAR, DAY, WEEK, WEEKDAY, HOUR, MINUTE, SECOND, DATEDIFF, ISNULL, CAST, ESCAPE, JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
To connect to an external data source you need to generate a connection string that can contain either all the connection parameters required for the selected ODBC driver or a reference to a generated DSN (Data Source Name) data source description. For more about connection string see page 2-879.
String connection examples are provided below.
String connection with a login and a password:
DRIVER={SQL Server};SERVER=(local);UID=user;PWD=password;DATABASE=AdventureWorksLT2008
Login and password should also be specified:
DRIVER={SQL Server};SERVER=CASH-SERVER;DATABASE=CasheReceipts
Specifying a pre-configured data source:
DSN=MyDB
Also, you should remember that connection to an external data source should be specified in both the Designer (if the table structure is imported for the external data source) as well as 1C:Enterprise mode to receive data.
23.1.2. General Usage Principles
To use information from external data sources in a 1C:Enterprise-based system you need to follow these general principles:
Analyze the external data source structure and understand which information (tables and fields) is required for algorithms and reports in 1C:Enterprise.
create an External data source object in the Designer and create subordinate Tables and fields for this object. This can be done using a special wizard.
Implement usage of the created objects in the application.
Configure parameters for connection to an external data source in the network where the application will be used. These parameters can be different from the parameters used to load the external data source structure.
NOTE
Exernal data source access parameters that were set in the Designer, will not be used by the system in 1C:Enterprise mode.
23.1.3. Editing External Data Source Structure
A branch with a name of the corresponding external data source of the External data source – Tables configuration tree branch is used to work with external data source tables. External data source definition is implemented in three steps:
Define the data source itself.
Define data source tables.
Define tables for every external data source table.
Configuration can describe fewer tables and fields than in a real database, but you can't define a table or a field that does not exist in a real database.
You can create external data source structure manually or you can load this structure using a special designer.
23.1.3.1. External Data Sources
You should specify a name for an external data source when it is created. This object is used to identify table groups when you are accessing data in the object. A data source consists of one or more tables which in turn are made up of fields.
23.1.3.2. External Data Source Tables
When you create a new table, the object editing window opens (see page 1-59).
The Name table property is used to identify a table within the application. You should fill in the Name in the data source property so the system will know which external data source physical table corresponds to the configuration object. This property value should exactly match the table name from the external data source. Adding data to one application table from multiple physical tables of an external data source is not supported.
The Table data type property determines which type of entities are stored in the table: object or non-object. If one field that unambiguously defines a record can be selected in the table, the table can store object data. A catalog can be the closest analogue of such tables. If a table record is identified by several key fields, such a table contains non-object data. An information register is the closest analogue of such tables. Table key fields should be specified in the Key fields property.
NOTE
It is not recommended fields that contain Null values as a key fields in the external data source table.
For object data tables you can specify a field that will act as an object representation. You can use the Presentation field property for this purpose.
If the table stores hierarchical data, the system can be informed about it via such properties as Parent field, Missing parent descriptor and Missing parent descriptor value. These properties are available only for tables where object data is stored. The Parent field property is intended to specify the database table field that stores a reference to the parent for the record. Fields that can be selected in this property should have the type of reference to the table being used. The parent field should have the ExternalDataSourceTableReference.DBF.products type for the products table from this example (see page 2-877). The Missing parent descriptor and Missing parent descriptor value properties specify which value will define the record with no parent record. After these properties are specified:
the data composition system uses them when working with hierarchical groups and when checking hierarchy conditions;
the query language uses them for operations in the INHIERARCHY construct;
these properties are used to display tables with hierarchical data (the display options are identical to the options used to display hierarchal catalogs).
The String input property (see page 1-258) can be used to specify the field names used to search in the field with the data type specifying the necessary external data source table.
When tables are created manually the Key fields, Presentation field and String input properties can be specified only after external data source table fields are created.
23.1.3.3. External Data Source Table Fields
Table fields describe which physical table data are available in the application. The Name field property is used to identify a table field within the application. The Name in data source property is used to specify a mapping between an application field and a physical table field. This property value should exactly match the name of a table column specified in the Name in data source property of the parent field object. Merging data in one data table field from multiple columns or multiple tables of an external data source is not supported.
If the value included in the Name in data source property is in single quotes, then it remains unchanged in the SQL query to the database irrespective of the characters used. Where the value in the fields is not set in single quotes, then it appears edged with double quotes in the SQL query to the database if the name includes special characters.
The Type field is used to specify a type of the field. A restricted set of types is available. These types include:
Number
String
Date
Boolean
UUID
BinaryData
types defined by external data source tables
If a composite type should be specified for a field, only Number, String, Data and Boolean types can be used in this type.
When data are extracted from external sources, the system automatically converts this data to the type specified for the corresponding field in the application. See the description of transformation rules on page 2-893.
23.1.3.4. Other Properties
The form used to display external data source table records and lists can be created automatically by the system or an application developer. Note that if the table is non-object and key fields are not specified, then:
the record form can't be displayed.
access to the ExternalDataSourceTableRecordSet object in not available.
This is due to the fact that you can't unambiguously identify the required recordset in a table.
23.1.3.5. Loading Table Structures from an External Data Source
You can load table structures from an external data source available to developers (e.g., a copy of a real database).
To do this you need to check the Select from list of external data source tables option in the external data source table designer when you are creating the table. Then you should specify an external database connection string in the Connection to Data Source window. You can use the external data source connection string designer when generating a connection string. Click the "…" button next to the Connection String field.
When the external database is successfully connected, a list of tables and fields of the connected data source opens. Then you need to select tables and fields that will be used by the Designer to create object structures describing the current data source.
Fig. 315. External data source table designer
The system performs the following actions when you obtain data source structure:
An attempt to determine the data table type (object or non-object) is performed. The table is considered to be an object if only one key field is specified; otherwise it is considered to be a non-object. If the system made a mistake in specifying the key fields, you can manually change the fields that form the table key. If the table is defined as an object table, you can specify a field for representing the data of such type. The representation field should be specified manually.
Data source column types are converted to 1C:Enterprise types that will be used to specify table attribute types. All external data source types are converted into the following 1C:Enterprise types: Number, String, Data, Boolean, UUID, Binary Data and types related to data source object tables.
An attempt to determine table field types is performed. In this case the system tries to determine which data type is stored in the table column; if it can be considered as a reference to other table data, the system specifies a corresponding type in the column. If the system selected the wrong table column type, this can be changed manually.
Then you should check tables and fields that will be migrated to application metadata.
If the Remove tables and fields not present in external data source from configuration checkbox is selected, when the table designer work is done fields and tables that do not exist in the external source will be deleted from the configuration (for example, if fields or tables were deleted from the external source).
When the Finish button is clicked, external data source structure is loaded.
23.1.4. Example of External Data Source Table Creation
Suppose we have dbf-format database that should be accessed from an application.
The database contains three tables (files):
products tables (stored in products.dbf), with information about products containing the following fields:
○ id – product identifier (key field). Type: Number.
○ code – product code. Type: String.
○ name – product name. Type: String.
○ article – product article. Type: String.
price tables (stored in price.dbf), with current product prices containing the following fields:
○ product – identifier of the product whose price is stored. Type: Number. ○ price – product price. Type: Number.
sales table (stored in sales.dbf) with data on every product sale, price, quantity and sum. The table contains the following fields:
○ product – identifier of the sold product. Type: Number.
○ price – product retail price. Type: Number.
○ qty – quantity of sold products. Type: Number.
○ summa – total product selling cost. Type: Number.
Let's create necessary configuration objects to get access to all data in these tables.
First, we will create the data source itself. We'll name it DBF. The next step is to create data source tables manually. Note that the real name of a data source physical table is specified in the Name in data source property of the created object. We will name the configuration object as follows when tables are created:
the products table will be displayed in the Products object.
the price table will be displayed in the Prices object.
the sales table will be displayed in the Sales object.
Fig. 316. External source tables
Now we need to create fields for each table, specify the field types and assign key fields and representation fields (if any).
The example will have one object table (the Products table). Other tables will be non-object. You can see the physical table fields and configuration object fields matching and field types in fig. 317.
Fig. 317. Table structure in the application
Then you should specify the subsystem that contains the tables created and other configuration object parameters (if necessary).
If you start the system in 1C:Enterprise mode and correctly specify the external data source connection parameters, the tables created will be shown in the navigation panel:
Fig. 318. External data source dynamic list
23.1.5. ODBC Connection String
To connect to an external database, you need to create a connection string that can contain all the connection parameters necessary for the selected ODBC driver or a reference to the completed data source name (DSN).
23.1.5.1. Full Connection String
For a description of a connection string, please see http://msdn.microsoft.com/en-us/ library/ms722656.aspx. The following aspects are important in this description:
Connection string parameters are written in pairs KeyWord=Value and are separated with ;.
If a connection string contains several parameters with the same keyword, only the last parameter specified in the connection string will be used. So, if for instance the User name or Password parameters are specified in the connection string and the Connection to Data Source dialog (see page 2-888), the values from the Connection to Data Source will be used, as they will be added to the connection string on the right.
Use http://www.connectionstrings.com/ for reference and help with simplifying creation of a connection string.
23.1.5.2. Describing a Data Source
You can use special utilities to create a data source name (DSN) instead of specifying the full connection string. In this case, you need to specify a special construction of the type DSN=DSNName in the connection parameters. There are user data source names and system data source names. User data source names are available on a given computer and only to the user who has created the description. A system description is created by the system administrator and is available to all the users of this computer (provided they have the necessary permissions).
23.1.5.3. ODBC Data Source Administration Utility
To create a data source description, you need to use the ODBC data source administration utility. To find this utility in Windows OS, go Control Panel – Administrative Tools – Data Sources (ODBC). This opens an administration utility (version compliant with the OS version) from the Windows Control panel. In Linux OS, the utility is called ODBCConfig and is only available if the required package is installed (this depends on the Linux OS version).
You should remember that drivers and data sources for a 32-bit and a 64-bit ODBC differ in 64-bit Windows. Therefore, to create a data source name, use the administration utility version that corresponds to the 1C:Enterprise version. For instance, if you call ODBC from a 64-bit version of the 1C:Enterprise server, you need to use a 64-bit version of the administration utility. If you use a file mode of 1C:Enterprise on 64-bit Windows, use a 32-bit version of the administration utility, since the client application is a 32-bit application.
In 64-bit Windows, Administration utilities are located in the following directories:
64-bit version: %SYSTEMROOT%\System32\odbcad32.exe
32-bit version: %SYSTEMROOT%\SysWOW64\odbcad32.exe
23.2. WORKING WITH AN EXTERNAL OLAP DATA SOURCE
23.2.1. Overview
OLAP is a data processing technology that prepares aggregated information from large data arrays structured by a multi-dimensional principle. Data in analytical databases (OLAP systems) are created on the basis of data from transactional data processing systems (OLTP systems). Information in OLTP systems is presented as a space (so-called cube), with axes that are measures, and the nodes of this space contain measures. Each cube measure is characterized by certain members.
A close (but not similar!) analogue of storing data in OLAP systems is an accumulation register, where the register is similar to a cube, measure of the CatalogRef type is similar to a cube’s measure, and the catalog contains measure members and register resources are similar to cube measurements.1C:Enterprise presents the OLAP system cubes as a model, where:
A cube is represented by the cube in configuration.
Dimensions and dimension members are represented as two objects:
○ Measures are analogues of measures.
○ Measures tables are used to describe measure member collections.
Measures are presented as resources.
Therefore, an external source that connects to the OLAP system includes cubes, and each cube consists of a table of dimensions, the dimensions themselves, and resources. Tables of measures consist of fields. Cube resources may be numerical or string.
The object that describes an external data source may be used as follows:
as a data source for queries.
as a data source in the data composition system.
as a source for dynamic lists.
as a member of common attributes (see page 1-204).
records of measurement tables and cubes may be displayed in managed forms of 1C:Enterprise (using standard forms for external data source objects is not supported).
the measurement tables of an external data source may serve as infobase attribute types.
access rights may be applied to cubes, measurement tables, measurement table fields, measurements and resources of external data sources.
the script supports access to cubes, measurement tables, measurement table fields, measurements, and resources.
the cubes and measurement tables of an external data source may be part of subsystems (see page 1-173).
the cubes, measurement tables and measurements of an external data source may be part of functional options (see page 1-211).
characteristics may be created for external data source cubes (see page 1-309).
The analytical XML mechanism (XML for Analysis, XMLA) is used to work with multidimensional external data sources. The platform receives access to data with the help of http requests to a web server that can be an external system (in relation to OLAP system) or integrated in an OLAP server. For further information on customizing access to OLAP server data, see the documentation for that system.
A line to connect to an analytical external data source is a combination of an XMLA provider URL address (for this OLAP system) and the parameters used by 1C:Enterprise to work with the source. Data from external data sources is only read-only.
The capabilities of 1C:Enterprise are taken into account when the following OLAP systems are used as an external data source (Database management system type property of data source connection parameters):
IBM Infosphere Warehouse
Microsoft SQL Server Analysis Services
Oracle Essbase
NOTE
When working with an external data source, errors may occur if the Database management system type property of the external data source connection parameters contain a value that does not match the system that is actually used.
When other DBMSs are used, the external data source capabilities depend on the DBMS itself. To connect to an external data source, create a connection string. For further details on connection strings, see page 2-883.
Moreover, an external data source connection should be specified in Designer (if a mechanism to import a table structure from an external data source is used) and in 1C:Enterprise mode to obtain the data itself.
Documentation on OLAP systems:
IBM Infosphere Warehouse:
○ http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.dwe.navigate.doc/ welcome_db2warehouse.html
Microsoft SQL Server Analysis Services:
○ Version 2008: http://technet.microsoft.com/en-us/library/bb522607(v=sql.100).aspx ○ Version 2008 R2: http://technet.microsoft.com/en-us /library/bb522607(v=sql.105).
○ Version 2012: http://technet.microsoft.com/en-us /library/bb522607(v=sql.110).aspx Oracle Essbase:
○ http://docs.oracle.com/cd/E17236_01/nav/portal_3.htm
23.2.2. The general principles of usage
To use information from external data sources in a 1C:Enterprise based system, follow the general principles described below:
Examine the structure of an external data source and work out what information (cubes, measurement tables, measurements, and resources) is necessary to support 1C:Enterprise algorithms and reports.
Create an External data source object in Designer, and create the required structure for this object. A special assistant can be used to do this.
Implement the use of objects created in the application.
Set up the external data source connection parameters in the network where the application will be used. These parameters may differ from those used when downloading an external data source structure.
NOTE
In 1C:Enterprise mode the system will not use external data source access parameters set in Designer.
23.2.3. Editing external data source structure
A branch with the name of the corresponding external data source of the External data source – Cubes branch of the configuration tree is used for working with external data source tables. An external data source is defined in several steps:
Definition of the external data source itself
Definition of cubes
Definition of measurement tables and fields for each measurement table
Definition of measurements for a cube
Definition of resources for a cube
While a configuration may describe fewer objects than there are in an actual database, you cannot create an object that is not present in an actual database.
The structure of an external data source may be created manually or with the help of a special wizard when the structure is loaded.
23.2.3.1. External data source
Specify the name of an external data source when it is created. This object is to be used to identify groups of cubes when querying data in these cubes. A data source contains one or more cubes that, in turn, contain other objects (measurement tables, measures, resources).
23.2.3.2. External data source cube
When a new cube is created, an object editing dialog opens (see page 1-59).
Table property Name is used to identify a cube within the application. Complete the Name in the data source property so that the system knows which physical cube of an external data source matches the configuration object. This property’s value shall match the name of a cube of the external data source exactly. Data from several external data source cubes cannot be located on one application cube.
23.2.3.3. Dimension table
A dimension table describes members of an OLAP system cube dimension. The Name in the data source property contains the exact name of a dimension or a hierarchy level in the data source. The Presentation field property contains the name of a dimension property or a hierarchy level used by 1C:Enterprise to create dimension table item representation.
If a dimension is hierarchical, a hierarchical dimension table can be used for such a measurement. A hierarchical dimension table can be described through the following properties:
Hierarchical dimension table – contains an attribute that shows that a dimension table describes a hierarchy in the data source.
Hierarchy name in the data source – specifies the name of a hierarchy to which a dimension table that describes this level belongs.
Level number – if a table describes a certain hierarchy level, this property contains the number of this level. This property is set to 0 for the hierarchical table itself, and the Hierarchy name in the data source property matches the value of the Name in the data source property.
The Missing parent descriptor and Missing parent descriptor value properties can be used to specify how values of the highest hierarchy level are to be identified in 1C:Enterprise.
23.2.3.4. The dimension table field
Dimension table fields describe which dimension properties will be available from within the application. The Name field property can be used to identify a property (dimension table field) in the application. Use the Name in data source property to specify correspondence between an application field and a dimension attribute. A value in this property should exactly match a dimension attribute name specified in the Name in data source property of the parent object of this field. Uniting several attributes of one or several dimensions belonging to an external data source in one data dimension table field is not supported.
The Type field can be used to specify the type of this field. A limited set of types is available for selection. The following field types are available:
Number
String
Date
Boolean
UniqueIdentifier
BinaryData
Types defined by external data source dimension tables
If a composite type needs to be specified for the field, such a composite type may only contain Number, String, Date and Boolean types.
When data from external sources is received, the system automatically casts this data into a type specified for the corresponding field of the application. For converting rules, see page 2-893.
23.2.3.5. Dimensions
An external data source dimension describes the OLAP system cube dimension. Only a reference to a corresponding dimension table may be considered as such a dimension type. In reality, dimension tables and the dimensions themselves form a 1:1 correspondence: one dimension is described by one dimension table.
Uniting data from several external data source dimension in one field is not supported.
23.2.3.6. Resources
An external data source resource describes the fact that there is an OLAP system cube. The cube’s resource may be numerical or string. Resource values are calculated on the basis of cube dimension values. The Name in the data source property should match the name of a fact (a resource) of the external data source cube.
Uniting data from several facts (resources) of an external data source in one data resource is not supported.
23.2.3.7. Loading the cube structure from an external data source
The cube structure may be loaded from an external data source available to the developer (for instance, a copy of a real database).
To perform this operation, select Select from external data source cube list in the external data source cube wizard when creating a cube. Next, specify a string to connect to an external database in the Connection to data source dialog. To create a connection string, use the external data source connection string wizard. Click "…" to the right of Connection string.
When a connection to an external infobase is successfully established, a list of cubes, dimension tables, dimension table fields, and resources of the connected data source is displayed. Select the required cube, dimension tables, fields, and resources to be used by the wizard to create a structure of objects that describe the current data source (see fig. 319).
To obtain the structure of an external data source, the system performs the following actions:
It attempts to define the fields that form presentations of dimension tables. If the systems performs the task with errors, the presentation field may be specified manually.
It transforms the data source column types into 1C:Enterprise types to be used to specify the object attribute types. All types from an external data source are transformed into the following 1C:Enterprise types: number, string, date, Boolean, unique identifier, binary data and types related to the data source dimension tables.
It attempts to define types for dimension table fields. In this case, the system tries to define the type of data stored in a dimension table column, and if this
can be considered a reference to data from another dimension table, the system specifies the corresponding type in the column. If the system has selected an incorrect table column type, it can be changed manually.
Fig. 319. External data source cubes wizard
Next, mark objects to be transferred to the metadata of the application.
If Remove objects that are not available in the external data source from the configuration is set, the objects (cubes, dimension tables, dimension table fields, dimension, and resources) that are absent in an external data source (a cube or a resource has been deleted from the external source) are deleted from the configuration when the cube wizard terminates.
Click Finish to load an external data source structure.
23.2.4. Query Language Limitations When Using an Analytical External Data Source
The following limitations should be taken into consideration when working with cubes and measurement tables in the query language:
|
Expression |
Microsoft SQL Server Analysis Services |
Oracle Essbase |
IBM Infosphere Warehouse |
|
ALLOWED |
– |
– |
– |
|
DISTINCT |
– |
– |
– |
|
GROUP |
– |
– |
– |
|
HAVING |
– |
– |
– |
|
JOIN |
– |
– |
– |
|
UNION |
– |
– |
– |
|
Subqueries |
– |
– |
– |
|
Expression |
Microsoft SQL Server Analysis Services |
Oracle Essbase |
IBM Infosphere Warehouse |
|
FOR CHANGE |
– |
– |
– |
|
EMPTYTABLE |
– |
– |
– |
|
YEAR |
Yes |
– |
– |
|
QUARTER |
Yes |
– |
– |
|
MONTH |
Yes |
– |
– |
|
DAYOFYEAR |
Yes |
– |
– |
|
DAY |
Yes |
– |
– |
|
WEEK |
Yes |
– |
– |
|
DAYOFWEEK |
Yes |
– |
– |
|
HOUR |
Yes |
– |
– |
|
MINUTE |
Yes |
– |
– |
|
SECOND |
Yes |
– |
– |
|
BEGINOFPERIOD |
Yes |
– |
– |
|
ENDOFPERIOD |
Yes |
– |
– |
|
DATEADD |
Yes |
– |
– |
|
DATEDIFF |
Yes |
– |
– |
Where:
– – the value specified is not supported when working with a corresponding OLAP system;
Yes – the value specified is supported when working with a corresponding OLAP system;
23.2.5. An OLAP Server Connection String
An OLAP server connection string looks as follows:
http://<OLAP host address>:<port>/<source>?<parameters>
Where:
OLAP host address, port, source – the OLAP system access address created by the rules that are described in the OLAP system documentation.
parameters – parameters used by 1C:Enterprise to access OLAP system data. Parameters are set as Parameter=Value. Parameters are separated by the & character. The following parameters are used:
○ ProviderName – service name of XMLA OLAP source.
○ DataSourceName – name of the OLAP source.
○ Catalog – catalog (or database) name of the OLAP source.
Sample connection strings are shown below:
For Microsoft SQL Server Analysis Services:
http://localhost:80/msolap/msmdpump.dll?ProviderName=Microsoft Analysis Services& DataSourceName= host&Catalog=Adventure Works DW
For Oracle Essbase:
http://localhost:13080/aps/XMLA?ProviderName=Essbase XML for Analysis&DataSourceName=host&Catalog=Sample
23.3. EXTERNAL DATA SOURCE MANAGEMENT
Before you can use data from external sources in an application, you should specify the connection parameters for the external sources being used. The Management of external data sources function that is called in the All functions – Standard window is used for this purpose.
Fig. 320. External data source management
This form lists all external data sources created in the Designer and available to the user opened this form.
The Connected column specifies the external data source connection status in the current session.
The Administration menu can be used to specify general connection options (the Change common parameters command) and specific user connection options, if they are different from the general options (Change user parameters).
Fig. 321. Connection options
The checkbox in the left side of the form (before the option names) means that this option is used in this set of connection parameters. Options are analyzed in the following order:
Options set for the session using the SetSessionConnectionOptions() method of the external data source manager.
Options set for the user in an interactive configuration or using the SetUserConnectionOptions() method of the external data source manager.
General options set interactively or using the SetGeneralConnectionOptions() method of the external data source manager.
The resulting set of options will be used to connect to the external data source. If some option is specified in multiple settings, the first value from the list given above will be used.
For example, you can combine connection options as follows:
The user and user password are specified in session connection options.
The connection string with an external data source base is specified in the general parameters.
Another connection string is specified as a specific user parameter that can be used to test the application.
When you are specifying a connection string you should remember that access to external data are implemented not from the computer where configuration takes place, but from another computer (see the description on page 2-890).
If you click the Connect button, a dialog opens where you can set (or refine) the connection options for the selected external data source.
Fig. 322. Connection options
The necessity of specifying a user name and password in this dialog depends on the ODBC driver and connection string content. There are situations when a login and password are not needed. If the user is not an administrator of the external data source, the Change general options and Change custom options buttons are not available.
The Use operating system authentication check box is available only if the user has the SessionOSAuthenticationChange right.
When the Connect button is clicked, the external data source is connected. If the connection is successful, content of the Connected column of the external data source list is changed.
If the Disconnect button is clicked, 1C:Enterprise is disconnected from the selected external data source.
23.4. CONNECTING TO AN EXTERNAL DATA SOURCE IN 1C:ENTERPRISE MODE
When an operation is executed that needs to get data from an external data source, an attempt to connect is made if this data source wasn't already connected. If connection is successful, any actions currently being performed are continued. If connection fails, an exception is called.
If an exception is raised in a client application that is related to the connection of an external data source, a dialog is shown to the user in which he/she can refine the connection options (if the user has rights to change the options) and reconnect. If connection is successful, the user is prompted to repeat the action that caused a connection error.
NOTE
In version 8.2.13 compatibility mode, ,sometimes the external data connection dialog cannot be displayed when you are working with a dynamic list.
You can also connect to a data source manually. The Management of external data sources standard function is used to do this.
23.5. USING EXTERNAL DATA SOURCES
23.5.1. The Execution Location of External Data Source Queries
External data source queries are executed:
file mode version – on the computer with the client application.
file mode version with web server access – on the computer where the web server extension is running.
client/server variant – on a computer running the following:
○ a service for working with external data sources via ODBC: to access relational data sources.
○ a service for working with external data sources via XMLA: to access analytical data sources (OLAP systems).
23.5.2. Using External Data Sources
If an external data source contains data from both relational and analytical data sources, their simultaneous use is not supported. This is due to the fact that at any time an external data source may be connected either to a relational DBMS or to an analytical one.
23.5.2.1. In queries
To external analitycal sources
External data source tables can act as query sources. In this case an external data source table is described as follows:
ExternalDataSource.<Source name>.Table.<Table name>
Example:
SELECT Products.Code, Products.Description, Products.SKU FROM ExternalDataSource.DBF.Table.Products AS Products
This example selects the Code, Name and Article fields from the Products table of the DBF external data source.
Temporary tables can be used in queries to an external data source. In this case the system attempts to create a temporary table directly in the database connected with the external database. An exception is called when the database management system does not support the creation of external data sources. When called, the name of the temporary table is generated as follows:
ExternalDataSource.<External data source name>.TemporaryTable.<Temporary table name>
Example:
SELECT Name, ProductID INTO ExternalDataSource.AdventureWorks.TemporaryTable.Balance FROM &ValueTable SELECT Name, ProductID INTO ExternalDataSource.AdventureWorks.TemporaryTable.Balance FROM ExternalDataSource.AdventureWorks.Table.Production_Balance
When using external data source queries remember the following:
One query can use one data source. Simultaneous usage of, for example, an external data source and a 1C:Enterprise infobase is not supported.
To external analytical sources
Objects of analytical external data sources may function as query sources. The cube of an external data source may be described as follows:
ExternalDataSource.<Source name>.Cube.<Cube name>
For example:
SELECT FIRST 10 AdventureWorks.InternetSalesAmount FROM ExternalDataSource.OLAP.Cube.AdventureWorks AS AdventureWorks
In this example, the first ten InternetSalesAmount resources from the cube AdventureWorks of the OLAP external data source are received
CAUTION!
We recommend you do not obtain all records from an external data source cube.
The number of entries is very large and may cause the system to overload.
A cube dimension table of an external data source may be described as follows:
ExternalDataSource.<Source name>.Cube.<Cube name>.DimensionTable.<Dimension table name>
Example:
CHOOSE AdventureWorksMeasurementTableProduct.Ref FROM
ExternalDataSource.OLAP.Cube.AdventureWorks.DimensionTable.Product AS AdventureWorksDimensionTableProduct
In this example, a list of references to the Product measurement table members is obtained from the cube AdventureWorks of an external data source OLAP.
Temporary tables may be used in an external data source query. An attempt is made to create a temporary table directly in the database connected to an external data source. If DBMS does not support creating external data sources, an exception will be thrown. The name of a temporary table when it is queried is created as follows:
ExternalDataSource.<Name of external data source>.TemporaryTable.<Temporary table name>
If external data sources are used in queries, remember of the following limitations:
Only one data source may be used in a query. The simultaneous use of an external data source and, for example, 1C:Enterprise infobase data, is not supported.
23.5.2.2. In the Data Composition System
The data composition system can use datasets from different data sources. Thus you can create a dataset describing a list of products. Data in this dataset will be received from 1C:Enterprise infobases. Another dataset can describe data received from an external data source, for example a product sales table from an external data source.
Then you can configure any dataset links in the data composition scheme designer and receive information for the report simultaneously from 1C:Enterprise and an external data source.
23.5.3. Rules for Converting Values
When data are extracted from external sources, the system automatically converts this data to the type specified for the corresponding field in the application. Conversion is performed according to the following rules:
String type:
○ A string value is converted to a string value.
○ A numeric value is converted to a string value (the regional operating system settings on the computer where an external data source query is executed are used for the conversion).
○ The date value is converted to a string value (the regional operating system settings on the computer where an external data source query is executed are used for the conversion.
○ The unique ID value is converted to a string representation of the unique ID value.
○ Binary data (and other data types) are converted to a string that is a text representation of converted data in the hexadecimal format.
Number type:
○ A numeric value is converted to a number.
○ For a string value, an attempt is made to convert the string to a number according to regional operating system settings on the computer where an external data source query is executed are used for the conversion.
○ An exception is raised for all other data types.
Boolean type:
○ A numeric value is converted to False if it is equal to 0, and it is converted to True for all other values in an external data source.
○ Binary data are converted based on the value of the first data byte. If the first byte is 0, the conversion result will be False, and the result will be True for all other values.
○ An exception is raised for all other data types.
BinaryData type:
○ All types are converted to binary data.
Date type:
○ The date value is converted to a Date-type value.
○ An exception is raised for all other data types.
23.5.4. An external data source included in a separator
If the external data source is part of a common attribute, the following should be considered:
The structure of external data source tables is not changed, and therefore the data are not separated.
The parameters of the connection to external data sources are stored corresponding to separator values that include the external data source.
The unused separator is an individual value corresponding to which the parameters of the connection to external data sources are stored.
Therefore, when an external data source is included in the separator, you can perform one of the following use scenarios:
The data available for users of different data areas is located in different databases with similar table structures. Since the parameters are stored corresponding to the separator values, you can configure the work with the required database for each data area.
The data available for users of different areas are located in one database but access to them is controlled by database management system tools (user name and password). In this case you can specify the name and password of the user on whose behalf data are accessed.
The scenarios provided are not exhaustive. They are given only as an example to illustrate the use of the external sources of data that are part of the separator.