require 'odbc' module Sequel module ODBC class Database < Sequel::Database set_adapter_scheme :odbc GUARDED_DRV_NAME = /^\{.+\}$/.freeze DRV_NAME_GUARDS = '{%s}'.freeze def initialize(opts) super(opts) case opts[:db_type] when 'mssql' require 'sequel_core/adapters/shared/mssql' extend Sequel::MSSQL::DatabaseMethods when 'progress' require 'sequel_core/adapters/shared/progress' extend Sequel::Progress::DatabaseMethods end end def connect(server) opts = server_opts(server) if opts.include? :driver drv = ::ODBC::Driver.new drv.name = 'Sequel ODBC Driver130' opts.each do |param, value| if :driver == param and not (value =~ GUARDED_DRV_NAME) value = DRV_NAME_GUARDS % value end drv.attrs[param.to_s.capitalize] = value end db = ::ODBC::Database.new conn = db.drvconnect(drv) else conn = ::ODBC::connect(opts[:database], opts[:user], opts[:password]) end conn.autocommit = true conn end def dataset(opts = nil) ODBC::Dataset.new(self, opts) end # ODBC returns native statement objects, which must be dropped if # you call execute manually, or you will get warnings. See the # fetch_rows method source code for an example of how to drop # the statements. def execute(sql, opts={}) log_info(sql) synchronize(opts[:server]) do |conn| r = conn.run(sql) yield(r) if block_given? r end end def execute_dui(sql, opts={}) log_info(sql) synchronize(opts[:server]){|conn| conn.do(sql)} end alias_method :do, :execute_dui # Support single level transactions on ODBC def transaction(server=nil) synchronize(server) do |conn| return yield(conn) if @transactions.include?(Thread.current) log_info(begin_transaction_sql) conn.do(begin_transaction_sql) begin @transactions << Thread.current yield(conn) rescue ::Exception => e log_info(rollback_transaction_sql) conn.do(rollback_transaction_sql) transaction_error(e) ensure unless e log_info(commit_transaction_sql) conn.do(commit_transaction_sql) end @transactions.delete(Thread.current) end end end private def disconnect_connection(c) c.disconnect end end class Dataset < Sequel::Dataset BOOL_TRUE = '1'.freeze BOOL_FALSE = '0'.freeze ODBC_TIMESTAMP_FORMAT = "{ts '%Y-%m-%d %H:%M:%S'}".freeze ODBC_TIMESTAMP_AFTER_SECONDS = ODBC_TIMESTAMP_FORMAT.index( '%S' ).succ - ODBC_TIMESTAMP_FORMAT.length ODBC_DATE_FORMAT = "{d '%Y-%m-%d'}".freeze def literal(v) case v when true BOOL_TRUE when false BOOL_FALSE when Time, DateTime formatted = v.strftime(ODBC_TIMESTAMP_FORMAT) usec = (Time === v ? v.usec : (v.sec_fraction * 86400000000)) formatted.insert(ODBC_TIMESTAMP_AFTER_SECONDS, ".#{(usec.to_f/1000).round}") if usec >= 1000 formatted when Date v.strftime(ODBC_DATE_FORMAT) else super end end UNTITLED_COLUMN = 'untitled_%d'.freeze def fetch_rows(sql, &block) execute(sql) do |s| begin untitled_count = 0 @columns = s.columns(true).map do |c| if (n = c.name).empty? n = UNTITLED_COLUMN % (untitled_count += 1) end n.to_sym end rows = s.fetch_all rows.each {|row| yield hash_row(row)} if rows ensure s.drop unless s.nil? rescue nil end end self end private def hash_row(row) hash = {} row.each_with_index do |v, idx| hash[@columns[idx]] = convert_odbc_value(v) end hash end def convert_odbc_value(v) # When fetching a result set, the Ruby ODBC driver converts all ODBC # SQL types to an equivalent Ruby type; with the exception of # SQL_TYPE_DATE, SQL_TYPE_TIME and SQL_TYPE_TIMESTAMP. # # The conversions below are consistent with the mappings in # ODBCColumn#mapSqlTypeToGenericType and Column#klass. case v when ::ODBC::TimeStamp DateTime.new(v.year, v.month, v.day, v.hour, v.minute, v.second) when ::ODBC::Time now = DateTime.now Time.gm(now.year, now.month, now.day, v.hour, v.minute, v.second) when ::ODBC::Date Date.new(v.year, v.month, v.day) else v end end end end end