Category Archives: Databases

SQL Server paranoia mode

This post is for Tony!

Worried about accidentally overwriting critical data with a typo in your database commands?

SQL Server Management Studio has an option for that: SET IMPLICIT TRANSACTIONS, which you’ll find (in 2008 / 2008 R2 at least) under Tools / Options / Query Execution / SQL Server / ANSI.

SQL Server implicit transactions option

Anything you do (from the next query window you open) will automatically be in a transaction, so you can ROLLBACK if you realise you’ve done the wrong thing.

Be warned, you’ll need to get into the habit of manually COMMITting everything. Don’t be tempted to just add the COMMIT at the bottom of your query… that would defeat the purpose.

It’ll prompt you do to so if you close a query window without having done a ROLLBACK or COMMIT. It can get a little irritating, but knowing you can’t accidentally trash all your data may give you piece of mind in return.

Freebie database dev tools

I used TOAD for a while for browsing around Oracle databases. Good stuff, though the free version is a bit limited, and Quest Software, who sell it, have the annoying thing of not selling it retail, but negotiating licence prices, making purchasing it a long drawn-out process.

Turns out there’s now an alternative: Oracle’s SQL Developer, which does a pretty good job at doing the same thing, and it’s free. It runs on Windows, Mac and Linux, and it uses Java and the JDBC client and apparently doesn’t need the humungous Oracle client software (though I haven’t actually verified that yet).

(Tony found the SQL Server equivalent some time ago, though it’s moved slightly. MySQL? MySQL GUI Tools.)

Database war stories

Databases have long been part and parcel of web development, but it seems that some of the big 2.0 sites have a few things to say on databases. Some love them, others hate them, and all are dealing with really big databases.

Second Life (database has grown and grown and split), Bloglines and (lovers of flatfiles), Flickr (almost a Tb of data – and we’re ignoring images here), NASA, Craigslist (dealing with masses of data), O’Reilly (doing interesting data mining / transformation), Google (not much gets said here), Findory and Amazon (Findory try to keep it all in RAM), finally MySQL repsonds saying “Flat files suck”

MySql woes

We’ve got MySql problems here at Geekrant central.

MySQL said: Documentation
#1016 – Can’t open file: ‘wp_comments.MYI’ (errno: 145)

Doesn’t sound good, does it? The ISP is looking into it.

Nothing else seems to be AWOL, but I’ve taken a backup of everything just in case. Wouldn’t you know it, the backup I have of wp_comments isn’t particularly recent. Hopefully the ISP has a newer one, but if not, I’ve grabbed a bunch of comments via Newsgator’s cache. Gawd knows how I’d restore them though.

Update: Fixed. May I just say, the support guys at AussieHQ hosting are deadset legends.

WordPress Most comments

Here’s the SQL to find which of your WordPress posts have the most comments:

SELECT wp_comments.comment_post_id, count(*) as commentcount, wp_posts.post_title, wp_posts.post_date FROM wp_comments, wp_posts
where wp_comments.comment_post_id = wp_posts.ID
group by wp_comments.comment_post_id, wp_posts.post_title, wp_posts.post_date
order by commentcount desc

Documenting Oracle databases

We all know what a right PITA it is keeping database documentation up to date. You’ll get a column added to the schema, and make a mental note to update the docs with the intricacies of how it works, then you’ll get distracted by something, never get around to it, then six months later you’ll be trying to remember the details.

Fortunately, the newer versions of Oracle have a rather marvellous commenting feature for tables and columns, so you can document it at the same time as you build it.

To put a comment on a table:

COMMENT ON TABLE xyz IS ‘This is a table for recording xyz usage’

Or on a column

COMMENT ON COLUMN xyz.frequency IS ‘Frequency of xyz usage’

Now, this might be of limited use if you couldn’t get the information out easily. Fortunately, you can. Apart from turning on the comments options in schema browsers such as TOAD, you can get a list of table names with their comments like this:

SELECT t.table_name, t.comments
FROM all_tab_comments t
WHERE t.owner = ‘tableowner’
AND t.comments IS NOT NULL
ORDER BY t.table_name

…and the following will generate a nice list of tables, columns, column types and their comments:

SELECT c.table_name, c.column_name, ac.data_type, ac.data_length, ac.nullable, c.comments
FROM all_col_comments c, all_tab_columns ac
WHERE c.owner = ‘tableowner’ and c.column_name = ac.column_name and c.table_name = ac.table_name
ORDER BY c.table_name, ac.column_id

Mind you, the data length field comes out a bit funny for CLOBs and Numbers. Still, with a little imagination you can write up a quick program to format this output nicely in HTML or WikiText or whatever, for your database documentation.

Queries in OLEDB: keep your parameters in order

If like me you earn your keep writing code to interrogate databases, you’re probably familiar with parameterised queries. If not, take a look here — they offer a way of passing parameters into queries without all that mucking about with formatting for different data types and so on. They also offer (so I’m led to believe) performance gains from the database server. And OLEDB makes it pretty easy to do it, too. So throw away those horrible old queries and switch to parameter queries today!

