RealTimeToDB Database Configuration

RealTimeToDB Database Configuration

RealTimeToDB updates database table fields with real-time values from real-time servers.

The field configuration table is used to configure what fields must be updated and how.

The Microsoft Excel formula format is used to configure. It's easy.

You may copy formulas from Microsoft Excel workbooks.

Also, you may change behavior for certain tables using the table configuration table.

RealTimeToDB loads the configurations on the Start operation.

You may check loading progress and possible errors in the message panel of the main form.

Field Configuration Table

RealTimeToDB reads the field configuration from the table that contains the following fields:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. COLUMN_NAME
  4. RTD_FORMULA
  5. IS_DISABLED

The table can contain an additional primary key first column.

Configuration data formats:

TABLE_SCHEMATABLE_NAMECOLUMN_NAMERTD_FORMULAIS_DISABLED
<Table schema><Table name><Column name><RTD or DDE formula>1/0/NULL

The TABLE_SCHEMA and TABLE_NAME fields specify the database table.

The COLUMN_NAME field specifies the column of database tables.

The RTD_FORMULA column specifies RTD or DDE formula used to get real-time data.

The IS_DISABLED field allows disabling field updating. Use this if you do not want to delete the row.

Formula format is very similar to Microsoft Excel format.

However, the formulas must contain parameter columns in square brackets instead of real tickers.

Below the example of the configuration table:

TABLE_SCHEMATABLE_NAMECOLUMN_NAMERTD_FORMULA
rtdQuotesESCode
rtdQuotesESSymbol=RTD("esrtd",,[Code],"Symbol")
rtdQuotesESTime=RTD("esrtd",,[Code],"Time")
rtdQuotesESLast=RTD("esrtd",,[Code],"Last")
rtdQuotesESChange=RTD("esrtd",,[Code],"Change")
rtdQuotesESPercentChange=RTD("esrtd",,[Code],"% Change")
rtdQuotesESOpen=RTD("esrtd",,[Code],"Open")
rtdQuotesESHigh=RTD("esrtd",,[Code],"High")
rtdQuotesESLow=RTD("esrtd",,[Code],"Low")
rtdQuotesESVolume=RTD("esrtd",,[Code],"Volume")
rtdQuotesTOSSymbol
rtdQuotesTOSTime=Time()
rtdQuotesTOSLast=TOS|LAST![Symbol]
rtdQuotesTOSChange=TOS|NET_CHANGE![Symbol]
rtdQuotesTOSPercentChange=TOS|PERCENT_CHANGE![Symbol]
rtdQuotesTOSOpen=TOS|OPEN![Symbol]
rtdQuotesTOSHigh=TOS|HIGH![Symbol]
rtdQuotesTOSLow=TOS|LOW![Symbol]
rtdQuotesTOSClose=TOS|CLOSE![Symbol]
rtdQuotesTOSVolume=TOS|VOLUME![Symbol]

The columns of the rtd.QuotesES table are updated from the eSignal FutureSource real-time RTD server ("esrtd").

The columns of the rtd.QuotesTOS table are updated from the Thinkorswim thinkDesktop real-time DDE server ("TOS").

rtd.QuotesES contains the Code column that is used as a parameter.
The Code column belongs to a task table (see below) and does not exist in rtd.QuotesES.
However, such parameter fields must be specified in the field configuration table to load parameter columns into the table column set.

rtd.QuotesTOS contains the Symbol column that is used as a parameter.

rtd.QuotesTOS contains the Time column calculated by RealTimeToDB as TOS have no time fields.
See the complete list of built-in functions.

Table Configuration Table

RealTimeToDB reads the table configuration from the table that contains the following fields:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. LOAD_CODE
  4. IS_HISTORY
  5. IS_DISABLED
  6. TIME_ZONE
  7. RTD_INSTANCE_NAME

The table can contain an additional primary key first column.

Configuration data formats:

TABLE_SCHEMATABLE_NAMELOAD_CODEIS_HISTORYIS_DISABLEDTIME_ZONERTD_INSTANCE_NAME
<Table schema><Table name><SQL command>1/0/NULL1/0/NULL<Time Zone><Instance Name>

The TABLE_SCHEMA and TABLE_NAME fields specify the database table.

The LOAD_CODE field allows changing the default code to load initial data from databases into RealTimeToDB.

The default code is

SELECT <COLUMNS> FROM <TABLE_SCHEMA>.<TABLE_NAME>

where <COLUMNS> are primary key columns, and real-time and formula parameter columns from the field configuration table.

