Development Team Blog

This blog contains information on choosing and changing the data source in the Data Access report writer.

Data Source?
During the creation of a new report a data source needs to be selected. A data source makes it possible for the report writer to access the rows of data in tables. Currently 3 data source categories are available; DataFlex, ODBC and RDS.

DataFlex
For the DataFlex database the data source is either the list of tables (usually named filelist.cfg) or individual tables.

Opening a workspace (.ws / .sws) is the best way to select tables from the data source. Selecting a workspace automatically loads the paths to the data (DataPaths) as well as the reference to the list of tables (usually filelist.cfg) as this is defined in the .ws file. If the workspace' data paths contain multiple folders or if the filelist is not in the same folder as the data files the environment is setup correctly automatically. Of course assuming the information in the .ws file is correct.

If there is no workspace available the best and most used option is selecting a list of tables (filelist). The data source is then set to the list of tables (usually filelist.cfg) and opening tables relies on the folder the list of tables file is in. If the files are in multiple folders or the filelist is in a separate folder this option results in having difficulties to add tables to the report. That's why a workspace is a much better option.

Selecting individual tables is really meant for those situations where the tables are not listed in the list of tables or where tables need to come from multiple folders and these folders are not all listed in the workspace file. Or where the same table appears multiple times in different folders. The first selected table will be set as the data source. The biggest downside of this method is that more work needs to be done if the paths are changing as each table needs to be re-connected.

Besides the previous 3 options it is possible to select an INT file. INT file are used to connect a DataFlex application to an foreign database such as Microsoft SQL, DB2, Pervasive.SQL etc. The use of INT files for the report writer offers a flexibility of having to define one report and work with different databases for each of the deployed situations. However, the biggest downside of this method is SPEED! Using an INT file is much slower and I wouldn't want that. With an INT it is impossible for the report writer to use SQL optimalizations. With the ability to change a data source on at application integration level it is not an option you need.

ODBC
An ODBC data source is located via a DSN (Data Source Name) reference (User, System or File DSN) or via DSN less connection as the data source. User, System or File DSNs are maintained via the Microsoft ODBC Manager. Before you can use one of these for a new report the DSN needs to be created. After selecting the DSN its name will be stored in the connection string. In a DSN less connection the connection string contains all the necessary information (such as driver, user and password information, database etc) to access the database and its tables, views or stored procedures. Each ODBC driver has its own keywords and specifications.

RDS
The third category is RDS. This abbreviation stands for Runtime Data Source and all table descriptions are made during the report definition and stored in the report. No external references are present. The use of RDS only make sense if the report is used via integration and therefore it is not an option available in the standard edition.

Execution
A report can be ran from the designer (developer or standard edition) or as part of an application. This is called report integration. The developer edition contains the classes to make integration possible and allows the owner of the license to distribute the reports with a fat client application. The next paragraph deals with this. If the report is distributed and the end-user has a developer or standard edition of the report writer they can (often need) to change the data source in the reports and re-save the reports. After a change of the data source it is always wise to check the database to correct mismatches between the database information stored in the report and the real information. If the report uses an ODBC database and a DSN name is stored it is not needed to change the data sources of the reports, just make sure the DSN contents is correct and the table layout matches the stored information.

Integration
If the report is used as part of an application it is most likely that the application does not use the same data source information as what is stored in the report when this was created or updated. A report stores the data source name (location) to know how to get access to the data and to be enable the option to check the structure of the tables against what was know when the report was created. The data may even be retrieved from a location independent of the application.

ODBC, RDS or DataFlex?
Does it matter what data source is used in the report? Yes it does matter as you need to do more or less (or nothing) to change.

DataFlex
Lets first focus on the usuage of the DataFlex database. If the data files are located in the workspace of the application and there is just one set of tables with the same name the report class does all the data source replaces based on a property named pbAutoLocateDFFiles.

If the data source is a list of tables file (this is a file whose name is seen as the filelist (see function IsFileList)) the filelist name of the workspace is taken and replaced at execution time. If the workspace does not have a filelist (DF_FILELIST_NAME attribute is blank) an error is reported but as far as I know each DataFlex application MUST have a list of tables file.

If the data source is a table name (so individual tables were selected for the report) the name of each table is retrieved, the path is stripped off and an attempt is made to find a file with that name in the workspace paths via an internal function named FindTablePath. This function enumerates all paths in the workspace' datapath (psDataPath property). If any of the tables cannot be found an error is returned.

This automatic path correction does work for the main report and all sub-reports and nested sub-reports. As might not be known, the DataFlex report writer can have an "unlimited" number of sub-report levels.

