Crystal reports with PostgreSQL

[Former Member]

Hello!

I have a PostgreSQL database (8.3) and want to attach it to Crystal reports for eclipse.

I'm using the following code for this purpose:

   public void attachCrystalReportsToDatabase(final DatabaseController aDatabaseController)
   {
      final String DBUSERNAME = "postgres";
      final String DBPASSWORD = "postgres";
      final String CONNECTION_STRING = "Use JDBC=b(true);"
            + "Connection URL=s(jdbc:postgresql://localhost/MyDatabaseName);"
            + "Database Class Name=s(org.postgresql.Driver);Server=s(localhost);"
            + "User ID=s(postgres);Password=;Database=s(MyDatabaseName);Trusted_Connection=b(false);"
            + "JDBC Connection String=s(!org.postgresql.Driver!jdbc:postgresql://localhost/MyDatabaseName;"
            + "DatabaseName={database};user={userid};password={password}!)";
      final String DATABASE_NAME = "MyDatabaseName";
      final String URI = "!org.postgresql.Driver!"
            + "jdbc:postgresql://localhost/MyDatabaseName;DatabaseName={database};"
            + "user={userid};password={password}!";
      final String DATABASE_DLL = "crdb_jdbc.dll";

      final Tables tables = aDatabaseController.getDatabase().getTables();

      for (int counter = 0; counter < tables.size(); counter++)
      {

         final ITable table = tables.getTable(counter);

         table.setName(table.getName());
         table.setAlias(table.getAlias());

         table.setQualifiedName(table.getName());

         final IConnectionInfo connectionInfo = table.getConnectionInfo();

         final PropertyBag propertyBag = new PropertyBag();

         propertyBag.put("Trusted_Connection", "false");
         propertyBag.put("Connection String", CONNECTION_STRING);
         propertyBag.put("Database Name", DATABASE_NAME);
         propertyBag.put("Server Type", "JDBC (JNDI)");
         propertyBag.put("URI", URI);
         propertyBag.put("Use JDBC", "true");
         propertyBag.put("Database DLL", DATABASE_DLL);

         connectionInfo.setAttributes(propertyBag);

         connectionInfo.setUserName(DBUSERNAME);
         connectionInfo.setPassword(DBPASSWORD);
         connectionInfo.setKind(ConnectionInfoKind.SQL);


         table.setConnectionInfo(connectionInfo);

         try
         {
            aDatabaseController.setTableLocation(table, tables.getTable(counter));
         }
         catch (ReportSDKException exception)
         {
            LOGGER.error("", exception);
         }
      }
   }

I am getting following exception, when I execute this code:

2008-09-12 20:48:56,011 [database.PostgreSQLConnection] ERROR com.crystaldecisions.sdk.occa.report.lib.ReportSDKLogonException: Fehler bei der Anmeldung: FATAL: no PostgreSQL user name specified in startup packet---- Error code:-2147217393 Error code name:dbLogonFailed at com.businessobjects.reports.sdk.JRCCommunicationAdapter.a(Unknown Source) at com.businessobjects.reports.sdk.JRCCommunicationAdapter.request(Unknown Source) at com.crystaldecisions.proxy.remoteagent.x.a(Unknown Source) at com.crystaldecisions.proxy.remoteagent.q.a(Unknown Source) at com.crystaldecisions.sdk.occa.report.application.dd.a(Unknown Source) at com.crystaldecisions.sdk.occa.report.application.ag.a(Unknown Source) at com.crystaldecisions.sdk.occa.report.application.av.if(Unknown Source) at com.crystaldecisions.sdk.occa.report.application.an.new(Unknown Source) at com.crystaldecisions.sdk.occa.report.application.cb.for(Unknown Source) at com.crystaldecisions.proxy.remoteagent.u.performDo(Unknown Source) at com.crystaldecisions.proxy.remoteagent.u.a(Unknown Source) at com.crystaldecisions.sdk.occa.report.application.DatabaseController.a(Unknown Source) at com.crystaldecisions.sdk.occa.report.application.DatabaseController.setTableLocation(Unknown Source) at com.crystaldecisions.reports.sdk.DatabaseController.setTableLocation(Unknown Source) at database.PostgreSQLConnection.attachCrystalReportsToDatabase(PostgreSQLConnection.java:361)

What am I doing wrong?

Thanks in advance

Dmitri Pissarenko

[Former Member]

Hello!

Thanks for your answer!

Finally, I managed to establish connection between and Crystal Reports by using JDBC-ODBC bridge driver instead of PostgreSQL JDBC driver.

However, now another problem occurs: When I try to generate a report with data from PostgreSQL database, I'm getting following error message:

