Figure 1a. Choose a type of database.
AdHocQ
| Home | Screenshots | Download | Support |
In AdHocQ, you build up your ad hoc query in steps, through a wizard-style interface. On the first page, you choose which kind of database you will connect to. A number of database types can be connected to (MS Access, MySQL, Oracle, SQL Server, SQL Server CE, SQL Server Express) and connection types (OLE DB and ODBC). More could be added - just get in touch!
Click to expand/shrink the images.
Figure 1a. Choose a type of database.
For ODBC connections, the database type is used to help construct the SQL in the most database-appropriate way.
For the Oracle data source, you can either give a tnsname (as defined in tnsnames.ora), or the long-hand
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=MyOracleSID)))
Once you have given your connection details, press the 'Test Connection' button before proceeding to the next page.
Figure 1b. Test the connection.
If the test is successful, you can re-use the connection in future by choosing it from the 'Previous connections' drop-down.
When you have finished your query, you can save and re-use it. For the moment, though, just create a new query. (If you enter your query directly as SQL, you'll go straight to the results page).
Figure 2. Create a new query.
Next, you can choose which tables or views to do a query on. You can specify joins between tables etc., although this is often best done by your database administrator and put in a view for everybody to use.
Figure 3a. Choose a table.
If there are a lot of tables to choose from, you can filter the list of tables by typing into the search box.
Figure 3b. Search for a table or view.
You then click 'Add Selected Table' to select the table. On subsequent tables, you also specify the joining condition to the previous tables.
Figure 3c. Add the table.
On the next page, you can give alternative names to the table columns, to make them more meaningful to you.
Figure 4a. The original column names.
Here, all three columns are given aliases.
Figure 4b. Giving aliases for column names.
Applying conditions allows you to filter the table to give you just the rows you want.
Figure 5a. The conditions page.
Clicking on 'New condition...' brings up a dialog box where you can specify an individual condition.
Figure 5b. Specifying an individual condition.
Here, we've specified that the Country must be 'England' and the County contains 'and'.
Figure 5c. Two conditions specified.
Next, you can specify which columns you want to show results for.
Figure 6a. Choose the columns you want to see results for.
Here, we've selected all of the columns.
Figure 6b. All columns selected.
You can also change the order the columns are given in. We've changed the order to be County, Country then Continent.
Figure 6c. Specify the order of the columns.
You can choose to either see every single value, or have aggregate functions such as Maximum, Minimum, Sum etc.
Figure 7. Choose to have either individual values, or a selection of aggregate functions.
On this page, you can specify the order in which the results are returned
Figure 8a. Press the big 'Create new rule to order by...' button to specify a rule for the ordering.
Here we've chosen to order by county, in ascending order. It is possible to specify multiple order-by rules, and set their precedence, for when rows have some values in common.
Figure 8b. Results ordered by county.
Finally, you can see the results. If there are lots of results, you can go through them a page at a time. You can also copy a page of the results to the clipboard, save the results of the entire query to a CSV file (which you can then open in Microsoft Excel, etc.), save the query to re-use another time, and show the SQL query.
If you're interested in displaying query results on web pages, your AdHocQ queries can be used in DashboardQ. DashboardQ uses a simple web-based approach to display reports and dashboards for database queries.
Figure 9. The results - counties in England which contain 'and'.
AdHocQ - Business Intelligence Made Easy