We're glad to announce the new version of Active Query Builder Java Edition. You can download the latest free version or request the trial at the download page.
What's new in the Active Query Builder Java Edition version 1.5?
Ability to show query by parts and to modify sub-query text apart from the main query
The new property PlainTextSQLBuilder.get/setTargetQueryPart is added. It allows you to generate the SQL query text not for the whole query, but for active part of the query only.
Possible values are the following: Query (default): generate SQL text for the whole query; SubQuery: generate text for the currently active sub-query tab; UnionSubQuery: generate text for the currently active union sub-query.
PlainTextSQLBuilder.get/setSQL property become writable to apply changes in the currently active query part to the query builder.
The new QueryBuilder.get/setFilterTabsByActiveQueryPart property is added. It allows to hide the tabs that do not belong to the currently active query part.
Possible values are the same as for the PlainTextSQLBuilder.TargetQueryPart.
Ability to add the necessary objects automatically to link the newly added object with existing objects in the query
The new properties are added to LinkOptions property group: get/setAddLinkedObjects(), get/setDeleteUnusedObjects().
The first property instructs the component to add the necessary objects to join the newly added object with existing objects.
For example, using the Northwind database, by adding Orders and Products tables to the query, the [Order Details] table will be added automatically and joins will be set according to the foreign keys.
The DeleteUnusedObjects property instructs the component to remove objects from the query if their fields are not used in the query and these objects are no longer used to link other objects.
General improvements
The new row controlling toolbar is added to the leftmost column of CriteriaListControl. You can control visibility of this column and it's state by using the is/setRowControllerVisible() and is/setRowControllerExpanded() properties.
The check box is added to the Grouping column header. It allows to toggle the grouping for all criteria items at once.
Default value of "Criteria for" for non-aggregated query columns is changed "For groups" (HAVING) to "For values" (WHERE).
Performance optimizations are made, memory leaks are eliminated.
Spanish translation is updated.
API improvements
The new GetProcedureParameters event is added to let provide procedure parameter values without showing of the embedded dialog.
The new get/setCreateWhereJoinsByDefault() property is added to all syntax providers. The newly created link conditions will be added to the WHERE clause instead of creating of joins in the FROM clause.
The new SQLBuilder.BeginBuildSQLForNode, SQLBuilder.EndBuildSQLForNode, BaseSQLBuilder.BeginBuildSQLForNode, BaseSQLBuilder.EndBuildSQLForNode events are added to perform additional processing on building the SQL query text.
The new methods are added to the UnionSubQuery: Clear(), getSQL(), setSQL().
The new UnionSubQuery.ConvertDatasourceToDerivedTable() method is added.
The new refreshDatasourcesMetadata() method is added. Use it to update bindings between query and metadata objects. Might be useful if you modified or reloaded metadata while a query is loaded in the query builder.
The new is/setCreateLinksByIdenticalFieldNames() property is added. It may be helpful for the databases that does not foreig keys, but suppose to link objects in the query by identical field names.
The new UnionSubQuery.addLinksFromExpression() method is added.
The new methods are added to help iterating through sub-queries in the query: QueryBuilder.getSubQueryList(), QueryBuilder.getUnionSubQueryList(), UnionGroup.getUnionSubQueryList().
Formatting options for CTE sub-queries are added via the PlainTextSQLBuilder.get/setCTESubQueryFormat() property.
The LinkCreating and LinkCreated events will be also fired for links being created automatically from foreign keys now.
SQL syntax fixes and improvements
DB2: The new get/setNamingConvention() property is added to affect the object name separator (slash or dot).
PostgreSQL: undocumented SUBSTRING function syntax "SUBSTRING('a','b','c')" will being automatically converted to "SUBSTRING('a' FROM 'b' FOR 'c') during the parsing.
Oracle: The bugs with "ORDER SIBLINGS BY" clause and "TRANSLATE" function are fixed.
Oracle: The bug when removing references to datasources from "CONNECT BY" clause. is fixed.
MS SQL Server: The bug with "OPENQUERY" function is fixed.
MS SQL Server: Definition of the "FOR XML" clause is updated according to the latest server version.
MS SQL Server: Loading of metadata objects from linkes servers is fixed.
MySQL: The missing built-in functions are added: COALESCE, GREATEST, LEAST and INTERVAL.
SQLite: Fixed list of aggregate and built-in functions.
SQLite: Loading of fields and foreign keys is made using PRAGMA queries.
PostgreSQL: Parsing of logical expressions from criteria cells is fixed.
Firebird: The "INT" alias is added for INTEGER type.
Significant bug fixes
Column aliases generation in ORDER BY is fixed.
The problem when diagram object could be moved off-pane is fixed.
Metadata object names hash was not rebuilt after MetadataContainer assignment. Fixed now.
The UnionSubQuery.setSQL() method did not remove the previous union sub-query content. Fixed now.
The QueryBuilder.clear() method is fixed.
The bug with generating invalid SQL when object and database names are equal is fixed.
The bug with adding derived table of CTE into the same CTE is fixed.
The bug when "ORDER BY" is added to a query with miltiple UNIONs is fixed.
A link might be attached to wrong datasource on the design pane in some rare conditions. Fixed now.
We have been using Active Query Builder for over a year and must say that both the product and support have been outstanding!
We chose Active Query Builder due to its flexibility and features, but have been truly pleased by its power and hidden capabilities. ...
In summary Active Query Builder provides excellent components, great support and a very flexible feature set. It has allowed us to provide features to our end users that I did not think would be possible in the first release of our new tools and in a timeframe that was much shorter than planned. I would recommend that anyone dealing with databases in the .Net world should be aware of this component and its capabilities!