You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 45 Next »

Introduction

Delft-FEWS has currently three flavours of local datastores all of which can be inspected with DBVisualizer.

  1. MS-Access (32-bit, Windows only)
  2. Firebird (32-bit)
  3. Derby

For inspecting a Delft-FEWS local datastore it is recommended to install DBVisualizer and the appropriate Jdbc driver that matches the local datastore format.

Note that only one program can access the local datastore simultaneously. The Delft-FEWS client has to be disconnected first before DBVisualizer can open it, and vice versa, otherwise exceptions will occur.

Installation of DBVisualizer

Download the latest 32-bit DBVisualizer from the download section of http://www.dbvis.com/. For Windows it is recommended to download the DBVisualizer with embedded JRE. This way it is guaranteed that the firebird driver is always started with 32-bit java. Install DBVisualizer in a directory that preferrably does not contain any spaces. On this page from now we will refer from now on to $DBVIS_DIR as the DBVisualizer installation directory.

Also the following settings in the Windows Environment variables are required.

DBVIS_DIR=$DBVIS_DIR
PATH=.;%DBVIS_DIR%;%PATH%

Installation of the MS Access driver

Select the Jdbc/ODBC bridge and specify the windows jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=d:\datastores\region_home\localDataStore\local.mdb

where d:\datastores\local.mdb is where the MSAccess file is located.

Installation of the Derby Jdbc Driver

If needed copy the latest derby driver from the Delft-FEWS bin dir, e.g. derby-10.8.1.2.jar to the $DBVIS_DIR/jdbc/derby directory.

Specify the database url as where the derby local datastore is located, e.g. jdbc:derby:D:/datastores/region_home/localDataStore/local.derby

Installation of the Firebird Jdbc Driver

LD_LIBRARY_PATH=$DBVIS_DIR/jdbc/firebird
FIREBIRD=$DBVIS_DIR/jdbc/firebird

 

Copy java libraries (Windows and Linux)

  • create the directory $DBVIS_DIR/jdbc/firebird
  • copy connector.jar to $DBVIS_DIR/lib (see above)
  • copy jaybird-2.1.6p.jar $DBVIS_DIR/jdbc/firebird

Copy native libraries (Windows)

Copy the following files to the $DBVIS_DIR directory:
fbembed.dll
icudt30.dll
icuin30.dll
icuuc30.dll
ib_util.dll
jaybird21.dll
(NB fbembed.dll depends on MSVCR80.dll }}while {{jaybird21.dll depends on MSVCR71.dll. On some computers you must need to include those also when not available. See the Fews binary or the attachments)

All in one zip file firebird_jdbc_win.zip for Windows.

URL: jdbc:firebirdsql:embedded:<filename.fdb>
e.g. jdbc:firebirdsql:embedded:d:\FEWS\testfews\EFAS\localDataStore\local.fdb

Copy native libraries and additional files (Linux)

Copy the following files to the $DBVIS_DIR/jdbc/firebird directory:
firebird.conf
firebird.msg
libfbdrop.so
libfbembed.so
libib_util.so
libicudata.so.30
libicui18n.so.30
libicuuc.so.30
libjaybird21.so
security2.fdb

All in one tgz file firebird_jdbc_linux.tgz for Linux.

Note: For this to work, any user wanting to access a firebird localdatastore needs to have write access to the directory where these files are located. Alternatively, the user can create symbolic links to or copies of there files in another directory and set the environment variables below accordingly to point to these "writeable" directories.

Set environment variables (Windows)

The Firebird drivers may need the following two environment variables set; replace $DBVIS_DIR with the directory where DBVisualizer has been installed.

On some Windows systems the altered Windows environment variables only become active after a restart.

Set environment variables (Linux)

The Firebird drivers need the following two environment variables set; replace $DBVIS_DIR with the directory where DBVisualizer has been installed.

This can be easily done a wrapper script which calls DBVisualizer, e.g.

#!/bin/bash


# Point $DBVIS_DIR to the directory where DBVisualizer has been installed
DBVIS_DIR=/opt/DBVisualizer

# Setting the variables
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DBVIS_DIR/jdbc/firebird
export FIREBIRD=$DBVIS_DIR/jdbc/firebird

# Start the application
$DBVIS_DIR/dbvis $*

Starting DBVisualizer with the correct java version

Make sure that the 32 bit java version is used by DBVIS as with the 64bit version it does not work
If necessary edit and use dbvisgui.bat.
set PATH=%PATH%;d:\DbVisualizer
set JAVA_EXEC=d:\DbVisualizer\jre\bin\java

Connection details

username: sysdba
password: masterkey

Only one process can access the local.fdb file at one time. To look at e.g. an OC localdatastore, exit the OC before accessing the localdatastore with DBVisualizer and vice versa.

Tips and tricks

  • (wink) The wildcard for texts when using the LIKE statement is % (e.g. SELECT * FROM TimeSeries WHERE moduleInstanceId LIKE '%Import%')
  • (wink) When looking for a date in an SQL string - use CAST('yyyy-MM-dd HH:mm:ss' as TIMESTAMP)
    (this is equivalent to the ORACLE to_Date command)

 

  • No labels

Disclaimer