ODBC Report with CR4E, how to create jdbc connection?

[Former Member]

Hi,

I am using CR4E to integrate Crystal reports in a thick client application.

My reports use ODBC connection, and I have the following error :

Error finding JNDI name (DB_NAME).

I have enabled log4j to see what's happening and I have the following trace:

ERROR com.businessobjects.reports.sdk.JRCCommunicationAdapter - detected an exception: Erreur lors de la recherche du nom JNDI (DB_NAME)

I have the following connection infos:

INFO com.crystaldecisions.reports.queryengine - Connection logon:Database DLL=s(crdb_odbc.dll);DSN=s(DB_NAME);UseDSNProperties=b(false);PreQEServerName=s(DB_NAME);database=s();User ID=s(xxx);Password=

I have read in the forum that I need to have a JNDI connection like jdbc/DB_NAME or jdbc:DB_NAME.

Wich one is really used?

So my original question was how do I create a JDNI connection?

Can I do it programmaticaly in java or should I use a config file?

Thanks.

[Former Member · Accepted solution]

So, I have good and bad news:

the good one:

with the following jndi datasource, the report is executed and I have seen the request to the database in the CR4E log.

<local-tx-datasource>
	 <jndi-name>BLIPS_PROD_REF</jndi-name>
	 <use-java-context>false</use-java-context>
	 
         <!--connection-url>jdbc:jtds:sqlserver://ox111:1433/DB_NAME</connection-url-->
	 <connection-url>jdbc:sqlserver://localhost:1433;databaseName=DB_NAME;</connection-url>
	 
	 <!--driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class-->
	 <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>

         <user-name>lms</user-name>
	 <password>lms</password>

	 <metadata>
	       <type-mapping>MSSQLServer 2000</type-mapping>
	 </metadata>
</local-tx-datasource>

it works with both jtds and microsoft drivers.

Now the bad news:

I have the following error:

