How to access Firebird database file from TFS4JIRA Synchronizer

Owned by Paweł Mazur
Dec 18, 2020
2 min read

Mac OS instructions

Connecting to the database using isql (Firebird Interactive SQL Utility)

  1. Download & install Firebird 3.0.5 https://firebirdsql.org/en/firebird-3-0.

    1. At the time I’m writing this page the Firebird series 3.0 is the latest one available. Do not install Firebird 2.0 series, it is not compatible and does not work with our embedded Firebird database version.

  2. The Firebird installation will be located inside the following directory /Library/Frameworks/Firebird.framework.

  3. In order to use the command-line Firebird Administration program isql, you need to make the binary file available to your Terminal session by adding it to your PATH. You can do that by editing your .bash_profile or .profile file in your home directory, appending the following lines:

    1. export FIREBIRD_HOME=/Library/Frameworks/Firebird.framework/Resources export PATH=$PATH:$FIREBIRD_HOME/bin

  4. Restart your Terminal session and navigate to the folder where your Firebird database file is located.

  5. Use the following isql command to access the database file:

    1. sudo isql -user <USER> -password <PASSWORD> <DATABASE_FILE_NAME>

    2. It is important to use sudo to avoid running into permission problems when attempting to open the file with isql.

    3. You’ll find the credentials to open the database file inside the Web.config file. This is the file from where the TFS4JIRA Synchronizer is reading the database connection details.

      1. Replace <USER> with sysdba

      2. Replace <PASSWORD> with masterkey

    4. Finally, we need to point to the database file.

      1. If you accessed the directory where the file is located as suggested in step 4, you can simply put the file name there.

        1. Replace <DATABASE_FILE_NAME> with CONFIG.FDB

      2. If you are not in the same directory where the file is located you’ll need the full path to the file.

        1. Replace <DATABASE_FILE_NAME> with full/path/to/CONFIG.FDB

    5. Here’s an example of what the isql will look like:

      1. sudo isql -user sysdba -password masterkey CONFIG.FDB

Running SQL commands

  1. Once you’ve connected to the Firebird database file using the steps mentioned above, you’ll see that you have entered the SQL session. Here’s an example of what you should see in your Terminal:

    1. brosa$ sudo isql -user sysdba -password masterkey /Users/brosa/Downloads/CONFIG.FDB Database: /Users/brosa/Downloads/CONFIG.FDB, User: SYSDBA SQL>

  2. From this moment on you can start running SQL commands. A few important things to keep in mind when building an SQL statement:

    1. Table names and column names should be enclosed by double quotes.

    2. Values should be enclosed by single quotes.

  3. Here are a few useful commands:

    1. SQL> show tables;

      1. This will display all tables inside the TFS4JIRA Synchronizer database.

    2. select "Name" from "SyncProfiles";

      1. This will give you a list of all sync profiles an their names.

    3. select * from "SyncProfiles" where "Name" = 'Test';

      1. This will give you all details from the sync profile called Test (enclosed by single quotes because this is the name of the sync profile) in the SyncProfiles table.

    4. update "SyncProfiles" set "InitialSyncStatusInt" = 0 where "Name" = 'Test';

      1. This will set the status of the initial sync for the Test sync profile in the sync profiles table to 0.

Additional information & reading