Database static and streaming provider
Author: Eugene
Creation Date: 11/8/2010 7:32 AM
profile picture

Eugene

#1
Our Wealth-Lab 6 provider for working with historical and real-time data stored in ADO.NET compatible databases has been released.

Check out its online help page in the Wealth-Lab Wiki to find out more.

Download and install Extension
profile picture

bonanza

#2
That's great Eugene!
It would be nice to add some optional additional fields, as in ASCII provider.
I would like to have access to such fields as OI,DailyCap,NumTrades,etc are stored in my db.
profile picture

Eugene

#3
Yes, custom data series support is planned.
profile picture

odeedo

#4
I tried to get it working with Access 64bit as a static database provider but i got the following error after defining the target database, connection string, table, columns and accessing to the first symbol in the database with a strategie

-> Runtime error: CurrentTimeZone not found.
bei WealthLabPro.CharForm.q()
bei WealthLab.BarsLoader.GetData(DataSource ds, String symbol)
bei WealthLab.DataProviders.Database.DBStatic.GetMarketInfo(String symbol)
bei Fidelity.Components.TimeZoneInformation.get_CurrentTimeZone()

as you see i have a german installation.

In my (WL-)projects i do not have any problem to access MS-Access 64bit, so i have no idea to get it working.

Frank


Ps: For me it was not possible to copy the stack trace directly, so i wrote it off. Would be easier to copy it to clipboard!
profile picture

Eugene

#5
Actually it's not a problem of the Database provider per se as the stack trace points to Fidelity.Components - a Wealth-Lab helper component. It's a more general problem that happens when your time zone can't be identified, in other words, when returned description of the time zone of the computer is incorrect.

As Wealth-Lab 6 reports, you actually have an English edition of Windows 7 Ultimate but the error message is in German ("bei"), you must have changed the display language to German. In the past we had a very similar case, the difference was that the customer's Windows 7 Ultimate was German and he changed most language options to English (only Ultimate and Enterprise editions allow that). Once the display language was restored back to default i.e. German, the error was gone for good.

So try changing your display language back to English, reboot, and if it didn't happen, please open a new support ticket.
profile picture

Eugene

#6
Database provider 2011.01 released on 01/12/2010.

Compatible with Wealth-Lab 6.1 and higher. Please upgrade to 6.1 as the Database provider's initial release is no longer supported.

This is an important release that brings 6.1 compatibility, adds possibility to define custom Named DataSeries, fixes some bugs. Users will have to create all their DataSets from scratch due to breaking format change.

Eliminating the so called "Default static source for Streaming" is a highlight of this version. This cumbersome extra step was actually a workaround to some bug of WL data provider API (5.x-6.0) that was fixed in 6.1. As a result, setting up the provider has become more user friendly.
profile picture

Eugene

#7
Database provider updated to version 2011.03

Maintenance release; the only change is a fix for potential missing tick problem in streaming charts due to SQL database delays.
profile picture

Eugene

#8
Just for the record. Lately I've got this report from our community member Sabawi:
QUOTE:
I have struggled for a full day to get this to work with MySQL database using ODBC on Windows7. I am almost giving up thinking that this may not be supported .. except that the manual says it is. The ODBC database "stocks" is accessible to this extension since I see all my tables in the drop-down menu. However, as soon as I select the table I get the Exception below. Note that my table "quotes5" was created with this SQL statement: "Mysql> create table quotes5 ( Symbol varchar(10) not null, Date datetime not null, Open float, high float, low float, close float);"

If I know what is the SQL query syntax it is complaining about I may be able to fix this.

QUOTE:
---- CUT and PASTE from the Exception window ----

See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.Data.Odbc.OdbcException: ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.5.9]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[quotes5]' at line 1
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at WealthLab.DataProviders.Database.DBStreamingSetup.?(Boolean ?, DbProviderFactory ?, String ?, ComboBox ?, ComboBox ?, DBStreamingSettings ?)
at WealthLab.DataProviders.Database.DBStreamingSetup.?(Object ?, EventArgs ?)
at System.Windows.Forms.ComboBox.OnSelectedIndexChanged(EventArgs e)
at System.Windows.Forms.ComboBox.WmReflectCommand(Message& m)
at System.Windows.Forms.ComboBox.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Unfortunately I don't own a working MySQL installation so I'm marking it for review later. Should someone with MySQL be able to reproduce the bug, please drop me a note. Also if you're using the native MySQL Connector/Net driver as opposed to ODBC, feel free to share your experiences.
profile picture

