nuby: advice sought on data-slinging script

[Roy Pardee]

Hey All,

I'd like to use ruby (v1.8 on win2k) to accomplish the following:

- Connect to an instance of mssql, using
integrated security.
- Issue a sql statement & spool the results off to
a tab-delimited text file (w/a header of field names).
- RSA-encrypt that file w/a particular public key
(shell out to gpg?).
- Log into an FTP server (using particular credentials)
& put my newly-encrypted file in say, /my_folder.

I'd like this to run as a scheduled task & so would also like to do
some logging.

If I can get this together, it will be my first "production" ruby
script. I'm about midway through the pickaxe 2 book & have read all
of _why's Poignant Guide. My background is vb.net. (I've actually
got a vb.net program that does all of this, but it's clunky & I'm
trying to learn ruby, so here I am.)

I'm writing to ask for feedback on the following general approach--in
particular, are these libs my best bet?

WIN32OLE to create an ADO Connection object for
the db interaction.

WIN32OLE to create an ADO Recordset to hold the
results of my sql statement.

IO.File to create my new text file.

Iterate over the recordset's .Fields
collection, writing their names to my File.

Iterate over the recordset's .Rows collection,
writing their values to my File.

%x() to shell out to the command line w/a
proper call to gpg.

Net::FTP to log into the ftp server & move my
newly-encrypted file.

Logger to do the logging.

Are there better libs for these things? Are there alternatives to
iterating over an ADO Recordset? Is there a native encryption lib
that I can feed a gpg-generated public key to for the encryption? Any
and all advice would be most welcome.

Thanks!

-Roy

 

[Robo]

Are there better libs for these things? Are there alternatives to
iterating over an ADO Recordset? Is there a native encryption lib
that I can feed a gpg-generated public key to for the encryption? Any
and all advice would be most welcome.

Thanks!

-Roy

You may like to try the ruby-dbi (http://ruby-dbi.rubyforge.org/) for
accessing the database, it provides a database independent interface.
Even if you'll only be using mssql, I find the dbi more usable anyway.

After connecting to the db, you can basically create the tab delimited
file by (untested):

dbh = DBI.connect(url, user, pass)

f = File.new("dump.txt", "w+")
sql = "SELECT..."
dbh.execute(sql) do |result|
result.column_names do |col|
f.puts col.join("\t")
end

result.fetch_array do |row|
f.puts row.join("\t")
end
end
f.close

 

[Roy Pardee]

Thanks very much! I'll check ruby-dbi out.

Cheers,

-Roy