postgresql - How do I change a Crystal Report's ODBC database connection at runtime?

Question

I have a report made with Crystal Reports 2008 that I need to deploy a production system which means that I need to be able to change the database connection at runtime. The database is PostgreSQL 8.3.0 and the connection I use for creating the initial report is an ODBC connection.

I have found various ways to change the database connection including the following:

reportDoc.Load(report);
reportDoc.DataSourceConnections[0].SetConnection("server", "database", "user", "pwd");
reportDoc.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, path);

However, this always fails with the following error message.

Failed to open the connection.

I have validated the connection parameters by successfully connecting to the database with pgAdmin III so I know the connection parameters are correct. In addition, if I remove the SetConnection(...) line so the code looks like this:

reportDoc.Load(report);
reportDoc.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, path);

then the report runs fine using the connection parameters that are stored in the report. Is it possible that this method does not work for ODBC connections?

How do I change a Crystal Report's ODBC database connection at runtime?

Answer 1

After even more research I found that there was a two part answer.

PART 1

If you are connecting to PostgreSQL via ODBC (the only way Crystal Reports can pull data from PostgreSQL as of the time of this writing) using the data owner you then you can use the following code:

reportDoc.Load(report);
reportDoc.DataSourceConnections[0].SetConnection("Driver={PostgreSQL ANSI};Server=myServer;Port=5432;", "myDatabase", "myUser", "myPassword");
reportDoc.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, path);
// Depending on your application you may have more than one data source connection that needs to be changed.

This method only works if you are connecting as a user that owns the data that you are reporting on because the schema name does not need to be supplied.

PART 2

If you are connecting to PostgreSQL via ODBC with a user other than the data owner then you need to manually supply the schema name. This is accomplished with the following code.

reportDoc.Load(report);

ConnectionInfo connInfo = new ConnectionInfo();
connInfo.ServerName = "Driver={PostgreSQL ANSI};Server=myServer;Port=5432;";
connInfo.DatabaseName = "myDatabase";
connInfo.UserID = "myUser";
connInfo.Password = "myPassword";

TableLogOnInfo tableLogOnInfo = new TableLogOnInfo();
tableLogOnInfo.ConnectionInfo = connInfo;

foreach (Table table in reportDoc.Database.Tables)
{
    table.ApplyLogOnInfo(tableLogOnInfo);
    table.LogOnInfo.ConnectionInfo.ServerName = connInfo.ServerName;
    table.LogOnInfo.ConnectionInfo.DatabaseName = connInfo.DatabaseName;
    table.LogOnInfo.ConnectionInfo.UserID = connInfo.UserID;
    table.LogOnInfo.ConnectionInfo.Password = connInfo.Password;

    // Apply the schema name to the table's location
    table.Location = "mySchema." + table.Location;
}

reportDoc.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, path);

Summary

There are two critical pieces of information here when trying to connect to a PostgreSQL database from Crystal Reports.

1. The driver, server, and port number must all be specified in the server name property.
2. If connecting as a user other than the data owner you must specify the schema name for each table you are pulling data from.

Sources

There were several sources used that did not have an answer that worked in my specific scenario but that led me in the right direction. These sources are listed below.

• Nathan Koop's Answer
• https://www.sdn.sap.com/irj/scn/thread?messageID=6913827#6913827
• Best Practices for Changing Databases at Runtime