If the workspace contains multiple tables with the same name (different folders) the automatic path correction might not work (depends on the workspace settings). In that case code needs to be written by the developer that does a similar activitiy as the auto locate of DataFlex files but corrected to the workspace. Study the private AutoLocateDFFiles method for the code that needs to be written.

ODBC
If the datasource is an ODBC datasource the change of data sources at integration level might not need to be done at all. It depends on what technique is used. In a DSN less connection it is probably needed to change the connection string contents while a user, system or file DSN only require the presence of the DSN and the correct configuration of the DSN. If the report integration code is generated by the integration wizard code can be generated (ChangeODBCDataSource) in which the data source can be modified. Modification means the change of psDataConnection and psDatabaseName.

In the following code snippet the connection string of the report and each of its possible sub-reports is read and changed. The report in this case used an DSN less connection string. The server name is replaced with information from an open table in the system. If the login information requires a change the code can be extended.

Code:

Procedure ChangeDatabaseConnection Handle hoReport    
    String sServerFromTable sConnectionString sServerFromReportDatabaseConnection sSubReportId
    Integer iSubReports iSubReport

    Get TableConnectionString Customer.File_Number to sConnectionString
    Get ServerFromConnectionString sConnectionString to sServerFromTable
    If (sServerFromTable <> "") Begin
        Get psDatabaseConnection of hoReport '' to sConnectionString
        Get ServerFromConnectionString sConnectionString to sServerFromReportDatabaseConnection
        Move (Replace (sServerFromReportDatabaseConnection, sConnectionString, sServerFromTable)) to sConnectionString
        Set psDatabaseConnection of hoReport '' to sConnectionString
        Get SubReportCount of hoReport '' to iSubReports
        Decrement iSubReports
        For iSubReport from 0 to iSubReports
            Get SubReportId of hoReport '' iSubReport to sSubReportId
            Get psDatabaseConnection of hoReport sSubReportId to sConnectionString
            Get ServerFromConnectionString sConnectionString to sServerFromReportDatabaseConnection
            Move (Replace (sServerFromReportDatabaseConnection, sConnectionString, sServerFromTable)) to sConnectionString
            Set psDatabaseConnection of hoReport sSubReportId to sConnectionString
        Loop
    End
End_Procedure

Function DriverIndex String sDriverName Returns Integer
    String sCurrentDriverName
    Integer iDriver iDrivers

    Get_Attribute DF_NUMBER_DRIVERS to iDrivers
    For iDriver from 1 to iDrivers
        Get_Attribute DF_DRIVER_NAME of iDriver to sCurrentDriverName
        If (Uppercase (sDriverName) = Uppercase (sCurrentDriverName)) Begin
            Function_Return iDriver
        End
    Loop

    Function_Return 0
End_Function

Function TableConnectionString Handle hTable Returns String
    Integer iDriver iConnections iConnection
    String sConnectionString sConnectionID
    
    Get_Attribute DF_FILE_LOGIN of StopPln.File_Number to sConnectionString
    If (Uppercase (Left (sConnectionString, 8)) = "DFCONNID") Begin
        Get DriverIndex "MSSQLDRV" to iDriver
        Get_Attribute DF_DRIVER_NUMBER_CONNECTION_IDS of iDriver to iConnections
        Decrement iConnections
        For iConnection from 0 to iConnections
            Get_Attribute DF_DRIVER_CONNECTION_ID of iDriver iConnection to sConnectionID
            If (sConnectionString contains sConnectionID) Begin
                Get_Attribute DF_DRIVER_CONNECTION_ID_STRING of iDriver iConnection to sConnectionString
            End
        Loop                       
    End
    
    Function_Return sConnectionString
End_Function

Function ServerFromConnectionString String sConnectionString Returns String
    String sServerPart
    Integer iServerPos iSemiColonPos

    Move (Pos ('server=', Lowercase (sConnectionString))) to iServerPos
    If (iServerPos > 0) Begin
        Move (Pos (';', sConnectionString, iServerPos)) to iSemiColonPos
        If (iSemiColonPos = 0) Begin
            Move (Length (sConnectionString)) to iSemiColonPos
        End
        Move (Mid (sConnectionString, iSemiColonPos - iServerPos, iServerPos)) to sServerPart
    End

    Function_Return sServerPart
End_Function

RDS
With an RDS data source there is nothing that can and should or needs to be done in changing. The table description is stored in the report and no external references are present that require actions to be coded.

Summary
The correct selection of a data source is important and it is not difficult to change a data source at integration level, only a bit of time to study how the system is build is needed. We are open for suggestions to improve the system.