12. Database API |
  
|
12. Database API |
|
The Swordfish Database API is a plugin feature to the Swordfish platform. It allows users to write a programs or application that can automatically place, edit, or cancel orders through the platform. The API is designed to allow the programmer to choose any preferred language.
This document presents a guide to using the Swordfish database API. The API was built on Windows 10 using Microsoft SQL Server 2012.
1.Requirements
One and only one instance of Swordfish platform (Version 3.20151125 or higher) running per database.
Database software (MS SQL Server preferred)
•Database created
•Tables Created
•Procedures created
•A script can be provided to create these.
2 UDL’s with database connection.
•OTSDatabaseAPI.udl for access to API table
•OTSData.udl for access to portfolio tables
Your application that automatically place, edit or cancel orders.
Your Swordfish service login user must have the DBAPI permission activated by Swordfish support staff.
2.Setup / Installation
Database install scripts can be found in addendum A.
UDL details can be found in addendum B.
Steps
1.Install Microsoft SQL Server.
2.Run query ‘Create Database' for DatabaseAPI.
3.Run query ‘Create API Tables' for DatabaseAPI.
4.Run query ‘Create Stored Procedures’ for DatabaseAPI.
5.Run query ‘Create Portfolio Tables' for DatabaseAPI.
6.Create the OTSData and OTSDatabaseAPI UDL files.
Do this by creating a text file OTSData.txt and OTSDatabaseAPI.txt
Paste the example udl text (in Addendum C) as the content of the text file. Change the text file to have udl as file extension. e.g OTSData.udl and OTSDatabaseAPI.udl
Copy both UDL files to your Estuary/Data/Intf folder. Create the folder if necessary.
3.API Guidelines
To start placing orders
•Start Swordfish
•Open the SFDatabase Form (Tools -> Links -> sfDatabase) (Requires DBAPI permission activated by Swordfish support staff otherwise you will not see the menu item SfDatabase)
•Checking the check box will start the polling
•Start your application.
To Start the Data Feed
•Start Swordfish
•Add Instruments to your watchlists
•Open the SFDatabase Form (Tools -> Links -> sfDatabase) (Requires DBAPI permission activated by Swordfish support staff otherwise you will not see the menu item SfDatabase)
•Checking the check box will start the feed
Limitations
•Minimum poll interval = 100 milliseconds
•Maximum poll interval = 2000 milliseconds
•Only allows Order Place, Edit and Cancel.
•Portfolio data updates occur at a maximum of 5 seconds.
Use only the given stored procedures to access the database. Do not manually edit orders marked as processing. Do not enter rows into the database manually to insert an order. Doing so will cause unpredictable results.
The poll interval increases if the message limit has been breached or there are no orders in the table. It is reset to the minimum when a new order is submitted.
4.Process Flow Diagram
Figure : Process flow diagram
5.Messages Formats
Order Insert
DB Command: DatabaseOrderInsert
Parameter Name |
Data Type |
Comments |
Required |
Example |
Instrument |
String |
Instrument code as given by Exchange. (Check the details window in Swordfish and obtain the Instrument code. The value in the round brackets is to be used. E.g (FHO18 USDZAR CSH 1000 1072624) or (FHO20 ALSI CSH 10 1084839) Or (FHO20 WMAZ)
|
Y |
“FGO15 ALSI CSH 10 1084838” or “FHO20 WMAZ” |
BidAsk |
Char |
‘B’ = Buy ‘S’ = Sell |
Y |
‘B’ |
Qty |
Float |
Limited by environment options. |
Y |
1 |
Price |
Float |
|
Y |
48483 |
Principal |
String |
Client details as per exchange |
Y |
“031XKB” |
Dealer |
String |
Y |
“ABD” |
|
Member |
String |
Y |
“ESTU” |
|
BaseOrderType |
String |
•Limit (Default) •Market (Only FOREX) |
N |
“Limit” |
TimeInForce |
string |
•NOR = GTC for FOREX / DAY for the rest (Default) •FOK = Fill or kill •TAK = IOC = Take and Kill / Immediate or cancel. |
N |
“NOR” |
ExchID |
int |
•1 = SXFIN •2 = SXAGR •3 = JSE •4 = Data Feed •5 = IRC •6 = JSSSF •8 = JSCFD •15 =FOREX |
Y |
1 |
Capacity |
Char |
•P = Principal •A = Agent |
Y |
‘P’ |
Order Edit
DB Command: DatabaseOrderEdit
Parameter Name |
Data Type |
Example |
Qty |
Float |
1 |
Price |
Float |
48482 |
Principal |
String |
“031XKB” |
ExchID |
int |
1 |
OrderSeq |
Int |
197091881 |
Order Cancel
DB Command: DatabaseOrderCancel
Parameter Name |
Data Type |
Example |
Instrument |
String |
“FGO15 ALSI CSH 10 1084838” |
ExchID |
int |
1 |
OrderSeq |
Int |
197091881 |
6.Test your System
Call Stored Procedure directly
DECLARE @Instrument nvarchar(40)
DECLARE @BidAsk char(1)
DECLARE @Qty float
DECLARE @Price float
DECLARE @Principal nvarchar(10)
DECLARE @Dealer nvarchar(10)
DECLARE @Member nvarchar(10)
DECLARE @TimeInForce nvarchar(3)
DECLARE @ExchID int
DECLARE @Capacity char(1)
-- TODO: Set parameter values here.
SET @Instrument = 'FGO15 ALSI CSH 10 1084838'
SET @BidAsk = 'B'
SET @Qty = 1
SET @Price = 47800
SET @Principal = ''
SET @Dealer = ''
SET @Member = ''
SET @TimeInForce = 'NOR'
SET @ExchID = 1
SET @Capacity = 'P'
EXECUTE [DatabaseOrderInsert]
@Instrument
,@BidAsk
,@Qty
,@Price
,@Principal
,@Dealer
,@Member
,@TimeInForce
,@ExchID
,@Capacity
GO
Example C# Code
private void openDBConnection()
{
string MyDocPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
if (File.Exists(MyDocPath + "/Estuary/Data/Intf/OTSDatabaseAPI.UDL") &&
oleDbConn.State != ConnectionState.Open)
{
oleDbConn.ConnectionString = @"File name=" + MyDocPath +
"/Estuary/Data/Intf/ OTSDatabaseAPI.UDL";
oleDbConn.Open();
}
}
private void button_CancelOrder_Click(object sender, EventArgs e)
{
try
{
string Instrument = tb_Instrument.Text;
string Exchange = tb_ExchID.Text;
string OrderSequence = tb_OrderSeq.Text;
using (OleDbCommand Command = new OleDbCommand("DatabaseOrderCancel", oleDbConn))
{
Command.CommandType = CommandType.StoredProcedure;
// Add parameters
Command.Parameters.Add("@Instrument", OleDbType.VarChar).Value = Instrument;
Command.Parameters.Add("@ExchID", OleDbType.Integer).Value = Exchange;
Command.Parameters.Add("@OrderSeq", OleDbType.Integer).Value = OrderSequence;
// Execute query
int count = Command.ExecuteNonQuery();
// Validation checks (if order inserted correctly)
}
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
ValidateCancelOrderTextBoxes();
}
}
Example VB Code
Private Sub OpenDBConnectionADO()
Dim MyDocPath As String =
Environment.GetFolderPath (Environment.SpecialFolder.MyDocuments)
If (File.Exists(MyDocPath + "/Estuary/Data/Intf/ OTSDatabaseAPI.UDL") And
(Not oleDbConn.State = ConnectionState.Open)) Then
DBConn.ConnectionString = "File name=" + MyDocPath +
"/Estuary/Data/Intf/ OTSDatabaseAPI.UDL"
DBConn.Open ()
End If
End Sub
Private Sub InsertOrderADO (strInsturment As String, intExchID As Integer,
intOrderSequence As Integer)
DBConn.CursorLocation = adUseClient
Dim command As New ADODB.Command
Dim param1 As ADODB.Parameter =
command.CreateParameter ("Input", adChar, adParamInput, strInsturment)
Dim param2 As ADODB.Parameter =
command.CreateParameter ("Input", adChar, adParamInput, intExchID)
Dim param3 As ADODB.Parameter =
command.CreateParameter ("Input", adInteger, adParamInput, intOrderSequence)
command.ActiveConnection = DBConn
command.CommandType = adCmdStoredProc
command.CommandText = "DatabaseOrderCancel"
command.Parameters.Append (param1)
command.Parameters.Append (param2)
command.Parameters.Append (param3)
Dim recordSet As New ADODB.Recordset
recordSet = command.Execute
recordSet.Close ()
End Sub
7.Addendum A: Database Install Scripts
Replace [SwordfishDatabaseAPI] with own Database name if preferred. Remember to adjust the UDL’s if you make use of your own Database name.
Create Database
USE [master]
CREATE DATABASE [SwordfishDatabaseAPI]
GO
Create API Tables
USE [SwordfishDatabaseAPI]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DatabaseAPIOrders](
[Key] [int] IDENTITY(1,1) NOT NULL,
[DateTimeInserted] [datetime2](7) NULL,
[Action] [nchar](20) NOT NULL,
[Instrument] [nvarchar](40) NULL,
[BidAsk] [char](1) NULL,
[Qty] [float] NULL,
[Price] [float] NULL,
[BaseOrderType] [nvarchar](10) NULL,
[TimeInForce] [nchar](3) NULL,
[Principal] [nvarchar](20) NULL,
[Dealer] [nvarchar](6) NULL,
[Member] [nvarchar](11) NULL,
[ExchID] [int] NOT NULL,
[Capacity] [char](1) NULL,
[OrderSeq] [int] NULL,
[RefCode] [nvarchar](40) NULL,
[SwordfishRefCode] [nvarchar](50) NULL,
[Processing] [bit] NULL
CONSTRAINT [PK_DatabaseAPIOrders] PRIMARY KEY CLUSTERED
(
[Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DatabaseAPIOrdersHist](
[DateTimeProcessed] [datetime2](7) NULL,
[DateTimeInserted] [datetime2](7) NULL,
[Action] [nchar](20) NOT NULL,
[Instrument] [nvarchar](40) NULL,
[BidAsk] [char](1) NULL,
[Qty] [float] NULL,
[Price] [float] NULL,
[BaseOrderType] [nvarchar](10) NULL,
[TimeInForce] [nchar](3) NULL,
[Principal] [nvarchar](20) NULL,
[Dealer] [nvarchar](6) NULL,
[Member] [nvarchar](11) NULL,
[ExchID] [int] NOT NULL,
[Capacity] [char](1) NULL,
[OrderSeq] [int] NULL,
[RefCode] [nvarchar](40) NULL,
[SwordfishRefCode] [nvarchar](50) NULL,
[Processing] [bit] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DatabaseAPIOrdersErrors](
[DateTimeProcessed] [datetime2](7) NULL,
[DateTimeChange] [datetime2](7) NULL,
[Action] [nchar](20) NOT NULL,
[Instrument] [nvarchar](40) NULL,
[BidAsk] [char](1) NULL,
[Qty] [float] NULL,
[Price] [float] NULL,
[BaseOrderType] [nvarchar](10) NULL,
[TimeInForce] [nchar](3) NULL,
[Principal] [nvarchar](20) NULL,
[Dealer] [nvarchar](6) NULL,
[Member] [nvarchar](11) NULL,
[ExchID] [int] NOT NULL,
[Capacity] [char](1) NULL,
[OrderSeq] [int] NULL,
[RefCode] [nvarchar](40) NULL,
[SwordfishRefCode] [nvarchar](50) NULL,
[Processing] [bit] NULL
) ON [PRIMARY]
GO
Create Stored Procedures
USE [SwordfishDatabaseAPI]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DatabaseOrderInsert]
@Instrument nvarchar(40) = '',
@BidAsk char = 'B',
@Qty float = 0,
@Price float = 0,
@Principal nvarchar (20),
@Dealer nvarchar (10),
@Member nvarchar (11),
@TimeInForce nvarchar (3) = 'NOR',
@ExchID int = 0,
@Capacity char = 'P'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Statements for procedure here
INSERT INTO [SwordfishDatabaseAPI].[dbo].[DatabaseAPIOrders]
([DateTimeInserted], [Action], [Instrument], [BidAsk], [Qty], [Price], [TimeInForce], [Principal], [Dealer], [Member], [ExchID], [Capacity], [Processing])
VALUES
(GETDATE(), 'Order', @Instrument, @BidAsk, @Qty, @Price, @TimeInForce, @Principal, @Dealer, @Member, @ExchID, @Capacity, 0);
END
GO
CREATE PROCEDURE [dbo].[DatabaseOrderEdit]
@Qty float = 0,
@Price float = 0,
@Principal nvarchar (20),
@ExchID int = 0,
@OrderSeq int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Statements for procedure here
INSERT INTO [SwordfishDatabaseAPI].[dbo].[DatabaseAPIOrders]
([DateTimeInserted], [Action], [Qty], [Price], [Principal], [ExchID], [OrderSeq], [Processing])
VALUES
(GETDATE(), 'Edit', @Qty, @Price, @Principal, @ExchID, @OrderSeq, 0);
END
GO
CREATE PROCEDURE [dbo].[DatabaseOrderCancel]
@Instrument nvarchar (40) = '',
@ExchID int = 0,
@OrderSeq int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Statements for procedure here
INSERT INTO [SwordfishDatabaseAPI].[dbo].[DatabaseAPIOrders]
([DateTimeInserted], [Action], [Instrument], [ExchID], [OrderSeq], [Processing])
VALUES
(GETDATE(), 'Cancel', @Instrument, @ExchID, @OrderSeq, 0);
END
GO
Create Portfolio Tables
USE [SwordfishDatabaseAPI]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ActiveOrders](
[DateTimeChange] [datetime2](7) NOT NULL,
[Instrument] [nvarchar](40) NOT NULL,
[BidAsk] [char](1) NOT NULL,
[Qty] [float] NOT NULL,
[Price] [float] NOT NULL,
[Principal] [nvarchar](20) NOT NULL,
[Dealer] [nvarchar](6) NOT NULL,
[RefCode] [nvarchar](40) NULL,
[OrderSeq] [int] NOT NULL,
[Member] [nvarchar](11) NOT NULL,
[OrderType] [nvarchar](10) NOT NULL,
[Exchange] [nvarchar](10) NOT NULL,
[InstrDisp] [nvarchar](40) NOT NULL,
[Capacity] [char](1) NULL,
[Origin] [char](1) NULL,
[Status] [tinyint] NULL,
[SwordfishRefCode] [nvarchar](50) NULL,
[GoodTillDate] [datetime2](7) NULL,
[IcebergQty] [float] NULL,
CONSTRAINT [PK_ActiveOrders] PRIMARY KEY CLUSTERED
(
[OrderSeq] ASC,
[Exchange] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ActiveOrdersHist](
[DateTimeChange] [datetime2](7) NOT NULL,
[Instrument] [nvarchar](40) NOT NULL,
[BidAsk] [char](1) NOT NULL,
[Qty] [float] NOT NULL,
[Price] [float] NOT NULL,
[Principal] [nvarchar](20) NOT NULL,
[Dealer] [nvarchar](6) NOT NULL,
[RefCode] [nvarchar](40) NULL,
[OrderSeq] [int] NOT NULL,
[Member] [nvarchar](11) NULL,
[OrderType] [nvarchar](10) NOT NULL,
[Exchange] [nvarchar](10) NOT NULL,
[InstrDisp] [nvarchar](40) NULL,
[Capacity] [char](1) NULL,
[Origin] [char](1) NULL,
[Status] [tinyint] NULL,
[SwordfishRefCode] [nvarchar](50) NULL,
[GoodTillDate] [datetime2](7) NULL,
[IcebergQty] [float] NULL,
CONSTRAINT [PK_ActiveOrdersHist] PRIMARY KEY CLUSTERED
(
[DateTimeChange] ASC,
[OrderSeq] ASC,
[Exchange] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CompletedOrders](
[DateTimeChange] [datetime2](7) NOT NULL,
[Instrument] [nvarchar](40) NOT NULL,
[BidAsk] [char](1) NOT NULL,
[Qty] [float] NOT NULL,
[Price] [float] NOT NULL,
[Principal] [nvarchar](20) NOT NULL,
[Dealer] [nvarchar](6) NOT NULL,
[RefCode] [nvarchar](40) NOT NULL,
[RefCodeSuf] [nvarchar](3) NOT NULL,
[ExchRefCode] [nvarchar](15) NOT NULL,
[Member] [nvarchar](11) NOT NULL,
[OrderSeq] [int] NOT NULL,
[Exchange] [nvarchar](10) NOT NULL,
[InstrDisp] [nvarchar](40) NOT NULL,
[Capacity] [char](1) NULL,
[Origin] [char](1) NULL,
[SwordfishRefCode] [nvarchar](50) NULL,
[Archived] [bit] NULL,
CONSTRAINT [PK_CompletedOrders] PRIMARY KEY CLUSTERED
(
[ExchRefCode] ASC,
[OrderSeq] ASC,
[Exchange] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CompletedOrdersHist](
[DateTimeChange] [datetime2](7) NOT NULL,
[Instrument] [nvarchar](40) NOT NULL,
[BidAsk] [char](1) NOT NULL,
[Qty] [float] NOT NULL,
[Price] [float] NOT NULL,
[Principal] [nvarchar](20) NOT NULL,
[Dealer] [nvarchar](6) NOT NULL,
[RefCode] [nvarchar](40) NOT NULL,
[RefCodeSuf] [nvarchar](3) NOT NULL,
[ExchRefCode] [nvarchar](15) NOT NULL,
[Member] [nvarchar](11) NULL,
[OrderSeq] [int] NOT NULL,
[Exchange] [nvarchar](10) NOT NULL,
[InstrDisp] [nvarchar](40) NULL,
[Capacity] [char](1) NULL,
[Origin] [char](1) NULL,
[SwordfishRefCode] [nvarchar](50) NULL,
[Archived] [bit] NULL,
CONSTRAINT [PK_CompletedOrdersHist] PRIMARY KEY CLUSTERED
(
[DateTimeChange] ASC,
[ExchRefCode] ASC,
[OrderSeq] ASC,
[Exchange] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Deals](
[DateTimeChange] [datetime2](7) NOT NULL,
[Instrument] [nvarchar](40) NOT NULL,
[BidAsk] [char](1) NOT NULL,
[Qty] [float] NOT NULL,
[Price] [float] NOT NULL,
[Principal] [nvarchar](20) NOT NULL,
[Dealer] [nvarchar](6) NOT NULL,
[RefCode] [nvarchar](40) NOT NULL,
[ExchRefCode] [nvarchar](15) NOT NULL,
[AllocFlag] [char](1) NOT NULL,
[SubAcct] [nvarchar](10) NOT NULL,
[Capacity] [char](1) NOT NULL,
[DealSeq] [int] NOT NULL,
[OrderSeq] [int] NOT NULL,
[Exchange] [nvarchar](10) NOT NULL,
[InstrDisp] [nvarchar](40) NULL,
[Origin] [char](1) NULL,
[SwordfishRefCode] [nvarchar](50) NULL,
[Version] [int] NULL,
[Member] [nvarchar](11) NULL,
[ExternalMatchedTradeID] [nvarchar](40) NULL,
CONSTRAINT [PK_Deals] PRIMARY KEY CLUSTERED
(
[DealSeq] ASC,
[Exchange] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DealsHist](
[DateTimeChange] [datetime2](7) NOT NULL,
[Instrument] [nvarchar](40) NOT NULL,
[BidAsk] [char](1) NOT NULL,
[Qty] [float] NOT NULL,
[Price] [float] NOT NULL,
[Principal] [nvarchar](20) NOT NULL,
[Dealer] [nvarchar](6) NOT NULL,
[RefCode] [nvarchar](40) NOT NULL,
[ExchRefCode] [nvarchar](15) NOT NULL,
[AllocFlag] [char](1) NOT NULL,
[SubAcct] [nvarchar](10) NOT NULL,
[Capacity] [char](1) NOT NULL,
[DealSeq] [int] NOT NULL,
[OrderSeq] [int] NOT NULL,
[Exchange] [nvarchar](10) NOT NULL,
[InstrDisp] [nvarchar](40) NULL,
[Origin] [char](1) NULL,
[SwordfishRefCode] [nvarchar](50) NULL,
[Version] [int] NULL,
[Member] [nvarchar](11) NULL,
[ExternalMatchedTradeID] [nvarchar](40) NULL,
CONSTRAINT [PK_DealsHist] PRIMARY KEY CLUSTERED
(
[DateTimeChange] ASC,
[DealSeq] ASC,
[Exchange] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Positions](
[DateTimeChange] [datetime2](7) NOT NULL,
[Instrument] [nvarchar](40) NOT NULL,
[OpenQty] [float] NOT NULL,
[BoughtQty] [float] NOT NULL,
[SoldQty] [float] NOT NULL,
[CurrentQty] [float] NOT NULL,
[Principal] [nvarchar](20) NOT NULL,
[Member] [nvarchar](11) NOT NULL,
[Dealer] [nvarchar](6) NOT NULL,
[Exchange] [nvarchar](10) NOT NULL,
[InstrDisp] [nvarchar](40) NULL,
[PhysPos] [float] NULL,
[PhysDeliv] [float] NULL,
[PositSeq] [int] NOT NULL,
CONSTRAINT [PK_Positions_1] PRIMARY KEY CLUSTERED
(
[Instrument] ASC,
[Principal] ASC,
[Member] ASC,
[Dealer] ASC,
[Exchange] ASC,
[PositSeq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PositionsHist](
[DateTimeChange] [datetime2](7) NOT NULL,
[Instrument] [nvarchar](40) NOT NULL,
[OpenQty] [float] NOT NULL,
[BoughtQty] [float] NOT NULL,
[SoldQty] [float] NOT NULL,
[CurrentQty] [float] NOT NULL,
[Principal] [nvarchar](20) NOT NULL,
[Member] [nvarchar](11) NOT NULL,
[Dealer] [nvarchar](6) NOT NULL,
[Exchange] [nvarchar](10) NOT NULL,
[InstrDisp] [nvarchar](40) NULL,
[PhysPos] [float] NULL,
[PhysDeliv] [float] NULL,
[PositSeq] [int] NOT NULL,
CONSTRAINT [PK_PositionsHist] PRIMARY KEY CLUSTERED
(
[DateTimeChange] ASC,
[Instrument] ASC,
[Principal] ASC,
[Member] ASC,
[Dealer] ASC,
[Exchange] ASC,
[PositSeq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Create Data Feed Tables
USE [SwordfishDatabaseAPI]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DatabaseAPIDataFeed](
[LastTime] [datetime2](7) NULL,
[Instrument] [nvarchar](40) NULL,
[BidPrice] [float] NULL,
[BidQty] [float] NULL,
[AskPrice] [float] NULL,
[AskQty] [float] NULL,
[LastPrice] [float] NULL,
[Volume] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DatabaseAPIDataFeedDepth](
[LastTime] [datetime2](7) NULL,
[Instrument] [nvarchar](40) NULL,
[TickLevel] [int] NULL,
[BidPrice] [float] NULL,
[BidQty] [float] NULL,
[AskPrice] [float] NULL,
[AskQty] [float] NULL
) ON [PRIMARY]
GO
8.Addendum B: DB table requirements for EDM and Currency markets only
CREATE TABLE [dbo].[Commission](
[ExchID] [int] NOT NULL,
[CommissionID] [nvarchar](100) NOT NULL,
[Market] [nvarchar](100) NOT NULL,
[InitiatingCM] [nvarchar](15) NOT NULL,
[InitiatingTM] [nvarchar](15) NOT NULL,
[DestinationCM] [nvarchar](15) NOT NULL,
[DestinationTM] [nvarchar](15) NOT NULL,
[ClientReference] [nvarchar](100) NOT NULL,
[CommissionReference] [nvarchar](100) NOT NULL,
[Amount] [float] NOT NULL,
[Status] [nvarchar](15) NOT NULL,
[EnteredTimestamp] [datetime2](7) NOT NULL,
[CancelTimestamp] [datetime2](7) NULL,
[Comment] [nvarchar](100) NULL,
[SendReceive] [nvarchar](100) NOT NULL,
[InitiatingAccountID] [nvarchar](11) NULL,
[DestinationAccountID] [nvarchar](11) NULL,
[VATRate] [int] NULL,
[SecondaryFirmReference] [nvarchar](100) NULL,
CONSTRAINT [PK_Commission] PRIMARY KEY CLUSTERED
(
[CommissionID] ASC,
[EnteredTimestamp] ASC,
[SendReceive] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Commission] ADD DEFAULT (NULL) FOR [InitiatingAccountID]
GO
ALTER TABLE [dbo].[Commission] ADD DEFAULT (NULL) FOR [DestinationAccountID]
GO
ALTER TABLE [dbo].[Commission] ADD DEFAULT (NULL) FOR [VATRate]
GO
CREATE TABLE [dbo].[CommissionHist](
[ExchID] [int] NOT NULL,
[CommissionID] [nvarchar](100) NOT NULL,
[Market] [nvarchar](100) NOT NULL,
[InitiatingCM] [nvarchar](15) NOT NULL,
[InitiatingTM] [nvarchar](15) NOT NULL,
[DestinationCM] [nvarchar](15) NOT NULL,
[DestinationTM] [nvarchar](15) NOT NULL,
[ClientReference] [nvarchar](100) NOT NULL,
[CommissionReference] [nvarchar](100) NOT NULL,
[Amount] [float] NOT NULL,
[Status] [nvarchar](15) NOT NULL,
[EnteredTimestamp] [datetime2](7) NOT NULL,
[CancelTimestamp] [datetime2](7) NULL,
[Comment] [nvarchar](100) NULL,
[SendReceive] [nvarchar](100) NOT NULL,
[InitiatingAccountID] [nvarchar](11) NULL,
[DestinationAccountID] [nvarchar](11) NULL,
[VATRate] [int] NULL,
[SecondaryFirmReference] [nvarchar](100) NULL,
CONSTRAINT [PK_CommissionHist] PRIMARY KEY CLUSTERED
(
[CommissionID] ASC,
[EnteredTimestamp] ASC,
[SendReceive] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CommissionHist] ADD DEFAULT (NULL) FOR [InitiatingAccountID]
GO
ALTER TABLE [dbo].[CommissionHist] ADD DEFAULT (NULL) FOR [DestinationAccountID]
GO
ALTER TABLE [dbo].[CommissionHist] ADD DEFAULT (NULL) FOR [VATRate]
GO
9.Addendum C: Database UDL’s
You need 2 UDL’s named OTSData.UDL and OTSDatabaseAPI.UDL. Edit these files with a text editor like Notepad. Both UDL will look similar to the text below and include your changes that reflect your custom details.
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLNCLI11.1; Integrated Security = SSPI; Persist Security Info = True; Initial Catalog = SwordfishDatabaseAPI; Data Source = (local);
OR
[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSOLEDBSQL; Integrated Security=SSPI; Persist Security Info=True; Initial Catalog=SwordfishDatabaseAPI; Data Source =(localdb)\ProjectsV13;
You must change the Data Source to your local PC name or a PC on the network where the SQL server is located.
Make sure to test the connection by double clicking the file and clicking ‘test connection’.
DO NOT click on OK on this dialog, click the Cancel button always. The click of Ok button might add unwanted changes the contents of the UDL file.
