Steven S. Ross

Ok ignore the title, SQL is all business so lets get back to the “serious” SQL train.

So maybe you have a table that is very flat, someone did something like data1, data2, data3 as column names and you want to loop over it and insert it into a new table that can hold an infinite amount of meta-data with a relationship. Hey after all we are programming with relational databases these days.

So here are the basics, the loop (from 1 to 10):

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <11)
BEGIN
-- DO LOOP STUFF HERE
SET @intFlag = @intFlag + 1
CONTINUE;
END
GO

So that is useful and all but, how do we create some dynamic sql with lovely concatenations?

Like so:

Declare @SQL VarChar(1000)
Select @SQL = 'INSERT new_table_name(description, status, date_due, date_complete, date_start, ref_id)'
Select @SQL = @SQL + ' SELECT descrip' + CAST(@intFlag AS varchar) + ', status' + CAST(@intFlag AS varchar) + ', due' + CAST(@intFlag AS varchar) + ', complete' + CAST(@intFlag AS varchar) + ', start' + CAST(@intFlag AS varchar) + ', old_id'
Select @SQL = @SQL + ' FROM old_table_name'
Select @SQL = @SQL + ' WHERE required_column' + CAST(@intFlag AS varchar) + ' IS NOT NULL'
Exec ( @SQL)

So now we just combine it all:

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <11)
BEGIN
Declare @SQL VarChar(1000)
Select @SQL = 'INSERT new_table_name(description, status, date_due, date_complete, date_start, ref_id)'
Select @SQL = @SQL + ' SELECT descrip' + CAST(@intFlag AS varchar) + ', status' + CAST(@intFlag AS varchar) + ', due' + CAST(@intFlag AS varchar) + ', complete' + CAST(@intFlag AS varchar) + ', start' + CAST(@intFlag AS varchar) + ', old_id'
Select @SQL = @SQL + ' FROM old_table_name'
Select @SQL = @SQL + ' WHERE required_column' + CAST(@intFlag AS varchar) + ' IS NOT NULL'
Exec ( @SQL)
SET @intFlag = @intFlag + 1
CONTINUE;
END
GO

Ask away if there are any questions.

Ok, not trying to start a flame war but, I’m republishing this, it was from an interview with Rob Pike (works for google, really smart, had his hands in the early days of Unix). This is a great response to a question posed to him and for the record the original article is here Rob Pike Responds but, I am just publishing one part of it. I’d encourage you to read it as we should always try to solve a problem the best way possible and that is what this is about.

Question Hello!

Maybe this is an overly-asked question, but I still often ponder it. Does object-oriented design negate or diminish the future prospects of Unix’s continuing popularity?

I’ve developed in C (which I still love), but lately, I’ve been doing a lot of purely object-oriented development in Java. Using things like delegation and reusable classes have made life so much easier in many respects. Since the *nixes are so dependent upon C, I was wondering what future you see in C combined with Unix. Like I said, I love C and still enjoy developing in Unix, but there has to be a point where you build on your progress and the object-oriented languages, in my opinion, seem to be doing that.

Thank you for all your contributions!!!

Pike:

The future does indeed seem to have an OO hue. It may have bearing on Unix, but I doubt it; Unix in all its variants has become so important as the operating system of the internet that whatever the Java applications and desktop dances may lead to, Unix will still be pushing the packets around for a quite a while.

On a related topic, let me say that I’m not much of a fan of object-oriented design. I’ve seen some beautiful stuff done with OO, and I’ve even done some OO stuff myself, but it’s just one way to approach a problem. For some problems, it’s an ideal way; for others, it’s not such a good fit.

Here’s an analogy. If you want to make some physical artifact, you might decide to build it purely in wood because you like the way the grain of the wood adds to the beauty of the object. In fact many of the most beautiful things in the world are made of wood. But wood is not ideal for everything. No amount of beauty of the grain can make wood conduct electricity, or support a skyscraper, or absorb huge amounts of energy without breaking. Sometimes you need metal or plastic or synthetic materials; more often you need a wide range of materials to build something of lasting value. Don’t let the fact that you love wood blind you to the problems wood has as a material, or to the possibilities offered by other materials.

The promoters of object-oriented design sometimes sound like master woodworkers waiting for the beauty of the physical block of wood to reveal itself before they begin to work. “Oh, look; if I turn the wood this way, the grain flows along the angle of the seat at just the right angle, see?” Great, nice chair. But will you notice the grain when you’re sitting on it? And what about next time? Sometimes the thing that needs to be made is not hiding in any block of wood.

OO is great for problems where an interface applies naturally to a wide range of types, not so good for managing polymorphism (the machinations to get collections into OO languages are astounding to watch and can be hellish to work with), and remarkably ill-suited for network computing. That’s why I reserve the right to match the language to the problem, and even – often – to coordinate software written in several languages towards solving a single problem.

It’s that last point – different languages for different subproblems – that sometimes seems lost to the OO crowd. In a typical working day I probably use a half dozen languages – C, C++, Java, Python, Awk, Shell – and many more little languages you don’t usually even think of as languages – regular expressions, Makefiles, shell wildcards, arithmetic, logic, statistics, calculus – the list goes on.

Does object-oriented design have much to say to Unix? Sure, but no more than functions or concurrency or databases or pattern matching or little languages or….

Regardless of what I think, though, OO design is the way people are taught to think about computing these days. I guess that’s OK – the work does seem to get done, after all – but I wish the view was a little broader.

