Changing database provider at runtime

[Former Member]

Hello,

I have the need to change the database provider at runtime. My reports are designed for - let's say - a MS-SQL database while at runtime, the report may be used again another kind of databse such Oracle or FireBird.

In another thread, Daniel Paulsen explained me that I have to use RAS. I found the documentation and some othe rthreads in this support forum.

I understtod that I have to use ISCDReportClientDocument interface and PropertyBagClass. Also I have to add some properties like "Database DLL", "crdb_ado.dll".

I was unable to setup a complete system.

I don't know where to find documentation for the required properties for all database providers. Maybe I can see the properties using the designer ? Or using VS2008 debugger and looking at ConnectionInfo instance ?

My problem is not a database connection issue since I can design the report for any of the database provider I have and use it at runtime with ReportDocument to generate a PDF file. The only issue I have is to change the database provider at runtime.

Any code snippet would be really appreciated.

Best regards,

--

Francois Piette

[Adam_Stone1 · Accepted solution]

The following [wiki|https://wiki.sdn.sap.com/wiki/display/BOBJ/ReportApplicationServerDatabaseConnectivity] will be useful in learning which property bag properties you are needing to set. You can look at the sample linked on that page to see how you can get the properties, and use similar logic on how to set them. Once you configure the property bags, you will need to use the SetTableLocation or ReplaceConnection methods from the DatabaseController.

[Former Member · Accepted solution]

Hello Adam,<br><br>

Your pointer helped me a lot. I'm now partially able to change the database provider at runtime. It work for executing the report against Firebird database and SQL2005 database , both using ODBC. But it doesn't work accessing the SQL2005 using OLEDB.

Here is the code I'm using:<br><br>

ReportDocument reportDoc = new ReportDocument();<br>

reportDoc.Load(reportPath);<br>

reportDoc.Refresh(); <br>

ISCDReportClientDocument rcd;<br>

rcd = reportDoc.ReportClientDocument;<br>

DatabaseController databaseController = rcd.DatabaseController;<br>

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos connectionInfos = databaseController.GetConnectionInfos(null);<br>

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldConnectionInfo = connectionInfos[0];<br>

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newConnectionInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfoClass();<br>

newConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;<br>

CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag QELogonProperties = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();<br>

CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag QEProperties = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();<br><br>

// Setup the new connection parameters<br>

QELogonProperties.Add("DSN", "Northwind MSSQL ODBC");<br>

QELogonProperties.Add("Trusted_Connection", false);<br>

QELogonProperties.Add("UseDSNProperties", false);<br>

QEProperties.Add("QE_LogonProperties", QELogonProperties);<br>

QEProperties.Add("Database DLL", "crdb_odbc.dll");<br>

QEProperties.Add("QE_DatabaseName", "Northwind");<br>

QEProperties.Add("QE_DatabaseType", "ODBC (RDO)");<br>

QEProperties.Add("QE_ServerDescription", "Northwind MSSQL ODBC");<br>

QEProperties.Add("QE_SQLDB", true);<br>

QEProperties.Add("SSO Enabled", false);<br>

newConnectionInfo.Attributes = QEProperties;<br>

newConnectionInfo.UserName = "sa";<br>

newConnectionInfo.Password = "fp";<br>

<br>

// Use the new connection<br>

databaseController.ReplaceConnection(oldConnectionInfo, newConnectionInfo, null, CrDBOptionsEnum.crDBOptionUseDefault);<br>

// Create the report<br>

reportDoc.Export(exportOptions);<br>

<br><br>

As I said, this works perfectly.

Now if I change the parameter to access the exact same DB on same server, but using OLEDB (Sql Native Client), the connection fails with error 17.

Here is the section of code changing the parameters:<br><br>

QELogonProperties.Add("Data Source", "Northwind MSSQL OLEDB");<br>

QELogonProperties.Add("Initial Catalog", "Northwind");<br>

QELogonProperties.Add("Integrated Security", false);<br>

QELogonProperties.Add("Provider", "SQLOLEDB");<br>

QELogonProperties.Add("SSOKEY", "");<br>

QELogonProperties.Add("Use DSN Default Properties", false);<br>

QELogonProperties.Add("Use Encryption for Data", "0");<br>

QEProperties.Add("QE_LogonProperties", QELogonProperties);<br>

QEProperties.Add("Database DLL", "crdb_ado.dll");<br>

QEProperties.Add("QE_DatabaseName", "Northwind");<br>

QEProperties.Add("QE_DatabaseType", "OLE DB (ADO)");<br>

QEProperties.Add("QE_ServerDescription", "Northwind MSSQL OLEDB");<br>

QEProperties.Add("QE_SQLDB", true);<br>

QEProperties.Add("SSO Enabled", false);<br>

newConnectionInfo.Attributes = QEProperties;<br>

newConnectionInfo.UserName = "sa";<br>

newConnectionInfo.Password = "fp";<br>

<br>

I don't understand what I'm doing wrong with OLEDB since the same code works for ODBC.

I followed the description in:[https://wiki.sdn.sap.com/wiki/display/BOBJ/ReportApplicationServerDatabaseConnectivity-SQL+Server]<br>

The error message returned is "Unable to start connection. Database provider code : 17" (Translated from french so exact english message may be a little bit different)<br>

<br>

Of course I have tested the data sources using the designer and all (Firebird ODBC, SQL2005 ODB and SQL2005 OLEDB) work perfectly.<br>

<br>

Any help appreciated,<br>

--<br>

Francois Piette<br>

[daniel_paulsen · Accepted solution]

For Sql Server Native Client, you will need to use the following provider:

QELogonProperties.Add("Provider", "SQLNCLI");

Dan

[Former Member · Accepted solution]

For Sql Server Native Client, you will need to use the following provider:

QELogonProperties.Add("Provider", "SQLNCLI");

This doesn't work much better altough the error message is now different: "La requête n'a pas pu être soumise pour traitement en arrière-plan" (English: "The request can't be submited for background processing"). No idea what it means...

Do you have a complete set of parameters to set for SQL Native Client ?

--

Francois Piette

[Adam_Stone1 · Accepted solution]

The best way to understand which properties you need to set is to create a report in the Crystal Report designer using the SQL native client and then run it through the application that is linked to on the wiki provided earlier. Each table that is shown on the wiki was created using that application.

[Former Member · Accepted solution]

Hello adam,

> through the application that is linked to on the wiki provided earlier

This application is a VB2003 application. VS2008 is not able to convert that application (maybe because I need IIS properly configured) and so I'm unable to run it.

Do you have the same application as a simple console mode C# application ?

Regards,

--

Francois Piette