I’d been putting off trying to link my rails app to our accounting deparment’s Pervasive SQL database because I thought it would be really hard. Turned out to be quite easy using ODBC. It might be nice if in the future someone wrote an adapter specifically for Pervasive SQL, but for now ODBC does what I need which is allow me to access employee data already populated in the accounting database so that I don’t have to duplicate in the rails database. I found the code to get me going on this from the first reply at this Ruby Forum conversation. Below is a method I defined in my employee controller.
def sync
@conn = DBI.connect('DBI:ODBC:odbc_name','username','password')
Employee.current_employees.each do |emp|
rs = @conn.select_one('SELECT EMPLOYEE.WC_Code, EMPLOYEE.Employee_Name
FROM EMPLOYEE WHERE ((EMPLOYEE.Employee) = \'' +
emp.employee_number.to_s + '\')')
emp.empl_workers_comp_code = rs["WC_Code"] unless rs.nil?
emp.save
end
redirect_to :action => 'list'
end
The only problem is I can only seem to call this method while running WEBrick. If I try the method while running Mongrel I get
DBI::DatabaseError in EmployeesController#sync
S1000 (-4019) [TimberlineODBC][TimberlineODBCEngine ODBC Driver][DRM File Library]Drive or directory unavailable [IO-WIN 3]
Screen\OD.scr
RAILS_ROOT: c:/rails/kj/config/..
Application Trace | Framework Trace | Full Trace
c:/ruby/lib/ruby/site_ruby/1.8/DBD/ODBC/ODBC.rb:95:in `connect'
c:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:584:in `connect'
c:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:384:in `connect'
#{RAILS_ROOT}/app/controllers/employees_controller.rb:9:in `sync'
c:/ruby/lib/ruby/site_ruby/1.8/DBD/ODBC/ODBC.rb:95:in `connect'
c:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:584:in `connect'
c:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:384:in `connect'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.12.2/lib/action_controller/base.rb:910:in `perform_action_without_filters'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.12.2/lib/action_controller/filters.rb:368:in `perform_action_without_benchmark'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.12.2/lib/action_controller/benchmarking.rb:69:in `perform_action_without_rescue'
c:/ruby/lib/ruby/1.8/benchmark.rb:293:in `measure'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.12.2/lib/action_controller/benchmarking.rb:69:in `perform_action_without_rescue'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.12.2/lib/action_controller/rescue.rb:82:in `perform_action'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.12.2/lib/action_controller/base.rb:381:in `process_without_filters'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.12.2/lib/action_controller/filters.rb:377:in `process_without_session_management_support'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.12.2/lib/action_controller/session_management.rb:117:in `process'
c:/ruby/lib/ruby/gems/1.8/gems/rails-1.1.3/lib/dispatcher.rb:38:in `dispatch'
c:/ruby/lib/ruby/gems/1.8/gems/mongrel-0.3.13.2-mswin32/lib/mongrel/rails.rb:85:in `process'
c:/ruby/lib/ruby/gems/1.8/gems/mongrel-0.3.13.2-mswin32/lib/mongrel.rb:563:in `process_client'
c:/ruby/lib/ruby/gems/1.8/gems/mongrel-0.3.13.2-mswin32/lib/mongrel.rb:562:in `process_client'
c:/ruby/lib/ruby/gems/1.8/gems/mongrel-0.3.13.2-mswin32/lib/mongrel.rb:648:in `run'
c:/ruby/lib/ruby/gems/1.8/gems/mongrel-0.3.13.2-mswin32/lib/mongrel.rb:648:in `run'
c:/ruby/lib/ruby/gems/1.8/gems/mongrel-0.3.13.2-mswin32/lib/mongrel.rb:637:in `run'
c:/ruby/lib/ruby/gems/1.8/gems/mongrel-0.3.13.2-mswin32/lib/mongrel.rb:969:in `run'
c:/ruby/lib/ruby/gems/1.8/gems/mongrel-0.3.13.2-mswin32/lib/mongrel.rb:968:in `run'
c:/ruby/lib/ruby/gems/1.8/gems/mongrel_service-0.1/bin/mongrel_service:84:in `service_main'
c:/ruby/lib/ruby/gems/1.8/gems/mongrel_service-0.1/bin/mongrel_service:153
c:/ruby/bin/mongrel_service:18
c:/ruby/lib/ruby/site_ruby/1.8/DBD/ODBC/ODBC.rb:95:in `connect'
c:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:584:in `connect'
c:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:384:in `connect'
#{RAILS_ROOT}/app/controllers/employees_controller.rb:9:in `sync'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.12.2/lib/action_controller/base.rb:910:in `perform_action_without_filters'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.12.2/lib/action_controller/filters.rb:368:in `perform_action_without_benchmark'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.12.2/lib/action_controller/benchmarking.rb:69:in `perform_action_without_rescue'
c:/ruby/lib/ruby/1.8/benchmark.rb:293:in `measure'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.12.2/lib/action_controller/benchmarking.rb:69:in `perform_action_without_rescue'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.12.2/lib/action_controller/rescue.rb:82:in `perform_action'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.12.2/lib/action_controller/base.rb:381:in `process_without_filters'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.12.2/lib/action_controller/filters.rb:377:in `process_without_session_management_support'
c:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.12.2/lib/action_controller/session_management.rb:117:in `process'
c:/ruby/lib/ruby/gems/1.8/gems/rails-1.1.3/lib/dispatcher.rb:38:in `dispatch'
c:/ruby/lib/ruby/gems/1.8/gems/mongrel-0.3.13.2-mswin32/lib/mongrel/rails.rb:85:in `process'
c:/ruby/lib/ruby/gems/1.8/gems/mongrel-0.3.13.2-mswin32/lib/mongrel.rb:563:in `process_client'
c:/ruby/lib/ruby/gems/1.8/gems/mongrel-0.3.13.2-mswin32/lib/mongrel.rb:562:in `process_client'
c:/ruby/lib/ruby/gems/1.8/gems/mongrel-0.3.13.2-mswin32/lib/mongrel.rb:648:in `run'
c:/ruby/lib/ruby/gems/1.8/gems/mongrel-0.3.13.2-mswin32/lib/mongrel.rb:648:in `run'
c:/ruby/lib/ruby/gems/1.8/gems/mongrel-0.3.13.2-mswin32/lib/mongrel.rb:637:in `run'
c:/ruby/lib/ruby/gems/1.8/gems/mongrel-0.3.13.2-mswin32/lib/mongrel.rb:969:in `run'
c:/ruby/lib/ruby/gems/1.8/gems/mongrel-0.3.13.2-mswin32/lib/mongrel.rb:968:in `run'
c:/ruby/lib/ruby/gems/1.8/gems/mongrel_service-0.1/bin/mongrel_service:84:in `service_main'
c:/ruby/lib/ruby/gems/1.8/gems/mongrel_service-0.1/bin/mongrel_service:153
c:/ruby/bin/mongrel_service:18
and a different error in Apache that I can’t reproduce right now. I’ll probably have to figure this out sooner or later once I have more data that needs to be synced on a regular basis. I tried just writing a method for the model that returns the data in question from the Pervasive database, thus providing completely updated data on every request and avoiding storing the data on the rails database, but it was waaaaaaay too slow. Maybe if there were a Pervasive SQL adapter it would be faster than over ODBC.