Changing the Database 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.

Note

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

Determining Settings

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.

Summary

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.