Query Console 8.3.

So, let's start with a simple one: in the configurator, create a New Processing, name it Query Console or Query Builder, as you like.

We can immediately add a label to the "Data" for future Parameters that we need so that in our Console we run not the most primitive request, but with parameters and links, for example, for our work we will create a request to periodic registers, but here without specifying Parameter=&Date nowhere.

To create our Parameters table, on the "Data" tab in its "Tabular section" add a new table, let's call it Query Parameters, here we add the columns of this table: 1) ParameterName, string type = 25 characters; ParameterValue, here is a composite data type, see fig:

Therefore, as shown in the picture - select the composite type for the ParameterValue column: in the type menu that opens, check the "Composite type" checkbox, select the number, string (specify 20 characters), date, boolean, and put the lowest checkmark - AnyReference - it means that further, when specifying the Parameters of our request, we can refer to any object of our configuration, for example, directories or documents.

Now we need to create the form of our future Query Console. In processing, go to the "Forms" tab and add a new one. We enter this form and there is already an unlimited field for creativity - you can arrange the two attributes you just created and the plate with the parameters as you like! For this you can use standard form elements like Group or Page with Pages (if you like turning pages more.

The main thing here is one thing: by dragging the attribute "TextValues" into the left edit field of the form - be sure to set "View" = Field of the text document in its properties. See figure:

In the properties of the "QueryTable" attribute, you can optionally specify - "Display Grid" and "Display Headers".

Next, in the right form editing window, go to the "Commands" tab and add a new button, when clicked, our Console will perform a certain action. Let's create the "Query Constructor" Button, at your own request you can add an icon to the button, the main thing is to move the button itself to the left form editing window - so that we can see it. Then, in the right form editing window, right-click on our button and select properties - in the properties, click on the "Action" item, a modal window will appear with the question - where exactly will our program code be executed, which we will assign to the button - select "On the client".

The Form Module will open with a ready-made empty procedure "Procedure Request Constructor (Command)". Inside this procedure, we will describe the call to the standard Query Builder 1c8. It is very easy: Constructor=New Query Constructor; But there are pitfalls here - the Query Constructor built into the platform works in user mode ONLY under a thick client! Therefore, we will insert the condition of the preprocessor instruction #If, but here you decide for yourself, based on your platform, or you have ordinary forms, then choose " ThickClientOrdinaryApplication" or you have a managed forms platform, then " ThickClientManagedApplication". see fig.

Now it remains to add to this procedure a condition for writing the request text, which the Query Builder will generate for us in our attribute of the "Text Request" form:

If Constructor.OpenModally()=True Then Object.QueryText=Constructor.Text; EndIf;

But we can manually change something in the text of the request (in user mode - in the "QueryText" attribute window), so that our changes get into the QueryConstructor when it is called again - let's add a simple condition here:

If Not EmptyString(Object.QueryText) Then Constructor.Text=Object.QueryText; EndIf;

That's it, we have connected the Query Constructor built into the 1c8 platform, let's look at our work. To do this, run 1C:Enterprise in thick client mode using one of the following methods: 1) main menu of the Configurator - Debugging - StartDebugging - ThickClient; 2) or if you have these keys on the control panel in the configurator - just press the button with a yellow circle with a thick dot, see figure:

The 1sEnterprise8 user mode is launched, we find our processing, launch it, click on our "Query Constructor" button and see how the constructor built into the platform opens. see fig.

So, the Query Constructor is starting up, we can already start adding our future request in it, but we are interested to see how the request we created will work out! And for this we need to create another button in the configurator in editing the form of our console, let's call it "Run Request". In the properties of the "Execute Request" button, click on "Action", the menu again drops out, in which we are asked where our program code will be processed, in this case, select "Both on the client and on the server", we again get into the ModuleForms.

In the ExecuteRequest() procedure, which we have on the client, we will write a condition if the user has not entered the request text, but asks to execute it:

If EmptyString(Object.QueryText) Then report("Enter the query text!"); EndIf;

The system has already automatically generated a link to the ExecuteRequestOnServer() procedure for us; - that's good, let's go to this procedure, which is executed on the server and write here the code for executing our entered request.

There are options here: You can write all the expressions related to query building yourself, i.e. manually, but there is an even easier option - inside the procedure, right-click and select "Query Builder with results processing, see figure" in the drop-down menu:

