Easy find and replace in SQL Text fields

As you may know, you can only use the REPLACE SQL function on varchar or char fields, which becomes a problem when you have a text field to update (as we often do). In my case I had a text field with values up to 14,000 bytes, far too long to do a CAST to varchar (with a maximum of 8,000 bytes).

The traditional solution to this problem is to either use the UPDATETEXT function in a cursor, with lots of nasty code, or to whip up a small throwaway .NET application to iterate through the database records using the VB.NET Replace function.

SQL Server 2005 introduces the idea of large-value data types, which allows you to specify a data type that can store values up to 2^31 (2,147,483,648) bytes of character, binary or Unicode data (equal to the String data type in .NET). The importance of this change is obvious from the help text on these:

“The built-in string functions that can operate on character and binary data are enhanced to support large-value data types as arguments”

The syntax for the type declaration is:

• varchar(max)
• nvarchar(max)
• varbinary(max)

This allowed me to write the following update statement:

UPDATE ItemContent
SET ItemContent = CAST(REPLACE(CAST(ItemContent AS VARCHAR(max)), '#ff9900', '#8b0b04') AS TEXT)

Much easier than messing around with textpointers and offset values with the UPDATETEXT function!!

SQL Server: Is that Linked Server really up?

Recently I needed to fix a SQL Server 2000 stored procedure that called various stored procedures on remote database servers (again using SQL Server 2000). In this case it was splitting a centralised queue of tasks across the database servers that needed to handle each request. For centralised management of remote sites this makes a lot of sense. To do this it relied upon declaring each of these servers as a Linked Server (an entry in the sysservers table).

The problem we had was that when one of those database servers was down it held up the rest of the process because the main stored procedure immediately died as soon as it could not see the Linked Server.

I found a nice explanation of why this happens on the SQL Server Engine Tips blog. They mentioned this was a problem with SQL Server 2000:

“Due to lack of exception handling and implementation of OPENQUERY/OPENROWSET/OPENDATASOURCE interfaces it is not possible to do it cleanly.”

However, there is a new stored procedure in SQL Server 2005 called sp_testlinkedserver that will solve the problem. Wonderful!

Unfortunately SQL Server 2005 is not an available option, so I was pleased to see someone had left another solution to this in the comments section. SQLDBATips.com has a sample stored procedure called usp_serverup that gets rid of the problem by using SQL-DMO to test the availability of the Linked Server. That gives us a tool we can use to test the connection prior to running the code that will down our SP. Now that is cool.

[UPDATE: The usp_serverup solution utilises OLE Automation (which is why it avoids the aforementioned problem), which means that the account running it must have sa privileges within SQL Server. That creates a major security issue, so we're falling back to making the Windows service that calls the stored procedure chunk its calls by site, thus insulating each site from the failure of other sites.]

Playing with Ruby on Rails

Recently I decided to play with Ruby on Rails and see what all the fuss was about. I've been around a while and taught myself a bunch of stuff along the way from Omnis7 to Java to T-SQL to .NET, and so Ruby on Rails shouldn't be too hard ... right?

Well, it wasn't, once I invested in Programming Ruby and Agile Web Development with Rails. Those two books helped me considerably, especially the follow-on tutorials in the Rails specific one. Without those I would have been left floundering.

However, there were two problems that neither book really helped me with (although the first one was properly shown in the Programming Ruby book, it just didn't bother to point out the weirdness of it).

Elsif

You might not have realised it, but that heading above is missing an "e" (and a space, but most programming languages ignore that). Unlike any other language I have learnt, Ruby's If ... Elseif ... End construct spells the "Elseif" without the second "e", as in "Elsif". Perhaps it was because Matz is Japanese, but this really threw me - I even ignored the way the Ruby book showed me it was.

Decimals

Rails' ActiveRecord is very nice for doing lots of hard work for you. Thank you very much David Heinemeier Hansson. Unfortunately it maps decimal database columns to float variables ... which if you have ever tried using floating numbers in financial calculations you would know means major problems!

Frankly this is a show-stopper for me. I can't imagine ever doing something real with Rails that would not involve dollars, and I certainly do not want nasty rounding errors creeping in because there are floats mapped to my decimal table columns. It turns out that the solution in Ruby is to use the BigDecimal data type. It took a little while to find the API documentation for this datatype, but eventually I did.

That didn't help me work out how to implement it. The Rails community is full of talk about modifying ActiveRecord to do the job properly - but it turns out that ends up involving a lot of database-specific code, and whilst ProgresSQL and SQL Server have been done, good old MySQL is left wanting, and that is what I am using to play around with. There is a little blurb in the book about overriding the accessor/mutator attributes for a model (don't worry if that makes no sense to you, it didn't to me either until about 8 hours into the tutorial). The online API documentation for ActiveRecord seems to hint that the _before_type_cast accessor could help here.

After too much mucking about, I finally got an answer that is elegant and works very nicely. I even have some sample code for those other newbies following the Rails tutorials. At the beginning of our Product model we need to add a couple of lines:

# modified this to get the price as a decimal

require "bigdecimal"
require "bigdecimal/util.rb"

Then later, we can override the default price accessor/mutator with a simple call to BigDecimal:

def price
    # we want to make Price a BigDecimal, the format
    # will look weird if we don't do anything to it
    # but we are using sprintf() in our fmt_currency
    # method, which handles this nicely
    BigDecimal(price_before_type_cast, 2)
end

As I've noted, the main problem with BigDecimal is that the native display of it is ugly. A value of 20.00 is displayed as 0.2E2, which is nasty. Fortunately the formatting seems to work out in text fields OK, and we end up formatting price everywhere we show it as a label so that it shows with dollar signs. The formatting function sprintf() has no problem with BigDecimal so that works fine!

So far (and I have all of the cart code working) this does not seem to lead to any issues with the Depot tutorial, and it gives an elegant solution (provided you are willing to create a formatting method for displaying these numbers wherever they are outside form fields).

[EDIT: The year-old enhancement request relating to the decimal issue is an interesting read.]

Submit to Reddit