Steps
Download and install the latest 64
Introduction
Delft-FEWS has currently three flavours of local datastores all of which can be inspected with DBVisualizer.
- MS-Access (32-bit, Windows only)
- Firebird (32-bit)
- 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.
Warning |
---|
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
...
-bit DBVisualizer from the download section of http://www.dbvis.com/.
Info - For Windows it is recommended to download the DBVisualizer
...
- 64-bit with embedded JRE. This way it is guaranteed that the firebird driver is always started with
...
- 64-bit java.
- !! Install DBVisualizer in a directory that
...
- does not contain any spaces
...
- !!
- Extract dbvis_addons.zip into
...
- the DBVisualizer installation directory
...
After installation copy connector.jar
from the FEWS bin dir (or see attachments of this page) to $DBVIS_DIR/lib.
Also the following settings in the Windows Environment variables are required.
Code Block |
---|
DBVIS_DIR=$DBVIS_DIR
PATH=.;%DBVIS_DIR%;%PATH%
|
For linux the following settings are recommended.
...
#!/bin/bash
# Point $DBVIS_DIR to the directory where DBVisualizer has been installed
DBVIS_DIR=/opt/DBVisualizer
# Start the application
$DBVIS_DIR/dbvis $*
- (e.g. jdbc subfolder and dlls are copied into the DBVisualizer installation directory).
- Start DBVisualizer (e.g. ignore / close the DriverManager).
- Import dbvis_settings.jar using File->Import settings. Restart when DBVisualizer prompts to restart.
- In the list of connections, right-Click on a suitable connection example and duplicate it.
- Edit the new connection and put in the correct connection details.
- If the connection doesn't work because of some Java class couldn't be loaded: add the DbVisualizer installation dir to the PATH system environment variable (either using Windows Explorer or using setx /m command)
Example urls for each local datastore database
Derby
jdbc:derby:d:\fews\OC\localDataStore\local.derby
Firebird
Download libraries
First download the dbvisualizer_jdbc_addons.zip zip file containing all the required additions and extract them into the $DBVIS_DIR.
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\region_home\localDataStore\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
For using DBVisualizer with Firebird under Windows specify following environment variable.
Code Block |
---|
LD_LIBRARY_PATH=$DBVIS_DIR/jdbc/firebird
|
For using DBVisualizer with Firebird under Linux include the following in the shell profile.
Code Block |
---|
# Setting the variables
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DBVIS_DIR/jdbc/firebird
|
Copy java libraries (Windows and Linux)
...
Open the driver manager window through the <Tools><Driver Manager> window
add the URL format jdbc:firebirdsql:embedded: (note: if the URL Format is not editable through the interface, please edit the dbvis.xml file which can be found in user settings e.g. c:\Users\minett\.dbvis\config70\)
Copy native libraries (Windows)
Copy the following files to the $DBVIS_DIR
directory:
fbembed.dll
icudt30.dll
icuin30.dll
icuuc30.dll
ib_util.dll
jaybird22.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).
Use the set PATH command using cmd.exe to verify whether the DBVisualizer path is available in the path so that the required dll files can be found.
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
Firebird connection properties
Specify the database url as where the Firebird local datastore is located, e.g. jdbc:firebirdsql:embedded:d:\datastoresfews\region_homeOC\localDataStore\local.fdb
...
?lc_ctype=UTF-8
Hsqldb
jdbc:
...
hsqldb:
...
file:d:\
...
fews\
...
OC\localDataStore\local.
...
Tips and tricks
- The wildcard for texts when using the LIKE statement is % (e.g. SELECT * FROM TimeSeries WHERE moduleInstanceId LIKE '%Import%')
- 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) - More scripts and suggestions can be found here: How+to+optimize+the+size+of+the+database+-+using+queries+to+determine+the+size+of+the+different+components
hsqldb\hsqldb