Sunday, September 30, 2007

Using Ruby & SQL-DMO to Automate SQL Server Tasks

If you work with Microsoft SQL Server, you might like to know that you can automate many of your administrative tasks by leveraging Distributed Management Objects (SQL-DMO). SQL Server's Enterprise Manager is based on DMO, so most (if not all) of what you do through the Enterprise Manager interface can be automated through SQL-DMO -- and Ruby. Create, manage, and backup databases, tables, views, jobs, stored procedures, and more. Let's take a look at some of the available objects and Ruby code that uses them.

We start by creating an instance of the SQLServer object:


require 'win32ole'
server = WIN32OLE.new('SQLDMO.SQLServer')

To connect to your server using SQL Authentication, call the Connect method providing the server IP address or name, the login, and password:

server.Connect('127.0.0.1', 'login', 'password')

To connect using Windows Authentication, set the LoginSecure property to true, then call the Connect method with just the server IP address or name:

server.LoginSecure = true
server.Connect('127.0.0.1')

When you're done, be sure to close your connection by calling the Disconnect method:

server.Disconnect

The Databases method returns a collection of Database objects. You can access a specific Database object by passing the database name to the Databases method:

database = server.Databases('my database')

The Database object includes dozens of methods and several collections, including Tables, Views, and StoredProcedures. This code, for example, prints the names of all tables:

for table in database.Tables
puts table.Name
end

Furthermore, many database objects expose a Script method that returns the SQL code for creating that object. This little block of code saves the SQL for creating all your Table objects:

for table in database.Tables
File.open("#{table.Name}.sql", "w") do |f|
f.puts table.Script
end
end

The following block of code writes the text of each stored procedure to a text file:

database.StoredProcedures.each do |sp|
File.open("#{sp.Name}.txt", "w") do |f|
f.puts sp.Text
end
end

The SQL Server Agent service, which runs and manages jobs, is accessed via the JobServer object. The following code starts and stops this service:

server.JobServer.Start
server.JobServer.Stop

The JobServer object's Jobs method returns a collection of all SQLServer Agent jobs:

for job in server.JobServer.Jobs
puts "Name: #{job.Name}\nDescription: #{job.Description}\n\n"
end

I have a script that iterates over the Jobs collection and creates a "jobs inventory", an Excel workbook listing each job's name, description, frequency (daily, weekly, etc.) and start time.

Further details on SQL-DMO can be found in this MSDN article, and in this SQL Team article.

That's all for today, but if you use SQL Server, perhaps this has given you some ideas on how to use your Ruby skills to make SQL Server admin tasks a little easier.

UPDATE: You may also be interested in this earlier article about using Ruby and ADO for your SQL Server queries.

Feel free to post a comment here or send me an email with questions, comments, or suggestions.

Thanks for stopping by!

Digg my article

Wednesday, September 26, 2007

Using Rake to Automate Windows Desktop App Builds

We've recently talked about compiling your Ruby app with RubyScript2Exe, and creating an install package with Inno Setup.

I was inspired by RoW reader Luis Lebron, who recently shared with me his Rakefile for automating the running of RubyScript2Exe from within the NetBeans Ruby IDE.

My process for packaging an application for distribution involves, among other things:


  • Compiling the code with RubyScript2Exe
  • Replacing the default EXE icon with my own (using Resource Hacker)
  • Moving the EXE file to my Install folder
  • Updating the ReadMe file with the new version number
  • Updating the Inno Setup script with the new version number
  • Running the Inno Setup script to create a new Setup.exe file

This isn't a particularly time-consuming list of manual tasks. But I spend my workdays developing tools to automate my fellow employees' manual tasks. So it was inevitable that I would seek to do the same for myself.

I had no prior experience with Rake, a DSL created by Jim Weirich to automate project builds. I'm neither a C programmer nor a web developer, so I wasn't really motivated to investigate what Rake might have to offer. But after receiving Luis' email, I looked into using Rake, and then researched the options for running Resource Hacker and the Inno Setup compiler from the command line.

I still know almost nothing about Rake, but I learned enough to create a simple Rakefile to automate all of the above tasks. This could, of course, be done in Ruby without the using Rake, but NetBeans' Rake integration and templates make it handy. Now I can right-click on my project icon in NetBeans, select Run Rake Task => create_setup, and Rake takes care of the rest.

In case you're interested, here's an example of my Rakefile. It can no doubt be improved upon, but should give you an example to start from (Beware of line-wrap):

require 'rake'
require 'fileutils'
include FileUtils

# set constant values:
LIB_FOLDER = File.expand_path('../lib')
INSTALL_FOLDER = File.expand_path('../install')
ISCC = "C:/Program Files/Inno Setup 5/iscc.exe"
RESHACKER = "C:/Program Files/ResHacker/ResHacker.exe"
ISS_FILE = "#{INSTALL_FOLDER}/Setup.iss"
README_FILE = "#{INSTALL_FOLDER}/ReadMe.txt"