Eugene

#9
Thanks for the heads-up, I confirm the bug with MySQL and it's clear to me why it happens. The error affects both ODBC and Connector/Net drivers. Will try to provide a solution in a future build of the provider.
profile picture

Eugene

#10
Database provider updated to version 2011.04.

This is a maintenance release fixing MySQL compatibility and some OLEDB issues.

* Fixed: failed to work with MySQL
* Fixed: MS Access streaming quotes malfunctioned via OLE DB
* Fixed: potential OLEDB issue
* Fixed: removed leftover code


Sabawi, please upgrade to 2011.04 -- MySQL is now working (verified with both ODBC and Connector/Net).
profile picture

Eugene

#11
Database provider updated to version 2011.09.

Change summary:

* Added: Market Manager support
profile picture

Eugene

#12
Database provider was updated to version 2012.02. This is a maintenance release fixing a couple of bugs affecting its usage in Strategy Monitor.

Change summary:

* Change: enabled heartbeat to force update streaming charts (of illiquid instruments) that have not had tick updates in time
* Fix: Strategy Monitor malfunction on intraday timeframes (time missing in SQL queries)
* Fix: Strategy Monitor worked properly in ET but not in other time zones e.g. UTC+4
* Fix: minor internal fixes
profile picture

abegy

#13
Do you have plans to allow GetSessionOpen with this extension ? If I have understood correctly, it's not working at this time.
profile picture

Eugene

#14
I can put it on the todo list for a future build.
profile picture

Eugene

#15
Please create a support ticket to keep this request in my queue.
profile picture

Eugene

#16
QUOTE:
Do you have plans to allow GetSessionOpen with this extension ?

I will add GetSessionOpen support to the Database provider for use in Strategies.

Note that due to an architectural challenge it is impossible to properly display the Open and Change fields in Quotes windows.
profile picture

Eugene

#17
What's new in the recent Database provider update (version 2012.06)?

* New: GetSessionOpen support
* Change: (internal) for versatility, switched both Static and Streaming providers to parameterized SQL queries
* Change: (internal) for maintainability, refactoring of classes
* Change: can be installed in WL 6.3+
* Fix: with ODBC/OLEDB sources, Strategy Monitor and some data loading options didn't work
* Fix: streaming may work properly in ET but not so in other time zones (fix similar to 2011.02)

To ensure full backward compatibility, this build has undergone testing with different database engines like MS SQL Server 2008 R2, MySQL, Access and some other OLEDB/ODBC sources. However, due to a lot of internal changes please be careful installing this version in production environment.
profile picture

sf631

#18
Hi guys, after doing some experimentation with the connection string, I got the static database connector up and running for historical data. Very nice!

I have two usage questions now.

1) I'm using this dataset to store other non-price data (e.g., shares available to short) and want to call data for a certain symbol into a strategy but not as price. How should I do this? Force the non-price data into the Close or Open or Volume fields and then create a DataSeries that specifically pulls the "close" from the database dataset? Use "Named Series" somehow? What would be the optimal way to do this?

2) When I connect to a SQL Server table with 200K rows, the connection works fine, but when I connect to a SQL Server "View" that has the exact same 200K rows in it, I get a timeout error when trying to build the dataset, which I assume is because the join operation inside of the View are needing to be run. Is there a way to change the timeout limit? Is this a bad idea (i.e., would it make my strategy unspeakably slow)? My reasoning for wanting a view rather than a table is because the table is static, would need to be rebuilt every day to have the data fresh, while the view will always contain the latest data from the database.

Thanks
profile picture

sf631

#19
I just answered part of #1 above using Bars GetExternalSymbol(string dataSetName, string symbol, bool synchronize), but still wondering if the NamedSeries has some usefulness here
profile picture

Eugene

#20
Hi Chad,

1) The ability to define a Named Series is here precisely to help with tasks like this. Check out the QuickRef, it's all exemplified there: FindNamedSeries, HasNamedSeries, NamedSeries. If you have further questions re: NamedSeries, please ask them in a new thread.

2) Yes, by appending ;Connection Timeout=some_value to your connection string with the value you wish. Reference: MSDN for ConnectionString. Nonetheless, are you sure that your View is indexed (i.e. not using the index of the table but its own)?
profile picture

Eugene

#21
Changes in version 2017.09:

* Fix: increased stability by fixing startup crash on broken configuration file