Using SQLite Data Sources

SQLite is a relational database system which stores data directly in local disk files. Many applications, including Exago, use SQLite in order to store local data. For example, the Exago Monitoring service collects monitoring data in SQLite files.

You can add SQLite data files as Exago data sources. Exago uses Open Database Connectivity (ODBC) as a translation layer in order to read the files. Before adding a SQLite data source, you need to install a SQLite ODBC driver on the Exago host computer.

Installing the SQLite ODBC Driver

There are several options for drivers, but the recommended one is a free and open source package by Christian Werner and other authors.

To install the SQLite ODBC Driver:

Windows

  1. Download the current version of sqliteodbc.exe from Christian Werner's website.
  2. Run the installer(s) as an administrator to install the drivers.

Adding a SQLite file as an ODBC data source

Once the SQLite ODBC Driver is installed, SQLite files can be added as ODBC data sources. Most operating systems include a graphical user interface to manage ODBC data sources. In Windows, this is called ODBC Data Source Administrator.

To add a SQLite file as an ODBC database:

Windows

  1. As an administrator, open the ODBC Data Source Administrator:
    1. Click Start>Run.
    2. Type odbcad32.
    3. Press Enter.
  2. On the User DSN page, Add a new User Data Source.
  3. Select the proper SQLite ODBC Driver for your data source:
  4. Click Finish. Then, in the Configuration window, enter the options for your data source:
    1. In the Data Source Name field, enter a name for your data source.
    2. In the Database Name field, enter the file path to your SQLite file, or click Browse and locate the file.
    3. Optional: Set the other fields according to the requirements of your data source.
  5. Click OK to close the Configuration window. Then click Apply or OK to save the data source.

Adding an ODBC data source to Exago

ODBC data sources are added to Exago in the same manner as other data sources. You can specify an optional Column Delimiter for an ODBC data source.

Either use the Administration Console or edit the Configuration File to add an ODBC data source to Exago:

Administration Console

  1. Open the Administration Console by browsing to http://.../{Exago}/Admin.aspx
  2. In the Contents pane on the left, select Sources. Then press the Add button to open a New Data Source window.
  3. In the New Data Source window, enter the options for your data source:
    1. Enter a Name for your data source.
    2. From the Type list, select odbc.
    3. Optional: Enter a Column Delimiter(s) and Schema/Owner Name.
    4. In the Connection String field, enter a connection string in the following format:

      DRIVER=driver;Database=path;[LongNames=0|1];[Timeout=timeout];[NoTXN=0|1];[SyncPragma=NORMAL|OFF|FULL];[StepAPI=0|1];

      where:
      DRIVER=driver specifies the type of SQLite ODBC Driver.
      Database=path specifies the file path to the SQLite file.

      Example

      DRIVER=SQLite3 ODBC Driver;Database=C:\Exago\databases\Northwind.sqlite;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;
  4. You can click the Test button to check whether the data source was configured correctly. If the connection is successful, click Apply or OK to save the data source.

Configuration File

  1. Open your Configuration File in a text or xml editor.
  2. Add a new <datasource></datasource> element in the <webreports> parent element.
  3. In the <datasource> element add the following fields:

    Example

    <webreports>
    ...
    <datasource>
    <id>1</id>
    <name>Northwind</name>
    <dbtype>odbc</dbtype>
    <dataconnstr>DRIVER=SQLite3 ODBC Driver;Database=C:\Exago\databases\Northwind.sqlite;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;</dataconnstr>
    </datasource>
    ...
    </webreports>
  4. Save the Configuration File.

After you add the data source, you must add the data objects and joins. See Data Objects and Joins.

Note. The Automatic Database Discovery tool is not compatible with ODBC.