« Christchurch | Main | Brendi's questions »
April 10, 2006
Development notes
I promise, I will finish writing up our trip to New Zealand. Really.
But right now I need to rant and make some notes for future reference.
First off, anyone who values having many development options available and the ability to adapt to new languages and technologies should avoid SQL Server like the plague that it is. Choosing SQL Server immediately limits your choices with respect to what languages you can use, and what platforms you can use with them. If your webservers are not windows boxes and you have SQL Server databases you have a very limited and limiting choice of technologies to choose from, and those that are available suffer from limitations and quirks that require significant effort to work around.
While you can get a lot of milage out of using FreeTDS and and ODBC layer like unixODBC, you will find that there are many SQL Server specific data types that don't work perfectly, problems using stored procedures, problems with inserts and updates in some languages. In short, it's a lot of work to get anything to work 100% and there are lots of sharp corners to snag an elbow on, which makes for brittle code.
The moral of the story? Choose a database that not only has the features you need (stored procedures, standards compliance, etc) but that also has a wide variety of language bindings to choose from.
My first choice of database would be postgresql. It's free, has lots of language bindings, lots of stored procedure language options, and has lots of features. It's scalable and performs very well.
Unfortunately my company is tied to SQL Server due to the crappy CRM software we use (another thing to think about is not using CRM software that ties you to a particular database), which happens to have a schema designed by retarded monkeys. But I digress...
Second, some notes:
- If you have to use SQL Server and want to use perl and Class::DBI you will have to use Class::DBI::MSSQL as a base class, and use ODBC to connect.
-
If you use non standard data types (like 'money') you will have to override the built in insert(), find_or_create() and the accessors and mutators for those columns. The reason being that you will get errors in the FreeTDS layer when trying to insert or update those columns, as the ODBC layer treats them like varchars when they should be handled as SQL_DECIMAL columns. This means you will have to something like this to your accessor/mutator methods:
__PACKAGE__->set_sql( update_price => qq{ UPDATE __TABLE__ SET price = CONVERT(money, ?) WHERE __IDENTIFIER__ } );sub price {
my $self = shift;
my $price = shift;
if ($price) {
my $sth = $self->sql_update_price;
if ( $sth->execute( $price, $self->id ) ) {
$self->_attribute_store( price => $price );
}
else {
$self->_croak();
}
}
return $self->_price_accessor;
}
Does this blow goats? You bet! But it is the only way to work around the limitations of FreeTDS and unixODBC.
Posted by ed at April 10, 2006 10:54 AM