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