Why?

One of the major reasons people use Rails and Database systems is to pull useful information out of them. We have happily been using a tool called ReportMan for our Windows based applications (ActiveX and Dot Net) and found it to have a number of desirable features:

1. Highly configurable
2. PDF and TXT generation
3. Straightforward GUI Report Designer
4. Promoted as being able to work under Linux as well as Windows
5. Open Source (Free – although we opted to pay for it.)
6. Command line tools for creating reports

This last feature caught our eye, so we decided to try our luck at deploying it on Linux, with shell support only. A combination of scattered documentation for ReportMan under Linux and our own inexperience with Linux administration made it difficult for us to install.

Ours is a standard setup hosted at Railsplayground, using:

• Rails 1.2
• MySQL
• Red Hat Enterprise Linux Virtual Server
• lighttpd
• Ferret
  
  Now the guys at Railsplayground are great and without them, we would never have finished deploying this – however, the documentation was just to thin for them as we tried to install ReportMan and its associated dependencies the first time around.

After a couple weeks of hitting brick walls, we shelved it, figuring there had to be a better way. We played with other free report generators, such as PDFWriter with some success, but never found anything that worked quite how we wanted. While we had a couple solutions that worked, we weren’t really happy with any of them, so we decided to take another look at ReportMan. Truly, if we had not already had such a good experience using it for stand-alone applications, we would have continued looking elsewhere. This second time, with a bit of hard work, luck, and great support from RailsPlayground we got it up and running. Hopefully, if anyone else is interested in doing this, our experiences will help move them along.

There are two central resources that we found for ReportMan:

• The ReportMan main page
• ReportMan on Yahoo Groups

ReportMan touts itself as also having a report server, but our only interest was in getting the command line tools up and running, so we could execute these from our Rails application. We would be using the GUI Report Designer to create our reports in MS Windows.

So our goal was fairly simple:
Get ReportMan installed on Linux, along with all the necessary dependencies, so that we could successfully create reports from the Rails environment.

Since ReportMan was programmed in Delphi, it shouldn’t be a big surprise that the Linux version was compiled with Kylix. Now information on Kylix is rather slim as well, but given its relatively short life and as it was abandoned by Borland back in 2004 or 2005. There are downloads available, but not in any obvious places or collected in any way that we were able to discover.

This was a Portugese page that we kept translated for some info on Kylix setup: http://tinyurl.com/yw9ocw

Installation

So the first key to getting everything working was that all of the ReportMan Linux executables as well as all of the correct Shared Library files (.so) needed to be located and installed on our box. At the bottom of this, we provide a link to all of the files that we used to get things working in one tar ball.

Two files need to be created in ~/.borland/

dbxconnections

We used the [MSConnection] setting in dbxconnections, with the following parameters, as this allows us to keep the report pretty much the same without too much modification to work between Linux and Windows.

[MSCONNECTION]
BlobSize=1
Database=your_database_name
DriverName=OpenODBC
ErrorResourceFile=
HostName=localhost
LocaleCode=0000
Password=your_db_password
User_Name=root

dbxdrivers

We used the OpenODBC driver settings in dbxdrivers.
[OpenODBC]
GetDriverFunc=getSQLDriverODBC
VendorLib=/usr/lib/libodbc.so
LibraryName=/usr/lib/libdbxoodbc.so
Database=Driver={MySQL};Server=localhost;Port=3306;Option=131072;Stmt=;Database=database_name;Uid=root;Pwd=your_db_password;
User_Name=root
Password=your_db_password

Required kylix3 lib files

See the attached reportman.tar.gz file. Under the directory kylix/kylix so files/ there are three libraries. Copy these into /usr/lib.
Run ldconfig after they’ve been copied.

libodbc.so

unixODBC-devel provides this lib. unixODBC is currently availible in a gzip, tar format. This means that you should;

1. copy the unixODBC-2.2.12.tar.gz file somewhere you can create files and directories
2. gunzip unixODBC-2.2.12.tar.gz
3. tar xvf unixODBC-2.2.12.tar

Doing so will create a unixODBC directory with all source files inside. Copy over the .so files into /usr/lib/.

Run “ldconfig” after they have been copied → if you copied them correctly you will get no message.

libdbxoodbc.so (found in DBDesigner4)

We provided this in the attached reportman.tar.gz file. It’s under the usr-lib/ directory, just copy this into /usr/lib and run ldconfig.

libmidas.so.1 library (found in DBDesigner4)

libdbxoodbc.so has a dependency on libmidas. Reportman won’t tell you this if you try to run it, instead it will just say it can’t find libdbxoodbc.so. Running `exec /usr/lib/libdbxoodbc.so` will tell your what files you need.

We installed the DBDesigner 4 linux tar file from http://www.fabforce.net/downloads.php which included these files. We created a symbolic link to them.

