SQL Tricks

There's a lot you can do with SQL that some developers don't realise. Here's a few things you might not have thought of...

Changing Text Columns en Mass

Example. You have a column that contains file names so that an application can find them on the disk. You want to move the files to a different drive and path, and that would mean updating 1000's of rows to set the new one. You can use SQL to do this nicely much of the time.

update projects set file_output_folder = replace(file_output_folder,'D:\\Data','C:\\DDrive\\Data');

Generating DOS/Linux Batch Files using SQL

Say you need to now create the folders on the disk, cause they don't exist yet. You can use the SQL to generate a DOS batch file like this...

select distinct
concat(
replace(
file_output_folder,
'D:\\Data','mkdir "C:\\DDrive\\Data'
),
'"'
)
from projects;

Sample of Output

mkdir "C:\DDrive\Data\Database\Indexer Output\Blah\XmlToUpload"
mkdir "C:\DDrive\Data\Database\Indexer Output\XML for Transfer"
mkdir "C:\DDrive\Data\Database\Indexer Output\XML for DB Import"
mkdir "C:\DDrive\Data\Database\Indexer Output\test"

Using SQL to generate files like this can save you lots of time!

Using SQL to create more SQL

Example: Someone has written 100 stored procedures that bulk load some data from tables in another database. Rather than painfully triggering them one-by-one, you want to write a new stored procedure that runs them all at once.

You can use an SQL statement to generate another one that you can run. This kind of trick can save you lots of typing!

Some SQL like this will do it...

/* Assumes that the 100 sprocs someone made all start with spLoadTable */
select 'create procedure spLoadAllData as'
union
select concat(concat('exec ', routine_name),';')
from information_schema.routines
where routine_name like 'spLoadTable%'

The output might be something like this:

create procedure spLoadAllData as
exec spLoadTableCustomers;
exec spLoadTableOrders;
exec spLoadTableLineItems;
...96 more!

You can then copy and paste into a new SQL window, tweak it as you see fit, and then run it. You don't really need the union statement at the top, but it does demonstrate how you can add lines to a query.

Amazon Queue Service

Amazons Message Queue Service looks very interesting? I'm not that clued up on all this stuff, but there's definately a trend appearing - we can already rent processing power through hosted grid computing networks, so why not rent the computing power to handle messaging?

In case you've not come across the concept of message queues, they're basically a tool to assist distributed computing. This is an area that is interesting me and more these days. I've been working on projects recently where two or three machines aren't really enough, and the prospect of being able to simply add more commodity machines to do more work is an extremely attractive one. Message queues are one technology that can play a big part in this kind of system since they allow separate programs running on separate machines to collaborate without worrying about all the communications overheads (working out which node should receive the message, waiting for responses, detecting ports being blocked, etc). The queue abstracts all that stuff, and more.

You can take your pick of vendor provided message queue implementations (MSMQ, OSMQ etc), but it's really interesting to see someone like Amazon offering a service to do this out of the box, no hardware puchases required.

Amazon's implementation looks nice and simple - just four methods! I guess there will be limits to the application of this service though. Firstly, using machines on an external network could present complications with both security and performance. Is HTTPS available? Would this slow things down even more? Sending messages over the web will be slower than to local networks, so I guess this technology is suited to problems that involve several disconnected networks. The 256K per-message limit might rule out some kinds of application also, such as web spiders and multimedia applications.

Still, and good to see Amazon do something cool with all that infrastructure!