One thing not often mentioned however is that the parameters have to be in order. Because in the SQL you identify them only as question marks:


they have to be added in the correct order. If you add CRITERIA2 then CRITERIA1 to your parameters, then you’ll get unexpected results. The parameter names don’t actually match up to your SQL by name, only by position. This is kinda logical, because if you wanted:


both your parameters couldn’t be named CRITERIA1.

For some of my code, I build an INSERT or an UPDATE depending on whether I have a new or updated record to write. To avoid two sets of code, I’ll build the parameter list with the key field last, and though it may seem counter-intuitive, I build the INSERT statement to match. Bad semi-VB pseudocode follows:

If (new record) Then
Else (updating record)
End If

…then add my parameters FIELD1, FIELD2 and KEYFIELD in that order. Easy.

Microsoft 2005 dev tool betas

To those who watch Microsoft’s dev tools, there are betas of the 2005 versions now available. MSDN subscribers can download full products straight away; others can order CDs, or stick to the Express products, which for trying out new languages, are quite nicely featured.

It’s a cunning strategy for Microsoft, helping to counter the proliferation of free programming languages such as Java and PHP by providing free development environments for ASP, VB, C#, C++ and their own (some would say mutated) J# implementation of Java.

MSDE, which has been around for years now, providing a royalty-free cut-down SQL Server, has been renamed SQL Server Express to show its heritage. (Well, its SQL Server heritage… most people know it grew out of Sybase, but that’s ancient history). Keeping it free thus helps fight off the MySql threat and allowing people for whom Access isn’t cutting it to be encouraged up to SQL Server).

Looking back 20 years to when I was growing up, trying out BASIC on my Commodore 64 or BBC Micro, I ponder how the next generation of programmers are getting hooked into this game. I suspect a mix of freebie entry-level products like this (and their counterparts from the world of open-source) is one way they can get involved. Which probably explains MS’s “Coding 4 Fun” web site.


Hax0r gameshow contestant wagers $1337 on Jeopardy. (via Rick)

Gary Schare, Director of Windows Product Management at Microsoft, talks about the future of IE, its features and security. (Via Cameron Reilly)

Speaking of ADO (which I was yesterday), trying to figure out the black magic that is an OLEDB connection string? Try here.

Feel like writing a little C++ or Java applet for your phone? Here’s tech specs for Nokia phones. For me that’s the kind of project I’d love to do, but it will have to happen after I invent a time machine so I’ve got the time to do it in.

Empty recordset from MDB queries via ADO

Every so often I’ll find the solution to an obscure issue, and wonder “why isn’t this in the MSKB? Why has nobody written an explanation I could find in Google?” Well through this site, now I can do something about that.

If you call an Access (Jet 4.0) query via ADO/MDAC, you may get empty recordsets back if the query uses parameters. There’s a bug that Microsoft reckons applies to RDO calls, but also appears to affect ADO/MDAC 2.7 in the same way.

To fix it you need to define the parameter size to an arbitrary amount before you set the parameter. Here’s some hopefully useful (to somebody, somewhere, some day) sample code…

(This is VB6. Dim everything beforehand of course, ‘cos everybody loves early-binding. What, you want to play with fire by using late-binding and no Option Explicit? Silly you.)

Set oCommand = New ADODB.Command
oCommand.ActiveConnection = gcDatabase.Connection
oCommand.CommandText = "select * from MyQuery"

Set oParam = New ADODB.Parameter
oParam.Size = 255 'Take this out and you get an empty recordset back, but no error. Wacky.
oParam.value = "your value"
oParam.Name = "your parameter name"
oParam.Type = adYourFieldType
oCommand.Parameters.Append oParam

Set oRS = New ADODB.Recordset
oRS.Open oCommand, adOpenForwardOnly, adLockReadOnly

(and close and destroy everything when you’re done, natch. You know what VB6’s garbage collection is like.)

Another MySQL GUI

A while ago I downloaded a cross platform DB manager, DB Tools Manager Professional. It works great for local databases but was painfully slow accessing remote MySQL installs. Daniel came across MySQL Administrator, a smooth Windows GUI for MySQL from the very same people that release MySQL. It seems to be as fast with remote access as it is with local and so far I’m impressed.

Sitepoint Anomaly

I’ve been meaning to buy a couple of books from sitepoint for a while now. I’ve borrowed a copy of their HTML Utopia: Designing Without Tables Using CSS, a fantastic guide to CSS and their Build Your Own Database Driven Website Using PHP & MySQL looks great so when they emailed me an offer of 20% off this book I thought why not.

That is until I saw the site. Ifyou spend over USD$70 (effectively two books) you get free postage anywhere in the world. Hmmm. Take the offer and save $7 off one book or reject the offer (which takes me below $70), pay full price and save $15?

Regardless, they’re great books.