Using the Database

Creating a Data Provider

This section describes how to add a new database using the console and test its connection. As a result, when you write a tap iFL expression, instead of passing db as the database implementation, you can now point to the new data provider that was configured. You also have the option of using many data providers and have events persist in different databases. iWay Enable will be able to aggregate event data across multiple databases.

Top of page

Procedure: How to Create a Data Provider

To create a data provider:

Open the iWay Service Manager Administration Console.
In the left console pane of the Server menu, select Data Provider
The Data Provider pane opens, as shown in the following image.
Click Add in the JDBC section.
Provide the required configuration parameters and test the connection.
For more information on creating data providers, see the iWay Service Manager User’s Guide.

Note: If required, you can use multiple data providers with iWay Enable events. Once the data provider is available you can use it during the configuration of your _EXPOSE() iFL function instead of using db as the default data provider.

Overriding Data Providers

Once you have written and activated a tap, you have the ability to override the data provider that is specified in the tap by changing the data provider name for a specific event on the Events tab, as shown in the following image.

The Data Provider column indicates which data provider is being used for that event. If the value is empty or contains db, the in-memory database (HSQL) is being used.

Setting Datatypes

When exposing an event using the _EXPOSE() iFL function, you have the ability to override the data type used to save the Fact that is attached to the event. A Fact is a piece of data associated to a specific event.

To expose an event (for example, enable.enable) you can execute the following iFL function:

_EXPOSE(enable.enable,1,db,int)

where:

enable.enable: Is the event.

1: Is the Fact being used.

db: Is the default in-memory database (HSQL)

int: Is the data type being used by the Fact.

This function will construct the following SQL statement:

INSERT INTO ENABLE_ENABLE VALUES (currentTimestamp, 1)

where:

currentTimestamp: Is set by iWay Enable.

If the table does not yet exist, iWay Enable will execute the following SQL statement:

CREATE TABLE ENABLE_ENABLE ( "TIME" TIMESTAMP NOT NULL, "VALUE" INT )

The table name is the same as the event name (all non-alpha characters are changed to underscore characters) and the value data type being INT.

You can also expose the event by removing the last two parameters:

_EXPOSE(enable.enable,1)

iWay Enable will check the metadata for the event to determine the data type of the Fact and the storage provider.

Note: If the event table has already been created and you change the data type of the Fact, iWay Enable will drop the existing event table and recreate it using the new data type. All previous real-time data that has been accumulated for this event will be lost.

Overriding SQL Statements

iWay Enable uses a set of SQL calls to capture and query event data. Some databases require the native syntax when creating tables or updating or querying data.

The following is a sample properties file that can be named enable.properties or enabled.properties. The file overrides key SQL words so that iWay Enable can work properly with the database.

Important: Make sure that the enable.properties file is included in the Classpath for iWay Service Manager.

#WHEN OVERRIDING THE DEFAULT DATABASE FOR
#REAL-TIME EVENTS, YOU CAN OVERRIDE THE
#DATATYPES TO THE TYPE NATIVE TO 
#YOUR DATABASE IMPLEMENTATION
#---------------------------------------
#mydataprovider.string=string
#mydataprovider.int=integer
#mydataprovider.intgter=integer
#mydataprovider.float=float
#mydataprovider.long=long
#mydataprovider.double=double
#mydataprovider.date=date
#mydataprovider.timestamp=timestamp
#mydataprovider.ddl=CREATE TABLE ? ( "TIME" TIMESTAMP NOT NULL, "VALUE" ?  )
#mydataprovider.existscode=-1111

#THIS SECTION IS AN EXAMPLE OF HOW TO
#OVERRIDE DATATYPES AND TABLE CREATION
#WHEN USING AN ORACLE DATA PROVIDER WHOSE
#ALIAS IS NAMED ora
#---------------------------------------
#OVERRIDE THE DOUBLE DATATYPE
ora.double=decimal 
#OVERRIDE STRING DATATYPE
ora.string=VARCHAR (256) 
#OVERRIDE CREATION OF EVENT TABLES
ora.ddl=CREATE TABLE ? ( "TIME" TIMESTAMP NOT NULL, "VALUE" ? ) 
#OVERRIDE THE TABLE CREATION ERROR HANDLING BY PROVIDING
#THE ERROR CODE FOR TABLE EXISTS
ora.existscode=955

#THIS SECTION IS AN EXAMPLE OF HOW TO
#OVERRIDE DATATYPES AND TABLE CREATION
#WHEN USING A SYBASE DATA PROVIDER WHOSE
#ALIAS IS NAMED jconn3
#---------------------------------------
#OVERRIDE THE TABLE CREATION ERROR HANDLING BY PROVIDING
#THE ERROR CODE FOR TABLE EXISTS
jconn3.ddl=CREATE TABLE ? ( "TIME" SMALLDATETIME NOT NULL, "VALUE" ? ) 
#OVERRIDE STRING DATATYPE
jconn3.string=VARCHAR (254) 
#OVERRIDE THE DOUBLE DATATYPE
jconn3.double=decimal 
#OVERRIDE THE TIMESTAMP DATATYPE
jconn3.timestamp=SMALLDATETIME 
#OVERRIDE THE TABLE CREATION ERROR HANDLING BY PROVIDING
#THE ERROR CODE FOR TABLE EXISTS
jconn3.existscode=2714
 
#THIS SECTION IS AN EXAMPLE OF HOW TO
#OVERRIDE DATATYPES AND TABLE CREATION
#WHEN USING AN ORACLE JDBC DRIVER
#---------------------------------------

#OVERRIDE THE DOUBLE DATATYPE
oracle.jdbc.driver.OracleDriver.double=decimal 
#OVERRIDE CREATION OF EVENT TABLES
oracle.jdbc.driver.OracleDriver.ddl=CREATE TABLE ?
      ( "TIME" TIMESTAMP NOT NULL, "VALUE" ? ) 
#THIS SECTION IS AN EXAMPLE OF HOW TO
#OVERRIDE DATATYPES AND TABLE CREATION 
#WHEN USING AN SYBASE JDBC DRIVER
#---------------------------------------
com.sybase.jdbc3.jdbc.SybDriver.ddl=CREATE TABLE ?
      ( "TIME" SMALLDATETIME NOT NULL, "VALUE" ? )