Archive for the ‘tech’ Category

Connecting to MySQL with Crystal Reports XI

Wednesday, June 13th, 2007

CrystalReports is not too into the open source scene, consequently it doesn’t work with MySQL out of the box. But you can easily get the Crystal Reports Designer to work against a MySQL database by using JDBC.

  1. Download the MySQL Connector J jar file. That download should contain a jar file that looks something like:

  2. Add the location of your newly downloaded jar file to the Classpath, as defined in CrystalReports CRConfig.xml file. On a Windows machine, the config file will be located somewhere like:
    C:\Program Files\Business Objects\Common\3.5\java\CRConfig.xml

  3. Once you have altered your CRConfig.xml, close and reopen Crystal Reports.
  4. From the menu: File -> New -> Standard Report
  5. In the “Available Data Sources” list, double-click to expand “Create New Connection”
  6. Double-click to expand “JDBC (JNDI)”
  7. Double-click “Make New Connection”
  8. Connection URL: “jdbc:mysql://” (Use your own database host name and db name).
  9. Database Classname: “com.mysql.jdbc.Driver”
  10. Click “Next”
  11. Enter a database user/password combination when prompted.
  12. You should now be able to inspect the tables/columns in the database to begin reporting.

If the connection is not allowed, make sure your granted access settings in MySQL allow you to connect from wherever you are. Try connecting using the vanilla MySQL command line client.

Java Injection in Jasper Reports

Wednesday, February 14th, 2007

I needed to create a JasperReports variable of type NumberFormat to format dollars without cents.

Try to stay calm while reading this.

JasperReports allows the creation of variables that can be evaluated at report run time. The variables can be of any Java object type. You can set the value of the variable to be the result of any Java expression that evaluates to the same type as the object variable (which cannot be void).

I created a JasperReports variable named “nfc” for (number format currency) and set it up to be of type java.text.NumberFormat. Then I set its value to be NumberFormat.getCurrencyInstance()


It’s convenient to use in fields throughout my report:


That worked, but to my chagrin, getCurrencyInstance() returns a formatter that includes two decimal places for cents. In free-form Java of course, we’d just call setMaximumFractionDigits(0) on the formatter, and be rid of the wretched pennies. But in JasperReports we can only evaluate Java “expressions.” Because setMaxiumFractionDigits returns void, it can’t be used as an expression.

Luckily I figured out a way to inject arbitrary Java into the JasperReports expression evaluator. You can do this in iReport or straight in the jrxml file.

Here’s how:
1. Create a variable: (View->Variables->New)
2. Type in the type: java.text.NumberFormat
3. Set the value to:


System.out.println( "Hello World! Java Injection!" );

There are two things to notice:

A. The fun begins with ); That is what JasperReports expression evaluator sticks at the end of your expression when it compiles. After you provide your own ); to complete that expression evaluation, you can add any Java statements you like. Just remember when you’re done, leave off the trailing ); because the expression evaluator will tack that onto the end.

B. “value” is the name of the temporary variable that will contain the result of your expression. In order to call setMaximumFractionDigits on my NumberFormat object, I first have to cast “value” to NumberFormat.

Here’s how that variable looks in the JasperReports xml (jrxml) file:

