Welcome to NexusFi: the best trading community on the planet, with over 150,000 members Sign Up Now for Free
Genuine reviews from real traders, not fake reviews from stealth vendors
Quality education from leading professional traders
We are a friendly, helpful, and positive community
We do not tolerate rude behavior, trolling, or vendors advertising in posts
We are here to help, just let us know what you need
You'll need to register in order to view the content of the threads and start contributing to our community. It's free for basic access, or support us by becoming an Elite Member -- see if you qualify for a discount below.
-- Big Mike, Site Administrator
(If you already have an account, login at the top of the page)
Getting data out of NinjaTrader.SDF SQL Server Compact
I use SAP/Sybase Powerbuilder development system to create my own analytical reports. It is C# / .NET friendly. However, connecting to the Ninja SDF database seems very difficult. I was able connect with a Registry kludge under a prior version of Windows, but now on Windows 8 and with a new version of Powerbuilder, I am going to give up. (That is, unless someone can tell me how to have the Powerbuilder Database Painter connect in a clean way to the SDF.)
Otherwise I want to export the entire schema and then the daily data from the Ninja database into a SQL Server LocalDB or Express database. Even better yet would be to pipe it daily into a Sybase/SAP SQL Anywhere database, which is native to Powerbuilder.
I would like to grab the Ninja schema and port it to the other database, then set up a daily pipe to copy the data completely into the second database, where I can easily access it.
I used to work in SQL Server (eenterprise) some time ago, and it seems this would be possible, but I don't remember. Then there was a Data Transformation Service that would probably do it.
(And who knows if MS is even going to support the SDF format anymore. I suppose the next version of Ninja might use LocalDB.)
Even better yet, would be to have the database painter in Powerbuilder 12.5 (SAP/Sybase) be able to access the SQL Compact database....but I'm not going to hold my breath. (Because Powerbuilder has an automated data pipe.)
Any ideas?
Thank you in advance.
Can you help answer these questions from other members on NexusFi?
Looks like there is a Replication service that can be set up between the Ninja SDF database technology and its bigger brother SQL Server Express. Once the data is ported then it is easy to get to by many different tools.
(C# can access the SDF, but I need these other analytic tools to get to the data.)
The Replication Servce requires that Ninja SDF be in version SQL Server Compact 3.5, which Ninja confirmed it is.
I am trying to read the NinjaTrader.sdf and that works fine, BUT, where I'm having trouble is with the orderid.
When i create an order with a unique ID through the ATI interface, the order gets into NT and it goes
to the market. When i ask the status through the ATI interface, i can follow the order until it gets
executed.
The problem is, when i touch the order in the chart trader and move the limit, my program has no possibility to
"see" this change to the limit, stop or target.
For this reason i want to read the DB and get the information directly from there, but i can not match my own orderid
with the orderid as it is stored in the DB, the value seems to be 'scrambled' or simply not correspond to my value,
anybody any idea or has looked to this before ?
I think i got one step closer to the solution. Whatever orderid i choose, the result in the database is a 32 hex string
this makes me think, this is not the orderId but a hash on the orderId. Most likely this is an MD5 hash algo...
A hash is a cryptographic reduction of a given text.
This also explain, if i query with my orderid, they are able to find back the order, without the reference being in the DB.
The mystery is solved. For every order there is a unique GUI created for orderid and orderref, nothing to do with the reference chosen to do the ATI call. The custom order number is stored in the BLOB userdata as XML
Thanks for the insight you provided from your research. Below is what I understood from your post -
From chart trader when ever a price crosses a limit order level... a Ninja generated OrderID is generated. and we can see this in Order tab. This GUID is order refID (YOU Referenced in your post.)
Now when this order is sent to broker and it is filled ..actual OrderID from broker is returned and gets stored in ORDERID filed in SQL db.
Now when you query with Ninja generated ID...you can trace the order status and actual order ID.
lET ME KNOW MY UNDERSTANDING IS CORRECT HERE OR NOT!!
In my case.. whenever a limit order is filled by chart trader I want to read the ninjatrader generated order ID. And after fill I want to know actual order ID. Whenever stop order is moved ON CHART CAN WE READ THAT VALUE DIRECTLY FROM THIS NINJA COMPACT DB.?
Thanks for the insight you provided from your research. Below is what I understood from your post -
From chart trader when ever a price crosses a limit order level... a Ninja generated OrderID is generated. and we can see this in Order tab. This GUID is order refID (YOU Referenced in your post.)
Now when this order is sent to broker and it is filled ..actual OrderID from broker is returned and gets stored in ORDERID filed in SQL db.
Now when you query with Ninja generated ID...you can trace the order status and actual order ID.
lET ME KNOW MY UNDERSTANDING IS CORRECT HERE OR NOT!!
In my case.. whenever a limit order is filled by chart trader I want to read the ninjatrader generated order ID. And after fill I want to know actual order ID. Whenever stop order is moved ON CHART CAN WE READ THAT VALUE DIRECTLY FROM THIS NINJA COMPACT DB.?
I don't remember exactly and i would have to deep dive into my code
the easiest way is to just do a few tests..
Can you please share a code snippet from your side. that will be of great help.
Also please confirm my understanding after reading your post.
Hope this helps you getting started :
// update order status
// search for order based on NTorderId
// - check limit
// - check quantity
// - (status is not updated) because we do this through the ATI interface
public void synchroniseLimitQuantityPositions(int dbDebug, List<PositionRec> PositionTable)
{
int int_i;
int quantity;
decimal limitprice;
decimal stopprice;
decimal avgfillprice;
int orderstate;
try
{
// open NT DB
string sdfFILE = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile) + "\\Documents\\NinjaTrader 7\\db\\NinjaTrader.sdf; " + "File Mode=Read Only; SSCE:Temp File Directory=C:\\myTempDir\\;";
// open the ninjatrader.sdf database
SqlCeConnection con = new SqlCeConnection("Data Source = " + sdfFILE);
{
con.Open();
// order table holds the open orders (entry orders)
//
for (int_i = PositionTable.Count - 1; int_i >= 0; int_i--)
{
if (!PositionTable[int_i].isSimulation && PositionTable[int_i].NTtargetId != "")
{
// prepare SQL statement
string mySQLcommand = "SELECT quantity, limitprice, avgfillprice, orderstate FROM nt_order WHERE name='Target1' AND orderid='" + PositionTable[int_i].NTtargetId + "'";
// Read in all values in the table.
using (SqlCeCommand com = new SqlCeCommand(mySQLcommand, con))
{
SqlCeDataReader reader = com.ExecuteReader();
while (reader.Read())
{
quantity = reader.GetInt32(0);
limitprice = (decimal)reader.GetDouble(1);
avgfillprice = (decimal)reader.GetDouble(2);
orderstate = reader.GetInt32(3);
// order quantity changed -> update quantity
if (quantity != PositionTable[int_i].Quantity)
{
if (dbDebug > 0 && PositionTable[int_i].Quantity !=0)
Log(PositionTable[int_i].NTcode + " Qty was manualy updated in NT for order " + PositionTable[int_i].OrderSeqNr + " from : " + PositionTable[int_i].Quantity + " to : " + quantity);
PositionTable[int_i].Quantity = quantity;
PositionTable[int_i].Manual = true;
}
// order limit changed - > update order
if (limitprice != PositionTable[int_i].Target && orderstate == 9)
{
if (dbDebug > 0 && PositionTable[int_i].Target != 0)
Log(PositionTable[int_i].NTcode + " Target limit was manualy updated in NT for order " + PositionTable[int_i].OrderSeqNr + " from : " + PositionTable[int_i].Target + " to : " + limitprice);
PositionTable[int_i].Target = limitprice;
PositionTable[int_i].Manual = true;
}
// order was executed, price is avgfillprice
if (orderstate == 2)
{
if (dbDebug > 0)
Log(PositionTable[int_i].NTcode + " Target got hit in NT for order " + PositionTable[int_i].OrderSeqNr + " at : " + avgfillprice);
PositionTable[int_i].AvgTorS = "T";
PositionTable[int_i].Avgfillprice = avgfillprice;
}
}
reader.Close();
}
}
if (!PositionTable[int_i].isSimulation && PositionTable[int_i].NTstopId != "")
{
// prepare SQL statement
string mySQLcommand = "SELECT quantity, stopprice, avgfillprice, orderstate FROM nt_order WHERE name='Stop1' AND orderid='" + PositionTable[int_i].NTstopId + "'";
// Read in all values in the table.
using (SqlCeCommand com = new SqlCeCommand(mySQLcommand, con))
{
SqlCeDataReader reader = com.ExecuteReader();
while (reader.Read())
{
quantity = reader.GetInt32(0);
stopprice = (decimal)reader.GetDouble(1);
avgfillprice = (decimal)reader.GetDouble(2);
orderstate = reader.GetInt32(3);
// order quantity changed -> update quantity
if (quantity != PositionTable[int_i].Quantity)
{
if (dbDebug > 0 && PositionTable[int_i].Quantity != 0)
Log(PositionTable[int_i].NTcode + " Qty was manualy updated in NT for order " + PositionTable[int_i].OrderSeqNr + " from : " + PositionTable[int_i].Quantity + " to : " + quantity);
PositionTable[int_i].Quantity = quantity;
PositionTable[int_i].Manual = true;
}
// order limit changed - > update order
//
if (stopprice != PositionTable[int_i].Stop && orderstate ==0)
{
if (dbDebug > 0 && PositionTable[int_i].Stop != 0)
Log(PositionTable[int_i].NTcode + " Stop limit was manualy updated in NT for order " + PositionTable[int_i].OrderSeqNr + " from : " + PositionTable[int_i].Stop + " to : " + stopprice);
PositionTable[int_i].Stop = stopprice;
PositionTable[int_i].Manual = true;
}
if (orderstate == 2)
{
if (dbDebug > 0)
Log(PositionTable[int_i].NTcode + " Stop got hit in NT for order " + PositionTable[int_i].OrderSeqNr + " at : " + avgfillprice);
PositionTable[int_i].AvgTorS = "S";
PositionTable[int_i].Avgfillprice = avgfillprice;
}
}
reader.Close();
}
}
}
con.Close();
}
}
catch
{
Log("Exception in : synchroniseLimitQuantityPositions ");
}
}