ERROR [JRCCommunicationAdapter]  detected an exception: com.crystaldecisions.Utilities.Environment.isMacOSX()Z
	at com.crystaldecisions.Utilities.FontFactory.<init>(Unknown Source)
	at com.businessobjects.crystalreports.paragrapher.PFont.<init>(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.TextFormatter.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.TextFormatter.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.TextFormatter.if(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.TextFormatter.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.AbstractFormattedTextualObject.dK(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.AbstractFormattedTextualObject.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.z.for(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.ae.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.ObjectFormatter.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.ObjectFormatter.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.x.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.x.h(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.f.h(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.x.for(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.ae.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.ObjectFormatter.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.ObjectFormatter.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.y.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.y.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.am.for(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.am.for(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.ColumnFormatter.for(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.ae.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.am.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.am.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.am.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.al.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.ReportColumnFormatter.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.paginator.SinglePageFormatter.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.am.for(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.am.for(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.ColumnFormatter.for(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.paginator.SinglePageFormatter.for(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.ae.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.paginator.PageFormatter.do(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.paginator.PageFormatter.formatPage(Unknown Source)
	at com.crystaldecisions.reports.formatter.export2.a.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.export2.ExportSupervisorEx.if(Unknown Source)
	at com.crystaldecisions.reports.formatter.export2.ExportSupervisorEx.a(Unknown Source)
	at com.businessobjects.reports.sdk.requesthandler.ReportViewingRequestHandler.a(Unknown Source)
	at com.businessobjects.reports.sdk.requesthandler.ReportViewingRequestHandler.int(Unknown Source)
	at com.businessobjects.reports.sdk.JRCCommunicationAdapter.do(Unknown Source)
	at com.businessobjects.reports.sdk.JRCCommunicationAdapter.if(Unknown Source)
	at com.businessobjects.reports.sdk.JRCCommunicationAdapter.a(Unknown Source)
	at com.businessobjects.reports.sdk.JRCCommunicationAdapter$2.a(Unknown Source)
	at com.businessobjects.reports.sdk.JRCCommunicationAdapter$2.call(Unknown Source)
	at com.crystaldecisions.reports.common.ThreadGuard.syncExecute(Unknown Source)
	at com.businessobjects.reports.sdk.JRCCommunicationAdapter.for(Unknown Source)
	at com.businessobjects.reports.sdk.JRCCommunicationAdapter.int(Unknown Source)
	at com.businessobjects.reports.sdk.JRCCommunicationAdapter.request(Unknown Source)
	at com.businessobjects.sdk.erom.jrc.a.a(Unknown Source)
	at com.businessobjects.sdk.erom.jrc.a.execute(Unknown Source)
	at com.crystaldecisions.proxy.remoteagent.RemoteAgent$a.execute(Unknown Source)
	at com.crystaldecisions.proxy.remoteagent.CommunicationChannel.a(Unknown Source)
	at com.crystaldecisions.proxy.remoteagent.RemoteAgent.a(Unknown Source)
	at com.crystaldecisions.sdk.occa.report.application.ds.a(Unknown Source)
	at com.crystaldecisions.sdk.occa.report.application.ReportSource.a(Unknown Source)
	at com.crystaldecisions.sdk.occa.report.application.ReportSource.a(Unknown Source)
	at com.crystaldecisions.sdk.occa.report.application.PrintOutputController.if(Unknown Source)
	at com.crystaldecisions.sdk.occa.report.application.PrintOutputController.export(Unknown Source)
	at com.crystaldecisions.sdk.occa.report.application.PrintOutputController.export(Unknown Source)

I am under windows XP SP3

SQL server 2005 express edition

P.S. I use CR4E 2.0.0.r454

Thanks for helping, my reporting component has made a big step.

[ted_ueda · Accepted solution]

You wouldn't happen to have any older jar files kicking around, like MetafileRenderer.jar?

What happens if you try manually loading the class com.crystaldecisions.Utilities.Environment, invoking the static method isMacOSX(), and introspect to find which jar it's contained in?

Sincerely,

Ted Ueda

[Former Member]

You get it right \o/

Thank you very much

[Former Member]

Yes,

here is the classic declaration I use:

<local-tx-datasource>
	    <!-- le nom de l'objet qui sera rcupr par les applications -->
	    <jndi-name>DB_NAMEDS</jndi-name>
	    <!-- url d'accs  la base de donnes -->
	    <connection-url>jdbc:jtds:sqlserver://ox111:1433/DB_NAME</connection-url>
	    <!-- classe du driver utilis. Cette librairie doit tre situe dans le rpertoire lib du serveur d'application -->
	    <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
	     <!-- nom d'utilisateur et mot de passe d'accs -->
	    <user-name>usr</user-name>
	    <password>pass</password>

	    <metadata>
	        <type-mapping>MSSQLServer 2000</type-mapping>
	    </metadata>

</local-tx-datasource>

and there the declaration I have just added :

<local-tx-datasource>
	   
	    <jndi-name>jdbc/DB_NAME</jndi-name>

	    <use-java-context>false</use-java-context>

	    <connection-url>jdbc:jtds:sqlserver://ox111:1433/DB_NAME</connection-url>
	    
	    <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
	     
	    <user-name>usr</user-name>
	    <password>pass</password>

	    <metadata>
	        <type-mapping>MSSQLServer 2000</type-mapping>
	    </metadata>

</local-tx-datasource>

I have also tried <jndi-name>comp/env/jdbc/DB_NAME</jndi-name>

But I still have the same error.

[ted_ueda]

I'd first try connecting and retrieving data from the JNDI outside of Crystal, to ensure you're getting the data.

Also - are you using CR4E v1? Unfortunately, the resource access error is obscuring the true error, so should try and fix that as well.

Sincerely,

Ted Ueda

[Former Member]

hi,

Thanks for your answer and sorry for my late reply.

I have created a jar file and add it in the lib directory of my jboss app-server.

I have now the following error :

10:42:47,443 INFO [STDOUT] java.util.MissingResourceException: Can't find resource for bundle java.util.PropertyResourceBundle, key Error_InvalidServerName

10:42:47,443 INFO [STDOUT] at java.util.ResourceBundle.getObject(ResourceBundle.java:325)

10:42:47,443 INFO [STDOUT] at java.util.ResourceBundle.getObject(ResourceBundle.java:322)

10:42:47,443 INFO [STDOUT] at java.util.ResourceBundle.getString(ResourceBundle.java:285)

10:42:47,443 INFO [STDOUT] at com.crystaldecisions.client.helper.ResourceManager.getString(Unknown Source)

10:42:47,443 INFO [STDOUT] at com.crystaldecisions.client.helper.SDKResourceManager.getString(Unknown Source)

10:42:47,443 INFO [STDOUT] at com.crystaldecisions.proxy.remoteagent.v.connect(Unknown Source)

10:42:47,475 INFO [STDOUT] at com.crystaldecisions.proxy.remoteagent.ac.a(Unknown Source)

10:42:47,475 INFO [STDOUT] at com.crystaldecisions.sdk.occa.report.application.ReportAppSession.int(Unknown Source)

10:42:47,475 INFO [STDOUT] at com.crystaldecisions.sdk.occa.report.application.ReportAppSession.initialize(Unknown Source)

10:42:47,475 INFO [STDOUT] at com.crystaldecisions.sdk.occa.report.application.ClientDocument.for(Unknown Source)

10:42:47,475 INFO [STDOUT] at com.crystaldecisions.sdk.occa.report.application.ReportClientDocument.for(Unknown Source)

10:42:47,475 INFO [STDOUT] at com.crystaldecisions.sdk.occa.report.application.ClientDocument.open(Unknown Source)

10:42:47,475 INFO [STDOUT] at com.oxial.test.CR.CRLoginNoParam.CR4E(CRLoginNoParam.java:63)

10:42:47,475 INFO [STDOUT] at com.lms.lips.server.navigation.session.CommandDispatcherSl.login(CommandDispatcherSl.java:296)

10:42:47,475 INFO [STDOUT] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

10:42:47,475 INFO [STDOUT] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

10:42:47,475 INFO [STDOUT] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

10:42:47,475 INFO [STDOUT] at java.lang.reflect.Method.invoke(Method.java:585)

10:42:47,475 INFO [STDOUT] at org.jboss.invocation.Invocation.performCall(Invocation.java:345)

10:42:47,475 INFO [STDOUT] at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:214)

10:42:47,475 INFO [STDOUT] at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:149)

10:42:47,475 INFO [STDOUT] at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:154)

10:42:47,475 INFO [STDOUT] at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:48)

10:42:47,475 INFO [STDOUT] at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:106)

10:42:47,475 INFO [STDOUT] at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:300)

10:42:47,475 INFO [STDOUT] at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:166)

10:42:47,475 INFO [STDOUT] at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:153)

10:42:47,475 INFO [STDOUT] at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:192)

10:42:47,475 INFO [STDOUT] at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)

10:42:47,475 INFO [STDOUT] at org.jboss.ejb.SessionContainer.internalInvoke(SessionContainer.java:624)

10:42:47,475 INFO [STDOUT] at org.jboss.ejb.Container.invoke(Container.java:873)

10:42:47,475 INFO [STDOUT] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

10:42:47,475 INFO [STDOUT] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

10:42:47,475 INFO [STDOUT] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

10:42:47,475 INFO [STDOUT] at java.lang.reflect.Method.invoke(Method.java:585)

10:42:47,475 INFO [STDOUT] at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:141)

10:42:47,475 INFO [STDOUT] at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)

10:42:47,475 INFO [STDOUT] at org.jboss.mx.server.Invocation.invoke(Invocation.java:72)

10:42:47,475 INFO [STDOUT] at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:245)

10:42:47,475 INFO [STDOUT] at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)

10:42:47,475 INFO [STDOUT] at org.jboss.invocation.jrmp.server.JRMPInvoker$MBeanServerAction.invoke(JRMPInvoker.java:805)

10:42:47,475 INFO [STDOUT] at org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:406)

