Getting data out of NinjaTrader.SDF SQL Server Compact

December 3rd, 2013, 06:47 PM

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.

December 4th, 2013, 06:50 AM

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.

Thanks.

May 19th, 2016, 12:19 PM

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 ?

May 19th, 2016, 02:41 PM

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.

May 19th, 2016, 04:55 PM

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

0x3C004E0069006E006A0061005400720061006400650072003E003C005F004C00650067006100630079004E0061006D0065003E0045006E007400720079003C002F005F004C00650067006100630079004E0061006D0065003E003C004100750074006F006D006100740065006400540072006100640069006E0067004F007200640065007200490064003E0031003C002F004100750074006F006D006100740065006400540072006100640069006E0067004F007200640065007200490064003E003C002F004E0069006E006A0061005400720061006400650072003E00

Which becomes after decoding

Code

<NinjaTrader><_LegacyName>Entry</_LegacyName><AutomatedTradingOrderId>1</AutomatedTradingOrderId></NinjaTrader>

Which allows to retrieve the orderID given to the order of ATI.
In this case 1.

September 4th, 2017, 04:19 AM

rleplae

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

0x3C004E0069006E006A0061005400720061006400650072003E003C005F004C00650067006100630079004E0061006D0065003E0045006E007400720079003C002F005F004C00650067006100630079004E0061006D0065003E003C004100750074006F006D006100740065006400540072006100640069006E0067004F007200640065007200490064003E0031003C002F004100750074006F006D006100740065006400540072006100640069006E0067004F007200640065007200490064003E003C002F004E0069006E006A0061005400720061006400650072003E00

Which becomes after decoding

Code

<NinjaTrader><_LegacyName>Entry</_LegacyName><AutomatedTradingOrderId>1</AutomatedTradingOrderId></NinjaTrader>

Which allows to retrieve the orderID given to the order of ATI.
In this case 1.

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.?

September 4th, 2017, 04:28 AM

saurabh

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..

The above post should allow you to make the link

September 4th, 2017, 04:38 AM

rleplae

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..

The above post should allow you to make the link

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.

September 4th, 2017, 04:44 AM

saurabh

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 :

Code

   // 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 ");
            }
        }

Getting data out of NinjaTrader.SDF SQL Server Compact

Discussion in NinjaTrader

Getting data out of NinjaTrader.SDF SQL Server Compact