Nuby: advice sought on data-slinging script

ruby-talk
1

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

2

Roy Pardee wrote:

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

3

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

Cheers,

-Roy

Robo <robo@mars.com> wrote in message news:<r%ibd.7895$mZ2.686527@news02.tsnz.net>...

···

Roy Pardee wrote:
> 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