From: Gavin Kistner <gavin@...>
Date: 2005-11-04T23:09:31+09:00
Subject: Re: MS SQL Access from Ruby in Windows

On Nov 4, 2005, at 12:42 AM, Horacio Sanson wrote:
> Can someone point me where to learn how to access a MS SQL 2000  
> database
> server from within ruby in a Windows machine??

Here's some (sanitized) code from a project of mine. A System DSN was  
defined on the machine for connecting to a two different MSSQL dbs,  
one on the intranet and one over the Internet.

DB_LOGIN = $test_db_flag ? [ 'dbi:ODBC:TestDSNName' ] :  
[ 'dbi:ODBC:LiveDSNName', 'liveuser', 'livepassword' ]

begin
   require 'dbi'
   dbh = DBI.connect( *DB_LOGIN )

   # Ensure that the category exists, and get the ID
   CATEGORY_NAME = 'Latest Behaviors'
   row = dbh.select_one( 'SELECT acID FROM tblAssCategory WHERE  
acName=?', CATEGORY_NAME )
   if row
     SCRIPT_REFERENCE_CATEGORY_ID = row[ 0 ]
   else
     dbh.do( 'INSERT INTO tblAssCategory (acName) VALUES (?)',  
CATEGORY_NAME  )
     SCRIPT_REFERENCE_CATEGORY_ID = dbh.select_one( 'SELECT CAST  
(@@IDENTITY AS int)' )[ 0 ]
   end


   #Prepare some SQL statements for speedier re-use
   get_article = dbh.prepare( <<-ENDSQL
     SELECT aID, aSummary, aBody, date_updated
     FROM tblAssItems
     WHERE acID=#{SCRIPT_REFERENCE_CATEGORY_ID} AND download_files=?  
AND aTitle=?
     ENDSQL
   )
   add_article = dbh.prepare( <<-ENDSQL
     INSERT INTO tblAssItems
      
(author,aTitle,aSummary,aBody,download_files,acID,time_to_complete,diffi 
culty,thumbnail,status)
     VALUES (?,?,?,?,?,#{SCRIPT_REFERENCE_CATEGORY_ID},'5  
Minutes','(Reference)','#{THUMBNAIL_URL}','public')
     ENDSQL
   )
   update_article = dbh.prepare( <<-ENDSQL
     UPDATE tblAssItems
     SET
       author=?,
       aSummary=?,
       aBody=?,
       date_updated=#{Time.new.to_sql}
     WHERE aID=?
     ENDSQL
   )

   require 'erb'
   Behavior.all.each_with_index{ |bvs,i|
     puts "#{bvs.category}/#{bvs.name} (#{i+1}/#{$behavior_count})"  
if $DEBUG

     #Create fields for the DB entry
     author = bvs.author
     #Do not change the title arbitrarily! This is used to find  
existing articles in the DB
     #If you change the format of the title, you must manually remove  
all the old articles from the database
     title = "#{bvs.name} (#{bvs.category} Behavior)"
     summary = bvs.summary
     body = ERB.new( IO.read( 'template_onlinebody.rhtml' ) ).result 
( binding )
     download_path = "#{WEB_PATH_TO_BEHAVIORS}/#{bvs.category}/# 
{bvs.safe_name}.bvs"

     #Check to see if the item should be added or updated in the DB
     #(Don't just shove the current content, because that would  
invalidate 'date_updated')
     existing_article = get_article.execute( download_path, title )
     rows = get_article.fetch_all

     if rows.empty?
       #Couldn't find an existing article for the behavior, time to  
add a new one
       puts "Adding article entry '#{title}'" if $DEBUG
       body.sub!( '%%CURRENT_TIME%%', Time.new.to_pretty )
       add_article.execute( author, title, summary, body,  
download_path )
       $add_count += 1
     else
       existing_info = rows[ 0 ]
       #Time to update the entry
       puts "Updating article entry '#{title}'" if $DEBUG
       update_article.execute( author, summary, body, existing_info 
[ "aID" ] )
       $update_count += 1
     end
   }

rescue DBI::DatabaseError => e
   puts "An error occurred"
   puts "Error code: #{e.err}"
   puts "Error message: #{e.errstr}"

ensure
   #close out statements
   get_article.finish if get_article
   add_article.finish if add_article
   update_article.finish if update_article

   #unplug from the database
   dbh.disconnect if dbh
end #db safety