Changing the Database Connection¶

Element conn_type¶

This element defines the primary type of the replacement connection. ReplaceDBConnection supports four connection types: ole db, odbc driver, odbc dsn, and Oracle connection. In the future more types may be added. Use the f2c_dbconnection_* enum values for this parameter such as f2c_dbconnection_oracle.

Element provider¶

This element sets the name of the provider. If there is a typo in the provider name things will not work and it can be difficult to determine why. To make this element less error prone, we’ve provided a list of common provider name definitions as f2c_provider_*. For f2c_dbconnection_odbc_dsn type connections, provider defines the DSN name. For f2c_db_connection_odbc_drv type connections, provider defines the ODBC driver name. For f2c_dbconnection_oledb type connections, provider defines the internal OLE DB provider name (such as SQLOLEDB or MSOLEDBSQL). Oracle (f2c_dbconnection_oracle) doesn’t use this parameter.

Although you can supply your own string value for provider, a list of commonly used providers is defined internally:

You may notice that some of the definitions are longer than the string they’re replacing. By using the definition you can be sure that no typos will get compiled into your application. If the definition has a type, the application won’t compile, but if the string has a typo, it will run and fail without indicating the reason.

Element server¶

This element can contain a server name or an IP address of the replacement server. All connection types require a value for this element. For Oracle, the server can be a net service name from the tnsnames.ora file or it could be a connect descriptor or listener protocol address.

Element database¶

This element should contain the name of the database on the server. This parameter is not used for Oracle connections.

Element owner¶

In some database systems, it’s necessary to know the owner of a table to properly find the table. This element is optional for PostgreSQL, Oracle, and Microsoft SQL Server. If the table’s owner isn’t the user used for login, you may need to pass this. For Microsoft SQL Server, if the owner is “dbo” (which it normally is), you don’t need to pass the owner. Other database servers may have differing requirements for this parameter.

Element user¶

Crystal doesn’t share a login with Flex2SQL. Each table used within the report must have connection details associated with it. This element is the user name used for that table to connect to the database server. Note that for Microsoft SQL server, if a trusted connection is being used, the user element should be left empty.

Element password¶

Crystal doesn’t share a login with Flex2SQL. Each table used within the report must have connection details associated with it. This element is the plain text password used for that table to connect to the database server. Note that for Microsoft SQL server, if a trusted connection is being used, the password element should be left empty.

Element trusted¶

Crystal doesn’t share a login with Flex2SQL. Each table used within the report must have connection details associated with it. This boolean element indicates that Windows Authentication should be used for to connect to the server. When trusted is True, the user and password elements are not used. The default value is False.

Element tcp_port¶

This optional element is used by some data sources to set the TCP port that Crystal should use for the connection. MySQL and PostgreSQL both use this method for setting the TCP port if the standard port is not used. For MS-SQL do not use this element. Instead, use the server element by appending "":#####"" to the server name with the ##### replaced by the port number. For Oracle, the port is often specified within the tnsnames.ora file, although it can be specified in the server element as well using the same format.

Element tables¶

This optional element is used by the ReplaceDBConnection procedure to specify the list of tables that should have their connections replaced. Note that you can specify all tables by leaving this element empty. To get a list of all tables, use the new TableNames function that simply returns a string[] all tables referenced in the report.

Replace DAT-based tables with a connection to MS SQL Server¶

Procedure OnInitializeReport Handle hoReport
      t_f2c_new_dbconnection newConn

      Move f2c_dbconnection_oledb  to newConn.conn_type
      Move f2c_provider_msoledbsql to newConn.provider
      Move "localhost"             to newConn.server
      Move "flex2crystal"          to newConn.database
      Move "dbo"                   to newConn.owner
      move True                    to newConn.trusted

      Send ReplaceDbConnection of hoReport newCon
End_Procedure

Note that no table names are passed to the ReplaceDBConnection procedure. When used in this way all tables (including tables in subreports) are replaced. This same format would be used if you needed to replace the development server embedded within reports to a production server, although there are other methods that could be used to accomplish this.

Replace all tables but codemast and codetype¶

Some applications (especially multi-tenant hosted apps) are setup to get codemast and codetype from a common database. When this is the case, codemast and codetype need to remain unchanged as they will already be pointing to the correct place. The new TableNames function is useful in this case:

Procedure OnInitializeReport Handle hoReport
      t_f2c_new_dbconnection newConn

      Move f2c_dbconnection_oracle  to newConn.conn_type
      Move "cust1pdb"                to newConn.server
      Move "cust1_owner"            to newConn.owner
      move "john_doe"               to newConn.user
      move "MyP455w0rD-8675309"     to newConn.password

      Get TableNames of hoReport to newConn.tables
      // Remove codemast and codetype from the array
      Move (RemoveFromArray(newConn.tables, SearchArray("codemast", newConn.tables, Desktop, RefFunc(DFSTRICMP)) )) to newConn.tables
      Move (RemoveFromArray(newConn.tables, SearchArray("codetype", newConn.tables, Desktop, RefFunc(DFSTRICMP)) )) to newConn.tables

      Send ReplaceDbConnection of hoReport newCon
End_Procedure

The above example illustrates how to replace all the tables connections except for two specific tables: codetype and codemast. It also illustrates how to specify the details needed to connect to an Oracle server. Notice that the provider element is unused in Oracle connections that use a native oracle driver.

Replace DAT-based tables with a connection to a PostgreSQL DSN¶

Procedure OnInitializeReport Handle hoReport
    t_f2c_new_dbconnection newConn

    Move f2c_dbconnection_odbc_dsn           to newConn.conn_type
    Move f2c_provider_pgsql_odbc_unicode_x64 to newConn.provider
    Move "localhost"                         to newConn.server
    Move "XYZ-DB"                            to newConn.database
    Move "john_doe"                          to newConn.user
    Move "MyP455w0rD-8675309"                to newConn.password

    // Change JUST the customer table
    Move "customer"                          to newConn.tables_list[0]

   Send ReplaceDbConnection of hoReport newConn
End_Procedure

In the above example we’re connecting to a PostgreSQL ODBC DSN data source. Notice that we’re using one of the f2c_provider_* definitions to make sure we’ve picked a correct provider string for a 64-bit unicode database.