Accessing ODBC Databases

[Former Member]

<p>Is it possible to report against an ODBC datasource (e.g., Microsoft Access) using CR4Eclipse?</p><p>We have a Java application (i.e., thick client) that attaches to different databases (Access or PostgreSQL) depending on the user&#39;s environment. In the past we have used the Crystal .NET runtime and an ODBC (RDO) database type connection. The application connected to the database via JDBC (the ODBC bridge in the case of Access) and Crystal used an ODBC Data Source which was dynamically defined by the application.</p><p>The CR4Eclipse report designer doesn&#39;t seem to work with the JDBC-ODBC bridge and Microsoft Access as it shows no schemas in the Database Explorer view.</p><p>Using the ReportViewer JAR as documented in the white paper on thick client deployment with the original ".rpt" file as used in the .NET deployment throws an error about being unable to find a JNDI data source with the name of the ODBC data source.</p><p>Ideally we want to run the reports in a pure Java environment. However, some of our user&#39;s will be using Crystal Reports Developer (version 10 at present) and we don&#39;t really want to try and move them to Eclipse. Yet we want to use those report definitions without alteration if possible.</p><p>Our current environment works pretty well but ongoing integration between Java and .NET runtime for deployment is not ideal.</p><p>Main question is "How do we report against a Microsoft Access database" using CR4Eclipse ???</p>

[SJohnson]

