Hacking the Trac Database

Unfortunately my Trac Server at work has been down for a number of days and I've had to manually log tickets in an Excel spreadsheet. The network team have finally fixed the issue and I've just finished importing the tickets using the excellent csv2trac.py Python program.

Unfortunately I need to adjust some of the dates which can be done easily using sqlite. Essentially log on the the trac database using the following command:

sqlite3 /path/to/trac/project/db

All dates and times in sqlite are stored as the number of seconds since 01-Jan-1970, therefore to subtract 5-days from the creation date you would run:

UPDATE ticket
SET     time = time - (5*24*60*60)
WHERE   id   = n

Configuring Hibernate 3 with c3p0

Thought I'd share a little tip that doesn't (currently) appear in the documentation for configuring c3p0 connection pooling with Hibernate 3.

Apart from setting the properties below (which are documented):

<!-- configuration pool via c3p0-->
<property name="c3p0.acquire_increment">1</property>
<property name="c3p0.idle_test_period">100</property> <!-- seconds -->
<property name="c3p0.max_size">100</property>
<property name="c3p0.max_statements">0</property>
<property name="c3p0.min_size">10</property>
<property name="c3p0.timeout">100</property> <!-- seconds -->

You must also set the following (un-documented) property:

<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>

If you do not set the provider_class property hibernate will continue to us the built-in connection pool!

Custom String Values for an Enum in Java

The default String value that Java returns for an enum is the actual name of the enum constant in use. For example given the following enum:

public enum DayOfWeek {

  MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY;

}

This simple program:

public class DayOfWeekTest {

  public static void main(String[] args) {
    DayOfWeek dow = DayOfWeek.MONDAY;
    System.out.println(dow.toString());
  }
}

Will return:

$ java DayOfWeekTest
MONDAY

But in the program above what I really want is an abbreviated version of the week day in Title Case; For example Mon. Now I could override the toString method of the enum class as follows:

public enum DayOfWeek {

  MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY;

  public String toString() {
    String dayOfWeek = name().toString();
    dayOfWeek = dayOfWeek.charAt(0) + dayOfWeek.substring(1,3).toLowerCase();
    return dayOfWeek; 
  }
}

Which does meet my needs, but it’s not the most obvious or readable piece of code. What Java allows you to do is provide a constructor for the enumeration as follows:

public enum DayOfWeek {

  MONDAY("Mon"), TUESDAY("Tue"), WEDNESDAY("Wed"), THURSDAY("Thu"), FRIDAY("Fri"), SATURDAY("Sat"), SUNDAY("Sun");

  private String dayOfWeek;

  private DayOfWeek(String dayOfWeek) {
    this.dayOfWeek = dayOfWeek;
  }
  
  public String toString() {
    return this.dayOfWeek;
  }
}

Which I think we can all agree is a far simpler piece of code.

Now to make this enum really useful we should provide a method to convert a String back to an enum. If we simply used the supplied valueOf method in the DayOfWeekTest as follows:

public class DayOfWeekTest {

  public static void main(String[] args) {
    DayOfWeek mon = DayOfWeek.MONDAY;
    System.out.println(mon.toString());
    DayOfWeek tue = DayOfWeek.valueOf("Tue");
    System.out.println(tue.toString());
  }
}

A java.lang.IllegalArgumentException: No enum const class DayOfWeek.Tue is thrown as the valueOf method expects the full name of the enum constant; For example TUESDAY. A new method is needed, therefore out final enum becomes:

public enum DayOfWeek {

  MONDAY("Mon"), TUESDAY("Tue"), WEDNESDAY("Wed"), THURSDAY("Thu"), FRIDAY("Fri"), SATURDAY("Sat"), SUNDAY("Sun");
  
  private String dayOfWeek;
  
  private DayOfWeek(String dayOfWeek) {
    this.dayOfWeek = dayOfWeek;
  }
 
