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 can copy formulas from Microsoft Excel workbooks.

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

RealTimeToDB loads the configurations on the Start operation.

You can 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:

TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
RTD_FORMULA
IS_DISABLED

The table can contain an additional primary key first column.

Configuration data formats:

TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	RTD_FORMULA	IS_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_SCHEMA	TABLE_NAME	COLUMN_NAME	RTD_FORMULA
`rtd`	QuotesES	Code
`rtd`	QuotesES	Symbol	`=RTD("esrtd",,[Code],"Symbol")`
`rtd`	QuotesES	Time	`=RTD("esrtd",,[Code],"Time")`
`rtd`	QuotesES	Last	`=RTD("esrtd",,[Code],"Last")`
`rtd`	QuotesES	Change	`=RTD("esrtd",,[Code],"Change")`
`rtd`	QuotesES	PercentChange	`=RTD("esrtd",,[Code],"% Change")`
`rtd`	QuotesES	Open	`=RTD("esrtd",,[Code],"Open")`
`rtd`	QuotesES	High	`=RTD("esrtd",,[Code],"High")`
`rtd`	QuotesES	Low	`=RTD("esrtd",,[Code],"Low")`
`rtd`	QuotesES	Volume	`=RTD("esrtd",,[Code],"Volume")`
`rtd`	QuotesTOS	Symbol
`rtd`	QuotesTOS	Time	`=Time()`
`rtd`	QuotesTOS	Last	`=TOS\|LAST![Symbol]`
`rtd`	QuotesTOS	Change	`=TOS\|NET_CHANGE![Symbol]`
`rtd`	QuotesTOS	PercentChange	`=TOS\|PERCENT_CHANGE![Symbol]`
`rtd`	QuotesTOS	Open	`=TOS\|OPEN![Symbol]`
`rtd`	QuotesTOS	High	`=TOS\|HIGH![Symbol]`
`rtd`	QuotesTOS	Low	`=TOS\|LOW![Symbol]`
`rtd`	QuotesTOS	Close	`=TOS\|CLOSE![Symbol]`
`rtd`	QuotesTOS	Volume	`=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:

TABLE_SCHEMA
TABLE_NAME
LOAD_CODE
IS_HISTORY
IS_DISABLED
TIME_ZONE
RTD_INSTANCE_NAME

The table can contain an additional primary key first column.

Configuration data formats:

TABLE_SCHEMA	TABLE_NAME	LOAD_CODE	IS_HISTORY	IS_DISABLED	TIME_ZONE	RTD_INSTANCE_NAME
<Table schema>	<Table name>	<SQL command>	1/0/NULL	1/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 can 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 https://learn.microsoft.com/en-us/windows-hardware/manufacture/desktop/default-time-zones?view=windows-11.

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 can also use the SQL code below to create configuration tables in your databases.

Configuration Tables for Microsoft SQL Server
Configuration Tables for Microsoft SQL Server Compact
Configuration Tables for MySQL and MariaDB
Configuration Tables for Oracle Database
Configuration Tables for IBM DB2
Configuration Tables for NuoDB
Configuration Tables for PostgreSQL

Configuration Tables for Microsoft SQL Server

You can 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 can 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 can 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 can 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 can 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 can 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 can 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
    );