• Potentially relevant links
• http://sourceforge.net/project/showfiles.php?group_id=59343&package_id=55389&release_id=107202
• http://rick-ross.com/papers/borcon2002/2282.html
• http://telaeris.com/download/kylix.zip
• DBDesigner4 keeps its library files in LinuxLib/, so just copy those files into /usr/lib

libmysqlclient.so, libmysql.so.1

May not be required for unixODBC setup, but should be properly installed with MySQL. These might be useful if you choose to try the direct MySQL drivers.

MySQL/unixODBC connector

You can use your package manager to install this:
yum install mysql-connector-odbc

Then you’ll need to configure it with these two files: /etc/odbcinst.ini and /etc/odbc.inst.

/etc/odbcinst.ini:
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/libmyodbc.so
Setup = /usr/lib/libodbcmyS.so
UsageCount = 4

[MySQL ODBC 3.51 Driver] DRIVER = /usr/lib/libmyodbc3.so UsageCount = 5 /etc/odbc.inst [your_database_name] Driver = /usr/lib/libmyodbc3.so SERVER = localhost PORT = 3306 DATABASE = your_db_name OPTION = 131072 USER = mysql_user PASSWORD = password

When you’re done configuring ODBC, you need to run two commands to read it:

bash> odbcinst -i -d -f odbcinst.ini bash> odbcinst -i -s -f odbc.inst

Finally, Reportman

To use Reportman, there are command line binaries that generates reports from .rep files. So, we simply build the report on Windows and generate PDFs on our server using Rails. Inside the attached file, we included reportman. Just copy it over to /usr/local/.

Using It

The actual command to create a PDF is:

bash> /usr/local/reportman/printreptopdf open_orders.rep new_file.pdf

The use this with Rails, simply make an action in our controller that sends the PDF:

def pdf data = `sudo -u apache -H /usr/local/reportman/printreptopdf PATH/TO/REPORT.rep` send_data(data, :type => “application/pdf”, :filename => “test.pdf”) end

We’re using the sudo -u apache -H option to run the command as user apache and use apache’s home directory (this is important, reportman will look under ~/.borland for the two config files, so make sure you put -H).

Also, if you’re having trouble, reportman will send errors to STDERR. You can direct that to a file to debug:

`sudo -u apache -H /usr/local/reportman/printreptopdf PATH/TO/REPORT.rep 2>> stderr.txt`

Errors and their Solutions

(note: DSACTIVITYOUT is the Dataset name that we chose in the ReportMan Designer)

1. ERROR> DSACTIVITYOUT:Error returned from ODBC function SQLExecute ODBC Return Code: -1: SQL_ERROR

ODBC SqlState: 42000
Native Error Code: 1064
[MySQL][ODBC 3.51 Driver][mysqld-5.0.27]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘[date_eta]-Date() AS days_out, IIf(Date()-[date_eta]>0,Date()-[date_eta],"OK") A’ at line 3

Solution> Fix the SQL statement. MS Access and MS SQL Server have significantly different SQL functions for things like string concatenation and type conversions.

2. ERROR> ODBC SqlState: IM002
[unixODBC][Driver Manager]Data source name not found, and no default driver specified

Solution> You need to configure your ODBC settings. See “MySQL/unixODBC connector” section above.

3. ERROR> DSACTIVITYOUT:Parameter ‘PARAMTITLE’ not found

Solution> Remove PARAMTITLE line or Parameter from report. Alternatively, be sure to pass it in correctly with the “-PARAMS=” statement

4. ERROR> DSACTIVITYOUT:TSqlConnectionOdbc.SetOption(eConnHostName) not valid (Read-only) Error returned from ODBC function SQLDriverConnect (NoPrompt) ODBC Return Code: -1: SQL_ERROR

Once we got the program to run on the command line, we figured it would be pretty straightforward. Unfortunately, we hit a minor snag while debugging this.

Since we were running lighttpd, the command was processed as user “apache”. apache, by default, executes in /var/www

We created a symbolic link to “.borland” so we could get to dbxconnections and dbxdrivers.

We also needed to make minor changes to the report files so they would be processed correctly in Linux. In your .REP files, change the lines:

DataInfo = < item Alias = ‘MATREQ’ DatabaseAlias = ‘DATABASE_NAME_HERE’ SQL = …

to use your preferred DatabaseAlias – this is needed especially if you are deploying multiple Rails apps on one server. DATABASE_NAME_HERE will be the name specified in your dbxconnections (.ini in windows) file.

Also change the lines:

DatabaseInfo = < item Alias = ‘DATABASE_NAME_HERE’ LoadParams = True LoadDriverParams = True LoginPrompt = False Driver = rpdataado

to

DatabaseInfo = < item Alias = ‘DATABASE_NAME_HERE’ LoadParams = True LoadDriverParams = True LoginPrompt = True

and make sure that your alias name matches the DatabaseAlias that we changed above.

Once this was all done, we were finally up and running in Rails, with beautiful reports!

Attachment

Here’s the tarball of all the .so files you need. It also includes Reportman and its dependencies (Kylix).

reportman.tar.gz