3.3. How to get the result SQL query text?
Since the retrieval of the resulting SQL query text is the main task of the component, it can be performed by different ways subject to the current situation and needs.
- QueryBuilder component properties
The QueryBuilder.SQL property determines the retrieval of the ordinary unformatted query text. To track any changes of this property you should use the QueryBuilder.SQLUpdated event. To disable the firing of this event during the programmatic change of the query, use BeginUpdate and EndUpdate methods.
The SQL property also lets you load the query into the component. The component needs the SQL text only to load the query. Just assign the SQL text to the QueryBuilder.SQL property and the user will get its visual presentation on the screen.
Still there's another QueryBuilder.LayoutSQL property in the component. It allows to save and to load the location of objects within the diagram along with the query text to save the layout between work sessions.
Note: The SQLUpdated event won't occur if you try to load the query text identical to the query that is loaded into the component already.
- On assigning the query text, Active Query Builder tries to parse it. During the parsing process the component builds the "abstract syntax tree" (AST) from the query text (all elements that irrelevant for the parser, such as spaces, line breaks, and even comments are ignored). In fact, AST is a set of meaningful tokens. After that, the new AST is compared with the old one, and if they are equal, the substitution of the old tree for the new one doesn't occur. Therefore, multiple assignment of the same syntactically correct query won't lead to the multiple firing of the SQLUpdated event, but the multiple assignment of the incorrect query will lead to the multiple firing of the SQLError event.
- PlainTextSQLBuilder component properties
The main task of the PlainTextSQLBuilder component is acquisition of the query text formatted in accordance with user-specified settings, though it has some other useful properties in the last versions of the component.
To work with PlainTextSQLBuilder you need assign an instance of the query building component to the PlainTextSQLBuilder.QueryBuilder property. After that you'll be able to get the formatted query text from the PlainTextSQLBuilder.SQL property.
In the general case you can use two and more PlainTextSQLBuilder components connected to one QueryBuilder component. It might be useful when you want to show the user the query text that is easier to read and understand, and when you want to use the unmodified variant of the same query for execution on the server. To ease understanding of queries by an end user you can use Alternate names and Virtual objects and fields. While using these features the control over the query text generation is performed with the help of UseAltNames, ExpandVirtualObjects and ExpandVirtualFields properties.
The last version of the component lets change the sub-queries text independently of the main query. Surely, when we use visual builder, we may talk about altering of the active (currently visible) sub-query text. For this purpose the PlainTextSQLBuilder component has the PlainTextSQLBuilder.SQLUpdated event which is similar to the QueryBuilder.SQLUpdated event, but it is additionally fired on changing of the currently active sub-query when PlainTextSQLBuilder.TargetQueryPart property is set to SubQuery or UnionSubQuery value. In such a mode the assignment to the PlainTextSQLBuilder.SQL property will lead to the change of the currently active sub-query text only but not of the whole query. You may read about this feature in detail here.