Nubie: How to start?

[Graham Foster]

I need to write a SQL report agent. Very simple.

-) Run various administrative SQL queries against different
databases

-) Run as a scheduled job, (under Windows AT command I guess) so they
will be automatically refreshed every 8 hours / day / week (or
whatever frequency it needs doing)

- Output would be a textual "report" from the SELECT recordset dump,
do a defined filepath

and/or

- e-mail (to me) saying all is still well (or not) optionally
including the report

I need to be able to add new scripts easily, and I can have a
dedicated machine running it if necessary (although I'd like a
service.. if this is possible?)

If this config doesn't work, then stand-alone EXE's would also suit
me. I'm assuming that this would be all entirely command line driven
with parameter files (or something).

I was about to reach for Perl (as I've done something similar there
before), but I think I'd like to try Ruby, anyone give me a start
here?

 

[James Edward Gray II]

I need to write a SQL report agent. Very simple.

-) Run various administrative SQL queries against different
databases

Ruby has a DBI, like you're probably use to with Perl.

-) Run as a scheduled job, (under Windows AT command I guess) so they
will be automatically refreshed every 8 hours / day / week (or
whatever frequency it needs doing)

I'm not a Windows user, so I'll trust your judgment here as it sounds
like you already have it figured out.

- Output would be a textual "report" from the SELECT recordset dump,
do a defined filepath

This is as trivial with Ruby as it is with Perl.

and/or

- e-mail (to me) saying all is still well (or not) optionally
including the report

Ruby's standard library covers sending e-mail. I use it frequently.

I need to be able to add new scripts easily, and I can have a
dedicated machine running it if necessary (although I'd like a
service.. if this is possible?)

I don't see any reason this isn't possible.

If this config doesn't work, then stand-alone EXE's would also suit
me. I'm assuming that this would be all entirely command line driven
with parameter files (or something).

I was about to reach for Perl (as I've done something similar there
before), but I think I'd like to try Ruby, anyone give me a start
here?

I don't think I understand this question? What exactly are you
needing? Sounds like you have a plan. Have you learned Ruby yet or is
that where you are starting? Did you need some other kind of
assistance?

James Edward Gray II

 

[Zach Dennis]

I need to write a SQL report agent. Very simple.

-) Run various administrative SQL queries against different
databases

-) Run as a scheduled job, (under Windows AT command I guess) so they
will be automatically refreshed every 8 hours / day / week (or
whatever frequency it needs doing)

- Output would be a textual "report" from the SELECT recordset dump,
do a defined filepath

You could look at some of the SQL projects at:

http://raa.ruby-lang.org/
http://www.ruby-forge.org

and/or

- e-mail (to me) saying all is still well (or not) optionally
including the report

This is very simple with the Net::SMTP library. It is apart of the
standard library. The API documentation can be found at:

http://www.ruby-doc.org/stdlib/libdoc/net/smtp/rdoc/

If you don't want to read docs and want to read some code, I can send
you a email script i wrote a while back. It even has a command
line interface to it as well. It may work unmodified for you.

I need to be able to add new scripts easily, and I can have a
dedicated machine running it if necessary (although I'd like a
service.. if this is possible?)

How you want top add scripts to the mix could be extremely easy. It
would moreover depend on
what type of structure you are going to give your program. One way might
be to create a class then
runs the scripts. Put the name of each script in a config file, have the
main class read it in. It could then
just loop through the scripts. Any time you add a new script, just add
it to the config file.

You can define a service by using srvany.exe. Check it out at:

http://support.microsoft.com/kb/q137890/

I was about to reach for Perl (as I've done something similar there
before), but I think I'd like to try Ruby, anyone give me a start
here?

I think you'll find this task is easier then you think! Probably way
easier then in Perl!

Zach

 

[Shashank Date]

Hi Graham,

--- Graham Foster

I need to write a SQL report agent. Very simple.

-) Run various administrative SQL queries against
different
databases

What databases are these? Are they heterogeneous?

-) Run as a scheduled job, (under Windows AT command
I guess) so they
will be automatically refreshed every 8 hours / day
/ week (or
whatever frequency it needs doing)

Is using the Scheduler an option?

- Output would be a textual "report" from the SELECT
recordset dump,
do a defined filepath

How about CSV files (or Excel Spread-sheets)?

and/or

- e-mail (to me) saying all is still well (or not)
optionally
including the report

No problem there. We use the "blat" utility although I
think it can be done using pure Ruby just as easily.

I need to be able to add new scripts easily, and I
can have a
dedicated machine running it if necessary (although
I'd like a
service.. if this is possible?)

Take a look at win32-utils project on RubyForge.

If this config doesn't work, then stand-alone EXE's
would also suit me. I'm assuming that this would be
all entirely command line driven with parameter
files (or something).

Perfect !

I was about to reach for Perl (as I've done
something similar there before), but I think I'd
like to try Ruby, anyone give me a start here?

You've come to the right place. Welcome !!!

All what you have described is daily bread and butter
for me (and I suspect many others on this list too). I
program queries for a farm of SQL Server 2000
databases.

If you could be more specific in what you want I would
gladly share my experience (and some code too).

Feel free to ask ...

-- shanko



__________________________________
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.
http://promotions.yahoo.com/new_mail

 

[Graham Foster]

Hello Shashank,

What databases are these? Are they heterogeneous?

They are either Oracle or MySQL, but we have several different
schema's and different servers - all intranet. The servers are all
Unix, but I have no Unix expertise myself, and I just query them as
remote databases using JDBC/ODBC or whatever other interface layer is
available in the language I use.

Is using the Scheduler an option?

Maybe.. I didn't know there was a Ruby Scheduler

How about CSV files (or Excel Spread-sheets)?

Useful extension.. plain old text would do as a start

No problem there. We use the "blat" utility although I
think it can be done using pure Ruby just as easily.

...blat? I assume this is a Unix utility?

You've come to the right place. Welcome !!! Thanks...

If you could be more specific in what you want I would
gladly share my experience (and some code too).
Feel free to ask ...

As a beginner .. that would be a great start for me. I think my
requirements are a bit clearer now, and these needn't be long
(Ruby) scripts or a complex setup I assume.