Database Output Adapter

Use a Database Output adapter to send data to an external database.

Prerequisites

To run this example, create a VwapWindow table in your database using the supported syntax. The table should include these values:

Column	Datatype	Value
Symbol	char(4)	not null
Price	money	not null

You must also create a unique index named ind1 on Trades (Ts) and grant all permissions on VwapWindow to public.

Finally, configure the services.xml file in <ESP_HOME>/bin using the following example as a model for configuration:

<Service Name="dbExample" Type="DB">

                <Parameter Name="DriverLibrary">esp_db_jdbc_sybase_lib</Parameter>
                <Parameter Name="Host">mydbserver</Parameter>
                <Parameter Name="Port">5000</Parameter>
                <Parameter Name="User">test4</Parameter>
                <Parameter Name="Password">password</Parameter>
                <Parameter Name="Database">interpubs</Parameter>
                <Parameter Name="ConnectString"></Parameter>
                <Parameter Name="ConnectionPoolSize">-1</Parameter>
        </Service>

The table is automatically populated with data from the File CSV Input adapter.

Example

The example creates a schema named TradeSchema, followed by an input window named TradeWindow that references TradeSchema.

The example creates an aggregate output window named VwapWindow, in which the volume weighted average price is returned for TradeWindow data. The return values are grouped by Symbol.

CREATE   output WINDOW VwapWindow
SCHEMA (Symbol STRING, vwap MONEY(2))
 PRIMARY KEY DEDUCED 
  AS 
SELECT TradeWindow.Symbol AS Symbol, 
((SUM(TradeWindow.Price * TradeWindow.Volume)) / (SUM(TradeWindow.Volume))) AS vwap
FROM TradeWindow
GROUP BY TradeWindow.Symbol;

The example attaches a Database Output adapter to VwapWindow. The project server processes date values in date format, which means date values are truncated.

ATTACH OUTPUT ADAPTER dbOutConn1 TYPE db_out TO VwapWindow 
PROPERTIES service = 'dbExample' , 
  table = 'VwapWindow' , outputBase = FALSE , truncateTable = TRUE , dateFormat = '%Y-%m-%d %H:%M:%S' ,timestampFormat = '%Y-%m-%d %H:%M:%S' , onlyBase = FALSE , batchLimit =1 ;

The example attaches a File CSV Input adapter to TradeWindow to read data from an external source and populate the database you set up as a prerequisite.

 ATTACH INPUT ADAPTER csvInConn1
 TYPE dsv_in
 TO TradeWindow
 PROPERTIES 
blockSize=1, 
dateFormat='%Y/%m/%d %H:%M:%S', 
delimiter=',', 
dir='../exampledata', 
expectStreamNameOpcode=false, 
fieldCount=0, 
file='stock-trades.csv', 
filePattern='*.csv', 
hasHeader=true, 
safeOps=false, 
skipDels=false, 
timestampFormat= '%Y/%m/%d %H:%M:%S';