If you clicked on the Query Builder with Result Processing item, the modal window "Query text not found. Create a new one?" will pop up, click yes. Bypassing the result". That's it, we don't need anything else from this constructor, click on the "OK" button - a modal window will pop up "No fields were selected in the request, click "OK".

After that, inside our procedure ExecuteRequestOnServer(), the following ready-made blank will appear:

Let's move on to the expression built by the constructor:

Request.Text = "";

Request.Text =Object.QueryText;

Everything is so simple, our "Perform Request" button on the processing form is already practically operational, so far it can only process simple requests without parameters, but the main thing is that it works! It remains only to display visually in the "Value Table" prop on the processing form - the results of our request. Let me remind you that our attribute "Table of Values" has the type "Spreadsheet Document", because otherwise we will not see our results in the user mode. The user is always engaged in the output of tabular data either by a Spreadsheet document or a Layout, I would really like it to be possible to display data through a table of values - since it is very easy to use and familiar, but, unfortunately, a table of values is just a tool that a developer needs , you cannot display data on the screen using it ...

Let's take a closer look at what a Spreadsheet Document is - it's like an Excel sheet - you can get to a record in a specific cell only using the Table Cells, here we call them an area, but we ourselves can select the range of this area into one specific Cell:

So, we figured out what a spreadsheet document is, determined for ourselves that we need to determine the data from our query in a specific cell of this spreadsheet document. But let's think: what is the "RequestResult" that the constructor generated for us so quickly? Opening the help - The query result is a table that has the appropriate properties! see fig.

And if we now write after the expression QueryResult = Query.Execute(); (created by the constructor) here is such a simple loop for Collections:

For each ColumnName From QueryResult.Columns Loop report(ColumnName.Name); EndCycle;

After this loop, for the time being, check out all the expressions built automatically by the constructor. And run 1C:Enterprise8 under a thick client. Create any simple request (You can use the Query Builder - it already works for us) and click on the "Run Request" button:

Then you will see at the bottom of the window in the messages - that the QueryResult table stores the names of the fields that we have just selected by creating a simple query.

Now let's display these names of the fields of our suffering in a Spreadsheet document:

For each ColumnName From QueryResult.Columns Loop Cell=Object.QueryTable.Scope(1,QueryResult.Columns.Index(ColumnName)+1); Cell.Text=ColumnName.Name; EndCycle;

To display the details on the query data, let's unmarshal the expressions created automatically by the constructor and insert exactly the same loop that we used to display the column names inside the "SelectionDetailedRecords" loop, only now we need to transfer not the data of the "QueryResult" table to the Cell text, and the data of the Selection itself, let's see in the help how you can access the field of the Detailed selection of the query:

SamplingDetailRecords = QueryResult.Select(); While SelectionDetailedRecords.Next() Loop //in the first line we already have the names of the table columns, so we load the data below the first lineDocumentLineNumber=Object.QueryTable.TableHeight+1; For each ColumnName From QueryResult.Columns Loop Cell=Object.QueryTable.Region(DocumentRowNumber,QueryResult.Columns.Index(ColumnName)+1); Cell.Text=SelectionDetailRecords[ColumnName.Name]; EndCycle; EndCycle;

That's all, we can check, load the enterprise under a thick client, enter a simple request without parameters, click on the "Run Request" button, see figure:

Hurrah, everything works!!!

It is very convenient when, when opening / closing our Query Console, our query text, which we worked with before closing the console, is again written in the "QueryText" field. To do this, you just need to enable the form property = Autosave see pic:

Everything, our console works. So that we can write more complex queries with parameters specified in them, we need to create another "FindParameters" button, as well as the code for the "RunQuery" button - the code for the "FindParameters" button will be executed on the client and on the server. Further, in the server procedure, we launch the request in the same way with the text passed to it from the "QueryText" window, using the "Query.FindParameters()" expression, we find the passed parameters and simply enter them into the tabular part of the "QueryParameters" form in a loop. Don't forget to pass them from the completed parameter table to the "Execute Request" procedure later.

You can also add a couple of buttons to our Console that will clear the Parameters window and the Query Text window in user mode.

Our Query Console is ready to use, wish you successful creative solutions with the help of such a simple and powerful tool as the Query Console!

