Making The Leap

It’s been awhile since I’ve written anything. I’ve been deep into sites using Ruby/Rails, but now I’ve started playing around with JRuby/Rails. As such, I want to write down the steps to connect to a sqlserver database from JRuby.

First, download the sqlserver.jar file from Microsoft. Place it in the lib directory of your JRuby installation.

Second, configure your database.yml file to read like so:

ActiveRecord::Base.establish_connection(
    :adapter=> 'jdbc',
    :url=> 'jdbc:sqlserver://MyDatabaseServer;databaseName=MyDataBaseName',
    :driver => 'com.microsoft.jdbc.sqlserver.SQLServerDriver',
    :username=>'xxxxxxxx',
    :password=>'xxxxxxxx'
    )

Thirdly…..there is not step three!

I still haven’t given up my subscriptions to my ColdFusion based mailing lists. Heck I’ve belonged to them sing 2000. It’s almost like leaving family. Alas I digress, a question came up asking how you would solve this problem in CF.

Say that I have a list of allowed nmbers: 32,48,64,72,144,160,200,288,320,400,512,576,640,720,800

If I give the user the option of selecting a number, and it happens to not be in this list, how might I go about automagically selecting the next lowest number? One exception being if the user selects a number lower than 32, in which case the code should return 32.

Examples: User selects 100, the code would return 72.

User selects 480, the code would return 400.

User selects 25, the code would return 32.

One of the simplest solutions presented was this:

<cfset input = 100 />
<cfset last = listFirst(numbers) />

<cfloop list="#numbers#" index="num">
  <cfif num GT input>
   <cfbreak />
 </cfif>
 <cfset last = num />
</cfloop>

#last#

I thought, how would I do this in ruby. Here’s what I came up with. Definitely more elegant than the above code:

# our test input
input = 25

# change the list to an array
nums = "32,48,64,72,144,160,200,288,320,400,512,576,640,720,800".split(",")

# find all the numbers which would be smaller
target = nums.find_all {|num| input >= num.to_i }

# take the last element of the found elements, or if nil return the first 
# element of the original array
puts target.last || nums.first

Rails and Microsoft SQLServer don’t seem to be the best of friends, particularly when consider blob images. I’ve outlined the steps that I had to cruft support for better binary support in MS SQLServer. Now, not all of this is my doing. A lot of the code was lifted from a sqlserver thread in the Ruby on Rails mailing list.

First off, download the SQL Native Client from Microsoft. This new provider has better support for statements longer than 8k. (About Time!)

Next, modify ADO.rb so that the execute method reads like so:

  def execute
    # TODO: use Command and Parameter
    # TODO: substitute all ? by the parametes
    # if there are no params, avoid the expensive scan operation of bind
    if @params.length > 0
        sql = bind(self, @statement, @params)
    else
        sql = @statement
    end

    @res_handle = @handle.Execute(sql) 
    # TODO: SELECT and AutoCommit finishes the result-set
    #       what to do?
    if @db['AutoCommit'] == true and not SQL.query?(@statement) then
      @db.commit
    end

  rescue RuntimeError => err
    raise DBI::DatabaseError.new(err.message)
  end

There’s a relatively expensive scan operation in the bind operation that really chokes when binary content is sent to it.

In the sqlserver_adapter.rb file, modify the the following functions:

  def self.string_to_binary(value)
    '0x'+value.unpack('H*').to_s
  end

  def self.binary_to_string(value)
    if value.kind_of? Array
      value.map {|c| c.chr}.join
    else
      value
    end
  end

Also, modify the Base class inside sqlserver adapter like so:

