Overview
TimeSeries reader for a database. The general identifier for this reader is "database". This import allows a FSS or stand alone system to import data from a database. The import reads this database directly. Besides connecting to a database server with a connection string is also possible to import msaccess *.mdb (only on 32 bit Windows systems) and firebird *.fdb database files.
Configuration
This reader supports the tableMetadata element in the general section of the timeseriesImportrun:
<?xml version="1.0" encoding="UTF-8"?> <timeSeriesImportRun xmlns="http://www.wldelft.nl/fews" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.wldelft.nl/fews http://fews.wldelft.nl/schemas/version1.0/timeSeriesImportRun.xsd"> <import> <!--QPEsums Actuals and Forecast--> <general> <importTypeStandard>database</importTypeStandard> <!-- example for mysql, with non embedded db drivers in FEWS bin --> <jdbcDriverClass>com.mysql.jdbc.Driver</jdbcDriverClass> <jdbcBinDir>$REGION_HOME$\Modules\jdbc</jdbcBinDir> <jdbcConnectionString>jdbc:mysql://myhost/cwb_ac</jdbcConnectionString> <user>?</user> <encryptedPassword>?</encryptedPassword> <!--example for oracle, with embedded drivers in FEWS bin--> <jdbcDriverClass>oracle.jdbc.OracleDriver</jdbcDriverClass> <jdbcConnectionString>jdbc:oracle:thin:@<server>:<port>:<db_instance></jdbcConnectionString> <user>?</user> <encryptedPassword>?</encryptedPassword> <relativeViewPeriod startOverrulable="true" endOverrulable="true" start="-1" end="1" unit="day"/> <table name="qpe_sums_obs"> <dateTimeColumn name="recdate"/> <valueColumn name="rad_gz" unit="mm/hr" locationId="Qpesums" parameterId="P.radar.actual" parser="Mosaic"/> </table> <table name="qpe_sums_foc"> <forecastDateTimeColumn name="createdate"/> <dateTimeColumn name="raddate"/> <valueColumn name="rad_gz" unit="mm/hr" locationId="Qpesums" parameterId="P.radar.forecast" parser="Mosaic"/> </table> <unitConversionsId>ImportUnitConversions</unitConversionsId> <importTimeZone> <timeZoneOffset>+00:00</timeZoneOffset> </importTimeZone> <dataFeedId>QPE_Sums</dataFeedId> </general> <timeSeriesSet> <moduleInstanceId>Import_Qpesums</moduleInstanceId> <valueType>grid</valueType> <parameterId>P.radar.actual</parameterId> <locationId>Qpesums</locationId> <timeSeriesType>external historical</timeSeriesType> <timeStep unit="minute" multiplier="10"/> <readWriteMode>add originals</readWriteMode> <expiryTime unit="day" multiplier="14"/> </timeSeriesSet> <timeSeriesSet> <moduleInstanceId>Import_Qpesums</moduleInstanceId> <valueType>grid</valueType> <parameterId>P.radar.forecast</parameterId> <locationId>Qpesums</locationId> <timeSeriesType>external forecasting</timeSeriesType> <timeStep unit="nonequidistant"/> <readWriteMode>add originals</readWriteMode> <expiryTime unit="day" multiplier="2"/> </timeSeriesSet> </import> </timeSeriesImportRun>
userColumn
Since 2016.02 there is a column that allows for specifying a user with each data point.
<table> <dateTimeColumn name="DATE" pattern="dd-MM-yy HH:mm"/> <locationColumn name="LOC"/> <unitColumn name="UNIT"/> <parameterColumn name="PARAM"/> <valueColumn name="VALUE"/> <userColumn name="USER"/> </table>
Samples
It is not possible yet to import sample meta data. Only the values will be imported
Configuration for Microsoft SQL Server
In the above example a jdbc connection example for Oracle was given, to connect to a Microsoft SQL server use the following:
FEWS 2018 and later branches
<jdbcDriverClass>com.microsoft.sqlserver.jdbc.SQLServerDriver</jdbcDriverClass> <jdbcConnectionString>jdbc:sqlserver://hostname;DatabaseName=TestDB;integratedSecurity=true;</jdbcConnectionString>
FEWS 2017 and earlier branches
<jdbcDriverClass>net.sourceforge.jtds.jdbc.Driver</jdbcDriverClass> <jdbcConnectionString>jdbc:jtds:sqlserver://hostname;DatabaseName=TestDB;integratedSecurity=true;</jdbcConnectionString>
Configuration for importing firebird database files
To import one or more Firebird database files, you can simply configure the folder where the *.fdb files are located with the <folder> element, the configured folder should only contain firebird files as the import will loop over all files in the folder regardles of the file extensions.