Module 5. Episode 7. Queries in the script

1C Junior Developer training course
Module 5. Queries basics
Link to the training course:

So, now we know how to tell a query what data we want from the database. But that’s not the whole story, right? Besides that, we need to create and run a query and then process the results. This is what we are here to talk about today.

Let’s take another look at the example we’ve already seen. This is the Sales document Posting event handler, that (among other things) checks if we have enough products left to commit this business transaction. And this is the query that does the job. It starts running after we wrote all the document’s data to the Inventory register. It takes all the products from the document, we’re currently posting, and looks up the Inventory balance for each product, filtering out positive balances and services.

So, let’s trace this Posting procedure execution and see what’s going on here at the runtime. So, these are my Sales documents. I’m opening and posting one of them, and we are at the breakpoint. Let’s check out what the Syntax Assistant has to say about working with queries. And here we go. This is the Query class, and we use the universal New constructor to create its instance, just like this. Then we fill out this Query object’s Text property like this. Then we use this SetParameter method to set the only query parameter value to the current document’s reference. And then we run the query using the Execute method of the Query object. So, by now the query is executed on the database side, and the result is ready for us to process.

The Execute method returns the object of the QueryResult class that lives in the same Syntax Assistant neighborhood. What’s interesting about this guy is that it features the IsEmpty method that knows if the resulting recordset contains any data or not. So, we probably should use it right here instead of trying to process a potentially empty result. It should look something like this.

But anyway. The next line of code passes the query results to the object of the QueryResultSelection class, which behavior might seem somewhat confusing, so let’s take a closer look. Here is this class in the Syntax Assistant and it looks like we can get the resulting recordset fields’ values using it. Except there might be several records in the query output, so which one will we get? OK. Let’s just check it with the debugger. And the value is undefined.

But as soon as I run this Next method the first time, I get my first resulting record. One more Next and here is my second record. So, this is, basically, what they call a CURSOR in SQL. You can think of the QueryResultSelection object as of the one-record-wide window you can drag your resulting recordset behind, fetching records and processing their fields values as you go. As soon as the QueryResultSelection runs out of records, the Next method returns False like this, and the application moves on doing its thing. This is how we use queries in the 1C script language.

erp training for beginners,1c enterprise tutorial,1ci academy,1ci,1с enterprise development tools,programming for beginners,getting started with 1c enterprise,education,1c international,1C Enterprise training course,enterprise application platform,erp platform,1C enterprise,software development,business app development,business software development,development platform,erp training,enterprise application development,business process automation,erp,