You may change the default code to load the task data from other tables.

For example:

SELECT DISTINCT Symbols FROM dbo.WatchList UNION SELECT DISTINCT Symbols FROM dbo.Portfolio

The IS_HISTORY field allows switching on the history mode.

In this mode, RealTimeToDB uses insert commands to update data first, and then uses the update command if the inserting has failed.

The IS_DISABLED field allows turning off the table update.

The TIME_ZONE field defines the time zone of the table. This time zone is used to adjust the current PC time in built-in functions like =Time().

Specify 'Eastern Standard Time' for US exchanges.
See available time zones at http://technet.microsoft.com/en-us/library/cc749073(v=ws.10).aspx.

The RTD_INSTANCE_NAME field specifies RealTimeToDB instances that must update this table. Other instances ignore such tables.

The default instance names are x86 for the 32-bit and x64 for 64-bit RealTimeToDB executables.
User defined names are supported in the RealTimeToDB Enterprise edition and can be configured in the application configuration files.

Creating Configuration Tables

The RealTimeToDB setup package contains the RTD Database source codes for supported databases platforms.
It is the fastest way to create database tables and ready-to-used configuration tables for several real-time data providers.

You may also use the SQL code below to create configuration tables in your databases.

Configuration Tables for Microsoft SQL Server

You may use the following SQL codes to create configuration tables in Microsoft SQL Server databases:

CREATE TABLE [rtd].[RealTimeFormulas](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128) NOT NULL,
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [COLUMN_NAME] [nvarchar](128) NOT NULL,
    [RTD_FORMULA] [nvarchar](255) NULL,
    [IS_DISABLED] [tinyint] NULL,
 CONSTRAINT [PK_RealTimeFormulas_rtd] PRIMARY KEY ([ID] ASC),
 CONSTRAINT [IX_RealTimeFormulas_Schema_Name_ColumnName_rtd] UNIQUE
(
    [TABLE_SCHEMA] ASC,
    [TABLE_NAME] ASC,
    [COLUMN_NAME] ASC
)
);
GO

CREATE TABLE [rtd].[RealTimeTables](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128) NOT NULL,
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [LOAD_CODE] [nvarchar](2000) NULL,
    [IS_HISTORY] [tinyint] NULL,
    [IS_DISABLED] [tinyint] NULL,
    [TIME_ZONE] [nvarchar](50) NULL,
    [RTD_INSTANCE_NAME] [nvarchar](50) NULL,
 CONSTRAINT [PK_RealTimeTables_rtd] PRIMARY KEY ([ID] ASC),
 CONSTRAINT [IX_RealTimeTables_Schema_Name_rtd] UNIQUE
(
    [TABLE_SCHEMA] ASC,
    [TABLE_NAME] ASC
)
);
GO

Configuration Tables for Microsoft SQL Server Compact

You may use the following SQL codes to create configuration tables in Microsoft SQL Server Compact databases:

CREATE TABLE [RealTimeFormulas](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128) NULL,
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [COLUMN_NAME] [nvarchar](128) NOT NULL,
    [RTD_FORMULA] [nvarchar](255) NULL,
    [IS_DISABLED] [tinyint] NULL,
 CONSTRAINT [PK_RealTimeFormulas] PRIMARY KEY ([ID]),
 CONSTRAINT [IX_RealTimeFormulas_Name_ColumnName] UNIQUE
(
    [TABLE_NAME],
    [COLUMN_NAME]
)
);
GO

CREATE TABLE [RealTimeTables](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128) NULL,
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [LOAD_CODE] [nvarchar](2000) NULL,
    [IS_HISTORY] [tinyint] NULL,
    [IS_DISABLED] [tinyint] NULL,
    [TIME_ZONE] [nvarchar](50) NULL,
    [RTD_INSTANCE_NAME] [nvarchar](50) NULL,
 CONSTRAINT [PK_RealTimeTables] PRIMARY KEY ([ID]),
 CONSTRAINT [IX_RealTimeTables_Schema_Name] UNIQUE
(
    [TABLE_NAME]
)
);
GO

Configuration Tables for MySQL and MariaDB

You may use the following SQL codes to create configuration tables in MySQL and MariaDB databases:

CREATE TABLE IF NOT EXISTS `rtd`.`real_time_formulas` (
    `ID` INTEGER NOT NULL AUTO_INCREMENT,
    `TABLE_SCHEMA` VARCHAR(128) NOT NULL,
    `TABLE_NAME` VARCHAR(128) NOT NULL,
    `COLUMN_NAME` VARCHAR(128) NOT NULL,
    `RTD_FORMULA` VARCHAR(255) NULL,
    `IS_DISABLED` TINYINT NULL,
  PRIMARY KEY (`ID`)
);

