RPT Convertion into PDF file by using Java

[arunamsi]

Hey Everyone , 
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 

1. RPT file with Data
   1. when I try this 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 suppose to pull from the new database connection
2. RPT file without Data
   1. when I try this 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 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
            }
        }
    }

}

Thanks in Advance 🙂 

Happy Coding