I’m stuck with the following issue. I have a requirement where I have to convert the RPT file to PDF by using java. I’ll have to override the database to new one and then get the updated data.

I have tried with 2 different types of RPT files:

RPT file with Data

• I’m able to convert the file into PDF but I can still see the old data, which was created as part of the RPT generation, not the new information which is supposed to pull from the new database connection

RPT file without Data

• I’m getting this exception:
  

  com.crystaldecisions.sdk.occa.report.lib.ReportSDKException: Error finding JNDI name (MySql DB)—- Error code:-2147467259 Error code name:failed at com.crystaldecisions.sdk.occa.report.application.PrintOutputController.if(SourceFile:238)

Here is the full code:

package com.businessobjects.samples;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import com.crystaldecisions.sdk.occa.report.application.DBOptions;
import com.crystaldecisions.sdk.occa.report.application.DatabaseController;
import com.crystaldecisions.sdk.occa.report.application.ReportClientDocument;
import com.crystaldecisions.sdk.occa.report.data.IConnectionInfo;
import com.crystaldecisions.sdk.occa.report.data.ITable;
import com.crystaldecisions.sdk.occa.report.exportoptions.ReportExportFormat;
import com.crystaldecisions.sdk.occa.report.lib.PropertyBag;
import com.crystaldecisions.sdk.occa.report.lib.ReportSDKException;
import com.crystaldecisions.sdk.occa.report.lib.ReportSDKExceptionBase;

public class CrystalReportToPDF {

    public static void main(String[] args) throws Exception {
        // Specify the report file path
    String reportFilePath = "C:\Users\aammasa\eclipse-workspace-crystal-report"
    + "\custom-reports\Sample Reports\MYSQL_SAMPLE_RPT1.rpt";
String exportFilePath = "MYSQL_SAMPLE_RPT1.pdf";

        // Database connection details
        String dbUrl = "jdbc:mysql://localhost:3306/qa_tenant_database?useSSL=false";
        String dbUsername = "root";
        String dbPassword = "";

        ReportClientDocument reportClientDoc = null;
        try {
            // Load the Crystal Report
            reportClientDoc = new ReportClientDocument();
            reportClientDoc.open(reportFilePath, 0);
            DatabaseController dbController = reportClientDoc.getDatabaseController();
            IConnectionInfo oldCOnnectionInfo = dbController.getConnectionInfos(null).getConnectionInfo(0);            
            System.out.println("OLD :"+oldCOnnectionInfo.getAttributes());
            clearData(reportClientDoc);
            IConnectionInfo newConnectionInfo = setDatabaseCredentialsForAllTables(reportClientDoc, dbUrl, dbUsername, dbPassword);
            System.out.println("NEW :"+newConnectionInfo.getAttributes());
            int options = DBOptions._ignoreCurrentTableQualifiers | DBOptions._doNotVerifyDB;
            dbController.replaceConnection(oldCOnnectionInfo, newConnectionInfo,null, options);
            reportClientDoc.refreshReportDocument();
            exportPDF(exportFilePath, reportClientDoc);

        } catch (Exception e) {
        System.out.println("Error Message :"+e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                if (reportClientDoc != null) {
                    reportClientDoc.close();
                }
            } catch (ReportSDKExceptionBase e) {
                e.printStackTrace();
            }
        }
    }

private static void exportPDF(String exportFilePath, ReportClientDocument reportClientDoc)
throws ReportSDKException, FileNotFoundException, IOException {
//Export to PDF
ByteArrayInputStream byteArrayInputStream = (ByteArrayInputStream) reportClientDoc
.getPrintOutputController().export(ReportExportFormat.PDF);
reportClientDoc.close();
byte byteArray[] = new byte[byteArrayInputStream.available()];
File file = new File(exportFilePath);
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);

byteArrayInputStream.close();
byteArrayOutputStream.close();
fileOutputStream.close();

System.out.println("Report exported to PDF successfully!");
}

    private static IConnectionInfo setDatabaseCredentialsForAllTables(ReportClientDocument reportClientDoc, String dbUrl, String username, String password) throws ReportSDKExceptionBase {
        // Loop through all tables in the main report
    IConnectionInfo connectionInfo = null;
    System.out.println("Total Table Size :"+reportClientDoc.getDatabaseController().getDatabase().getTables().size());
        for (int i = 0; i < reportClientDoc.getDatabaseController().getDatabase().getTables().size(); i++) {
            ITable table = reportClientDoc.getDatabaseController().getDatabase().getTables().getTable(i);
            connectionInfo = setConnectionInfo(table, dbUrl, username, password);
        }
        
        for (String subreportName : reportClientDoc.getSubreportController().getSubreportNames()) {
            ReportClientDocument subreportDoc = (ReportClientDocument) reportClientDoc.getSubreportController().getSubreport(subreportName);

            // Apply the same connection settings to subreport tables
            for (int i = 0; i < subreportDoc.getDatabaseController().getDatabase().getTables().size(); i++) {
                ITable table = subreportDoc.getDatabaseController().getDatabase().getTables().getTable(i);
                setConnectionInfo(table, dbUrl, username, password);
            }
        }
        return connectionInfo;
    }
    
    private static IConnectionInfo setConnectionInfo(ITable table, String dbUrl, String username, String password) throws ReportSDKExceptionBase {
        IConnectionInfo connectionInfo = table.getConnectionInfo();
        PropertyBag connectionAttributes = connectionInfo.getAttributes();
        connectionAttributes.clear();

        // Set the database server information
        //PropertyBag connectionAttributes = new PropertyBag();
        connectionAttributes.put("Server", dbUrl);
        connectionAttributes.put("Database", "qa_tenant_database");
        connectionAttributes.put("User ID", username);
        connectionAttributes.put("Password", password);
        connectionAttributes.put("Use JDBC", "true");
        connectionAttributes.put("JNDI", "false");
        connectionAttributes.put("UseSSL", "false");
        connectionInfo.setAttributes(connectionAttributes);

        // Overwrite existing properties to ensure JDBC is used
        connectionInfo.setUserName(username);
        connectionInfo.setPassword(password);

        // Apply the connection info to the table
        table.setConnectionInfo(connectionInfo);
        return connectionInfo;
    }
    
    static void clearData(ReportClientDocument reportClientDoc) throws ReportSDKExceptionBase, IOException {
        // Disconnect all tables from their existing data sources
        DatabaseController dbController = reportClientDoc.getDatabaseController();
        for (int i = 0; i < dbController.getDatabase().getTables().size(); i++) {
            ITable table = dbController.getDatabase().getTables().getTable(i);
            table.setConnectionInfo(null);             
        }

        // Disconnect all subreport tables
        for (String subreportName : reportClientDoc.getSubreportController().getSubreportNames()) {
            ReportClientDocument subreportDoc = (ReportClientDocument) reportClientDoc.getSubreportController().getSubreport(subreportName);
            for (int i = 0; i < subreportDoc.getDatabaseController().getDatabase().getTables().size(); i++) {
                ITable table = subreportDoc.getDatabaseController().getDatabase().getTables().getTable(i);
                table.setConnectionInfo(null); // Disconnect the subreport table from its data source
            }
        }
    }
}