# extract values from main.rb file:
main_rb = open('../lib/main.rb').read
APP_TITLE = main_rb.scan(/APP_TITLE = '(.+)'/)[0][0]
EXE_NAME = main_rb.scan(/EXE_NAME = '(.+)'/)[0][0]
EXE_BASENAME = EXE_NAME.gsub('.exe', '')
APP_VERSION = main_rb.scan(/APP_VERSION = '(.+)'/)[0][0]

# rake tasks:
task :default => [:create_setup]

desc "Create setup.exe"
task :create_setup => [:move_exe, :modify_icon, :create_iss_script,
:edit_readme] do
puts "Creating setup.exe"
Dir.chdir(INSTALL_FOLDER)
system(ISCC, ISS_FILE)
end

desc "Create ISS script"
task :create_iss_script => [:move_exe] do
puts "Creating ISS script"
Dir.chdir(INSTALL_FOLDER)
data = ISS_TEXT.gsub('[APP_TITLE]', APP_TITLE)
.gsub('[APP_VERSION]', APP_VERSION)
.gsub('[EXE_NAME]', EXE_NAME)
.gsub('[EXE_BASENAME]', EXE_BASENAME)
File.open(ISS_FILE, 'w') do |f|
f.puts(data)
end
end

desc "Edit ReadMe.txt"
task :edit_readme do
puts "Updating ReadMe.txt file"
Dir.chdir(INSTALL_FOLDER)
txt = nil
open(README_FILE) do |f|
txt = f.read
end
old_version = txt.scan(/Version (\d\d\.\d\d\.\d\d)/)[0][0]
txt = txt.gsub(old_version, APP_VERSION)
File.delete(README_FILE)
open(README_FILE, 'w') do |f|
f.puts(txt)
end
end

desc "Modify EXE icon"
task :modify_icon => [:move_exe] do
puts "Modifying EXE icon"
Dir.chdir (INSTALL_FOLDER)
arg = " -addoverwrite #{EXE_NAME}, #{EXE_NAME}, application.ico,
icongroup, appicon, 0"
system(RESHACKER + arg)
end

desc "Move EXE to install folder"
task :move_exe => [:compile_code] do
puts "Moving EXE to install folder"
mv("#{LIB_FOLDER}/main.exe", "#{INSTALL_FOLDER}/#{EXE_NAME}")
end

desc "Compile code into EXE"
task :compile_code do
puts "Compiling main.rb into EXE"
system("rubyscript2exe.cmd", "#{LIB_FOLDER}/main.rb")
end

# text of Inno Setup script:
ISS_TEXT =<<-END_OF_ISS

[Setup]
AppName=[APP_TITLE]
AppVerName=[APP_TITLE] version [APP_VERSION]
AppPublisher=David L. Mullet
AppPublisherURL=http://davidmulletcom
AppContact= david.mullet@gmail.com
AppVersion=[APP_VERSION]
DefaultDirName=C:\\[APP_TITLE]
DefaultGroupName=[APP_TITLE]
UninstallDisplayIcon={app}\\[EXE_NAME]
Compression=lzma
SolidCompression=yes
OutputDir=.
OutputBaseFilename="[EXE_BASENAME]_Setup"

[Files]
Source: "[EXE_NAME]"; DestDir: "{app}"; Flags: ignoreversion
Source: "Readme.txt"; DestDir: "{app}"; Flags: isreadme ignoreversion

[Icons]
Name: "{group}\\[APP_TITLE]"; Filename: "{app}\\[EXE_NAME]";
WorkingDir: "{app}"
Name: "{group}\\View ReadMe File"; Filename: "{app}\\ReadMe.txt";
WorkingDir: "{app}"

END_OF_ISS

As always, post a comment here or send me an email with questions, comments, or suggestions.

Thanks for stopping by!

Digg my article

Sunday, September 23, 2007

Installing Your Ruby App with Inno Setup Installer

In my last article, we looked at 'compiling' your Ruby app into a single portable executable file using RubyScript2Exe.

As a reader commented, "For windows it becomes so important to make things 'one click'. I guess the next step would be to also wrap this in some kind of simple installer script to store it in the right place and add the appropriate menu/desktop icons."

Which brings us to the topic of today's discussion: using Inno Setup to create an Install program (ie, setup.exe) for your Ruby applications.

Inno Setup, created, by Jordan Russell, has been around for about a decade, is very well documented, and frequently updated. It's very customizable and extendable, and allows you to create professional installation packages that will allow you to create directories, install files to multiple locations (install folder, Windows system folder, etc.), create Start menu Program groups and icons, and place shortcut icons on the user's Desktop and Quick Launch bar. And it's free of charge.