This processing is written on the 1s8.3 platform (managed forms), it runs under a thick client. It can also be written on the 1s8.2 platform both under normal forms and under managed ones.

In the download is a sample of the Query Console we just created.

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Query Console Upgrade:

1) Now our homemade Query Console with built-in Query Builder will run under any client: under the thick client of normal and managed forms and under the thin and web client.

ps The form and appearance of the built-in Query Builder is different - depending on which client we launched our Console under. (Personally, the Query Builder form under a thick client is more familiar and convenient for me)

&AtClient Procedure Query Constructor(Command) //calling the standard Query Constructor is possible only under a thick client #If ThickClientManagedApplication or ThickClientRegularApplication Then Constructor=New Query Constructor; If Not EmptyString(Object.QueryText) Then Constructor.Text=Object.QueryText; EndIf; If Constructor.OpenModally()=True Then Object.QueryText=Constructor.Text; EndIf; // #Otherwise // Report("The Query Builder can only be called under a thick client"); // Return; //# EndIf #Else Report("You are running Query Builder under a thin client - it differs slightly in its form and performance speed!"); Constructor = New Request Constructor(); If Not EmptyString(Object.QueryText) Then Constructor.Text=Object.QueryText; EndIf; AlertConstructor = New AlertDescription("RunAfterClosingConstructor", ThisForm); Constructor.Show(ConstructorAlert); # EndIf EndProcedure &AtClient Procedure ExecuteAfterClosingConstructor(Result, Constructor Parameters) Export //Result=text if Concer was closed by ok button Object.QueryText = ShortLP(Result); //works!!! EndProcedure

2)Added the ability to our simple Query Console to enter complex queries with the Temporary Table passed to the parameters!!! The mechanism turned out to be very simple and elegant - without the use of XML code, as they do in professional consoles.

You can see the code itself and the procedures for the mechanism of transferring to the parameters of Temporary Tables in the second attached file. How to start developing your own version of Temporary tables in the parameters can be viewed at this link https://forum.site/forum9/topic183700/

Now how to use the Console with a complex query, when a temporary table is passed to its parameters. For example, you can take the code of this request;

SELECT ExternalData.Product, ExternalData.Quantity PUT ExternalData FROM &ExternalData AS ExternalData; ////////////////////////////////////////////////// ////////////////////////////// SELECT ExternalData.Item, ExternalData.Quantity, ISNULL(ItemsRemainsRemains.QuantityRemains, 0) AS Field1, ISNULL(StockItemsRemains.QuantityRemains, 0) - ExternalData.Quantity AS Remaining FROM ExternalData AS ExternalData LEFT JOIN software statistics ExternalData.Product = RemainingProductsRemains. Product

Similar to the query code above - you can create your own complex query, taking into account your data objects.

So, in the query constructor, we created the above query by closing the Constructor - the query text will fall into our field of the console "Query Text", click on the "Find Parameters" button, we see that the string = "External Data", value type \u003d "Value Table" appeared in the Parameters table , see fig.

In this Table of Parameters - enter the Date parameter, for example, today's date, then click to attempt editing on our parameter of the temporary table "ExternalData", click on three dots in the field with "Value Table" - a choice of types will appear, click the line, our mechanism turns the page on the form for us, where we need to manually enter this very temporary table.

Here, note that in this case, on the "TempTables" page at the bottom in the "Temporary table name in parameters" field, the name of our temporary table will appear (it is copied from the Parameters table).

So far, on the "TempTables" page, we see only one empty table - this is the table of Types of our future temporary table. Using the "Add" button, we will add the name of the attribute and type of the future table. Be careful - the name and type must match what we specified in the request for &ExternalData:

Now we press the "UpdateTemporaryTable" button - and we will have a second table here - we will directly fill it with the data of the temporary table through the "Add" button.

That's it, we can double-check ourselves once again whether we entered the primitive query parameter data in the parameter table on the 1st processing page, and click the "Run Request" button - everything counts, selects accordingly with the restriction on the data transmitted in the parameter of our temporary table

ps If you made a mistake when typing the names of the details and their types (in the first table) - just close the Console and open it again - the temporary data table will be deleted - and the Types table can be edited again and a new data table can be created again.

