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

by

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.

More Related Contents:

  1. What is the format for the PostgreSQL connection string / URL?
  2. Is there a timeout for idle PostgreSQL connections?
  3. In psql, why do some commands have no effect?
  4. How to log PostgreSQL queries?
  5. How to search a specific value in all tables (PostgreSQL)?
  6. PG::ConnectionBad – could not connect to server: Connection refused
  7. How do you create a read-only user in PostgreSQL?
  8. Generating a UUID in Postgres for Insert statement?
  9. Why does PostgreSQL perform sequential scan on indexed column?
  10. PostgreSQL naming conventions
  11. How to set value of composite variable field using dynamic SQL
  12. In-order sequence generation
  13. Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
  14. How to generate the “create table” sql statement for an existing table in postgreSQL
  15. PostgreSQL: serial vs identity
  16. Postgres password authentication fails
  17. Generate series of dates – using date type as input
  18. Copy a few of the columns of a csv file into a table
  19. Postgres query to check a string is a number
  20. Using pyspark to connect to PostgreSQL
  21. Returning from a function with OUT parameter
  22. ERROR: permission denied for relation tablename on Postgres while trying a SELECT as a readonly user
  23. How do you create a random string that’s suitable for a session ID in PostgreSQL?
  24. Joining Results from Two Separate Databases
  25. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  26. Update timestamp when row is updated in PostgreSQL
  27. Postgresql query array of objects in JSONB field
  28. Multicolumn index on 3 fields with heterogenous data types
  29. How to add a conditional unique index on PostgreSQL
  30. Select from a table variable

Leave a Comment