Skip to Content
0

JRC Changing Database HANA JDBC in report

Oct 13, 2016 at 02:33 PM

190

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

11 Answers

Best Answer
Giu Gallo Oct 18, 2016 at 09:57 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Don Williams
Oct 13, 2016 at 02:36 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Giu Gallo Oct 13, 2016 at 03:29 PM
0

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?

Share
10 |10000 characters needed characters left characters exceeded
Don Williams
Oct 13, 2016 at 03:31 PM
0

What SDK are you using and version?

Share
10 |10000 characters needed characters left characters exceeded
Giu Gallo Oct 13, 2016 at 04:29 PM
0
Share
10 |10000 characters needed characters left characters exceeded
Don Williams
Oct 13, 2016 at 04:47 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Don Williams
Oct 13, 2016 at 05:19 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Don Williams
Oct 13, 2016 at 05:26 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Giu Gallo Oct 13, 2016 at 05:18 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Shawn Penner
Oct 13, 2016 at 09:29 PM
0

A couple points:

1. The JNDI error occurs because when CR Java fails to make a connection via JDBC, it tries to fallback on JNDI connections. If that fails, it then throws the JNDI error even though the actual error is with the JDBC connection.

2. According to the Supported Platforms documentation, it is supported to make a JDBC connection to Hana using JDBC.

3. As per http://help.sap.com/saphelp_hanaplatform/helpdata/en/ff/15928cf5594d78b841fbbe649f04b4/frameset.htm, you need to obtain the JDBC driver for Hana (ngdbc.jar) from the Hana client. There is an example of the required JDBC parameters on the page I linked.

4. I couldn't find a sample for batch exporting to PDF, but you can find an example for exporting to PDF here: https://archive.sap.com/documents/docs/DOC-6892

5. To create the connection at runtime, you need to do all the properties in the propertybag. I couldn't find a list of them for the Hana connection, but you can use the sample from here: https://archive.sap.com/documents/docs/DOC-6690 to display the property bags for the report. The sample was designed to compare the properties from two reports, so if you just want to look at one, you can use the same report for both.

Shawn

Share
10 |10000 characters needed characters left characters exceeded