That's all, we can create a very powerful working tool with our own hands, in addition, our console is still very nimble compared to professional ones - and this is a very big plus for developers! And, of course, now our console works under any client! Good luck with your creative developments!!!

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

I am constantly working with my Query Console, and recently I came across a very interesting request, in which Temporary tables were not in the first request package, but in the next ones - and here my console got a little depressed ... I had to do another upgrade.

So, the Temporary Table manager in the query does not always understand that they are working with it)) Therefore, you need to explicitly set this very Temporary Table manager:

&OnServer Procedure ExecuteQueryOnServer() //upgrade2 - explicit definition of TemporaryTable manager! VTManager=NewTempTableManager; Request = New Request; //upgrade2 - explicit definition of the TemporaryTable manager! Query.TempTableManager = VTManager; Request.Text = Object.QueryText;

This version of the console is in the third boot.

And fast information. In this article, I will try to describe how to use the Query Console and provide a link to download the Query Console.

Let's take a closer look at this tool.

Download request console 1C

First of all, to get started with the query console, you need to download it from somewhere. Processing is usually divided into two types - managed forms and regular (or sometimes they are called 8.1 and 8.2 / 8.3).

I tried to combine these two views in one processing - in the desired mode of operation, the desired form opens ( in managed mode, the console only works in thick mode).

You can download the 1C 8.3 (8.2) request console for managed forms and regular ones at the link:.

Description of the 1C query console

If you are interested in how queries are written in the 1C system, I recommend that you read the article and.

Let's start our consideration of the query console with a description of the main processing panel:

In the header of the query console, you can see the execution time of the last query with an accuracy of milliseconds, this allows you to compare different designs in terms of performance.

The first group of buttons in the command bar is responsible for saving the current queries in an external file. This is very convenient, you can always return to writing a complex query. Or, for example, store a list of typical examples of certain constructions.

On the left, in the Request field, you can create new requests and save them in a tree structure. The second group of buttons is just responsible for managing the list of requests. With it, you can create, copy, delete, move a request.

Get 267 1C video lessons for free:

Run request- simple execution and getting the result;
Execute package- allows you to view all intermediate requests in
Viewing temporary tables- allows you to see the result that temporary queries return in the table.

Request parameters:

Allows you to set the current parameters for the request.

In the query parameters window, the following is interesting:

Button Get from request automatically finds all parameters in the request, for the convenience of the developer.
Flag Single parameters for all requests- when installed, its processing does not clear the parameters when moving from request to request in the general list of requests.

Set a parameter as a list of values very simple, when choosing a parameter value, it is enough to click on the value clear button (cross), the system will prompt you to select the data type, where you need to select "Value list":

Also in the top panel there is a button for calling the query console settings:

Where you can specify query autosave options and query execution options.

The request text is entered in the console request field. This can be done with a simple query test set or by calling a special tool - the query constructor.

Called from the context menu (right mouse button) when clicking on the input field:

Also in this menu there are such useful functions as cleaning or adding hyphen characters (“|”) to the request, or getting the request code in this convenient form:

Request = New Request; Request. Text = " |SELECT | Currencies.Reference |FROM | Handbook. Currencies AS Currencies"; RequestResult = Request. Execute() ;

The lower field of the query console displays the query result field, for which this processing was created:

Also, the query console, in addition to the list, can display data in the form of a tree for queries containing totals.

The Query Console is very convenient to use when you need to debug some complex , or quickly get a selection of certain data.

You can find many different consoles on the internet. For regular and managed forms, these are usually completely different consoles. According to this, you can download the universal query console 1C 8.3 and 8.2, which will work in both cases. Just keep in mind that the console on the managed interface will open only in the thick client.

Working with the Query Console

In the 1C program, the query console opens in the same way as any external processing: the menu "File" - "Open", or using the hot key combination "Ctrl + O". With frequent use, it can be added to external processing of the infobase itself.

The list of your requests is presented on the left side of the request console. This allows you to store not just one request, but several at once. The query tree has a hierarchical structure, which is very convenient for grouping them.

By clicking on the "Add" button, you can add a new request by giving it a name yourself. Also, the built-in functionality allows you to create queries with copying, move up or down the list, and much more.

The request text itself is displayed on the right side of the processing form. It can be written manually or using the query builder. The query builder only works in the thick client.

