[CR2008] Changing database type at runtime fails

[Former Member]

Hello,

I'm using the code below to change database connection at runtime. It woks fine as long as the database type remains the same as the one defined when creating the report. As soon as the database type change for something else, it doesn't work anymore (for example changing from MSSQL to Firebird).

                ConnectionInfo connectionInfo = new ConnectionInfo();
                connectionInfo.ServerName = serverName;   //physical server name
                connectionInfo.DatabaseName = databaseName;
                connectionInfo.UserID = loginName;
                connectionInfo.Password = loginPassword;

                // Assigne les données de connexion à toutes les tables contenues
                // dans le rapport
                TableLogOnInfo tableLogOnInfo;
                foreach (Table table in reportDoc.Database.Tables)
                {
                    tableLogOnInfo = table.LogOnInfo;
                    tableLogOnInfo.ConnectionInfo = connectionInfo;
                    table.ApplyLogOnInfo(tableLogOnInfo);
                }

To say it in other words:

When I use the report designer to set database connection to a MSSQL database and then at runtime change the parameters for another MSSQL database server hosting the same database (for example development to operational databases), it works. It also works if I do it with a Firebird database (again development to operational). But if the report is setup with MSSQL and I runtime Firebird is selected, then connection fails.

Using VS2008 debugger, putting a breakpoint on the line after ApplyLogOnInfo, I can see that collection table.LogOnInfo.ConnectionInfo.Attributes contains values from the report at design time such as "Database DLL".

I don't know which code to use the change ALL connection parameters, including database type and driver.

Any help appreciated,

--

Francois Piette

[Former Member · Accepted solution]

Actually there is no solution with CR2008. We must either use RAS or a different report for each database provider or use the same database provider for all database kind, for example ODBC for all databases engine.

[daniel_paulsen · Accepted solution]

Actually, yes there is a solution with CR2008 and I have pointed out several links explaining it.

You do not need a RAS server to use these APIs and all processing is handled in-process. If thinking of the RAS APIs as a different product or solution is complicating the issue then just think of them as an extension of the ReportDocument object model offering full report modification. (note this extended functionality is only offered with the full CR2008 and CR XIR2 versions of Crystal Reports and not the versions bundled with Visual Studio).

The link to note 1201097 shows how to access the extended functionality of the ReportDocument object and mentions some limitations.

The link to the developer guide shows example code of how to use ReplaceConnection(oldConnInfo, NewConnInfo, params, dbOptions)

The link to the Wiki shows all of the required property bags for several different connection types and providers. It also has a link to a sample application that you can run against your report to get a complete list of the property bags required to build your unique connection information (Whatever your database may be). You can also upload the table generated by this sample application to the wiki in order to help others that may be using your specific connection type.

Piecing these together provides a very powerful solution giving you full control over the connection information stored in the report.

Additionally, as you pointed out, you can build all of your reports to use a single provider (ie: ADO.NET or ODBC) which in turn can connect to different database types. This could be a simpler solution (ie: less code) if you do not have a large number of reports to convert.

Dan

[Former Member · Accepted solution]

> Actually, yes there is a solution with CR2008 and I have pointed out several links explaining it.

I probably don't understood correctly. What I understood is that the only solution is using RAS which is a different product than CR2008. btw: I'm using the full CR2008, not the version bundled with VS2008.

> The link to note 1201097 shows how to

That link doesn't work. The result is an empty search page.

> The link to the Wiki shows

The first sentence of that link says it is for RAS.

Regards,

--

Francois Piette

[daniel_paulsen · Accepted solution]

RAS comes in a few flavours:

1: Managed RAS - servers and reports are hosted and managed in the Enterprise environment

2: Unmanaged RAS - reports are opened from the file system and executed on the RAS Server

3: InProc (in-process) RAS - no RAS server and reports are accessed/loaded through the ReportDocument API

#3 is the option you have with the full version of CR2008. All of the RAS runtime is installed and the missing component (which you do not need) is the RAS server.

All three 'flavours' share the same SDK. The only difference from a coding standpoint is how you open the report. See [this |;post where I try to explain it.

The link to the note was intentionally the empty search page. Just paste in the note number and search (if I pasted the link directly to the note it would have my cookie/session information in the URL and would not work for you)

the Wiki link is for RAS which can be any one of the 3 types mentioned above. The code is identical in all scenarios except for how you open the report.

[former_member183750]

What version of Crystal Reports are you using?

Ludek

[Former Member]

> What version of Crystal Reports are you using?

Crystal Reports 2008.

About box in designer reports "CR Developer Version 12.0.0.683".

Regards,

--

Francois Piette

[former_member183750]

As a test, are you able to change the databases in the CR Designer?

Ludek

[Former Member]

> As a test, are you able to change the databases in the CR Designer?

Yes, no problem to access all databases. I can also access the databases at runtime provided I don't change the parameters

btw: I'm using ReportDocument, not the viewer.

Regards,

--

Francois Piette

[former_member183750]

Open the report in the designer. Got to the Database menu and select "Set Datasource Location". When connecting to the MSSQL, what do you see at the Properties (Database Type:)?

What do you see when connecting to Firebird?

Ludek

[Former Member]

> what do you see at the Properties (Database Type:)?

I see "OLE DB (ADO)"

> What do you see when connecting to Firebird?

I see "ODBC (RDO)"

Regards,

--

Francois Piette

[daniel_paulsen]

you can change databases and servers with the reportdocument SDK (as you have discovered) but you cannot change the database provider (ie oledb to ODBC).

To do this you will need to use the RAS sdk (reportDocument.ReportClientdocument). This SDK will allow you to add/remove/change your connectioninfo. Have a look at the [Report Application Server SDK Developer guide|https://www.sdn.sap.com/irj/boc/sdklibrary] and the Datasource Connectivity section of "Using the SDK"

Dan

[Former Member]

> cannot change the database provider (ie oledb to ODBC).

Sure !?

If this is true, then it is a unexpected undocumented limitation of CR2008.NET

Please confirm.

Regards,

--

Francois Piette

[daniel_paulsen]

yes, I am very sure that Changing the connectionInfo properties using CrystalDecisions.Shared.ConnectionInfo will not allow you to change the database provider stored in the report. This is considered to be report modification which can be accomplished using the RAS APIs.

You can find it documented in [SAP Note 1201097 |https://www.sdn.sap.com/irj/scn/advancedsearch?cat=sdn_ossnotes&query=&adv=true&sdn_author_name=&sdn_updated_on_comparator=ge&sdn_updated_on=]

The RAS apis are exposed through the reportDocument SDK in CR2008 and can be used to perform almost any report modification (Reportdocument.ReportClientDocument). The connectionInfo object you will want to use is CrystalDecision.ReportAppServer.DataDefModel.ConnectionInfo (not the CrystalDecisions.Shared one).

The SAP Note I referenced above shows how to access the RAS APIs from a ReportDocument object and the tutorial I mentioned in the Developer documentation shows how to modify the property bags of the connectionInfo object. Typically, the properties you need to set in the property bags will be the properties you see in the "Set Datasource Location" window of the crystal reports designer.

If your database schemas are the same in each database, then you will not need to change the Table properties (except for maybe the username and password), so the best suited method for changing the database provider would be ReplaceConnection(). If you need to replace the Table as well as the connection then use .SetTableLocation(oldTable,newTable)

Here's a [link|https://www.sdn.sap.com/irj/scn/wiki?path=/display/bobj/reportApplicationServerDatabaseConnectivity] showing different properties required for various database providers like OLEDB (crdb_ado.dll) and ODBC (crdb_odbc.dll)

Dan