I was doing some query tuning the other day (live search on a page and I needed it to go as fast as possible) I was searching about 3000 records and seeing how much performance I could squeeze out of mysql and a simple LIKE search. Put it simply if you want the most fastest (yes I just said that) you need to switch your tables from InnoDB to MyIsam because MyIsam doesn’t use transactions. Could be a no go for you but, if you need to do a fast search against the db it is your best bet. I saw my searches improve in speed about 2-3 times (however your mileage may vary).

If you use rails, or are considering it, it just got a whole lot easier to deploy using apache and mod_rails a.k.a Phusion Passenger

I think the best part of the experience was the installer, if you have ever compiled anything on linux you know it can be a pain when you don’t know if you have the required libraries. So the Phusion Passenger folks wrote their installer in ruby and it tells you what is missing (if there is anything). Sweet stuff.

Its much easier than running on mongrel, especially dealing with restarts and reboots (if you ever have to reboot, typically apache is already configured to start on startup). So how to restart an app? no shutdown you just navigate to the rails directory and issue a “touch tmp/restart.txt” next request the app will restart. Slick, wish everything was that simple.

So all in all (with out ruby enterprise) it took me 15 mins to install it and switch all my apps over from Mongrel to Phusion Passenger.

Just thought I’d put this out there that my company, zerium Interactive, has a new site: http://zerium.com and is geared toward helping small business eliminate some of their website issues. Each web package contains most of the tools you need to help your business succeed on the web.

We also do custom application design, development and hosting, so if you don’t just need the basics we can get it done.

Pretty simple stuff, say you are using mysql administrator and you want to point it at a server that you have ssh access to but the firewall prevents you from making a direct connection over the internet (which it should).

Here is what you do (if mysql is running on its default port):

ssh -N -L3306:localhost:3306 username@server.com

You can also pass in a port variable if you are running ssh on a non-starndard port (which you should also do) all you do is add ” -p 1234” (1234 would be the port number where ssh is wanting you to connect)

Now just point mysql administrator at 127.0.0.1 and supply your credentials. Bang. You’re in.

So, maybe you have seen this Microsoft tool called Photosynth its pretty cool but, I think that Microsoft is trying to compete with Flickr or something instead of putting this technology to good use. If you watch this video (7mins) you’ll see the potential of this software. I think M$ missed the mark here, I certainly hope that photosynth is not the only project they are working on that will use Seadragon. Also sorry for us mac users, it only runs on windows.

Check it out for yourself, pretty slick stuff, push for a SWF or AJAX. Taken from the Juggernaut website: “The Juggernaut plugin for Ruby on Rails aims to revolutionize your Rails app by letting the server initiate a connection and push data to the client. In other words your app can have a real time connection to the server with the advantage of instant updates. Although the obvious use of this is for chat, the most exciting prospect is collaborative cms and wikis.” I can think of a few more uses as well! Check it out

So I spent about an hour setting up a new printer on my parents two windows boxes last night, It was a fancy schmancy network printer that even had WiFi built into it. So After the drivers were installed (its an HP by the way) I try to just go to the network settings on the computer and point the “add printer network printer” at it. No dice, I fiddle with it for about 10-15 mins and nothing, it won’t see the printer or if it does it asks me for a password to access it (apparently it was trying to access the card readers on the printer, bizarre).

So of course I end up looking through the manual and finally give in and load up the CD that came with the printer and there is a network setup program that you have to run from the CD. That really didn’t make much since to me but, it did work at least.

So enter my mac this morning, fire up the pinter prefrerence pane and of course when I click add printer there is the printer. Why is it so easy? Bonjour. Bonjour is a service discovery protocol that goes way back in the Apple OS. It makes it stupid simple to setup any network device and I wish that every OS on the planet had Bonjour integrated into it. Imagine how much less time we (us computer guru’s who support our family’s PC’s) would spend trying to troubleshoot add a silly printer connection.

You can read more about Bonjour and even add it to your Windows box (something I probably should have done) here: Read via Apple

Incase you missed it VMWare Fusion 2 Beta 2 is out (out yesterday). Seems like there are some nice tweaks and that resuming / suspending is even faster than beta 1. Also, sound is working again for me. I tested out playing a movie through netflix’s online viewer and the playback was very nice. I’ll give another update after some use.

This just came across the wire (my wire that is) hot way to view a site’s css definitions inline: XRAY: look beneath the skin

Nothing to type… just go see it. Wow. Make sure you click play (be patient, its worth it).

To steal two things from my co-worker Dan Skaggs (the term army proof for one) and the other is Buildix Super simple install of Subversion, Trac, Mingle, and Cruise Control.

Doubt me? On Ubuntu its as easy as “sudo apt-get install Buildix” done. Yep Done. I haven’t tried it yet but, check it out. Trust me it will save you time as I recently setup Trac and SVN the manual way.

Enjoy!

Pretty cool, stumbled upon this today. Seems that google has added photos to their maps via panoramio (their data provider I believe). Check it out (be sure to click “explore this area”)

:update: : you can also get wikipedia info on various sights… updated the link tag to auto check it. (if you don’t see it click on “more” then click “wikipedia” and “photos”.

Side note… looks like you can’t embed these yet.

Apparently google is upgrading gmail across the country at the moment. I guess 4:00pm EST is as good a time as any. The good news is that google apps for your domain (gmail) in particular looks to have received colored labels. Woot!

Well this info is probably old but, my server was having issues. So now you can read about if you missed it somehow.