To the right of the constructor button is the "Insert Query Operators" button. It will immediately add an empty query structure with a condition, grouping, order, and totals.

To the right are very useful buttons that will help if you use this query when developing solutions for 1C.

The button allows you to convert the query you wrote into text. In our case, it will look like the image below.

You can save requests to a file or load from it. This is very convenient for future use.

Before directly executing the query, you need to set its input parameters (if any), then click on the "Run query" button. Also here it is possible to execute requests not entirely, but separately any package.

The view mode determines how the query result will be displayed: a list, a hierarchical list, or a tree.

The result of the execution will be displayed at the very bottom of the request console. It also displays the number of rows received and the execution time. If necessary, you can move the rows of the query result.

Very often, users are asked to select some data and save it in Excel. You may need this too. To complete the task, you just need to select the “Display list” item in the “More” menu. The program will prompt you to mark the required columns.

In addition to setting the list of columns for output, you can also specify specific rows here. This is done by highlighting rows in the query itself. Further, in the column selection form, it is enough to set the flag in the “Only selected” setting.

In our example, we have displayed all the query result data. You can save them to an external file by copying them from the opened spreadsheet document, or by using the Ctrl+O hotkey combination. There are many options for saving formats, we chose "*. xlsx".

As you can see, working in the query console is very convenient and useful, so we strongly recommend downloading it and putting it into practice.

Query Console 1s- a convenient tool for building and debugging queries in enterprise mode 1s. Using the Query Console You can execute a request to the 1s database and see the result of the request directly on the screen in 1s enterprise. In this article, we will consider two consoles, one for 1s 8.2 (regular forms), and the second can work both in normal and managed forms 1s 8.3.

Download query console 1s

In order to start working with processing, you first need to download query console 1s, below we will give download links, but for now let's note the fact that the 1s request console can be classified and classified into one of the following types:

For regular forms (1s 8.2)
For managed forms (1s 8.3)
Mixed (works both on 1s 8.2 and 1s 8.3)

In our article, we will consider two consoles, the first works only on 1s 8.2 (regular forms), and the second works on 1s 8.3 (managed forms). They are slightly different from each other, we will talk about the differences further, but for now, we note that in managed applications, the console will work properly only in the "Thick Client" launch mode.

Download the 1s request console for a regular application 1s 8.2

Below is a screenshot of the processing, you can download the 1s request console for a regular 1s 8.2 application

Download 1s request console for managed application 1s 8.3

And this is the second console, you can also see its screenshot on the screen below, using the link you can download the 1s request console for the managed application 1s 8.3

Description of the principles of operation and the main functionality of the 1s request console for a regular application 1s 8.2

This query console 1s 8.2 for a regular application easy to use and does not require any special knowledge from the user. On the screenshot you can see a test field for writing a query, you can write a query in it both manually and using the constructor. Under the number 1 in the figure you see the "Parameters" button, by clicking on it we will see a form for substituting parameters into the request, we will return to it later. Under the number 2 you see the workspace and the action bar with the request itself, here we can add requests, delete them, and also save them to a separate file on disk. Under the number 3 in query console 1s 8.2 located setting result output, it can be displayed in the form of a table or hierarchically in the form of a tree, the second option is used when we need to see the hierarchy as a result of the query. Item 4 shows us whether there are temporary tables in the query, if there are, then we can debug them and see the result of the execution, which is placed in a temporary table.

Now let's look at some individual points in query console 1s.

Above you see a window in which parameters are substituted into the request. By clicking on the "From request" button, we will receive the parameters that are specified in the request, now we just have to set the desired values and close the form. By the way, if you are editing a request and your previously added parameters on the form are no longer used, they will be highlighted in gray. If there are many such parameters, they can be removed from the window using the "Delete unused" button.

If you have temporary tables in your query, then you can debug them in separate subqueries and see the result of executing this subquery, if in the main window of the 1c query console we see that there are temporary tables, then click on the "Temporary tables" button (point 4 on the main console window). After clicking, the window that we saw above will appear, it shows all the temporary tables of the query, click "Debug" and we will see the following window.

Brief description of the functionality of the 1s request console for a managed application 1s 8.3

We got acquainted with the main functions query console 1s 8.2, now it's time to consider query console 1s 8.3. We will not consider this processing in such detail as the previous one, but briefly go over the main parameters and settings.