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?
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.
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.
(continue reading) (discuss!)
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@example.com: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)