Visual Query Builder: component wish
Since the readership here might be well-placed to make a few product
I've been to ComponentOne. They don't have what I want.
What I have been wanting, for 5 years, is a component for building queries
visually --in an enterprise setting. The component would be (visually) a
knock-off of the visual query builder in Microsoft Access or Paradox, where
tables/views are represented by rectangles containing column names, and you
drag a table onto the query pallette to work with it, and where joins are
represented by connecting lines, and join-charactertics can be edited by
double-clicking on the line. See Access for details. BUT,
I want this component to work in UNBOUND mode. I want to be able to handle
all database I/O myself. UNBOUND mode is actually more important than bound
mode for its target audience of enterprise developers. This component does
not have to make any connection to a database!!!! It does not have to know
how to write to the database or concern itself with permissions. I want to
be able to explicitly specify the tables and columns that the users may see,
and establish the primary key/foreign key relationships, and the datatypes,
I want to be able to specify the joins and put the component optionally into
auto-join mode, at run-time.
I want the component to return the SQL command to me, NOT AS A STRING, but
as a read-write hierarchical SQL **object**.
I want to be able to modify any of the WhereConditions or substitute a
different table in the Tables collection (making appropriate changes to the
items in the Joins collection) and then get a reformulated SQL command
myCommand$ = oSQL.ToString
Additional intelligence: the component should be able to identify [maybe
in version 2 ;-) ] whether the SQL command would produce a cartesian
product, or whether two joined columns are of a different datatype. In
version 1, the oSQL object has to be constructed so that I can easily
iterate the collection classes and determine such things for myself.
I must be able to examine the Joins collection, see which columns are joined
to which, and find out the datatypes of the columns. I should be able to see
which tables are involved in the query and whether any of them is not joined
to another table.
The component should be able to produce valid SQL command strings for
various target databases (SQLServer and Oracle for starters); the database
should be changeable at run-time:
oSQL.TargetDatabase = VQB_Targets.VQB_ORACLE
Such a product, if done well, would be a very successful app in the
enterprise space, IMO. What would I be willing to pay for such a component?
~$599 for a single seat with royalty-free deployment. $2500 for a
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center