ALTER TABLE `rtd`.`real_time_formulas`
    ADD UNIQUE INDEX `ix_real_time_formulas_schema_name_column_name`
    USING BTREE (
       `TABLE_SCHEMA`,
       `TABLE_NAME`,
       `COLUMN_NAME`
    );


CREATE TABLE IF NOT EXISTS `rtd`.`real_time_tables` (
    `ID` INTEGER NOT NULL AUTO_INCREMENT,
    `TABLE_SCHEMA` VARCHAR(128) NOT NULL,
    `TABLE_NAME` VARCHAR(128) NOT NULL,
    `LOAD_CODE` VARCHAR(2000) NULL,
    `IS_HISTORY` TINYINT NULL,
    `IS_DISABLED` TINYINT NULL,
    `TIME_ZONE` VARCHAR(50) NULL,
    `RTD_INSTANCE_NAME` VARCHAR(50) NULL,
  PRIMARY KEY (`ID`)
);

ALTER TABLE `rtd`.`real_time_tables`
    ADD UNIQUE INDEX `ix_realtimetables_schema_name`
    USING BTREE (
       `TABLE_SCHEMA`,
       `TABLE_NAME`
    );

Configuration Tables for Oracle Database

You may use the following SQL codes to create configuration tables in Oracle Database databases:

CREATE TABLE "RTD"."REAL_TIME_FORMULAS" (
    "ID" NUMBER NOT NULL,
    "TABLE_SCHEMA" VARCHAR2(128) NOT NULL,
    "TABLE_NAME" VARCHAR2(128) NOT NULL,
    "COLUMN_NAME" VARCHAR2(128) NOT NULL,
    "RTD_FORMULA" VARCHAR2(255) NULL,
    "IS_DISABLED" NUMBER(1) NULL,
  CONSTRAINT "PK_REAL_TIME_FORMULAS"
    PRIMARY KEY ("ID"),
  CONSTRAINT "IX_REAL_TIME_FORMULAS_COLUMN"
    UNIQUE ("TABLE_SCHEMA", "TABLE_NAME", "COLUMN_NAME")
);

CREATE SEQUENCE "RTD"."SQ_REAL_TIME_FORMULAS_ID"
  INCREMENT BY 1
  START WITH 1
  MAXVALUE 1E28
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;

CREATE TRIGGER "RTD"."TRG_REAL_TIME_FORMULAS_ID"
  BEFORE INSERT OR UPDATE
  ON "RTD"."REAL_TIME_FORMULAS"
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
  IF :new.ID IS NULL THEN
    SELECT SQ_REAL_TIME_FORMULAS_ID.NEXTVAL INTO :new.ID FROM dual;
  END IF;
END;
/

CREATE TABLE "RTD"."REAL_TIME_TABLES" (
    "ID" NUMBER NOT NULL,
    "TABLE_SCHEMA" VARCHAR2(128) NOT NULL,
    "TABLE_NAME" VARCHAR2(128) NOT NULL,
    "LOAD_CODE" VARCHAR2(2000) NULL,
    "IS_HISTORY" NUMBER(1) NULL,
    "IS_DISABLED" NUMBER(1) NULL,
    "TIME_ZONE" VARCHAR2(50) NULL,
    "RTD_INSTANCE_NAME" VARCHAR2(50) NULL,
  CONSTRAINT "PK_REAL_TIME_TABLES"
    PRIMARY KEY ("ID"),
  CONSTRAINT "IX_REAL_TIME_TABLES_TABLE"
    UNIQUE ("TABLE_SCHEMA", "TABLE_NAME")
);

CREATE SEQUENCE "RTD"."SQ_REAL_TIME_TABLES_ID"
  INCREMENT BY 1
  START WITH 1
  MAXVALUE 1E28
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;

CREATE TRIGGER "RTD"."TRG_REAL_TIME_TABLES_ID"
  BEFORE INSERT OR UPDATE
  ON "RTD"."REAL_TIME_TABLES"
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
BEGIN
  IF :new.ID IS NULL THEN
    SELECT SQ_REAL_TIME_TABLES_ID.NEXTVAL INTO :new.ID FROM dual;
  END IF;
END;
/

Configuration Tables for IBM DB2

