I accidentally posted this to the RoR forum; my apologies to anyone who
receives this twice.
As a new Ruby user (JRuby, actually), I’ve picked up this language with
a very specific purpose: to export data from a JDBC database into flat
files. I’ve figured out how to get the data out of the DB and into a
file (using ActiveRecord-JDBC), but it requires a bit too much
configuration. My first stab implementation requires me to define an
activerecord class for each table I want to export, and to define a
method all_columns in each class
that lays out the columns in the correct order. I’ve also setup a
module (CSVable) that extends ActiveRecord so I can call the export
function directly on each class. Here is the code:
Module definition
module CSVable
extend ActiveRecord
class Base < ActiveRecord::Base
def self.export_table_to_csv(delimiter = “,”, options = nil)
File.open("#{self.table_name}.dat",
File::WRONLY|File::TRUNC|File::CREAT) do |file|
self.find(:all, options).each do |trow|
file.puts trow.all_columns.join(delimiter)
end
end
end
end
end
Class definitions
class USR_USER_ACCOUNT < CSVable::Base
set_table_name “USR.USER_ACCOUNT”
def all_columns
[ self.UID, self.USER_NAME, self.USER_INIT, self.ACTIVE_FLAG,
self.LAST_MODFD_TS ]
end
end
class GRP_GRP < CSVable::Base
set_table_name “GRP.GRP”
def all_columns
[ self.PARTY_ROLE_ASSIGN_PID, self.GRP_NBR_ID,
self.LAST_MODIFIED_TIMESTAMP ]
end
end
…
Usage for the module
USR_USER_ACCOUNT.export_table_to_csv("|")
The major problem with this approach is that I have to setup all the
column info for each of the 36 tables. Is there an easier way to get an
array of the columns while still retaining the original order?
I’ve tried using the attributes method, but since it returns a hash, the
columns are not in order. My dabbles with the columns method have not
been successful either. Any ideas?
I also have a feeling there is a much better way to implement the export
function…
Any help or suggestions are most appreciated!
-Adam
On Oct 31, 9:30 am, Adam B. [email protected] wrote:
that lays out the columns in the correct order. I’ve also setup a
self.find(:all, options).each do |trow|
def all_columns
end
–
Posted viahttp://www.ruby-forum.com/.
If you’re always doing (essentially) “select *”, won’t Ruby’s dbi
work? I don’t know if it’s guaranteed, but the columns always seem to
be returned in the order they are defined in the database.
[email protected] wrote:
If you’re always doing (essentially) “select *”, won’t Ruby’s dbi
work? I don’t know if it’s guaranteed, but the columns always seem to
be returned in the order they are defined in the database.
Hmm… unfortunately I’m using JRuby and ActiveRecord-JDBC because my
database is only accessible via JDBC (I’m sure there are other ways to
access a Caché database, but not many), so I can’t use DBI, unless
someone has developed access to Caché with DBI!
I’ll look into it anyway!
function directly on each class. Here is the code:
require ‘fastercsv’
def to_csv(records, filename=nil, columns=nil)
filename ||= “#{records.first.class.table_name}.dat”
columns ||= records.first.class.columns.map {|x| x.name }
FasterCSV.open(filename, ‘w’) do |csv|
records.each do |record|
csv << columns.map {|column| record.attributes[column] }
end
end
end
class UsrUserAccount < ActiveRecord::Base
set_table_name “USR.USER_ACCOUNT”
end
to_csv(UsrUserAccount.find(:all))
On Oct 31, 9:22 pm, Adam B. [email protected] wrote:
I’ll look into it anyway!
Posted viahttp://www.ruby-forum.com/.
I see. I don’t know much about either JDBC or Caché, but this might
help:
Daniel S. wrote:
require ‘fastercsv’
def to_csv(records, filename=nil, columns=nil)
filename ||= “#{records.first.class.table_name}.dat”
columns ||= records.first.class.columns.map {|x| x.name }
FasterCSV.open(filename, ‘w’) do |csv|
records.each do |record|
csv << columns.map {|column| record.attributes[column] }
end
end
end
Daniel, thank you for your reply and effort. I tried this function out
with no avail. For some reason the output file is always empty (I have
confirmed the table is not empty). The file has the right number of
lines, but no data. I have come to the same end with this code:
#within the class inheriting ActiveRecord::Base
def self.export_table_to_csv(delimiter = “,”, options = nil)
file = File.open("#{self.table_name}.dat",
File::WRONLY|File::TRUNC|File::CREAT)
rec = self.find(:all, options)
for row in rec do
line = columns.inject([]) { |arr, col| arr << row[col]; arr}
file.puts(line.join("|"))
end
end
I am beginning to suspect that there is a problem with the columns logic
in AR as related to the AR-JDBC extension (or perhaps specifically with
the driver I’m using), but that is purely speculation on my part. My
Rubyism is not advanced enough to know how to debug that, but I’ll look
at the code for AR-JDBC and see if anything jumps out at me.
The link from [email protected] looks promising for setting up JRuby DBI
with JDBC. I’m going to try it out if I am unable to resolve this using
AR-JDBC soon!
Thank you both for all your help!
[email protected] wrote:
I see. I don’t know much about either JDBC or Cach�, but this might
help:
Ola Bini: Programming Language Synchronicity: Ruby-DBI and JDBC
I think the DBI solution suggested is going to work. Once I have some
clean code to play with, I’ll post my solution. Thanks again.
[email protected] wrote:
Ola Bini: Programming Language Synchronicity: Ruby-DBI and JDBC
After following the instructions found at the URL above, I was able to
finally do what I needed! Thanks for all your help! My full
implementation follows:
#####################################################################
Export_ODS.rb
jdbc drivers must be located in the CLASSPATH or in $JRUBY_HOME/lib
#####################################################################
require ‘rubygems’
require ‘dbi’
Load DB configuration - yml file in same directory
def connect()
cfg = YAML::load(File.open(‘dbinfo.yml’))
DBI.connect(‘DBI:’ + cfg[“url”], cfg[“username”], cfg[“password”],
‘driver’=>cfg[“driver”])
end
Connect to the database
dbh = connect
Define the delimiter used in the CSV output files
delim = ‘|’
export_tables = File.open(‘schema_table_list.dat’, ‘r’).readlines.each
do |line|
build an array from the schema/table file in the form schema.table
line.strip!.gsub!(delim, ‘.’)
end
for each table in the list…
export_tables.each do |table|
open a file named after the table and…
File.open(“#{table}.dat”, File::WRONLY|File::TRUNC|File::CREAT) do
|file|
# write out each row from the table to the aptly named file.
dbh.select_all(“select * from #{table}”).each do |row|
file.puts row.join(delim)
end
end
end
End Export.rb
#####################################################################
dbinfo.yml
#####################################################################
driver: com.intersys.jdbc.CacheDriver
url: jdbc:Cache://cache-server:56773/cachedb
username: _system
password: mypass
#####################################################################
schema_table_list.dat
Currently generated from Oracle, may write a ruby script to pull it
out of the Cache side.
#####################################################################
ENROLL|ENROLLMENT
GRP|GRP
MEMBER|MEMBER
MEMBER|MEMBER_DX
ORGROLE|EMPLOYERGROUP
…