Roland's blog

Yesterday I had to import some 80,000 records into 4 different tables. The data was "|" delimited and formatted almost properly. My first instinct was to use queries to insert all records in the database. I cleaned up the data and created the appropriate mappings into the database fields.

INSERT statements took some 5-10 minutes for all the records. I used PHP for the task and the restrictions on PHP scripts to execute within a certain amount of time was very annoying at first. Using the Command Line Interface I was able to get all the data in.

I figured, there has to be a better way to do things. So I researched data loading in MYSQL: http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Here's how you import the data:

LOAD DATA INFILE '/tmp/data.txt' INTO TABLE test
  FIELDS TERMINATED BY '|'  LINES TERMINATED BY '\n';

Huhuh!!! The execution time was much much faster 1-3 seconds at the most. Lesson learned!

To connect to the Sql Server in from Rails in Linux there are some very useful resources such as:
HowtoConnectToMicrosoftSQLServerFromRailsOnLinux.

Note that HowtoConnectToMicrosoftSQLServer works only if the Rails app lives in a windows environment. In Linux there's no "win32ole", which helps with the ADO connection. So, we have to rely on ODBC. The weird thing is that I developed a rails app that connected to Sql Server 2005 a few years back, but we did it in windows and had not to worry about something like this. Oh well, c'est la vie!

On my laptop I run Arch Linux so these instructions are pretty much for Arch, but as the first resource says, they can be adapted to pretty much any other distro. First, start off by installing freetds. Luckily, freetds on Arch is built with odbc support (you can see that the PKGBUILD of freetds depends on unixodbc).

The MSSQL module is not enabled by default in php in Arch. I don't know why but it seems there are just some weird fixes that need to be implemented. I am a noob at these stuff, but I was able to compile the module. Here is the how to:

1. Compile FreeTDS (package is created after compilation)

cd ~/abs
cp -r /var/abs/community/lib/freetds/ .
cd freetds
makepkg

Sym link the library
cd src/freetds-0.82/
ln -s src/tds/.libs/ lib

This will leave the source files and build in your file system under src.

2. Install FreeTDS (if you haven't already) either with

sudo pacman -U freetds-0.82-1-i686.pkg.tar.gz
or
sudo pacman -S freetds

3. Get and build PHP

cd ~/abs
cp -r /var/abs/extra/php .
vim PKGBUILD

After line 124 in phpextensions add this line
--with-mssql=/home/your_username/abs/freetds/src/freetds-0.82 \

NOTE: your_username is the username you use to login into the system

Build PHP:
makepkg

4. Uninstall any php install that you might have with
sudo pacman -R php

5. Install the version you compiled
sudo pacman -U php-5.2.6-5-i686.pkg.tar.gz

6. Configure php.ini
sudo vim /etc/php/php.ini

and add (line ~1299)
extension=mssql.so

Test, php -m to see that mssql is loaded

The php install might look for "libtds.a" in /usr/lib, but this has been renamed to "libct.so.4.0.0"
So, you might want to add a symlink
sudo ln -s /usr/lib/libct.so.4.0.0 /usr/lib/libtds.a

Personally, I didn't see a problem with not having it.

To install the mssql adapter you need to install

sudo yum install php-msssql

This will install freetds as well. Next you will have to edit /etc/freetds.conf

[CP]
host = db.example.com
port = 1234
tds version = 8.0

You might have to restart the webserver.

Next you can simply connect to the instance with

$link=mssql_connect("CP","username","password") or die("Another kill");
mssql_select_db("DB_NAME");

I have seen references to a domain username being used too such as "AD\username". Give it a shot