I decided to write an article on how to extrude data from 1C by SQL queries. All of the following applies to 1C version 8.2, it should also work in 1C version 8.1. Particular attention is paid to the problem with extracting enumeration headers.

Cultural way

Ideally, a 1C programmer should sample data from 1C. It is good if it creates processing that will output the data to the so-called “buffer database”: csv files, tables in SQL — whatever. The designer of HD and ETL should take data from the buffer.

In this case, everything works extremely well: the responsibility areas are divided, if an error is found in the report data - it is first searched for in the cube, if everything is OK in the cube - it is searched in HD, if in HD everything is OK - it is searched in ETL, if in ETL everything is good - then let the 1C programmer understand himself where he has an error in processing that fills the “buffer database”.
')
But not always this method is available. It happens that a 1C specialist is either not at all, or too busy, or there is not enough iron capacity to “push” data from 1C using processing. And one thing remains - to do data extraction using SQL queries.

Not very cultural way

This is actually this way - “to make a SQL request for 1C-base”. The main task is to write the queries correctly. I think it is no secret to anyone that in 1C the data structure is “tricky” and that the fields and tables have intricate names. The task of the ETL designer is to extract data from this structure.

View metadata

There are treatments that make it possible to see which fields of reference books / documents are in which tables / fields of the database.

Here you can download several such treatments (which we “filtered out” by sorting through a dozen of these, choosing the best ones). They do almost the same thing - they let you see all the fields, understand which field the directory is on, and even offer to automatically build a query:


Thus, we begin to investigate the documents we need:


Then open any of them, and find where it is written in - in which registers:


Well, then find this register and generate a SQL query using the above processing (as in the first picture) is not difficult.

We usually make two levels of SQL queries: “lower level” - views for renaming fields, “upper level” - views that take data from the lower level, and they already make necessary joins.

Transfers

There is one big problem - these are listings. Example:

And now if we try to pull this field out of the database directly, we get this:

Yes, we found where the headings of the enums are sitting: the table is called Config, in it is the image of the field in which the ziped set of bytes sits, which if we unzip we get an incomprehensible structure of the character set, separators, etc. Unfortunately, this data format is not documented.

Therefore, we found another way. We made a small program in C # that uses the 1C-ki COM object to establish a connection with it, and pull all the values ​​of all the enumerations into one table.

You can download it from here.

Code:

using System; using System.Data; using System.Data.SqlClient; namespace _1CEnumParser { class Program { /// <summary> ///          :  ,  ,  /// </summary> private static void DataTableFill(DataTable aTable, dynamic a1CConn) { foreach (dynamic catalog in a1CConn.Metadata.Enums) { string enumName = catalog.Name; dynamic query = a1CConn.NewObject("Query"); query.Text = "select * from enum." + enumName; dynamic items = query.Execute().Unload(); //    for (int i = 0; i < items.Count(); i++) { string enumValue = null; int enumOrder = -1; for (int j = 0; j < items.Columns.Count(); j++) { string colName = items.Columns.Get(j).Name; dynamic colValue; try { colValue = a1CConn.String(items.Get(i).Get(j)); } catch { colValue = "-1"; } switch (colName.ToLower()) { case "": enumValue = colValue.ToString(); break; case "": enumOrder = int.Parse(colValue.ToString()); break; default: throw new ApplicationException("unknown column name in enum.recordset: " + colName); } } //  3  : enumName, enumValue, enumOrder.    aTable.Rows.Add(new object[] {enumName, enumValue, enumOrder}); } } } /// <summary> ///   ,  DataTable  ,    /// </summary> /// <param name="aConnectionString1C"></param> /// <param name="aConnectionStringSQL"></param> /// <param name="aTableName"></param> private static void ConnectAndFill(string aConnectionString1C, string aConnectionStringSQL, string aTableName) { //   SQL       var connSQL = new SqlConnection(aConnectionStringSQL); connSQL.Open(); //   1 var connector1C = new V82.COMConnector(); dynamic conn1C = connector1C.Connect(aConnectionString1C); //      var command = new SqlCommand("delete from " + aTableName, connSQL); command.ExecuteNonQuery(); //   var da = new SqlDataAdapter("select EnumName, EnumValue, EnumOrder from " + aTableName, connSQL); var thisBuilder = new SqlCommandBuilder(da); var ds = new DataSet(); da.Fill(ds); DataTableFill(ds.Tables[0], conn1C); da.Update(ds); //   connSQL.Close(); } static void Main() { string[] args = Environment.GetCommandLineArgs(); string aConnectionString1C = args[1]; string aConnectionStringSQL = args[2]; string aTableName = args[3]; ConnectAndFill(aConnectionString1C, aConnectionStringSQL, aTableName); } } } 

Runs like this:

 1cEnumParser.exe "__1" " __SQL" "__SQL" 

It does the following: connects to 1C using COM, takes all the enumerations from there, and puts them into the table of the specified database you specified, after having cleaned it. The table should have the following structure

 CREATE TABLE [dbo].[tbl1CEnums]( [EnumName] [nvarchar](1024) NULL, [EnumValue] [nvarchar](2014) NULL, [EnumOrder] [int] NULL ) ON [PRIMARY] 

Further, it is clear that the SSIS package (or other mechanism) can run this code before retrieving the facts / references data, and we will get a filled table


and then you can already build a join across the _EnumOrder field: the directory refers to the _Enum table by IDRRef, in it there is the _EnumOrder field which refers to the EnumOrder field of your table that you just pulled out the C # code.

If you have comments or additional ideas - they are all accepted with pleasure, write to ibobak at bitimpulse dot com.