10:42:47,475 INFO [STDOUT] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

10:42:47,475 INFO [STDOUT] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

10:42:47,490 INFO [STDOUT] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

10:42:47,490 INFO [STDOUT] at java.lang.reflect.Method.invoke(Method.java:585)

10:42:47,490 INFO [STDOUT] at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:294)

10:42:47,490 INFO [STDOUT] at sun.rmi.transport.Transport$1.run(Transport.java:153)

10:42:47,490 INFO [STDOUT] at java.security.AccessController.doPrivileged(Native Method)

10:42:47,490 INFO [STDOUT] at sun.rmi.transport.Transport.serviceCall(Transport.java:149)

10:42:47,490 INFO [STDOUT] at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:460)

10:42:47,490 INFO [STDOUT] at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:701)

10:42:47,490 INFO [STDOUT] at java.lang.Thread.run(Thread.java:595)

After this error, I have adapted the code from changeDataSource(...) in CRJavaHelper.java sample file to use the jndi datasource from Jboss (java:DB_NAMEDS) like this:

....
propertyBag = new PropertyBag ();
propertyBag.put ("Trusted_Connection", "false");
propertyBag.put ("Server Type", "JDBC (JNDI)");
propertyBag.put ("Use JDBC", "true");
propertyBag.put ("Database DLL", "crdb_jdbc.dll");
propertyBag.put ("JNDIOptionalName", "java:DB_NAMEDS");
propertyBag.put ("Server Name", "localhost"); 
propertyBag.put ("Database Name", "DB_NAME");

reportClientDocument.getDatabaseController().getConnectionInfos(null).getConnectionInfo(0).setAttributes(propertyBag);
reportClientDocument.getDatabase().getConnections().getConnection(0).getConnectionInfo().setAttributes(propertyBag);
...
connectionInfo = newTable.getConnectionInfo ();
connectionInfo.setAttributes (propertyBag);

but I still have the same error.

what's wrong with my propertyBag ?

Thanks again.

Edited by: Oxial on Mar 5, 2009 11:09 AM

[ted_ueda]

Did you configure a JNDI connection in your application server?

Sincerely,

Ted Ueda

[ted_ueda]

JNDI is created by your container - i.e., if you're running the report within a Java Web Application Server, then you'd be configuring it in the app server.

How you'd do so is different depending on the Java Web App Server brand.

Sincerely,

Ted Ueda