如何在运行时更改Crystal Report的ODBC数据库连接？ [英] How do I change a Crystal Report's ODBC database connection at runtime?
问题描述
我有一份使用Crystal Reports 2008做的报告，我需要部署一个生产系统，这意味着我需要能够在运行时更改数据库连接。数据库是PostgreSQL 8.3.0，用于创建初始报告的连接是一个ODBC连接。
我已经找到了多种方法来更改数据库连接，包括以下：
reportDoc.Load（report）;
reportDoc.DataSourceConnections [0] .SetConnection（server，database，user，pwd）;
reportDoc.ExportToDisk（CrystalDecisions.Shared.ExportFormatType.PortableDocFormat，path）;
但是，此操作总是失败，并显示以下错误消息。
无法打开连接。
我已通过成功连接验证了连接参数到pgAdmin III的数据库，所以我知道连接参数是正确的。此外，如果我删除SetConnection（...）行，代码看起来像这样：
reportDoc.Load ）;
reportDoc.ExportToDisk（CrystalDecisions.Shared.ExportFormatType.PortableDocFormat，path）;
，那么报表将使用报表中存储的连接参数正常运行。
如何在运行时更改Crystal Report的ODBC数据库连接？
h2> PART 1
如果您通过ODBC连接到PostgreSQL（Crystal Reports可以在撰写本文时从PostgreSQL中提取数据的唯一方式）您可以使用以下代码：
reportDoc.Load（report）;
reportDoc.DataSourceConnections [0] .SetConnection（Driver = {PostgreSQL ANSI}; Server = myServer; Port = 5432;，myDatabase，myUser，myPassword）;
reportDoc.ExportToDisk（CrystalDecisions.Shared.ExportFormatType.PortableDocFormat，path）;
//根据您的应用程序，可能有多个数据源连接需要更改。
此方法仅在您以拥有您报告的数据的用户身份连接时才有效，因为
第2部分
如果您要通过ODBC连接到PostgreSQL与数据所有者以外的用户，则需要手动提供模式名称。这是通过以下代码实现的。
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（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;
//将模式名称应用于表的位置
table.Location =mySchema。 + table.Location;
}
reportDoc.ExportToDisk（CrystalDecisions.Shared.ExportFormatType.PortableDocFormat，path）;
摘要
- 必须指定驱动程序，服务器和端口号
- 如果以数据所有者以外的用户身份连接，则必须为要从中提取数据的每个表指定模式名称。
来源
有几个来源没有在我的具体情况下工作的答案，我在正确的方向。这些来源如下所示。
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?
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.
- The driver, server, and port number must all be specified in the server name property.
- 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
这篇关于如何在运行时更改Crystal Report的ODBC数据库连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
