As promised, I now present the SQL Query tool I built for my users. Dubbed "Comstock," it is an ad hoc query tool that enables the user to use drop-down lists to create data queries against our databases. In this way, someone with limited or no SQL experience can perform searches without the necessity of having the suite of SQL tools installed on the computer.
Additionally, the queries generated can be saved to be run later. Also, more complicated queries can be generated by someone with more SQL experience, and those queries saved for others’ use. Since Comstock isn't tied to any particular application or set of tables, it's more flexible for the user to jump between retrieving data from different databases easier and more nimble.
For example, one query can be searching for particular information from Database 1, immediately followed by a query for other information against Database 2, and then a query against a third database. Sure, each could be created as a report in their respective applications, but this doesn't require a report to be built, or the individual applications to be opened.
Additionally, the queries generated can be saved to be run later. Also, more complicated queries can be generated by someone with more SQL experience, and those queries saved for others’ use. Since Comstock isn't tied to any particular application or set of tables, it's more flexible for the user to jump between retrieving data from different databases easier and more nimble.
For example, one query can be searching for particular information from Database 1, immediately followed by a query for other information against Database 2, and then a query against a third database. Sure, each could be created as a report in their respective applications, but this doesn't require a report to be built, or the individual applications to be opened.
The query string in the Query field is built on the fly as the selections from the drop-downs are made. The contents of each drop down is dynamically created based on the previous selection, i.e., select a database, and the "From Table" drop down is populated with tables from that database, select a table, and the "Select" and "Where" drop downs reflect the columns of the selected table.
If more than the single field in the original drop down “Select” is wanted, selecting another field will append each to the Select portion of the query. That way, if the user wants multiple fields, selecting each from the drop down will add them. The query field is also editable, so additional mods can be made to the query as well.
Dealing with the saved queries is pretty straightforward. Click the Open Query button gives the user this:
If more than the single field in the original drop down “Select” is wanted, selecting another field will append each to the Select portion of the query. That way, if the user wants multiple fields, selecting each from the drop down will add them. The query field is also editable, so additional mods can be made to the query as well.
Dealing with the saved queries is pretty straightforward. Click the Open Query button gives the user this:
From this screen the user selects a query they want to run, or delete one. The only queries they can delete are ones they originally authored. When a saved query is open, the information about that query is shown on the screen:
Saving a query involves naming, providing a description and identifying any additional users that are to have access to the query.
When a query is executed a small validation routine is run to ensure something drastic like Drop Table isn't in there. This app is designed to be a data extractor, not a data terminator!
When the results are displayed in the datagrid at the bottom, the user can sort by any column. Also, using the output buttons, the data can be extracted to a text file or the clipboard for repurposing. One bit of trickery there is that if the user has selected any rows, only those rows are exported/copied, providing a bit of selectivity.
I think it's pretty simple and straightforward for the end user, which means it might get used, and enable some "can you find this data for me?" questions to be answered by the users themselves.
Thoughts? Comments welcome.
When a query is executed a small validation routine is run to ensure something drastic like Drop Table isn't in there. This app is designed to be a data extractor, not a data terminator!
When the results are displayed in the datagrid at the bottom, the user can sort by any column. Also, using the output buttons, the data can be extracted to a text file or the clipboard for repurposing. One bit of trickery there is that if the user has selected any rows, only those rows are exported/copied, providing a bit of selectivity.
I think it's pretty simple and straightforward for the end user, which means it might get used, and enable some "can you find this data for me?" questions to be answered by the users themselves.
Thoughts? Comments welcome.
RSS Feed