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






.

Relevant Pages

  • Re: MS Access
    ... both to an intranet-local SQL server, and also to a remote SQL server on our web host over the 'net. ... INSERT INTO tblAssItems ... puts "Updating article entry '#'" if $verbose_flag ...
    (comp.lang.ruby)
  • mysql update with ruby + dbi
    ... I am trying to modify a mysql database using a file for information. ... puts "we have a match" ... # Split the web address in the database into the array ...
    (comp.lang.ruby)
  • Re: SQLite3 and ruby / shoes gui
    ... Try to make it not depend on shoes (e.g. use puts for output) ... i get more errors talking about the that database isn't open or some ... database file or can you have multiple tables within 1 database file? ...
    (comp.lang.ruby)
  • instead of MySql, use ActiveRecord or other ORM
    ... INITIALIZE AND CONNECT TO DATABASE test ... SHUT DOWN THE DATABASE CONNECTION ...
    (comp.lang.ruby)
  • Re: Newbie question concerning ruby and mysql
    ... dbh.do("DROP TABLE IF EXISTS map") ... sth = dbh.prepare("INSERT INTO map ... # read each line from file, split into values, and insert into database ... puts "An error occurred" ...
    (comp.lang.ruby)
Loading