<variable name=”nfc” class=”java.text.NumberFormat” resetType=”Report” calculation=”Nothing”>
<variableExpression> <![CDATA[
System.out.println( “Hello World! Java Injection!” );

Note: JasperReports also allows the use of Java ‘Scriptlets’ that would solve this problem, but I didn’t want to use that feature because it would force other users of my report to get the pre-compiled external class file into their classpath, yadda blah blah ClassNotFoundException blah half-the-day-wasted yadda yadda… My point is that I wanted the JasperReports XML to stand on its own three feet, shielding everyone else from external dependencies.

UPDATE: I have now learned that JasperReports has formatting built in and while this Java injection technique is “cool”, it is not necessary for number formatting. Here’s what I eventually figured out: In iReport, right click on the number field, choose Properties, click on the “Text Field” tab, and then enter #,##0.0 in the Pattern box. This example will round to one decimal digit. You can use any pattern supported by java.text.NumberFormat.

Password Protect a Folder on the Mac

Wednesday, November 15th, 2006

I was looking for a way to password protect a folder on the Mac. I needed to be able to backup a large collection of files onto a USB hard drive and then send it securely through the mail for safe off-site storage. For some reason, this functionality is not integrated directly into Finder.

Finally I stumbled upon a way to do it with built-in OS X software. That is perfect because I wanted to avoid using random 3rd-party trialware for something that an operating system ought to be able to do for free.

How to Password Protect a Folder on the Mac:

  • Open up the “Disk Utility” application
  • File -> New -> Disk Image from Folder…
  • choose a folder to protect
  • choose “AES-128” encryption and press Save
  • Enter your desired new password twice (Do not forget it.)

This process creates an ordinary Macintosh disk image (.dmg) file. The disk image contains the entire contents of the folder, but cannot be opened unless the correct password is supplied. To open it, just double-click the .dmg file in Finder. A password dialog box will appear. Once you supply your correct password, Finder will automatically unencrypt your data and mount the image as a disk.

Palm Desktop Import Export

Sunday, May 8th, 2005

A system that supports data import/export should be able to import its own export format without corruption to the data. That property allows the user to temporarily export data for external manipulation before re-importing back into the primary system.

I noticed a bug in Palm Desktop 4.1.4 where email addresses are imported into “work” phone number fields. This happens when I import Palm Desktop CSV (that was just exported from Palm Desktop) back into Palm Desktop.

Volker also discovered this bug when he was trying to import his contacts from Thunderbird. He wrote his own .tpa file to help him correctly import the Thunderbird format into Palm.

In my case, I simply wanted to be able to re-import what I had just exported from Palm Desktop. I created pd414.tpa to work around the bug and import Palm Desktop’s own CSV format back into Palm Desktop without corruption.

To use a .tpa file, just drop it into your PalmDesktop application folder. The next time you import, that rule-set will appear as a choice in the import format drop-down list. If you use my .tpa file, you will see this new choice:

Palm Desktop 4.1.4 (All Fields) (*.csv)

Clean URIs in JSP

Monday, January 17th, 2005

Yes, it *is* in fact possible to have clean, well-designed, query-parameter free URIs with JSP.

Expose the public URI:


Then you can use Apache to re-write it to:


With the following directives:

RewriteEngine on
RewriteRule ^/tag/([^/]*)/ /index.jsp?tag=$1 [PT]

Breaking the Megaperson Barrier

Sunday, January 9th, 2005

The idea of making photographs that are vastly more detailed than humans can perceive is fascinating. Max Lyons broke the gigapixel barrier by stitching together 196 six-megapixel images, and then later researchers in the Netherlands pushed it further by producing a 2.5 Gigapixel image

Now that gigapixel images exist, its only a matter of time before they are printed appropriately. Imagine a billboard sized print that had the same granularity of a high-quality magazine, but throughout the whole giant print. From a distance, it would appear like any other billboard, but as you go in for closer and closer inspection, you would find more and more detail.

What if the 2.5 gigapixel image taken by TNO was instead a photo of a massive, tightly-packed crowd? How many recognizable people would we be able to fit in a photo like that?

Just for estimation, let’s say that it is possible to recognize a person with only a 40×60 image (2400 pixels) like the one below:


The 2.5 gigapixel image could fit 1,036,345 people. (2,487,227,305 / 2400)

One megaperson fits in a 2.5 gigapixel image!

I wonder if the entire population of Trinidad and Tobago would pose for a photo?

Use Tag Files For Modularity in JSP

Monday, December 6th, 2004

Have you ever come across JSP code peppered with <%@ include %> directives? Back when we were limited to JSP 1.1, include directives were a decent means to build modularity and re-use code. The biggest problem with the include directive, however, is that included code does not get its own stack. Variables used by the included fragment are the same variables used by the JSP that included it. For a large scale project, the complexity of documenting the inputs of fragments and the manual enforcement of those inputs become a burdon upon productivity. In practice, the include directives are a source of very difficult-to-track errors.

Don’t worry though, because you can use Tag files to address all of those issues. JSP 2.0 Tag files offer huge modularity advantages over include page fragments. Tag files are almost identical to regular JSP files, except they have the <%@ tag %> directive at the top instead of <%@ page %>. You will probably also want to make use of some <%@ attribute %> directives.

Use tag files when you need:

  • re-usable, modular JSP code
  • required attributes
  • attributes type checking
  • a stack (for recursion)

Here is a great step-by-step guide by Andrei Cioroianu that will take you through the process of converting included jsp fragments into .tag files.