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.
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.