Exception in formula ""{@Record_Selection}"" at ""{TABLE.field}"": This field value cannot be used in a formula: {0}.

This is a translation of the German message

Ausnahme in Formel ""{@Record_Selection}"" bei ""{TABLE.field}"": Dieser Feldwert kann nicht in einer Formel verwendet werden: {0}.

The stack trace of the exception is given below.

2008-10-12 16:44:24,938 [com.crystaldecisions.reports.dataengine] ERROR	Formula error: recompile formulas failed
com.crystaldecisions.reports.formulas.FormulaException: Ausnahme in Formel ""{@Record_Selection}"" bei ""{TABLE.field}"":
Dieser Feldwert kann nicht in einer Formel verwendet werden: {0}.
	at com.crystaldecisions.reports.formulas.r.a(Unknown Source)
	at com.crystaldecisions.reports.formulas.r.a(Unknown Source)
	at com.crystaldecisions.reports.formulas.h.if(Unknown Source)
	at com.crystaldecisions.reports.formulas.r.a(Unknown Source)
	at com.crystaldecisions.reports.formulas.r.b(Unknown Source)
	at com.crystaldecisions.reports.formulas.r.a(Unknown Source)
	at com.crystaldecisions.reports.formulas.h.a(Unknown Source)
	at com.crystaldecisions.reports.formulas.r.a(Unknown Source)
	at com.crystaldecisions.reports.formulas.h.for(Unknown Source)
	at com.crystaldecisions.reports.formulas.r.a(Unknown Source)
	at com.crystaldecisions.reports.formulas.r.do(Unknown Source)
	at com.crystaldecisions.reports.formulas.h.long(Unknown Source)
	at com.crystaldecisions.reports.formulas.am.a(Unknown Source)
	at com.crystaldecisions.reports.formulas.FormulaInfo.do(Unknown Source)
	at com.crystaldecisions.reports.formulas.FormulaService.compile(Unknown Source)
	at com.crystaldecisions.reports.reportdefinition.fw.compile(Unknown Source)
	at com.crystaldecisions.reports.reportdefinition.df.compile(Unknown Source)
	at com.crystaldecisions.reports.reportdefinition.eq.else(Unknown Source)
	at com.crystaldecisions.reports.reportdefinition.eq.else(Unknown Source)
	at com.crystaldecisions.reports.reportdefinition.eq.else(Unknown Source)
	at com.crystaldecisions.reports.dataengine.m.if(Unknown Source)
	at com.crystaldecisions.reports.dataengine.m.a(Unknown Source)
	at com.crystaldecisions.reports.dataengine.m.a(Unknown Source)
	at com.crystaldecisions.reports.dataengine.bk.a(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.bv.<init>(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.objectformatter.bv.if(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.e.l.<init>(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.e.p.<init>(Unknown Source)
	at com.crystaldecisions.reports.formatter.formatter.e.p.a(Unknown Source)
	at com.businessobjects.reports.sdk.b.b.a(Unknown Source)
	at com.businessobjects.reports.sdk.b.b.byte(Unknown Source)
	at com.businessobjects.reports.sdk.JRCCommunicationAdapter.request(Unknown Source)
	at com.crystaldecisions.proxy.remoteagent.x.a(Unknown Source)
	at com.crystaldecisions.proxy.remoteagent.q.a(Unknown Source)
	at com.crystaldecisions.sdk.occa.report.application.dd.a(Unknown Source)
	at com.crystaldecisions.sdk.occa.report.application.ReportSource.a(Unknown Source)
	at com.crystaldecisions.sdk.occa.report.application.ReportSource.getPage(Unknown Source)
	at com.crystaldecisions.sdk.occa.report.application.AdvancedReportSource.getPage(Unknown Source)
	at com.crystaldecisions.sdk.occa.report.application.NonDCPAdvancedReportSource.getPage(Unknown Source)
	at com.crystaldecisions.reports.reportengineinterface.JPEReportSource.getPage(Unknown Source)
	at com.businessobjects.crystalreports.viewer.core.rs.b.if(Unknown Source)
	at com.businessobjects.crystalreports.viewer.core.aw.a(Unknown Source)
	at com.businessobjects.crystalreports.viewer.core.aw.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)

How can I solve this problem?

Is it possible to use PostgreSQL database with Crystal reports for eclipse ?

Thanks in advance

Dmitri Pissarenko

Edited by: Dmitri Pissarenko on Oct 12, 2008 4:57 PM

[Former Member]

It seems as though it might be a communication issue with the JDBC driver. Are you able to use this same driver to connect to the database through the Crystal Reports designer?