2.3. How to retrieve 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.
If you need to analyze the query immediately after its assignment, for example, while loading it from the file, use the QueryBuilder.SyncSQL property. In this case the update of internal query representation objects and query statistics will occur immediately after the assignment.
- After the assignment of the query text to SQL and LayoutSQL properties, the update of the internal query representation objects won't occur immediately but asynchronously in the next message processing cycle. The SQLUpdated event will be fired after the update. This was made to allow the user work with both the SQL text and graphic query presentation simultaneously using the text editor visible at the same time on a form to avoid impacts during possible simultaneous programming and visual change of the query, but now these problems are gone. In the next major version all SQL assignment properties will work synchronously.
- 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. While assigning the query text to any properties described above, the component is trying to parse the query text. During the parsing process all elements irrelevant for the parser (spaces, line breaks and even comments) are ignored. And the so-called "abstract syntax tree" (AST) is being built from the query text. In general, AST is a set of tokens. After that this set is compared with the one loaded into the component before, and if they equal, the substitution of the old tree for the new one doesn't occur. Therefore the 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.