Skip to Content
avatar image
Former Member

JRC Changing Database HANA JDBC in report

I'm developing a java application (not web) that loads a .rpt template from filesystem and convert it in pdf.

I'm using JRC in order to execute the report made by CrystalReport.

The report uses a connection on a sap hana db.

As first step I tried to load the .rpt file, providing the database credentials, but I recived this error:

JRCAgent1 detected an exception: Database driver name is not supported: crdb_odbc.dll

Serching on the web I got that I need to use a JDBC connection. I found some example, with connection to oracle and sql server, but not hana connection, so I tried to adapt this examples.

public void testCrystalReportCall(){
try {
			
	ReportClientDocument reportClientDoc = new ReportClientDocument();
	reportClientDoc.open("...myRptFile.rpt", 0);
			
	DatabaseController databaseController = reportClientDoc.getDatabaseController();
	switch_tables(databaseController);
			
	ByteArrayInputStream byteArrayInputStream = (ByteArrayInputStream)reportClientDoc.getPrintOutputController().export(ReportExportFormat.PDF);
	reportClientDoc.close();
			
	IOUtils.copy(byteArrayInputStream, new FileOutputStream("...myRptFile.pdf"));
	} catch (Exception e) {
		e.printStackTrace();
	} 
}
	
private static void switch_tables(DatabaseController databaseController) throws ReportSDKException {
		final String DBUSERNAME = "SYSTEM";
		final String DBPASSWORD = "myPassword";
		final String SERVERNAME = "myServer";
		final String HANA_URL = "jdbc:sap://"+SERVERNAME+":30015/";
		final String DRIVER = "com.sap.db.jdbc.Driver";
		final String DLL = "crdb_jdbc.dll";
		final String DB_NAME = "myDBName";
		final String CONNECTION_STRING = 
				"Use JDBC=b(true);Connection URL=s("+HANA_URL+");" +
				"Database Class Name=s("+DRIVER+");Server=s("+SERVERNAME+");" +
				"User ID=s("+DBUSERNAME+");Password=;Database=s("+DB_NAME+");Trusted_Connection=b(false);" +
				"JDBC Connection String=s(!"+DRIVER+"!"+HANA_URL+"!user={userid}!password={password})"; 
		
		for (int i = 0; i < tables.size(); i++) {
			ITable table = tables.getTable(i);
			table.setName(table.getName());
			table.setAlias(table.getAlias());
			IConnectionInfo connectionInfo = table.getConnectionInfo();
			PropertyBag propertyBag = new PropertyBag();

			propertyBag.put("Server Type","JDBC (JNDI)"); 
			propertyBag.put("Use JDBC","true");
			propertyBag.put("Connection URL",HANA_URL);
			propertyBag.put("Database Class Name",DRIVER); 
			propertyBag.put("Database Name",DB_NAME); 
			propertyBag.put("Database DLL",DLL); 
			propertyBag.put("Connection String",CONNECTION_STRING);
			
			connectionInfo.setAttributes(propertyBag);
			
			connectionInfo.setUserName(DBUSERNAME);
			connectionInfo.setPassword(DBPASSWORD);
			table.setConnectionInfo(connectionInfo);
			databaseController.setTableLocation(table, tables.getTable(i));
		}
	}

I don't no why now I have this error:

Error finding JNDI name (myServer:30015)

It seams strange to me, that JRC is looking for a JNDI resource... why? Is same config info missing in the propertyBag?

At this point I also tried to provided a JNDI resuorce in this way:

propertyBag.put("JNDI Datasource Name","jdbc_test");

.....
//Application init
DataSourceSAP dataSourceSAP = new DataSourceSAP();
            dataSourceSAP.setUrl(HANA_URL);
            dataSourceSAP.setUser("SYSTEM");
            dataSourceSAP.setPassword("myPassword");
            
 internalContext.bind("java:comp/env/jdbc/jdbc_test", dataSourceSAP);

But now I have this error:

java.lang.NullPointerException: while trying to invoke the method java.sql.Connection.getMetaData() of a null object loaded from field com.crystaldecisions.reports.queryengine.driverImpl.e.byte of an object loaded from a local variable at slot 18 at com.crystaldecisions.reports.queryengine.driverImpl.o.if(Unknown Source)

Anyone can help?

Thanks

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