module ActiveRecord
  class Base
    def self.sqlserver_connection(config) #:nodoc:
      require_library_or_gem 'dbi' unless self.class.const_defined?(:DBI)

      config = config.symbolize_keys

      mode        = config[:mode] ? config[:mode].to_s.upcase : 'ADO'
      username    = config[:username] ? config[:username].to_s : 'sa'
      password    = config[:password] ? config[:password].to_s : ''
      provider      = config[:provider] ? config[:provider].to_s : 'SQLOLEDB'
      autocommit  = config.key?(:autocommit) ? config[:autocommit] : true
      if mode == "ODBC" 
        raise ArgumentError, "Missing DSN. Argument ':dsn' must be set in order for this adapter to work." unless config.has_key?(:dsn)
        dsn       = config[:dsn]
        driver_url = "DBI:ODBC:#{dsn}" 
      else
        raise ArgumentError, "Missing Database. Argument ':database' must be set in order for this adapter to work." unless config.has_key?(:database)
        database  = config[:database]
        host      = config[:host] ? config[:host].to_s : 'localhost'
        driver_url = "DBI:ADO:Provider=#{provider};Data Source=#{host};Initial Catalog=#{database};User Id=#{username};Password=#{password};" 
      end
      conn      = DBI.connect(driver_url, username, password)
      conn["AutoCommit"] = autocommit
      ConnectionAdapters::SQLServerAdapter.new(conn, logger, [driver_url, username, password])
    end
  end # class Base

  def type_cast(value)
    return nil if value.nil? || value =~ /^\s*null\s*$/i
    case type
    when :datetime  then cast_to_datetime(value)
    when :timestamp then cast_to_time(value)
    when :time      then cast_to_time(value)
    when :date      then cast_to_datetime(value)
    when :boolean   then value == true or (value =~ /^t(rue)?$/i) == 0 or value.to_s == '1'
    else super
    end
  end

  def quote(value, column = nil)
    return value.quoted_id if value.respond_to?(:quoted_id)

    case value
      when String
        if column && column.type == :binary
            column.class.string_to_binary(value)
        else
            super
        end
      when TrueClass             then '1'
      when FalseClass            then '0'
      when Time, DateTime        then "'#{value.strftime("%Y%m%d %H:%M:%S")}'" 
      when Date                  then "'#{value.strftime("%Y%m%d")}'" 
      else                       super
    end
  end

Make sure to use SQLNCLI as your provider in your database.yml and you should be golden! I’ve tested this with files up to 12 meg. 50 meg files caused the interpreter to crash.

I’d recently created a document management application for my company. I used a third party ocr app that I called from the command line. I deployed it and everything worked great….until the user said, let’s try this big document (180 pages). Amazingly, 15 minutes later, the document had been ocr’d and the text had been stuffed in the database, but the users browser had timed out after about 3 minutes and showed a screen that intimated the document import had failed.

I wrote a script that I threw in the root of my rails app directory. The script uses the the environment files and activeRecord objects from the main app. I think I may have been able to trim down some of my require statements had I used script/runner, but I developed this script while working on a solution involving the win32-service gem.

load 'config/environment.rb'
require 'app/models/document'
require 'app/models/notifications'

loop do

  @document = Document.find(:first, :conditions=>'processed=0')

  if !@document.nil?
    begin
      cmd = "#{CONFIG[:ocr]} -k 6 -6 -y 2 -o %FILENAME.pdf2 -p -t #{File.dirname(@document.full_filename)} #{File.expand_path(@document.full_filename)}" 
      cmd.gsub!(/\//,"\\")
      output = `#{cmd} 2>&1`
      #try to delete the original and rename the searchable one.
      File.delete(File.expand_path(@document.full_filename))
      File.rename(File.expand_path(@document.full_filename)+(2.to_s),File.expand_path(@document.full_filename))
      f=File.open(File.dirname(@document.full_filename)+"\\"+@document.filename.gsub(/pdf$/,"txt"),"r")
      text = f.read
      f.close
      @document.text_data = text
      @document.processed = true
      @document.save
      Notifications.deliver_processed(@document.created_by, @document, output)
    rescue
      Notifications.deliver_error($!)
    end

  end

  sleep 5

end

So I started digging around for ways of offloading the ocr portion into a background process. There are a lot of different ways to do this…..if you run on a unix/linux system. I, unfortunately, deploy to windows machines. After trying several different paths, I remembered my old friend SRVANY.exe. This utility is available in the Windows NT Resource kit (downloadable from Microsoft).

The Resource kit contains a lot of files, but these two, instsrv.exe and srvany are what interest us right now. The format to install a service is as follows

resource_kit\instsrv.exe MyServiceName resource_kit\srvany.exe

Your path to the resource kit may vary. Once you run that command, you should get some output like so:

The service was successfully added!

Make sure that you go into the Control Panel and use
the Services applet to change the Account Name and
Password that this newly installed service will use
for its Security Context.

Now, open up regedit and navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services

Locate your new service in the left panel. Highlight the name and right click and choose New -> Key and name it Parameters

so that it looks like this:

Now, in the right panel, right click and and choose New -> String Value. Name it “Application”. Repeat the first step 2 more times, but name these “AppDirectory” and “AppParameters”. Double click the “Application” key and type in the path to your ruby interpreter.

For AppDirectory, type in the working directory for your project, e.g. c:\sites\myRailsApp

For AppParameters, type in the name of the script relative to the working directory. e.g. lib\import.rb. Since my script was in the root of the working directory, I was able to use just the script name.

Ok, next step….wait, there is no next step. You’re done! Now you can change all of the service specific settings in the Service snap-in, stuff like service login, start mode, etc.

Have fun with this!

Okay, I’ve been running a sample app on GoDaddy for approximately a little over year now. It started out as a quick way for a group of friends to identify movies in a picture….It was one of those time wasting things you do on a Friday….or Monday or whenever I guess. Since then, I had posted that I was able to get a rails app up and running on GoDaddy and provided a link to the sample app and it’s received a fair amount of traffic, but people are still trying to get it to work for them.

So here it is…Hitchhikers Guide To GoDaddy Rails!

First off, let’s create a new rails app

c:\sites>rails GoDaddyTest

Switch to the GoDaddyTest directory. Let’s edit the Dispatch.cgi and Dispatch.fcgi files in the public directory so that they point to the right ruby interpreter. Edit the first line of both files to read like so:

#!/usr/local/bin/ruby

Edit the .htaccess file in the public directory next. Find the line that reads

RewriteRule ^(.*)$ dispatch.cgi [QSA,L]

and change it to read

RewriteRule ^(.*)$ dispatch.fcgi [QSA,L]

This makes GoDaddy use FCGI to run your app. Believe me, you’ll never ever ever want to use CGI on GoDaddy to run a rails app. EVER!

Exit back out of your editor and freeze rails in your app. This is pretty important because GoDaddy is running a very old version of rails.

c:\sites\GoDaddyTest>rake rails:freeze:edge TAG=rel_1-2-3

Lots of scrolling text will fly past you and your rails will be frozen to your app.

For simplicity’s sake, we’re not going to implement a database in this sample app. It’s pretty straight forward if you need a database, edit the database.yml file like so:

production:
  adapter: mysql
  database: xxxxxxxx
  username: xxxxxxxx
  password: xxxxxxx
  port: 3306
  host: mysqlXXXX.secureserver.net

You can get your database host name from your GoDaddy control panel.

Back to our guide though….let’s create a controller now.

c:\sites\GoDaddyTest\ruby script\generate controller Say hello goodbye pardon

The ole’ say controller…..straight from every beginning rails tutorial :) At this point, you might want to run the local server and see that everything works as expected on your development machine.

