| Table of Contents |
|---|
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 synchronise 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.
...
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.
| No Format |
|---|
..\jre
-mx512m
-cp
$JARS_PATH$
nl.wldelft.fews.jdbc.FewsJdbcServer
<OC-Name>_JDBC
|
...
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:
...
It is possible to have every client application (OC) to have also its own JDBC service.
Therefore you need to configure in systemConfigFiles\Explorer.xml a piServicePortRange. If configured, the OC automatically starts the JDBC service at the localhost.
| Code Block | ||||
|---|---|---|---|---|
| ||||
<piServicePortRange start="2000" end="2001"/>
|
...
You will see messages in the log panel like:
| Code Block |
|---|
INFO - FewsJdbcServer.init - JdbcServer.Info: version: 2011.02, build: 34718 Apr3, type: stable, jre: 1.6.0_29, mx: 518m, db: Firebird, region: ....
INFO - Log4JLogger.info - Starting RMI-Registry on port 2000
INFO - Log4JLogger.info - Binding remote object to 'VJdbc'
INFO - FewsJdbcServer.run - JDBC server started at <localhost>:2000
|
...
There are a number of SQL queries that can be used to retrieve data from the database. Only (read-only) statements are supported. Statements must be formatted as:
| No Format |
|---|
SELECT [DISTINCT] <select_expr> FROM TABLE_NAME [WHERE <where_condition>] [ORDER BY COLUMN_NAME [ASC |DESC]]
<select_expr>: (* | <COLUMN_NAME [, COLUMN_NAME, ...|, COLUMN_NAME, ...])
<where_condition>: COLUMN_NAME <operator> [AND <where_condition> OR <where_condition> LIKE <where_condition>]
<operator>: (= | <> | < | > <value>) | BETWEEN <value> AND <value>
|
...
A valid query for the TimeSeries Table is as follows:
| No Format |
|---|
SELECT * from TimeSeries
WHERE moduleInstanceId = 'ImportSHEF'
AND parameterId = 'FMAT'
AND locationId = 'DETO3IL'
AND valueType = 'scalar'
AND time BETWEEN '2008-12-19 12:00:00' AND '2008-12-23 12:00:00'
AND timeSeriesType = 'external forecasting'
AND timeStep = 'unit=hour multiplier=6'
|
Or, when using filter id's:
| No Format |
|---|
SELECT time, value from TimeSeries
WHERE filterId = 'ImportSHEF'
AND parameterId = 'FMAT'
AND locationId = 'DETO3IL'
AND time BETWEEN '2008-12-19 12:00:00' AND '2008-12-23 12:00:00
|
...
Example Locations queries
| No Format |
|---|
SELECT name, y,x from Locations ORDER BY name DESC
SELECT name, y,x from Locations WHERE X > '161000'
SELECT * from Locations where id = '46DP0003' OR id = '46DP0004'
SELECT name from Locations WHERE name <> 'Meerselsche Peel (WAM)'
SELECT id, name, y, x from Locations WHERE id LIKE '254%'
SELECT id, name, y, x from Locations WHERE name LIKE '%STUW%' or name LIKE '%Gemaal%'
|
...
Return all location and parameter combinations from a specific filter
| No Format |
|---|
SELECT id, locationid, parameterid FROM filters WHERE id = 'ImportSHEF' ORDER BY location
|
...
Return all locations from a specific filter
| No Format |
|---|
SELECT DISTINCT locationid FROM filters WHERE id = 'ImportSHEF'
|
...
Return a list of the main filter groups
| No Format |
|---|
SELECT DISTINCT id FROM filters WHERE issubfilter = false
|
...
The Time series can be queried with or without the Filter ID. An example of a query without using the filter ID is:
| No Format |
|---|
SELECT * from TimeSeries
WHERE moduleInstanceId = 'ImportSHEF'
AND parameterId = 'FMAT'
AND locationId = 'DETO3IL'
AND valueType = 'scalar'
AND time BETWEEN '2008-12-19 12:00:00' AND '2008-12-23 12:00:00'
AND timeSeriesType = 'external forecasting'
AND timeStep = 'unit=hour multiplier=6'
AND Value BETWEEN '1.9' AND '2.0'
|
The same query with the use of a filter ID will be as follows:
| No Format |
|---|
SELECT * from TimeSeries
WHERE filterId = 'ImportSHEF'
AND parameterId = 'FMAT'
AND locationId = 'DETO3IL'
AND time BETWEEN '2008-12-19 12:00:00' AND '2008-12-23 12:00:00'
AND Value BETWEEN '1.9' AND '2.0'
|
...
The Time series can be extracted from the database as a graph (binary obejct) through the Timeseriesgraphs table. Queries with or without the Filter ID can be used, similar to the time series table. An example of a query with the use of a filter ID is:
| No Format |
|---|
SELECT * from TimeSeriesgraphs
WHERE filterId = 'ImportSHEF'
AND parameterId = 'FMAT'
AND locationId = 'DETO3IL'
AND time BETWEEN '2008-12-19 12:00:00' AND '2008-12-23 12:00:00'
|
By default the graphs have a size of 300 (width) * 200 (height) pixels. In the SQL query the width and height can also be fixed.
| No Format |
|---|
SELECT * from TimeSeriesgraphs
WHERE filterId = 'ImportSHEF'
AND parameterId = 'FMAT'
AND locationId = 'DETO3IL'
AND time BETWEEN '2008-12-19 12:00:00' AND '2008-12-23 12:00:00'
AND height = 100 AND width = 150
|
As from 201001 it is allowed to combine data from different locations and/or parameters into one graph by 'joining' them using OR-operators. Such a clause with OR-operators must be put in between brackets:
| No Format |
|---|
SELECT * from TimeSeriesgraphs
WHERE filterId = 'ImportSHEF'
AND parameterId = 'FMAT'
AND (locationId = 'DETO3IL' OR locationId = 'DETO3IL2')
AND time BETWEEN '2008-12-19 12:00:00' AND '2008-12-23 12:00:00'
AND height = 100 AND width = 150
|
| No Format |
|---|
SELECT * from TimeSeriesgraphs
WHERE filterId = 'ImportSHEF'
AND (parameterId = 'FMAT' OR parameterId = 'FMAT2')
AND locationId = 'DETO3IL'
AND time BETWEEN '2008-12-19 12:00:00' AND '2008-12-23 12:00:00'
AND height = 100 AND width = 150
|
| No Format |
|---|
SELECT * from TimeSeriesgraphs
WHERE filterId = 'ImportSHEF'
AND (parameterId = 'FMAT' OR parameterId = 'FMAT2' OR parameterId = 'FMAT3')
AND (locationId = 'DETO3IL' OR locationId = 'DETO3IL2' OR locationId = 'DETO3IL3')
AND time BETWEEN '2008-12-19 12:00:00' AND '2008-12-23 12:00:00'
AND height = 100 AND width = 150
|
...
Example of a graph query which will plot the data in GMT-1:
| No Format |
|---|
SELECT *
FROM TimeSeriesgraphs
WHERE filterId = 'Ott_ruw'
AND parameterId = 'H.diepte'
AND (locationId = '10.H.59' OR locationId = '15.H.20')
AND time BETWEEN '2008-05-01 00:00:00' AND '2008-05-01 10:00:00'
AND height = 500 AND width = 750 AND timezone='GMT-1';
|
...
No special jars other than the ones provided by the JRE are required.
| Code Block |
|---|
Connection con = DriverManager.getConnection("jdbc:vjdbc:rmi://localhost:2000/VJdbc,FewsDataStore");
//example get Locations
Statement stmt = con.createStatement();
ResultSet set = stmt.executeQuery("SELECT * from Locations");
while (set.next()) {
System.out.print("Name:" + set.getString("name"));
System.out.print(", Id:" + set.getString("Id"));
System.out.print(", X:" + set.getString("x"));
System.out.print(", y:" + set.getString("y") + "\n");
}
|
...