11 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 18, 2016 at 09:57 AM

    Ok, I simplified my report and now it works, so maybe there was also some problem in the report itself. So the final solution to export a rpt to pdf using a jdbc connection to sap Hana db is:

    import com.crystaldecisions.reports.sdk.*;
    import com.crystaldecisions.sdk.occa.report.lib.*;
    import com.crystaldecisions.sdk.occa.report.data.*;
    import com.crystaldecisions.sdk.occa.report.exportoptions.ReportExportFormat;
    
    ...
    
    private void exportReport() throws Exception{
    	//Open report.
    	ReportClientDocument reportClientDoc = new ReportClientDocument();
    	reportClientDoc.open(REPORT_NAME, 0);
    
    	//The DatabaseController will be used to modify the connection properties of the database before 
    	//viewing the report.  This method iterates through each table in the report and changes the 
    	//connection info properties.  
    			
    	//Switch all tables on the main report.  See utility method below.
    	switch_tables(reportClientDoc.getDatabaseController());
    			
    	//Perform the same operation against all tables in the subreport as well.
    	IStrings subreportNames = reportClientDoc.getSubreportController().getSubreportNames();
    			
    	//Set the datasource for all the subreports.
    	for (int i = 0; i < subreportNames.size(); i++ ) {
    		ISubreportClientDocument subreportClientDoc = reportClientDoc.getSubreportController().getSubreport(subreportNames.getString(i));
    				
    		//Switch tables for each subreport in the report using the same connection information.  See utility
    		//method below.
    		switch_tables(subreportClientDoc.getDatabaseController());						
    	}
    	
    	ByteArrayInputStream byteArrayInputStream = (ByteArrayInputStream)reportClientDoc.getPrintOutputController().export(ReportExportFormat.PDF);
    	reportClientDoc.close();
    	
    	IOUtils.copy(byteArrayInputStream, new FileOutputStream(PDF_REPORT_NAME));
    }
    
    
    private void switch_tables(DatabaseController databaseController) throws ReportSDKException {
    	final String DBUSERNAME = "myUser";
    	final String DBPASSWORD = "myPassword";
    	final String TABLE_NAME_QUALIFIER = "myDB.";
    	
    	//Obtain collection of tables from this database controller.
    	Tables tables = databaseController.getDatabase().getTables();
    
    	//Set the datasource for all main report tables.
    	for (int i = 0; i < tables.size(); i++) {
    
    		ITable table = tables.getTable(i);
    
    		//Keep existing name and alias.
    		table.setName(table.getName());
    		table.setAlias(table.getAlias());
    						
    		//Change properties that are different from the original datasource.
    		table.setQualifiedName(TABLE_NAME_QUALIFIER + table.getName());
    		
    		//Change connection information properties.
    		IConnectionInfo connectionInfo = table.getConnectionInfo();
    		
    		PropertyBag innerProp = connectionInfo.getAttributes();
    		innerProp.clear();
    			
    		//Set new table connection property attributes.
    		PropertyBag propertyBag = new PropertyBag();
    		
    		//Overwrite any existing properties with updated values.
    		propertyBag.put("Server Name","jdbc:sap://myServer:30015");
    		propertyBag.put("Database Name","myDB");
    		propertyBag.put("Server",null);
    		propertyBag.put("Database DLL","crdb_jdbc.dll");
    		propertyBag.put("Database","myDB");
    		propertyBag.put("Connection URL","jdbc:sap://myServer:30015");
    		propertyBag.put("Database Class Name","com.sap.db.jdbc.Driver");
    		propertyBag.put("Server Type","JDBC (JNDI)");
    		propertyBag.put("Use JDBC",true);
    		propertyBag.put("PreQEServerName","jdbc:sap://myServer:30015");
    		propertyBag.put("JDBC Connection String","!com.sap.db.jdbc.Driver!jdbc:sap://myServer:30015!user={userid}!password={password}");
    				
    		connectionInfo.setAttributes(propertyBag);
    		
    		//Set database username and password.
    		//NOTE: Even if these the username and password properties don't change when switching databases, the 
    		//database password is *not* saved in the report and must be set at runtime if the database is secured.  
    		connectionInfo.setUserName(DBUSERNAME);
    		connectionInfo.setPassword(DBPASSWORD);
    		connectionInfo.setKind(ConnectionInfoKind.SQL);
    						
    		table.setConnectionInfo(connectionInfo);
    		
    		//Update old table in the report with the new table.
    		databaseController.setTableLocation(table, tables.getTable(i));		
    	}			
    }
    

    Libraries:

    JRC: https://wiki.scn.sap.com/wiki/display/BOBJ/SAP+Crystal+Reports+version+for+Eclipse+-+Downloads

    Driver: C:\Program Files\sap\hdbclient\ngdbc.jar (From the local installation of hana sap client)

    Thanks for the supporto

    Giu

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 13, 2016 at 02:36 PM

    Hi Giu,

    Have you installed the Hana Client? And did you see an option to install a JNDI driver? If so then add the path to the Environment as per usual.

    I don't play with Hana myself but I do know there is an ODBC driver but not sure if there is a Java client.

    Don

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 13, 2016 at 03:29 PM

    For the moment I don't have Hana Client, but from Crystal Report is possible to create a JDBC connection. I did it adding on the CRConfig.xml:

    <JDBCURL>jdbc:sap://myServer:30015</JDBCURL>
    <JDBCClassName>com.sap.db.jdbc.Driver</JDBCClassName>
    <JDBCUserName>SYSTEM</JDBCUserName>
    

    After that I created a test report with a JDBC connection on Crystal Report. Processing the report inside Crystal Report, everything works fine. But the report I have to process in batch, has no JDBC connection.

    In order to know what values I should pass in the propertyBag to manually create di JDBC connection at runtime, I copied the properties of the JDBC connection that I created on Crystal Report. So my really question is, why JRC is looking for a JNDI connection? The necessary info should already be in the property bag. Am I wrong?

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 13, 2016 at 03:31 PM

    What SDK are you using and version?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 13, 2016 at 04:29 PM
    Add comment
    10|10000 characters needed characters exceeded

  • Oct 13, 2016 at 04:47 PM

    Hi Giu,

    Unfortunately that Eclipse package is very old, 2008. You need the latest SP 18 download:

    https://wiki.scn.sap.com/wiki/display/BOBJ/SAP+Crystal+Reports+version+for+Eclipse+-+Downloads

    Don

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 13, 2016 at 05:18 PM

    Thanks for the link.

    I changed the libraries in my project, but the behaviour is exactly the same.

    Is there somewhere an example of exporting a .rpt file to pdf via java batch? An .rpt that needs a connection to hana db?

    Giu

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 13, 2016 at 05:19 PM

    Not that I know of.... Use Fiddler to see if it capture anything you can use to debug the issue.

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 13, 2016 at 05:26 PM

    Hana team said you should have the Hana client installed then it's located here:

    <C:\Program Files\SAP\hdbclient110\ngdbc.jar>or something like that....

    Make sure the path is in your Class Loader also.

    Looking to see if anyone has any examples, or can make one.

    Don

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 13, 2016 at 05:28 PM
    Add comment
    10|10000 characters needed characters exceeded