SQL Query builder in Delphi

Question

I need to give users the ability to build a simple SQL query against our database. Our application is written in Delphi.

I am assuming only moderate levels of knowledge by the user, but they need the ability to build a simple select statement to be able to query against a couple of tables. If I can make this easy for them, that would be most wondrous.

Does anyone know of a tool or a set of components that I can use to help the users build SQL SELECT statements...

Answer 1

It seems like you're basically asking for what you're trying to write, a user interface to the SQL DB. So, there's not going to be many 'components' to do this for you. :)

If you think they can write an actual Select statement, then that should be easy to put into any existing program:

Query.ReadOnly;
Query.SQL := ValidateSQL(Edit.Text);
Query.Active := True;

I would think you either have to limit it to basic searches via text boxes and drop-downs; or spend a lot of time validating, and limiting, the Select-like statements you seem to want to give them access to, so that they don't go getting at stuff they shouldn't.

The more tables involved, the more difficult the interface is going to be to write. :)

Answer 2

I've used the DevExpress ExpressFilter control to allow users to specify SQL where constraints before.

Answer 3

I've written a couple of similar things in Delphi. It's easy enough to allow the user to pick table and column names by querying the metadata and using lists and tree views for display. The difficulty comes when trying to implement things like joins. I've never come up with a good interface for this, and in my experience neither have many of the major data tools players.

Answer 4

I've come across FastQueryBuilder http://fast-report.com/en/products/visual-query-builder.html and OpenQueryBuilder http://fast-report.com/en/products/free-query-builder.html (apparently differing versions of the same tool) at Fast-Report. It looks like the right idea. Anyone have any experience with it?

Answer 5

I've used SimpleQuery for years and am quite happy with it. (http://devtools.korzh.com/eq/vcl/) It's not completely intuitive, but once you get the hang of it, you can present fields to the user from your database(s) and they can combine any series of them with ANDs and ORs to make very complex queries. You get a separate SQL window to save the resulting code. I make The resulting dataset available for printout with PrintDat! or to save as CSV data from the Woll2Woll Infopower grid that I use for display. I occasionally allow for XLS export too with XLSReadWrite. It's all quite automatic at this point. Just take the SimpleQuery example and mold that to your particular needs. You should be able to have something very usable in a day. There is a trial and an example free query builder standalone program that will give you an idea of the final look.

Answer 6

Try EMS Advanced Query Builder.

is a powerful component suite for Borland® Delphi® and C++ Builder® intended for visual building SQL statements for the SELECT, INSERT, UPDATE and DELETE clauses. It allows you to build new queries visually and/or graphically represent the existing queries in your own applications. The suite includes components for working with standard SQL, MS SQL, InterBase/Firebird, MySQL, PostgreSQL and many more dialects. Advanced Query Builder enables users to make up large and complicated SQL queries with unions and subqueries for different servers without any knowledge of the SQL syntax.

Answer 7

I think the most powerful one is ActiveQueryBuilder, which now included with Delphi IDE (Since 2007 I think)

Answer 8

TMS Query Studio? Good value at 75 Euro..