DSN-less ODBC Connections on Linux

On the Rails Wiki Connect To MicrosoftSQLServer From Rails On Linux Box page a database.yml sporting a DSN-less connection is shown like this:



That won't work on my Linux box given the way unixODBC and FreeTDS installed themselves. Looking at that you might think the quick solution is just to correct the path to the driver file but that won't fix the problem either. This is where the head scratching starts. Apparently when installing on Mac OS X ODBC puts an entry in the /etc/odbcinst.ini that is labeled [/path/to/the/driver/file.so] (or maybe that setup actually works with a path, but I don't think so). On my Ubuntu Linux box, the only entry in /etc/odbcinst.ini is labeled [FreeTDS]. So getting the DSN-less connection to work is as simple as using:

I Canz haz Ur Nullz at Bottom?

SQL Server when doing ORDER BY will put rows with null values in the sort column at the top - of course a simple adjustment to your ORDER BY can fix that:



Aloha on Rails is coming! I'll blog a bit more about this in the future but this should be a great conference with great sessions and Waikiki Beach just across the street!

Rails ActiveRecord Order by a Function

Of course you can pass functions in the order clause through ActiveRecord finders:



In the above case a Foo has fiscal_year and fiscal_month columns unfortunately stored as VARCHAR(255) containing, for example "2008" and "7". While the sorting will work that query will be able to leverage an index on these columns only if the database used can create indices that include functions. I'm not sure which databases can do this. From a quick look at the SQL Server and MySQL documentation it doesn't appear that either of these databases can do this.

SQL - Last Index Of lastIndexOf

Quick flush - lastIndexOf in two varieties of SQL plus using the technique to cut off the last segment in a materialized path:

Attach and Detach SQL Server Databases from the Command Line with osql

I'm usually working with MySQL but I also work on a project that uses SQL Server for a back end. Lately I've been writing a script that massages some data from a CSV dump from another system into our system. This led to a rather, rinse, repeat cycle of switching to SQL Server in VMWare, detaching the existing database, copying a fresh copy of the database into place, and re-attaching the data when doing sample runs of the script against the data. This got to be a real hassle! Finally I took the time to learn how to detach and attach a SQL Server database using command line osql commands. Here is an example of a script that detaches the database, copies clean files into position, and then re-attaches the database:



Enjoy!

ActiveRecord Change SQL Server Query Timeout

Recently I had a rake task that was timing out while moving some data from a fact table into another data table. This task has to be run only when we get a new batch of data, do some ETL work, and then dump the data into the production database tables. I found out how to change the SQL Server query timeout on the Backyard Bamboo blog. Since I just wanted to turn off the timeout for the current session I ran some code like this:



The 0 turns the timeout off, otherwise the value is the number of seconds to set the timeout to. The default appears to be 30 seconds.

The timeout probably wouldn't occur if we add an index to a certain field on our fact table but this will get our data into production until we can make that change in the ETL process.

SQL Server information_schema.views Length Limitation and rails_sql_views

Recently I was running into a problem with a plugin called rails_sql_views (part of the Active Warehouse project). This is a great plugin created by some friends of mine that helps you manage views in a rails project. The plugin manages the proper creation of the view in the schema.rb file. For the SQL Server adapter the code to produce view comes from a query that looks like this:



The problem was that the view queries were being cut off. The cut off point seemed to be around 4000 characters. I mentioned this to my colleague Semergence and he recommended I look into the column definition for the view_definition column in the information_schema.views table. Sure enough, the column was defined as a nvarchar(4000). Who would be crazy enough to create a view that required more than 4000 characters right? Well, errrr, me I guess.

So armed with this 4000 number my google searches became very effective and turned up this from Joseph Scott. In the blog article Joseph reveals the sp_helptext stored procedure. Using this stored procedure you can easily get the full text of a views create statement, however, you do have to reconstruct it as it returns the result in separate rows under the key "Text". Here is the simple code: