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. 

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:

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.

Shocking to all, I was able to run both days of the weekend, giving me a two-day mileage total not seen in these here parts for nigh on 4 months. Veni, vucuri, vici.

 I did day one in Ash Canyon where I did a nice climb and out and back on the new trail. The only tracks in the snow were those of a deer, a bobcat and a huffing runner. Peaceful and quiet (except for the heavy breathing), and as a bonus, I made it back to the car before the rain came.

Sunday I headed to Centennial. The storm had arrived, but, as usual in this area, most of the precipitation hung on the west side of town, and Centennial - being on the northeast - was only windy. The wind was cold as it enhanced my evaporative cooling, but I was still out there in shorts and only two layers on top (same as Saturday). I did the Devil's Butterfly route, but this time I switched it up and did it clockwise. Not sure if it really made a difference on my time (I ran hard) but it was nice for a bit of a different look. As usual, the wild horses were out, but at least this time, they weren't hogging the trail. I only saw them from a distance. I do wonder, though, why they seem to poop so much directly on the trail?

Looking forward to achieving a milestone on the upcoming Pre-Snarf C-Hill T-Day trudge scheduled for Thursday on the 8 Mile EZ Up route. More on that when it occurs.

Wow, my last post was a tad over three weeks ago. Seems longer. With a combination of increasing workload, decreasing interest in anything outside of work other than SOMA*, there hasn't been anything even *I* think is worth jotting down. Kids are busy with school  and that is going well, thank Hermes (god of knowledge), Titan (god of intellect) and Athena (goddess of wisdom). As time progresses, I am more and more impressed with Sage Ridge. All I can say is I consider myself fortunate to be able to afford to send my kids there, though it is a fiscal challenge.

A couple of things I have done at work are kind of interesting that I intend to post about in the near future. I need to lock them down a bit more, then I'll post them in all their glory. One is a SQL query generator for non-SQL folks, and the other is a comment and review tool that hides a LOT of the complexity of Microsoft  Word's Track Changes functionality, without hamstringing it. Both very useful for my peeps.