Inno Setup creates your Setup.exe file based on parameters that you define in a text-based script. Your ISS script file is divided into sections, with parameter=value pairs, much like a Windows INI file. Starting out, you'll probably use the Inno Setup user interface to create and edit your scripts, and then to compile your installation package into a setup.exe and run it.



Section names may include [Setup], [Files], [Icons], and [Code]. Inno Setup includes a comprehensive help file detailing all of the options, and there is an extensive FAQ document on the website.

A typical ISS script might look something like this:


[Setup]
AppName=My Ruby Application
AppVerName=My Ruby Application version 09.23.07
AppPublisher=David Mullet
AppPublisherURL=http://davidmullet.com
AppContact=david@davidmullet.com
AppVersion=09.23.07
DefaultDirName=C:\My Ruby Application
DefaultGroupName=My Ruby Application
UninstallDisplayIcon={app}\MyRubyApp.exe
Compression=lzma
SolidCompression=yes

[Files]
Source: "MyRubyApp.exe"; DestDir: "{app}"; Flags: ignoreversion
Source: "ReadMe.txt"; DestDir: "{app}"; Flags: isreadme ignoreversion

[Icons]
Name: "{group}\My Ruby Application"; Filename: "{app}\MyRubyApp.exe"; WorkingDir: "{app}"
Name: "{group}\View ReadMe File"; Filename: "{app}\ReadMe.txt"; WorkingDir: "{app}"

Inno Setup is highly customizable. You can, for example, customize the install wizard, or include a [Code] section with Pascal procedures (see the Help file for details and examples). The scripts are straight text, and the compiler can be called from the command line, so you can create and compile your ISS script all within Ruby, if you like, such as in a Rakefile. I'll include just such an example in an upcoming article.

So, you can now create a single executable file for your Ruby application and use a professional-looking installer to distribute it.

Questions? Comments? Suggestions? Post a comment here or send me an email message.

Thanks for stopping by!

Digg my article

Tuesday, September 18, 2007

Compiling Your Ruby App with RubyScript2Exe

I've mentioned RubyScript2Exe previously. This tool allows you to 'compile' a script/application into a portable executable file (EXE) that you can easily provide to your users without requiring them to install Ruby and the required libraries. RubyScript2Exe traces and gathers all the necessary files, including the Ruby interpreter, and 'compiles' them into a single EXE file. You can easily embed images and icon files, and DLLs such as SQLite.

I put the word 'compile' in quotes above because RubyScript2Exe does not transform your code as a C compiler or .Net compiler would. Rather, it collects all the files necessary to run your application and bundles them into a single EXE file. When the user runs that EXE file, that bundle is quickly extracted to a temporary file and your Ruby code is executed.

Installing RubyScript2Exe is as easy as falling off a log, thanks to RubyGems. Just get to a command prompt and enter:


gem install rubyscript2exe

Include the following require statement at the top of your script:

require 'rubyscript2exe'

Whenever possible, include all your require statements at the top of your script. This ensures that RubyScript2Exe successfully traces and includes all the necessary files your application will need.

If you are compiling a non-console script and therefore want to use the rubyw.exe interpreter, rather than the ruby.exe interpreter, include the following module variable near the top of your script:

RUBYSCRIPT2EXE.rubyw = true

When I use the NetBeans Ruby IDE, which defaults a new project's main script name to "main.rb", I include the above code to avoid renaming the script with a ".rbw" extension or providing command-line parameters to the RubyScript2Exe compiler. More on that later.

A Ruby Forum reader and RubyScript2Exe user recently mentioned "I want to be able to wrap the icon file along with the rest of the application." You can embed additional files such as icons or DLLs in the executable like this:

RUBYSCRIPT2EXE.bin = ["my_icon.ico", "sqlite3.dll"]

When you run your compiled executable, RubyScript2Exe extracts all the files from your executable into a temporary directory. But sometimes you need to know the location of the folder the executable was originally run from. Just call the RUBYSCRIPT2EXE.exedir method:

APPLICATION_PATH = RUBYSCRIPT2EXE.exedir

Enough preparation! Let's compile our application. Go to a command prompt and enter:

rubyscript2exe my_script.rb

...or...

rubyscript2exe my_script.rbw

If your script has a filename extension of .rb, RubyScript2Exe will include the ruby.exe interpreter and a console window. If your script has a filename extension of .rbw, RubyScript2Exe will include the rubyw.exe interpreter and your app will therefore not have a console window; this is the same as if you had included RUBYSCRIPT2EXE.rubyw = true in your code.

