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,difficulty,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
···
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??