<p>Currently the Database Explorer which we use in CR4E for database connectivity does not appear to support the JDBC-ODBC bridge. Unfortunately, the Database Explorer is a core Eclipse view so we have little control over the features that it supports (note: we are encountering similar issues with Stored Procedures not appearing as well). That being said Crystal Reports for Eclipse does support using the JDBC-ODBC bridge if you want to convert existing ODBC reports to use the JDBC bridge at runtime.</p><p>You mention that you had existing ODBC reports so you are actually in a better position to try this than creating new reports from scratch. I understand that you are building a thick-client application, but the easiest way to test this is to take advantage of the tooling available in our Web Project. This will automatically generate the required code, which you can re-use in your thick-client application after we confirm that it works. To test your existing ODBC reports follow these steps:</p><ol><li>Create a new Crystal Reports Web Project</li><li>Import your existing ODBC report into the project</li><li>Right-click on the report and select <strong>Crystal Reports -> Create Viewer JSP</strong></li><li>Choose the option to <strong>"Change the data source at runtime"</strong>. You can add the option to include the viewer if you want to test it. This will create a JSP page. Open the JSP page in the editor</li><li>In the JSP page uncomment the code to change the data source. Change the value for the <strong>connectString</strong> variable to <strong>"jdbc:odbc:<YOUR DSN N</strong><strong>AME HERE>"</strong> <br /></li><li>Change the value for the <strong>driverName</strong> variable to<strong> "sun.jdbc.odbc.JdbcOdbcDriver"</strong></li></ol><p>That should do it. You can optionally add a SaveAs comment to the end of the code if you want to save the report with the new data source connection information. To do this add the following line to your code:</p><p><strong>clientDoc.saveAs("newReportName.rpt","C:
",1); </strong></p><p>I should point out that the code generated by the wizard uses a sample class to wrap the API calls. If you want to use this code in your thick-client application you will need to copy the class files from your web application to your thick-client application. The class in question is called <strong>JRCHelperSample </strong>and can be found under the <strong>src</strong> folder in the <strong>com.businessobjects.samples </strong>package.</p><p>Finally, it is probably worth mentioning that both the original ODBC reports and the new report using the JDBC-ODBC bridge can be edited in the CR4E designer. The only difference is that the original ODBC reports cannot refresh the data in Preview, whereas the JDBC-ODBC reports can.</p><p>Try this and let me know if it works for you. </p><p>Regards,<br />Sean Johnson (CR4E Product Manager) </p>

[Former Member]

Thanks very much for the quick and comprehensive response Sean.

I've followed your suggestions and everything seems to be working fine so far. My biggest concern was the difficulty in switching datasource at run time for complex reports with multiple sub reports. However, the combination of generated code and the sample helpers seems to have done the job so far.

Had been waiting for BIRT to mature and then make the jump from Crystal to BIRT to achieve better Java integration (always seemed far to difficult in Crystal previously). Now with CR4E, the decision is no longer clear cut. Both products have some significant advantages over the other for our environment but no clear winner.

I've just swung back to preferring CR4E though will still need to use Crystal 10 Developer until the JDBC-ODBC issue can be fixed for design. Incidently this seems to work fine in BIRT.

We'll be exploring greater integration with the Java application over the next few days which will probably be the decider.

Once again, many thanks for you prompt assistance.

[SJohnson]

<p>Hi Peter,</p><p> I&#39;m glad to hear my suggestions appear to be working for you. The unfortunate part with why BIRT is able to do this and we are not is primarily due to the fact that we are re-using the components Eclipse itself recommends for creating Database Connectivity. BIRT created their own database view and specifically added ODBC support for their sample database. Even the most recent version of DTP seems to have these issues. However, if you are comfortable with a BIRT-like design-time experience we can get something similar using the JDBC-ODBC bridge. Follow the instructions outlined in the Custom SQL tutorial video:</p><p><u><strong><a href="/node/452">http://diamond.businessobjects.com/node/452</a> </strong></u> </p><p>You should be able to see the results when you run the SQL Query (although context assist may not work). After you have successfully created your query, right-click and choose either to add it to a new or existing Crystal Report.</p><p>This appeared to work on my end.</p><p>So, I guess I will qualify my previous comment about the Database Explorer not supporting the JDBC-ODBC bridge. It currently does not allow you to navigate the databases in these types of connections, thus allowing Crystal Reports to automatically generate the SQL query for you. However, if you are comfortable writing your own queries then this solutions should work.</p><p>Regards,<br /> Sean Johnson (CR4E Product Manager) </p>

[SJohnson]

<p>UPDATE</p><p>I just realized that the test I ran above was using a Commercial JDBC-ODBC driver (I had downloaded a number of them to see if they would work in the Database Explorer). When I tried the steps above with the Sun JDBC-ODBC bridge everything still worked from the Crystal Reports side, however I received an error when running the SQL Query in the SQL Page editor. My error was specific to MS Access, so this issue may be limited to ODBC Data Sources connecting to Access on the back-end. Still, keep this in mind if you get an error when running the SQL query in the SQL Scrapbook Page. If the query is formed correctly it will likely work in the Crystal Reports designer.</p><p>&nbsp;</p><p>Regards,<br /> Sean Johnson (CR4E Product Manager) </p>

[Former Member]

<p>Thanks once again for your suggestions Sean.</p><p>I&#39;m not so concerned with the design time as we can continue to either work with Crystal Developer for non Java programmers or perhaps design against PostgreSQL with CR4E and rely on changing data source at run time for production.</p><p>It&#39;s the ability to deploy our existing Crystal reports in a pure Java environment that is most critical and we appear to be well on the road to succeeeding based on implementation of your previous suggestions.</p><p>I&#39;ll give your latest suggestions a try and let you know.</p>

[Former Member]

JRCHelperSample is not in the distribution of CR4E v1.0.0.v671 (eclipse\plugins\com.businessobjects.crystalreports.samples_1.0.0.v671). Where can I download it separately?

[SJohnson]

<p>The source code for this sample should be in the following plugin:</p><p><strong>com.businessobjects.sdks.jrc.11.8.0_11.8.1.v671 </strong></p><p>Under the sample\com\businessobjects\samples folder </p><p>Regards,<br />Sean Johnson (CR4E Product Manager) <br /><strong><br /><a href="http://www.eclipseplugincentral.com/Web_Links-index-req-ratelink-lid-639.html">Rate this plugin @ Eclipse Plugin Central</a></strong> </p>

[Former Member]

<p>Hi. I tried this methodology and it works perfectly attaching to a FireBird database using Tomcat from within Eclipse. I then exported to a WAR file which I placed under a standalone installation of Tomcat. I get the following error message:</p><p>com.crystaldecisions.sdk.occa.report.lib.ReportSDKLogonException: Logon Error: unavailable database---- Error code:-2147217393 Error code name:dbLogonFailed <br /></p><p>So, this is the same jsp file/db authentication using jdbc-odbc bridge in both environments. One works, one doesn&#39;t. <br /></p><p>Any help would be greatly appreciated.</p><p>Thanks,</p><p>Dave </p>

[SJohnson]

<p>Hi Dave,</p><p> Based on the error message it appears that the JRC engine cannot locate the database. I think we can narrow down the issue if we answer the following questions:</p><ol><li>Is this running on the same machine?<br /><br /></li><li>What type of DSN are you using (User or System)? I would recommend a System DSN so that it can be seen all users. I have seen this returning similar issues to the one you are experiencing if the Tomcat Server is started under a different user.<br /><br /></li><li>What happens if you try and read through a java.sql.ResultSet using the same JDBC connection. This will determine if the issue revolves around the Crystal components or the Tomcat environment.</li></ol><p>Anyway, let me know the answers to these questions and we should be able to narrow down the issue.</p><p>Regards,<br />Sean Johnson (CR4E Product Manager) <br /><br /> <a href="http://www.eclipseplugincentral.com/Web_Links-index-req-ratelink-lid-639.html">Rate this plugin @ Eclipse Plugin Central</a> </p>

[Former Member]

<p>Hi,</p><p>I have a problem with jdbc connectivity to database. We prefer MSSQL, but...</p><p>If I create a new report in Crystal report IXr2 application, I am able to connect to the database. I can see my tables and columns. When I want to show preview, the crystal report says "The database table "MY_TABLE" cannot be found. Procced to remove this table from the report?".</p><p> If I use firebird database, the situation is worse. There is a second error message says "Failed to retreive data from database" and third says "Database connection error: <font color="#ff0000">&#39;An internal error occurred: Please contact Business Object technical support</font>".</p><p>But the original task was to generate PDF from a java project. </p><p>I made a report using JDBC connection. In a java I read properties from document.databaseController.getDatabase().getTables().getTable(index).getConnectionInfo().getAttributes()<br />I changed properties in a real report to this value. (original connection ODBC). It should be ok, doesn&#39;t it? This properties made a crystal report... But I got an exception <font color="#000000">"</font><font color="#000000">com.crystaldecisions.reports.exportinterface.exceptions.ExportException: Some parameters are missing values"</font> </p><p>So I decided to fill data in from a result set, but I got the same exception. This exception doesn&#39;t say what is wrong. If I fill the data from a resultset, the database connection shouldn&#39;t be necessary, so why this exception? </p><p>Thanks fro your answer,<br />Martin</p>

[Former Member]

<p>Sean,</p><p>Thanks for the quick response. </p><p>1. Yes, all running on my laptop.</p><p>2. System DSN; logging onto Tomcat as "Local System account".</p><p>3. I created my own jdbc connection in the jsp. Again, works great under Eclipse. WAR file failed again during getConnection with same message "unavailable database". Driver loaded fine.<br /></p><p>So, I assume that points to Tomcat environment. Any thoughts? </p><p>&nbsp;</p><p>Thanks,<br />Dave <br /></p><p>&nbsp;</p>

[SJohnson]

<p>Hi Martin,</p><p>Does your report contain parameters? From the error message it sounds as though some parameters are not being populated.</p><p>Have you tried running the report in Crystal Reports for Eclipse? The reason I ask is that the CR4E tool includes a tool that allows you to do a SetTableLocation in the designer. The CR4E designer uses the JRC which is the same tool that you are using at runtime so it may be an easier test to determine if there are any other issues. If you have not yet tried this then it may be worthwhile giving it a shot to see if it relieves any of the issues you are currently encountering. </p><p>Regards,<br />Sean Johnson (CR4E Product Manager) <br /><br /> <a href="http://www.eclipseplugincentral.com/Web_Links-index-req-ratelink-lid-639.html">Rate this plugin @ Eclipse Plugin Central</a> </p>

[SJohnson]

<p>Hi Dave,</p><p>Unfortunately, I do not have a tremendous amount of knowledge with Firebird, in fact it would be more accurate to describe it as very limiting. On top of this, my success with accessing ODBC data sources using a JDBC-ODBC bridge has been sporadic. </p><p>Anyway, as it looks like the issue may be on the Tomcat side of things I did find the following page on the Firebird site which does have steps that you can follow to configure your Tomcat to work correctly with Firebird.</p><p><a href="http://firebird.sourceforge.net/index.php?op=devel&sub=jdbc&id=faq#8"><strong>http://firebird.sourceforge.net/index.php?op=devel&sub=jdbc&id=faq#8</strong></a></p><p>Once you get the stand-alone ResultSet working in your JSP page, I am quite confident that the Crystal Reports pieces will work as well. Good luck! </p><p>Regards,<br />Sean Johnson (CR4E Product Manager) <br /><strong><br /><a href="http://www.eclipseplugincentral.com/Web_Links-index-req-ratelink-lid-639.html">Rate this plugin @ Eclipse Plugin Central</a></strong> </p>