On Sep 15, 2005, at 3:14 PM, Sean Armstrong wrote:
> 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::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