  public String toString() {
    return this.dayOfWeek;
  }
   
  public static DayOfWeek getValue(String dayOfWeek) {
    for (DayOfWeek dow : DayOfWeek.values()) {
      // Use equalsIgnoreCase to make the getValue method a little more robust
      if (dow.toString().equalsIgnoreCase(dayOfWeek)) {
        return dow;
      }
    }
    return null;
  }
}

We then change the valueOf method in the DayOfWeekTest to the getValue method as follows:

public class DayOfWeekTest {

  public static void main(String[] args) {
    DayOfWeek mon = DayOfWeek.MONDAY;
    System.out.println(mon.toString());
    DayOfWeek tue = DayOfWeek.getValue("Tue");
    System.out.println(tue.toString());
  }
}

Run the program and Voila!

$ java DayOfWeekTest
Mon
Tue

Mimicing "break" in a PL/SQL FOR Loop

It seems a major omission in the Oracle PL/SQL Programming language that the BREAK keyword cannot be used in FOR loops (or WHILE loops) to skip to the next iteration.

Here is a (trivial) example of how you can implement "break" functionality using exceptions:

DECLARE
  e_skip_row EXCEPTION;
  l_total PLS_INTEGER := 0;
BEGIN
  --
  dbms_output.enable(10000);
  --
  FOR l_index IN 1..100 LOOP
  BEGIN
    --
    -- If the row is even then skip
    IF MOD(l_index,2) = 0 THEN
      RAISE e_skip_row;
    END IF;
    --
    l_total := l_total + l_index;
    --
  EXCEPTION
    WHEN e_skip_row THEN NULL;
  END;
  END LOOP;
  --
  dbms_output.put_line('Total is: '||l_total);
  --
END;

Connecting to MySQL from BI Publisher

Since BI Publisher just uses a JDBC connection you can of course configure it connect to a MySQL database! I'm using v5.1 of the MySQL Connector/J and BI Publisher is running on Apache 5.5. Here's the instructions:

1. Download the latest MySQL JDBC driver, which can be found here
2. Copy the mysql-connector-java-5.1.7-bin.jar JAR file to $CATALINA_HOME/shared/lib directory
3. Restart you application server (Tomcat in this case)
4. Connect to BI Publisher and click on the Admin tab and then click on the JDBC Connection hyperlink
5. Select the Add Data Source button
6. The connection String should be as follows:

jdbc:mysql://<server>:<port>/<database name>

7. The database driver class should be:

com.mysql.jdbc.Driver

8. Enter the other required information such as username, password and then click on Test Connection.

Join two lines in sed

I was trying to load a CSV file into an Oracle database using SQL*Loader today and came across an issue where some of the fields contained CR within the data. Luckily each field is enclosed with double quotes ". Knowing this I was able to run the folling sed command which finds any lines that do not end with a double quote and joins them to the next line.

sed -e :a -e '/[^\"]$/N; s/[^\"]\n//; ta' _
  old_filename > new_filename

Just thought I'd share - someone might find it useful

BI Publisher API jar files

If you want to use BI Publisher Java API then you do not need to install BI Publisher server, you only need to include the following JAR files within you application:

xdocore.jar - the core BIP/XMLP library
xdoparser.jar - this is the scalable XML parser and XSLT 2.0 engine
xmlparserv2.jar (10.1.3.4) or xmlparserv2-904.jar (< 10.1.3.4) - the main XML parser/XSLT engine
aolj.jar - this is an Oracle EBS library, we need it whether you're developing in EBS or not
i18nAPI_v3.jar - this is the i18n library used for localization functions
collections.jar - you only need this if you are working with the delivery APIs or bursting engine.

Optional JARs for Charting Support
======================
bipres.jar - charting library
bicmn.jar - charting library
jewt.jar - charting support library
share.jar - charting support library

These JARs can all be found in the WEB-INF/lib directory or the xmlpserver.war (which is located in manual/generic from the installation zip archive).