You may use the following SQL codes to create configuration tables in IBM DB2 databases:

CREATE TABLE "RTD"."REAL_TIME_FORMULAS" (
    "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "TABLE_SCHEMA" VARCHAR(128) NOT NULL,
    "TABLE_NAME" VARCHAR(128) NOT NULL,
    "COLUMN_NAME" VARCHAR(128) NOT NULL,
    "RTD_FORMULA" VARCHAR(255) NULL,
    "IS_DISABLED" SMALLINT NULL,
  CONSTRAINT "PK_REAL_TIME_FORMULAS"
    PRIMARY KEY ("ID"),
  CONSTRAINT "IX_REAL_TIME_FORMULAS_COLUMN"
    UNIQUE ("TABLE_SCHEMA", "TABLE_NAME", "COLUMN_NAME")
);


CREATE TABLE "RTD"."REAL_TIME_TABLES" (
    "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "TABLE_SCHEMA" VARCHAR(128) NOT NULL,
    "TABLE_NAME" VARCHAR(128) NOT NULL,
    "LOAD_CODE" VARCHAR(2000) NULL,
    "IS_HISTORY" SMALLINT NULL,
    "IS_DISABLED" SMALLINT NULL,
    "TIME_ZONE" VARCHAR(50) NULL,
    "RTD_INSTANCE_NAME" VARCHAR(50) NULL,
  CONSTRAINT "PK_REAL_TIME_TABLES"
    PRIMARY KEY ("ID"),
  CONSTRAINT "IX_REAL_TIME_TABLES_TABLE"
    UNIQUE ("TABLE_SCHEMA", "TABLE_NAME")
);

Configuration Tables for NuoDB

You may use the following SQL codes to create configuration tables in NuoDB databases:

CREATE TABLE "RTD"."REAL_TIME_FORMULAS" (
    "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "TABLE_SCHEMA" VARCHAR(128) NOT NULL,
    "TABLE_NAME" VARCHAR(128) NOT NULL,
    "COLUMN_NAME" VARCHAR(128) NOT NULL,
    "RTD_FORMULA" VARCHAR(255) NULL,
    "IS_DISABLED" SMALLINT NULL,
    PRIMARY KEY ("ID")
);

CREATE UNIQUE INDEX "IX_REAL_TIME_FORMULAS_COLUMN"
    ON "RTD"."REAL_TIME_FORMULAS" ("TABLE_SCHEMA", "TABLE_NAME", "COLUMN_NAME");

CREATE TABLE "RTD"."REAL_TIME_TABLES" (
    "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "TABLE_SCHEMA" VARCHAR(128) NOT NULL,
    "TABLE_NAME" VARCHAR(128) NOT NULL,
    "LOAD_CODE" STRING NULL,
    "IS_HISTORY" SMALLINT NULL,
    "IS_DISABLED" SMALLINT NULL,
    "TIME_ZONE" VARCHAR(50) NULL,
    "RTD_INSTANCE_NAME" VARCHAR(50) NULL,
    PRIMARY KEY ("ID")
);

CREATE UNIQUE INDEX "IX_REAL_TIME_TABLES_TABLE"
    ON "RTD"."REAL_TIME_TABLES" ("TABLE_SCHEMA", "TABLE_NAME");

Configuration Tables for PostgreSQL

You may use the following SQL codes to create configuration tables in PostgreSQL databases:

CREATE TABLE IF NOT EXISTS rtd.real_time_formulas (
    id serial NOT NULL,
    table_schema varchar(128) NOT NULL,
    table_name varchar(128) NOT NULL,
    column_name varchar(128) NOT NULL,
    rtd_formula varchar(255) NULL,
    is_disabled smallint NULL,
  PRIMARY KEY (id)
);

CREATE UNIQUE INDEX ix_real_time_formulas_schema_name_column_name ON rtd.real_time_formulas
    USING BTREE (
       table_schema,
       table_name,
       column_name
    );


CREATE TABLE IF NOT EXISTS rtd.real_time_tables (
    id serial NOT NULL,
    table_schema varchar(128) NOT NULL,
    table_name varchar(128) NOT NULL,
    load_code varchar(2000) NULL,
    is_history smallint NULL,
    is_disabled smallint NULL,
    time_zone varchar(50) NULL,
    rtd_instance_name varchar(50) NULL,
  PRIMARY KEY (id)
);

CREATE UNIQUE INDEX ix_realtimetables_schema_name ON rtd.real_time_tables
    USING BTREE (
       table_schema,
       table_name
    );
This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.OK