I had a hard time finding a way of connecting to csv files using ruby's dbi through the odbc driver without using a DSN. The odbc turned not to work with Microsoft Text driver for odbc because the name of the driver has a semicolon in it. Thus, the ruby code fro the driver was splitting the name of driver, resulting in a keyword syntax error. The following is the (working) code that shows how to connect to csv files using dbi and odbc, note that I had to override require to add the fix to the odbc driver because dbi loads the driver using require:
require "dbi"
alias :old_require :require
def require(library)
old_require(library)
if library.downcase.include?("odbc")
define_odbc_fix()
end
end
def define_odbc_fix
DBI.class_eval <<-'THERE'
module DBD
module ODBC
class Driver < DBI::BaseDriver
#JPT: fixed to allow the ";" character in the connection string.
# Obeys the rule of "If a semicolon (;) is part of a value it must be delimited by quotation marks (")"
# quoted from http://connectionstrings.com/default.aspx?article=what_rules_apply_to_connection_strings
def connect(dbname, user, auth, attr)
colon_stuffing_string = "@WACKYsUFF@"
dbname.gsub!('";"', colon_stuffing_string)
driver_attrs = dbname.split(';')
if driver_attrs.size > 1
# DNS-less connection
drv = ::ODBC::Driver.new
drv.name = 'Driver1'
driver_attrs.each do |param|
param.gsub!(colon_stuffing_string, ";")
pv = param.split('=')
next if pv.size < 2
drv.attrs[pv[0]] = pv[1]
end
db = ::ODBC::Database.new
handle = db.drvconnect(drv)
else
# DNS given
handle = ::ODBC.connect(dbname, user, auth)
end
return Database.new(handle, attr)
rescue ODBCErr => err
raise DBI::DatabaseError.new(err.message)
end
end
end
end
THERE
end
begin
conn_str = "DBI:ODBC:Driver={Microsoft Text Driver (*.txt\";\" *.csv)};Dbq=$$path_to_folder_with_csvs$$;Extensions=csv;"
conn_str.gsub!('$$path_to_folder_with_csvs$$', File.expand_path(".\\spreadsheets\\simple"))
dbh = DBI.connect(conn_str, "", "")
row = dbh.select_one("SELECT * from this_year_trancripts.csv")
puts row.to_s
rescue DBI::DatabaseError => e
puts "An error occurred"
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
ensure
# disconnect from server
dbh.disconnect if dbh
end
Comments