Now to the meat of the post. Fire up your Firefox (you are using Firefox, right?) and log into the GoDaddy site. Navigate to your Hosting account page and verify that you have at least the Deluxe package and your on a Linux account. Rails will not work on the GoDaddy Windows servers.

From there, goto the CGI Admin app. Create a rails application directory. This only needs to be done once and you can put as many rails apps in that directory as you want to. Pick whatever name you want to like

Next we upload the site into a subdirectory of your rails app directory. In our example we’d upload into the ‘my_rails_apps’ directory. Start the ftp process and then go get a cup of coffee. Change the oil in your car. Get a haircut. By the time you get back, you should be able to watch the last file upload.

Now, go back to the CGI admin page and create a symbolic link. This creates a link in the root of your site so that people can access your site via url like http://www.mycoolsite.com/my_rails_app versus having to access it like http://www.mycoolsite.com/my_rails_app/my_rails_app

Go ahead….click that ‘Show Rails Applications’ link. If you’ve uploaded your app, it will automagically appear in the list. Select our application my_rails_apps/GoDaddyTest and give it a cool name like superbad. Now let’s try out the application. http://www.mydomain.com/superbad/ <—don’t forget the trailing slash!!

Success!!! Let’s break out the champagne! We’re going public! Now let’s check some of our actions. http://www.mydomain.com/superbad/say/hello

KAAAAAHHHHHHHHNNNNNNNNNNN!!!!!!! So put the champagne cork back in and let’s figure out what’s gone wrong. Actually, I know what’s gone wrong, but I’ll give you a few minutes to ponder it.

Okay, figured it out yet? Not yet? Don’t feel bad, having an average IQ isn’t the end of the world. It’s a permissions issue. Windows doesn’t like dealing with Unix permissions. Hopefully your ftp client allows you to change execute permissions. Navigate to the public directory of your rails app on the server. From our example, that would be /my_rails_apps/GoDaddyTest/public/. If you’re using FileZilla, which I am, you can set them pretty easy. Highlight dispatch.fcgi and right click.

Go ahead and set execute on all of the levels.

now, do the same for the dispatch.rb file, and if you’re particularly masochistic, do the same for dispatch.cgi. Back to the browser and check http://www.mydomain.com/superbad/say/hello

Woohoo! Uncork that champagne again, pat yourself on the back, pat the dog, pat Pat…..you’ve successfully deployed your application on one of the slowest Rails providers in the known universe.

After you play with it a while and decide that hey, my sites’ for senior citizens and this should be fast enough for them, experiment with setting up domain pointers so that you can access the site via http://www.myalternatedomain.com/say/hello