Sunday, September 30, 2007

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 = trueserver.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.Nameend

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:

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):

Sunday, September 23, 2007

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.

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.

Tuesday, September 18, 2007

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:

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.

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.

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

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.