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

Compare with Current View Page History

« Previous Version 17 Next »

How to Set-up a Fews JDBC Server - Viewer for a FEWS LocalDataStore

Introduction

To be able to query timeseries directly using SQL statements Delft-Fews can be set up to act as a jdbc server. This can be done using an OC configuration (which will log in and automatically syncronise date with the MC, thereby assuring all data is constantly being updated, or by running this stand-alone. In the latter case the system will only export what is in the local datastore at startup.

Fews JDBC API

Installing a FEWS JDBC Server

Windows

Step 1: Install an OC

Step 2: Delete the "Log4jConfig.xml" from the "OC" directory. When starting the application a new "Log4jConfig-JdbcServer.xml" file will be generated for logging.

Step 3: Make a new "<OC-Name>_JDBC.exe" and "<OC-Name>_JDBC.jpif" file in the \bin directory. The "<OC-Name>_JDBC.jpif" must contain the following information.

..\jre
-mx512m
-cp
$JARS_PATH$
nl.wldelft.fews.jdbc.FewsJdbcServer
<OC-Name>_JDBC

Step 4: Start the FewsJdbcServer by clicking on the <OC-Name>_JDBC.exe. The Server will start as an OC and synchronise its localDataStore with the Central Database using the synchprofiles of an OC.

Step 5: Stop the FewsJdbcServer by killing the application using the System Monitor. In the attachements an exe is provided that opens a console window. If this console window is stopped, the FEWS JDBC driver process is also stopped.

Install windows service
Follow the above listed steps to install and test the JDBC server. Finally stop the server and proceed with the next steps, based on the attached file JDBC service install.zip

Step 6: unzip the "JDBC service install.zip" to a directory at the same level as the bin and application directory, eg. like "service"
Step 7: replace in the file "run_installscript.bat" the BIN directory and the FEWs application name and directory
Step 8: run the batch file "run_installscript.bat"
Step 9: go to the services window and define the correct properties for the just installed service, like

  • automatic startup
  • correct user settings in login tab
  • restart options after 5 minutes

Notice that the batch calls the file install_JDBC_Service.bat, that contains a list of the *.jar files in the bin directory. If these filenames have changed or the list has changed, this list should be updated. If not, running the service may not be successful. Also notice that your JAVA_HOME environment variable has been set and refers to your JRE directory. This JRE directory should not contain space characters in the name. If so, make a copy of your JRE to a directory with a name without space and set in the run_installscript.bat the JAVA_HOME variable to this new path.

Linux

Step 1: Install an OC

Step 2: Delete the "Log4jConfig.xml" from the "OC" directory. When starting the application a new "Log4jConfig-JdbcServer.xml" file will be generated for logging.

Step 3: Take the fews_jdbc.sh script file and place this one level higher than the \bin directory.

Step 4: Go to the directory where the ./fews_jdbc.sh script file is located and type ./fews_jdbc.sh <OC-Name>.

Step 5: Stop the FEWS JDBC service by typing exit in the console window where the JDBC startup script was executed. An other option is to kill the process of the FEWS JDBC service.

Starting JDBC Service from FEWS Explorer

For debugging purpose it is possible to start the JDBC from the stand-alone FEWS Explorer. With the F12 key you get a list of debug options. Select "start embedded vjdbc server". The service will start and can be accessed from a database viewer.

Setting up connection in DbVisualizer

Step 1: Install DbVisualizer on your PC. Make sure it is not installed in a folder with spaces, such as "Program Files". When there is a space in the folder name, it will NOT work correctly. This is a DbVisualizer bug that can not be solved by FEWS.
Step 2: Copy the files "commons-logging-1.1.jar" and "vjdbc.jar" to a folder on your computer. These are the drivers used by DBVisualizer. Also this folder name should not contain any space characters (use the 8.3 format).
Step 3: Add a new JDBC driver to DBVisualiser:

  • Start DbVisualizer
  • Open the Tools menu and the Driver Manager
  • Create a new driver and give it the name "vjdbc". Load the two jar files in the "User Specified" tab. * Close the Driver Manager Window.

Step 4: Create a new Database Connection in DbVisualizer.

  • Give it the Alias "<OC-Name> JDBC"
  • Select the vjdbc driver
  • Enter the database URL: "jdbc:vjdbc:rmi://<host>:2000/VJdbc,FewsDataStore (under <host>, enter the machine where the fews jdbc application runs. You can get the IP adress by typing ipconfig in the command line of the Server). The number "2000" is the default port number, the correct port number is shown in the FEWS log file on the Server when it is started.

Setting up an ODBC-JDBC bridge

The FEWS JDBC Server has been tested with the Easysoft JDBC-ODBC bridge, this can be purchased. This allows the user to access the JDBC Server from other applications like Microsoft ACCESS that do only support ODBC. To use the JDBC driver with the ODBC-JDBC bridge, do the following:

  • Install the Easysoft JDBC-ODBC bridge
  • Go to the Windows Start Menu -> Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC)
  • Select the System DDS tab and add a new data source.

  • Make sure you add the vjdbc.jar and commons-logging-1.1.jar file to the classpath
  • The url is: jdbc:vjdbc:rmi://<host>:2000/VJdbc,FewsDataStore (under <host>, enter the machine where the fews jdbc application runs)

When the FEWS JDBC application runs you can test the connection using the Test button.

Using a different port number (available 200901)

By default the port number of the JDBC Server is 2000. It is possible to use a different port number when starting the application. In the global.properties a property can be added like this:

JdbcServerPort=2078

This will start the JDBC Server on port 2078.

Example SQL queries

Miscellaneous

Rolling Barrel

When the FEWS JDBC Server is started, the OC rolling barrel configuration will not be used. Instead the Rolling Barrel will run once a day at 02:00 GMT. After the FEWS Rolling Barrel, the compact Database script (only for MS ACCESS databases) will also be executed automatically.

(Java) JDBC Clients, Timezones and DayLightSaving conversion

FEWS stores timeseries with timestamp in GMT, without DayLightSaving (DLS) conversion.

JDBC Client applications like DBVisualizer adopt timezone settings from the (local) Operating System.
This means that data is converted (from FEWS GMT) to local timezone. When DLS conversion is active, a query on data from the night that DLS is switched (zomertijd to wintertijd, when clock is set back a hour) results in 'double' timeseries records between 2:00 and 3:00 AM.

The JVM for the JDBC client (like DBVisualizer) can be started with an extra commandline option, and forces timezone setting for the JVM rather tha adopting it form the local OS. This commandline option looks like:
-Duser.timezone=GMT
or
-Duser.timezone=GMT+1
or
-Duser.timezone=GMT-5
and so on...

When starting DBVisualizers JVM with -Duser.timezone=GMT results are in GMT, without DLS conversion.

Another noticeable issue:
The FEWS-JDBC Server, started as described above, in a standalone manner, has a (hardcoded) timezonesetting of GMT.
The FEWS-JDBC Server can also be started embedded from the FEWS Explorer using F12 key. In the latter case it runs in the timezone set for the FEWS Explorer!
This means that a standalone FEWS-JDBC Server and a embedded FEWS-JDBC Server started from fi. a FEWS Explorer with Dutch timezone settings, may give different timestamps on (the same) timeserie values with a shift up to 2 hours, depending on DLS conversion.

Known issues

  • java.sql.SQLException: java.net.MalformedURLException: no protocol....
    This is an exception that occurs due to a bug in DBVisualizer. Check whether DBVisualizer OR the vjdbc drivers are located in directories that contain spaces in their path. Move them to a directory path without spaces to solve this issue.
  • No labels

Disclaimer