Problem using ODBC CSV when exporting to PDF

[Former Member]

Good day all,

I've posted this question in Java Ranch before ([here|http://www.coderanch.com/t/560533/java/java/Trouble-ODBC-CSV-connection-when]), but was thinking someone might be able to help me here.

I'm trying to write a library that will convert/export a crystal RPT report to PDF, but is having problems accessing the data.

Background

In our case there is no database to access, the system simply produces a CSV where the RPT must extract its data from.

This is done by using "Microsoft Access Text Driver" ODBC pointing to the "Current Directory".

(When exporting a RPT to PDF with no data access data it works fine.)

Environment

Microsoft Windows Server 2008 64bit.

The environment the system run in can consume java libraries, thus our interest in using java (despite my obvious unfamiliarity with java beyond the simple if and for statements).

Problem

Running the below code I get "Error finding JNDI name (CSV_DSN)". I understand that it is failing to find connection to CSV_DSN and thus will try to use JNDI.

I then try to set up a JNDI connection using the CRJavaHelper but then get "com.businessobjects.reports.datamodel.DFException: The database connector cannot be changed when the connection is open."

(I've tried setting up a JDBC ODBC (bridge?) then referencing it with JNDI - hoping it will solve the problem, but after spending 2 days reading up on JNDI I did not manage to accomplish this. Most samples being for hosted environments).

Request / Plea

Please help me in getting the below to work.

An explanation on why it is going wrong, maybe a pointer in the right direction would also be much appreciated.

Thank you in advance,

Marius

//Crystal Java Reporting Component (JRC) imports.
import com.crystaldecisions.sdk.occa.report.application.ReportClientDocument;
import com.crystaldecisions.sdk.occa.report.exportoptions.*;
import com.crystaldecisions.sdk.occa.report.lib.ReportSDKException;

//Java imports.
import java.io.*;

public class Rpt2Pdf {
    public void Convert(String sourceRpt, String destinationPdf) {
        try {
            //Open report.
            ReportClientDocument reportClientDoc = new ReportClientDocument();
            reportClientDoc.open(sourceRpt, 0);

            //Set DataSource
            /* !!!!!! With the following line I get this error:
             * "com.businessobjects.reports.datamodel.DFException: The database connector cannot be changed when the connection is open."
             * !!!!!! */
            //CRJavaHelper.changeDataSource(reportClientDoc, "", "", "jdbc:odbc:CSV_DSN", "sun.jdbc.odbc.JdbcOdbcDriver", "jdbc/CSV_DSN");
            /* !!!!! Without the the above line I get this error:
             * "Error finding JNDI name (CSV_DSN)"
             * !!!!! */
            reportClientDoc.verifyDatabase();

            //Export report and obtain an input stream that can be written to disk.
            ByteArrayInputStream byteArrayInputStream = (ByteArrayInputStream) reportClientDoc.getPrintOutputController().export(ReportExportFormat.PDF);

            //Release report.
            reportClientDoc.close();

            //Use the Java I/O libraries to write the exported content to the file system.
            byte byteArray[] = new byte[byteArrayInputStream.available()];

            //Create a new file that will contain the exported result.
            File file = new File(destinationPdf);
            FileOutputStream fileOutputStream = new FileOutputStream(file);

            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream(byteArrayInputStream.available());
            int x = byteArrayInputStream.read(byteArray, 0, byteArrayInputStream.available());

            byteArrayOutputStream.write(byteArray, 0, x);
            byteArrayOutputStream.writeTo(fileOutputStream);

            //Close streams.
            byteArrayInputStream.close();
            byteArrayOutputStream.close();
            fileOutputStream.close();
        } catch (ReportSDKException ex) {
        } catch (Exception ex) {}
    }
}

[Former Member]

Instead of changing the connection properties to point to a new data source, which didn't work. I loaded the data and then assigned it to the report.

[Former Member]

It looks like what I was trying to do was not possible.

What I got working in the end is to assign a ResultSet to tables.

Here is the code that worked in the end:

//Crystal Java Reporting Component (JRC) imports.
import com.crystaldecisions.sdk.occa.report.application.ReportClientDocument;
import com.crystaldecisions.sdk.occa.report.exportoptions.ReportExportFormat;
import com.crystaldecisions.sdk.occa.report.lib.ReportSDKException;
import com.crystaldecisions.sdk.occa.report.data.Tables;

//Java imports.
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Collections;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Map;

public class Rpt2Pdf {

    public void Convert(String sourceRpt, String destinationPdf) {
        try {

            //Open report.
            ReportClientDocument reportClientDoc = new ReportClientDocument();
            reportClientDoc.open(sourceRpt, 0);

            // Set data source.
            Map<String, ResultSet> results = new HashMap<String, ResultSet>();

            Tables tables = reportClientDoc.getDatabaseController().getDatabase().getTables();
            for (int i = 0; i < tables.size(); i++) {

                String tableAlias = reportClientDoc.getDatabaseController().getDatabase().getTables().getTable(i).getAlias();
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                Connection con = DriverManager.getConnection("jdbc:odbc:CSV_DSN");
                Statement stmt = con.createStatement();
                String query = "select * from " + tables.getTable(i).getName();
                ResultSet rs = stmt.executeQuery(query);

                results.put(tableAlias, rs);
            }

            final Enumeration<String> aliasEnumeration = Collections.enumeration(results.keySet());
            while (aliasEnumeration.hasMoreElements()) {
                String oldAlias = aliasEnumeration.nextElement();
                ResultSet rs = results.get(oldAlias);
                reportClientDoc.getDatabaseController().setDataSource(rs, oldAlias, oldAlias + "_rs");
            }

            //Export report and obtain an input stream that can be written to disk.
            ByteArrayInputStream byteArrayInputStream = (ByteArrayInputStream) reportClientDoc.getPrintOutputController().export(ReportExportFormat.PDF);

            //Release report.
            reportClientDoc.close();

            //Use the Java I/O libraries to write the exported content to the file system.
            byte byteArray[] = new byte[byteArrayInputStream.available()];

            //Create a new file that will contain the exported result.
            File file = new File(destinationPdf);
            FileOutputStream fileOutputStream = new FileOutputStream(file);

            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream(byteArrayInputStream.available());
            int x = byteArrayInputStream.read(byteArray, 0, byteArrayInputStream.available());

            byteArrayOutputStream.write(byteArray, 0, x);
            byteArrayOutputStream.writeTo(fileOutputStream);

            //Close streams.
            byteArrayInputStream.close();
            byteArrayOutputStream.close();
            fileOutputStream.close();

        } catch (ReportSDKException ex) {
        } catch (Exception ex) {
        }
    }
}