Does anyone have any good code examples? The hardest thing for
people like
Steve, and myself, is that the documentation for these items are very
limited and not very revealing to newbies. For instance, I could
not find a
single code example for connecting to a SQL database, let alone
Access, in
the Pickaxe book. So if anyone has any good code examples for
manipulating
data in Access or MySQL, I would like to see it here.
Following is a sanitized example of code I use at work to connect
both to an intranet-local SQL server, and also to a remote SQL server
on our web host over the 'net.
For both, I created a system ODBC DSN,which is what 'VerneCSR' and
'LiveServer' are.
DB_LOGIN = $test_db_flag ? [ 'dbi:ODBC:VerneCSR' ] :
[ 'dbi:ODBC:LiveServer', 'username_removed', 'password_removed' ]
puts "Working on the #{ $test_db_flag ? 'TEST' : 'LIVE' } database"
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|
#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
changed_db_flag = false
if rows.empty?
#Couldn't find an existing article for the behavior, time to
add a new one
puts "Adding article entry '#{title}'" if $verbose_flag
body.sub!( '%%CURRENT_TIME%%', Time.new.to_pretty )
add_article.execute( author, title, summary, body,
download_path )
changed_db_flag = true
$add_count += 1
else
existing_info = rows[ 0 ]
old_body = body.sub( '%%CURRENT_TIME%%', existing_info
[ 'date_updated' ].to_time.to_pretty )
if ( existing_info[ 'aSummary' ] != summary ) ||
( existing_info[ 'aBody' ] != old_body )
#Time to update the entry
body.sub!( '%%CURRENT_TIME%%', Time.new.to_pretty )
puts "Updating article entry '#{title}'" if $verbose_flag
update_article.execute( author, summary, body, existing_info
[ "aID" ] )
changed_db_flag = true
$update_count += 1
end
end
}
rescue DBI:
atabaseError => 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
