Re: Easy power-user ad-hoc tool

[Quoted] If you're just looking for a query tool:

I created a web-based ad-hoc query tool back in 1995 that has been in use at the University of Minnesota (basically unchanged) ever since. It has enabled many hundreds of users to build and run hundreds of thousands of queries. I'm not trying to sell anything (and I don't own it anyway) but it would be very easy to duplicate.

The key is that all it does is generate SQL (or let a user just type some in) that simply gets executed as a batch via that database's command line tool (isql for MS or SQL*Plus for Oracle). That means that it can accept absolutely anything the the command-line tool accepts - anything. And the results are returned to the web server as a simple text file (and all the security is handled by the database). Then I found a third-party tool called DBMS/Copy that lets you convert text files to many other formats "on the fly" (with no need to create a "conversion" beforehand). But both isql and SQL*Plus let you return basic comma-separated files.

A very basic web form that just presents a text box and sends that text through the command-line tool is very easy to do. For the Query-By-Example I just wrote a series of scripts that present first a list of tables then a list of columns and then a form to generate conditions. It can easily generate Joins and Group-By's by reading the database schema and acting accordingly.

So this tool has been used by everyone from true beginners who don't know anything about SQL to expert SQL writers to DBAs (it will also accept administrative commands and stored procedures and PL-SQL, etc -- *anything* that isql or PL*SQL will accept).

True - this is *not* a report writer. But over the years I (and others) have tried to replace it with something much fancier but our users continue to prefer this "primitive" tool (it was finally changed from CGI scripts to Active Server Pages but it still works the same way). I have experience with both Cognos and Brio (and have looked at a lot of other tools). If a tool is truly "thin client" then all the work is done at the web server (or application server) level and that's fine if the result sets will always be small. But our users sometimes brought back thousands (or every tens of thousands) of rows and then that server's memory got used up very quickly. If a tool is "fat client" then you have a plug-in or Java applet or something else which has to be downloaded to each end-user and performance depends on the power of that user's desktop.

And - best of all - if you make your own simple query tool then you can add some little custom features that no off-the-shelf tool would have (over the years we've done things like 'saved queries' for each user, a query log of every bit of SQL that has ever been run, row and time limits, etc.).

The best off-the-shelf tool I've seen lately is Hotquery - www.hotquery.com (written with Cold Fusion). The only problem I had with it is that it *is* truly thin-client so my web-server's memory got swamped pretty quickly (but they may have some way to deal with that now).

Bob Malos

Jared wrote:

> Hi, all -
>
> I am looking to replace Access within my user base. With one
> exception there is no VBA to worry about (and I will take care of the
> exception). Basic requirements are: ability to access Oracle and SQL
> Server (2000), output Excel files. I am trying to avoid the tools
> that require catalogs in teh middle (we have Cognos Query in house if
> I want to go that route).
>
> I have tried Discoverer and consider it slow, btw, but in terms of
> user interface it is OK.
>
> Without igniting a flame war, I would appreciate some feedback fro
> people who have implemented such a tool within their user base with
> good results.
>
> Thank you!
>
> Regards,
> jh
Received on Tue Dec 17 2002 - 19:41:14 CET