posted under category: Database on September 26, 2008 by Nathan
Along the trail of web-based ad-hoc query tools, I also have the need to back up my database from time to time. Having no direct connection to SQL Server across the internet on my host, I can't just use the SQL Server tools to do it.
Another scenario: a couple weeks ago I installed SQL Server Express 2008 64 bit. It's been running really well on Vista 64 and working fantastically with ColdFusion 8. The problem is, I had to recreate a local copy of dopefly.com's database (it runs the blogs and family photos apps). Then, I knew I wanted to populate it with some data, as close to the live data as possible. This is a pain because the only way to get the structure is to eyeball and hand-jam it. yuck, no thanks.
Well, I came up with a pretty good solution for myself, but I was hoping to get some advice from other people as to how they would do it. Any ideas out there?
(Discuss with Disqus!)
posted under category: Database on September 19, 2008 by Nathan
I was just thinking about managing my database here on Dopefly. I don't have direct SQL access to the server (SQL Server 2000), so there's no using SQL Manager to do things. That sort of leaves me with FTPing my ad-hoc queries to the server and running one at a time (no please). That sucks, so my real solution was to make a web-based ad-hoc query tool, SQLSurfer (warning, old code!). I've got an admin area of the site here in a secured directory, so it's basically secure & safe, but I would not recommend installing it anywhere public, or anywhere that a client could find. Generally, I just use it for local development when I don't want to open big clunky tools.
The need for an ad-hoc query tool is debatable, but I would say it is needed just to verify my data validity and run some small reports, like, I don't have a screen that will display the most recent comments on the blog, reversed by date (for spam checking when my email was down). Or what about when I am trying to remember the names of my tables when I don't have a local copy running - it's perfect.
I'm sure I'm not the only one to have this general problem, so my question to you is - do you have a web-based ad-hoc query tool? What tools do you use? Really, I guess it comes down to how do you solve the problem?
The reason behind this is that I'm thinking of updating the old SqlSurfer (run a selected block of code, better history management, ajax), but am wondering if anyone has anything better already.
(Discuss with Disqus!)
posted under category: Database on April 3, 2008 by Nathan
I felt the need to mention that to anyone who isn't using the Eclipse Data Tools Platform (DTP), you should look into it. I recently finally figured out how to make it run. It wasn't a very obvious process, so I thought I would type up some help in a blog entry. This is part review, part new user help, part promotion for another deserving open source product.
The first hurdle for me was connecting to my database. It's not easy, even with help from all the sources. Remember that someone out there has already done it, so keep googling until you find what you need. For me, I was missing my database instance name / service ID on the connect string. The format for my Oracle server is [email protected]:port:instance. This is not documented hardly anywhere. Once connected, I could browse the server, introspect the tables, procedures, views and everything. These things were pretty easy to figure out at this point.
The worst thing for me was figuring out how to just execute some adhoc SQL. It doesn't make sense at first. Eclipse makes you create a SQL file, even for adhoc statements. That's a true pain, but it's somewhat standard in the Eclipse IDE: you have to make a file before you can put anything in it. This differs from most other products, such as Dreamweaver or Flash, Adobe products, and also Notepad or Word, both MS products. Microsoft is so good at this, that you can make a visual studio project, put a bunch of files together, compile an exe and run it, then, if you don't want to save it, just close the project and, with confirmation, throw it all away. This kind of adhoc throw-away mentality is what most of us have been trained on, and is one of the major hurdles when switching to Eclipse.
(jump break - stunning conclusion and an Adobe rant after the break)
Back to DTP. you've created a .SQL file. Make sure you select your database connection at the top of the editor, then, when typing, you will be blessed with content assistance. It's not the best I can imagine, but it's rather good. Knowing what brand of database I use, the SQL editor gives me shortcuts to all the database functions as well as my own database objects. Now, getting your queries to run is a trick. Select what you want to execute, right click and pick "Execute Selected Text". Alt+X (CMD+X for mac?) is the shortcut key. Also, you can run all the queries in the file at once by selecting "Execute All" - Ctrl+Alt+X. The SQL Results view should pop up immediately and show you what you just did. In the results view, the left pane shows a history log of the queries you ran (and is persisted even between sessions, until you clear it), the right pane shows the data that was returned, if any.
I like the way it keeps track of historical queries. I toyed with a web version of something close to this years ago, though not so successfully. From the results view's data, you can export what you selected in different ways, which can even be loaded back into your database through the data source explorer (right-click on a table, data, load).
One last note in this review/plug/tutorial. DTP will generate DBUnit tests. I haven't used it yet, and I doubt I'll get to it soon, but for a full time DB developer with java experience, this is great news.
Overall, I like the integration and flow and how the aspects of the tool work together. Once you make your connection and figure out how to do the basics, the Eclipse Data Tools make a powerful database IDE. The price, as always, can't be beat, and you know the Eclipse plugin developers are always hard at work on improvements for the next version. While the features are not as deep as deep as many proprietary tools such as Toad or SQL Server Manager, its integration into an already well-loved IDE, Eclipse, makes it worthwhile to have on hand, though, as with the Eclipse culture, it won't give you a quick pick-up-and-edit experience.
For users of other Eclipse plugin DMBS's such as SQL Explorer or Quantum, would you want to switch? This, I can't say for sure. Perhaps, if you like the integrated nature of tools made by the Eclipse foundation, this could be up your alley. Trying it out doesn't hurt, but I would not bet money that it's better than your current tools. If anyone has experience, please share.
For ColdFusion developers, why would I choose DTP over the Adobe ColdFusion RDS database tools? For one very simple reason. The CF RDS SQL editor is terrible. Unbelievably awful. Go ahead, try to use it. Try block indenting. Try to hit the delete key. No color coding, no database introspection, no right-clicking on the text, no select all (not even with ctrl+a), no find or replace. Thank you Adobe for making a text editor that's worse than Notepad. Even worse than 'edit' from MSDOS. If I were Adobe, I would focus on connecting a JDBC driver to an RDS server. Then we could use RDS with DTP, SQLExplorer or a pure java app, etc., etc.
(Discuss with Disqus!)
