Trouble with ODBC CSV connection when export Crystal RPT to PDF

[Marius Coetzee]

Good day all,

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) {}
    }
}

[Marius Coetzee]

I've posted the same question at "SAP Community Network" for if my problem is on the Crystal Report side. (here)

[Matthew Brown]

(I'm not sure which is the best forum for this, but it's certainly too complex for Beginning Java! So moving it here.)

[Marius Coetzee]

Thank you Matthew - I didn't know under which category to put it. The problem made me feel like I'm "Beginning Java"

[Marius Coetzee]

I now tried to retrieve the data and assign it to the table, but get an AssertionError when trying to assign the ResultSet to the report (c'mon! give me a break):

//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;
import com.crystaldecisions.sdk.occa.report.data.ITable;
import com.crystaldecisions.sdk.occa.report.data.Tables;

//Java imports.
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Rpt2Pdf {

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

            // Set data source.
            Tables tables = reportClientDoc.getDatabaseController().getDatabase().getTables();
            for (int i = 0; i < tables.size(); i++) {
                ITable origTable = tables.getTable(i);

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

                // !!!!! Get "java.lang.AssertionErron" here !!!!!
                reportClientDoc.getDatabaseController().setDataSource(rs, tableAlias, tableAlias + "_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) {
        }
    }
}

[Jan Cumps]

Did you set up the ODBC as a System datasource in windows?

[Jan Cumps]

Marius Coetzee wrote:I now tried to retrieve the data and assign it to the table, but get an AssertionError when trying to assign the ResultSet to the report...

What does that AssertionError say?

[Marius Coetzee]

Thank you Jan for replying.

It looks like I got it to work with the (non refactored) code below.

The AssertionError did not give me additional information, which I also thought was funny. But I did figure out it only did it when I was using JUnit to test it (not blaming JUnit, I'm almost 100% sure it was something I did).

//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) {
        }
    }
}