The size of your compiled executable can vary widely depending on what files are needed to be included. A simple console app may be 1mb in size, a wxRuby 0.6 GUI app may be 3-4mb, and a wxRuby 2 GUI app may be 6-8mb in size. Part of this size is due to a known 'bug' that may cause some files (the 8mb wxRuby2.so file, for example) to be included twice. This affects the size of the EXE file but not the performance.

There you have it. But this post just scratches the surface. RubyScript2Exe's creator, Erik Veenstra, has done a great job maintaining and documenting this tool, and you should take a few minutes and read the docs here.

Questions? Comments? Suggestions?

Post a comment here or send me an email.

Thanks for stopping by!

Digg my article

Friday, September 14, 2007

Ruby & Excel: 911 Characters Only, Please

I was writing some code this week to query an SQL Server database and write the results to an Excel workbook.

I was defining a range in an Excel worksheet and inserting data using code something like this:


worksheet.Range("A2").Resize(data.size, data[0].size).Value = data

The above code defines a range starting at cell A2 and extending the width and length of my 2-dimensional array, then inserts my array into that range of cells.

This worked great -- about 95 percent of the time. But occasionally it would bomb. My debugging uncovered the fact that it would always and only bomb if a string exceeding 911 characters was inserted into a cell.

Some quick googling revealed that this is a known Excel 2003 bug, acknowledged by Microsoft. Microsoft's suggested workaround is "don't do that"; don't try to insert more than 911 characters into a cell as part of an array. Gee, thanks.

I was using the method above because it is significantly faster than inserting data one cell at a time. This isn't a big deal for 100 lines x 10 columns of data, but when you're inserting a million cells of data, there's a huge time difference. I could successfully insert the data one cell at a time, but my users might have to take extended coffee breaks.

I could write the data to a tab-delimited, comma-delimited, or XML text file; then open it in Excel and tweak it if necessary. But I hoped to avoid that if possible.

In case you're wondering, I resolved it with a compromise. Rather than insert the data all at once or cell-by-cell, I inserted it row-by-row:

worksheet.Range("A#{r + 2}").Resize(1, row.size).Value = row

This will still raise an exception when attempting to insert more than 911 characters into a cell. But we'll handle those exceptions by inserting that row's data cell-by-cell.

The revised code looks something like this:

data.each_with_index do |row, r|
begin
# try to insert the entire array into the row
worksheet.Range("A#{r + 2}").Resize(1, row.size).Value = row
rescue
# if exception, then insert each cell individually
row.each_with_index do |field, c|
worksheet.Cells(r + 2, c + 1).Value = field
end
end
end

It's not as fast as an all-at-once insert, but much faster than a cell-by-cell insert.

By the way, the '+1' offset for column number is because Ruby arrays have zero-based indexes, while Excel's column indexes are 1-based. And the '+2' offset for row number is for the same reason, plus 1 to skip the first row of the worksheet, which contains the field names.

So, insert data into worksheets as arrays when you can. But don't let the 911-Characters Bug bite.

Wednesday, September 12, 2007

NetBeans Ruby IDE

If you're looking for a new Ruby editor, I suggest you check out NetBeans. Tor Norbye and the gang have been doing a great job adapting this Java IDE for use with Ruby and Rails.

I usually use SciTE, the Scintilla text editor, for writing my Ruby code. It's fast, flexible, and lightweight, consuming a fraction of the resources of a full-blown IDE. It's still my editor of choice for small scripts. But I've begun using NetBeans for larger projects and am very pleased so far.

NetBeans is now available in a Ruby-only version which, presumably, is a little slimmer than the full Java + Ruby IDE. And you can trim a little more fat by deactivating a few plug-ins. On my Windows XP systems, memory usage is in the 80-120Mb range. That's acceptable, even on my more memory-challenged machine, when you consider the potential productivity gains offered by the IDE features. And the fact that it's free is certainly a plus.

Further details can be found on the NetBeans Ruby Support page, on the NetBeans Ruby wiki, and in numerous in-depth reviews like this one by Daniel Spiewak.

New builds are posted every few hours, and the latest stable build can always be found here. I usually replace my nbrubyide folder every day or two.

So if you're looking for a full-featured Ruby IDE, take NetBeans for a test drive and see if it meets your needs. It may be a dumb name, but it's a good Ruby IDE.

Sunday, September 9, 2007

RubyConf 2007 Comes to Charlotte

Somehow, I had missed the fact that RubyConf 2007 will be held here in Charlotte, North Carolina (USA) November 2-4.

Fortunately, a friend made me aware of this fact in time for me to register to attend.

Lots of good stuff is on the agenda. Here's a couple talks that caught my eye as a Windows desktop developer:

John Lam on The State of IronRuby
Bruce Williams on Writing Client and Desktop Applications in Ruby

Registration is still open, but I hear that seats are going fast.