Legacy method to change DB connection¶
When you create a new workspace in DataFlex Studio you specify the database for the workspace. You are prompted for information such as the server name, user name, database, and password, and this becomes the default database for the workspace. Later, you can modify the database connection information using the Studio Configure Database Connection wizard. What if you need to modify the connection information at runtime?
Modifying Properties for an Existing Connection¶
The old Crystal Reports RDC (COM) interface allowed developers to alter connection properties on a table-by-table basis at runtime. The new .NET environment limits the properties that can be updated at runtime to the User ID, Password, DSN, Data Source, Database, Initial Catalog, and Integrated Security.
Connection properties are specific to the database provider.
Example 1: Modifying Properties for an Existing MS SQL Connection¶
The example below shows how to change two connection properties at runtime. The code loops through all the tables in the report looking for tables that are ADO based. When a match is found the DSN is changed to “MYSERVERSQLEXPRESS” and the Initial Catalog is changed to “MY DATABASE”. This code is usually placed in the OnInitializeReport method.
String sTableType Integer iTableItem iTableCount Handle hoDatabaseTable hoConnProps Handle hoTables Variant vConnProps Boolean bOK Get TableObjects of hoReport to hoTables Move (SizeOfArray(hoTables)) to iTableCount For iTableItem from 0 to (iTableCount-1) Move hoTables[iTableItem] to hoDatabaseTable If (hoDatabaseTable) Begin // Check database type Get ComDllName of hoDatabaseTable to sTableType If (sTableType = "crdb_ado.dll") Begin Get Create of hoReport (RefClass(cFlex2CrystalConnectionProperties)) to hoConnProps Get ComConnectionProperties of hoDatabaseTable to vConnProps Set pvcomObject of hoConnProps to vConnProps Get IsComObjectCreated of hoConnProps to bOK If (bOK) Begin Send ComAdd of hoConnProps "Initial Catalog" "MY DATABASE" Send ComAdd of hoConnProps "DSN" "MYSERVER\SQLEXPRESS" Send ComApplyLogOnInfo of hoDatabaseTable (pvComObject(phoLogon(hoConnProps))) End End End Loop
To modify connection properties for ODBC based tables, change the code to look for ComDllName = crdb_odbc.dll by changing the following line of code:
If (sTableType = " crdb_odbc.dll") Begin
Example 2: Modifying Properties for an Existing Oracle Connection¶
Changing the username, password, and server for an Oracle connection causes the table schema to be lost, therefore a different method is required to update these Oracle properties. The example below shows how to change the username, password, and server for an Oracle connection at runtime.
Handle hoTables Handle hoDCC Variant vDCC Integer i Get ComDatabaseController of (ReportClientDocumentObject(hoReport)) to vDCC Get Create U_cFlex2CrystalDatabaseController to hoDCC Set pvComObject of hoDCC to vDCC Get TableObjects of hoReport to hoTables For i from 0 to (SizeOfArray(hoTables)-1) Send ComSetTableLocationByServerDatabaseName of hoDCC (ComName(hoTables[i])) "127.0.0.1" "" "tester" "zabxyc" Loop Send Destroy of hoDCC
Replacing a Connection¶
If additional changes are required (for example, changing the type of connection from ODBC to ADO or from DataFlex to ODBC), then the connection must be replaced.
Example 3: Replacing a Connection¶
The example below shows how to replace a table connection for an MS SQL Server.
Function Locate_ADO_Table Handle ohTable Returns Boolean Boolean bOK Variant vConnectionProperties Handle ohConnectionProperties hoRASConn hoCI String sInitialCatalogue sTable Variant vDCC vLogonProps Handle hoDCC hoLogonProps hoQELogonProps Get Create (RefClass(cFlex2CrystalConnectionProperties)) to ohConnectionProperties Get ComConnectionProperties of ohTable to vConnectionProperties Set pvcomObject of ohConnectionProperties to vConnectionProperties Get IsComObjectCreated of ohConnectionProperties to bOK If (bOK) Begin Get phoRASConnInfo of ohConnectionProperties to hoRASConn Get ComName of ohTable to sTable // Debug command to list all the current connection properties Send ComDumpSettings of ohConnectionProperties ohTable Get ComDatabaseController of (ReportClientDocumentObject(Self)) to vDCC Get Create U_cFlex2CrystalDatabaseController to hoDCC Set pvComObject of hoDCC to vDCC Get Create U_cFlex2CrystalConnectionInfoClass to hoCI Send CreateComObject of hoCI Get Create U_cComF2CPropertyBagClass to hoLogonProps Send CreateComObject of hoLogonProps Get Create U_cComF2CPropertyBagClass to hoQELogonProps Send CreateComObject of hoQELogonProps // These remarked out properties seem to have defaults that just work // Send ComAdd of hoQELogonProps "OLE DB Services" "-5" // Send ComAdd of hoQELogonProps "Auto Translate" "-1" Send ComAdd of hoQELogonProps "Connect Timeout" "15" Send ComAdd of hoQELogonProps "Data Source" "OLI-VM\SQLEXPRESS" Send ComAdd of hoQELogonProps "Integrated Security" "False" // "SSPI" or "False" Send ComAdd of hoQELogonProps "Initial Catalog" "crystaldev" Send ComAdd of hoQELogonProps "Locale Identifier" "1033" Send ComAdd of hoQELogonProps "Provider" "SQLOLEDB" // or "SQLNCLI10" // These remarked out properties seem to have defaults that just work // Send ComAdd of hoQELogonProps "Use Encryption for Data" "0" // Send ComAdd of hoQELogonProps "Use DSN Default Properties" "False" // Send ComAdd of hoQELogonProps "Trust Server Certificate" "0" // Send ComAdd of hoQELogonProps "MARS Connection" "0" // Send ComAdd of hoQELogonProps "General Timeout" "0" // Send ComAdd of hoQELogonProps "Tag with column collation when possible" "0" Send ComAdd of hoLogonProps "QE_LogonProperties" (pvComObject(hoQELogonProps)) Send ComAdd of hoLogonProps "Trusted Connection" "True" Send ComAdd of hoLogonProps "QE_DatabaseType" "OLE DB (ADO)" Send ComAdd of hoLogonProps "QE_DatabaseName" "crystaldev" Send ComAdd of hoLogonProps "QE_ServerDescription" "OLI-VM\SQLEXPRESS" Send ComAdd of hoLogonProps "QE_SQLDB" "True" Send ComAdd of hoLogonProps "Database DLL" "crdb_ado.dll" Send ComAdd of hoLogonProps "SSO Enabled" "True" // If Integrated Security is False and you don't want a dialog to popup for // user/password, you need to set these here. // Set ComUserName of hoCI to "test" // Set ComPassword of hoCI to "test" Set ComAttributes of hoCI to (pvComObject(hoLogonProps)) Set ComKind of hoCI to OLEF2CCrConnectionInfoKindEnum_crConnectionInfoKindCRQE // The last parameter can also be OLEF2CCrDBOptionsEnum_crDBOptionUseDefault // which verifies the database right now. You will get a general exception if // you set it to default, have integrated security off, and DON'T set a // user/password, since the verify can't take place. If you want a user/password // dialog you need to use the DoNotVerifyDB setting. Send ComReplaceConnection of hoDCC (pvComObject(hoRASConn)) (pvComObject(hoCI)) (NullComObject()) OLEF2CCrDBOptionsEnum_crDBOptionDoNotVerifyDB Send Destroy of ohConnectionProperties Send Destroy of hoLogonProps Send Destroy of hoQELogonProps Send Destroy of hoCI Send Destroy of hoDCC End Function_Return bOK End_Function // Locate_ADO_Table
The property settings for any given connection vary greatly based on the specific server version, and type, and it can be difficult to figure out the correct settings. For instance, the connection properties for an Oracle connection are completely different than the ones shown above for MS SQL. One way to determine the correct settings is to design a test Crystal Report that connects to the required destination server and then run the report from Flex2Crystal. When you run the report from Flex2Crystal you can use the ComDumpSettings method to see a list of all the settings that were used to successfully make the connection. A call to the ComDumpSettings method is shown in the above example. ComDumpSettings can be called on any existing ConnectionProperties object to dump all the advanced settings for the connection.
If your report relies entirely on one database type (for example, ADO), then you can change connection properties (for example, the server that it connects to, whether or not it uses integrated security, or the database name) at runtime by simply updating the properties for the existing connection (Example 1 or Example 2).
However, if you have reports that are MS SQL based and you need to change them to be Oracle based at runtime, you cannot update the properties for the existing connection. Instead, you need to use the connection replacement procedure outlined in Example 3.