FIDAL - Financial Data Access Library

  Home | Documentation | Download
 

SQL Data Source

1.0 Introduction

2.0 Connecting to the database

3.0 Setting Queries

4.0 Historical Data

5.0 Command Line FD_SQL

6.0 FD_AddDataSource Parameters Details

1.0 Introduction

Access to most SQL database is available through ODBC. This include:

See MSDN for further information about ODBC.

Win32 Support
ODBC support is totally integrated in FIDAL for the Win32 platform.

Linux Support
On Linux, you must install both unixODBC and TA-unixODBC. Check the download section and read the documentation included in the package.

MySQL Direct
Some users might wish to bypass ODBC and access directly MySQL database through the MySQL++ connector. For further information, download the TA-MySQL package and read the included documentation. For now, this works only on Win32.

2.0 Connecting to the database

Access to a SQL database is added with FD_AddDataSource. Multiple database access can be added to the same unified database.

FIDAL can reach a database using the information provided in the "location" parameter.

Example:
   FD_AddDataSourceParam param;
   memset( &param, 0 , sizeof(FD_AddDataSourceParam) );
   param.id       = FD_SQL;
   param.location = "odbc:myDatabase";
   param.username = "JohnDoe";
   param.password = "secretword";
   ...
   retCode = FD_AddDataSource( udb, &param );
   if( retCode == FD_SUCCESS )
       printf( "SQL data source successfully added" );

ODBC
Connection to an ODBC database is specified with the following format: "odbc:<database name>"
No host name is supported; the ODBC data source should have been already configured on the local host. The reserved name "DEFAULT" connect to the default ODBC data source defined on your host.
Examples:  
 param.location = "odbc:myDatabase";
 param.location = "odbc:DEFAULT";

MySQL Direct
Connection using the MySQL direct  library is specified with a string with the following format: "mysql://<server>:<port>/<database name>"
The server name is just a name (like "localhost") or FQDN or IP address.  No username/password supported here. The port is optional and defaults to 3306 (MySQL default port). The path has one segment only and is interpreted as the database name (and a default stock symbol in case other fields do not  specify how to obtain stock symbols).
Examples:
 param.location = "mysql://myserver.mydomain.org/myDatabase";
 param.location = "mysql://10.40.15.3/anotherDatabase";
 param.location = "mysql://localhost/myDatabase";

Authentication
param.username and param.password are used to log in to the server. May be NULL if not needed. In that case, username defaults to user login name, password defaults to an empty password.

3.0 Setting Queries

Up to 3 queries can be needed to be specified to extract the index and historical data from the database. Example:

FD_AddDataSourceParam param;
memset( &param, 0 , sizeof(FD_AddDataSourceParam) );
...
param.category = "SELECT category FROM categoryTable";
param.symbol = "SELECT symbol FROM symbolTable WHERE cat='$c' ";
param.info = "SELECT date,close FROM myData WHERE cat='$c' and sym='$s' ";
...
retCode = FD_AddDataSource( udb, &param );
if( retCode == FD_SUCCESS )
   printf( "SQL data source successfully added" );

Category/Symbols Query
In FIDAL, a data source must provide an index of all available category and symbols.

The param.category contains the default category string or an SQL query returning all categories within the database. This can be done either by returning a record set with column "category". Optionally, the query may return column "symbol" identifying all symbols within a given category. All recognized columns must be of type CHAR or VARCHAR.

The param.symbol contains an SQL query returning all symbols in the database. This field is used only when param.category query does not return a column named "symbol" (type CHAR or VARCHAR). If this field is NULL, (and the query in param.category field did not return column "symbol"), the database name is used as the symbol. If this field does not contain an SQL query, it is taken literally as the symbol name.

Usually, categories and symbols are not orthogonal (i.e. the list of symbols depends on the category). To facilitate this in queries, a number of placeholders are recognized and expanded with proper values:
   $c expanded with the category string
   $s expanded with the symbol string

Applicability of placeholders is as follows:
  param.category: none expanded
  param.symbol: $c expanded
  param.info: $c $s expanded

Example:
param.symbol = "SELECT symbol FROM symbol_list WHERE category = '$c' ";

Data Query
The param.info identifies the SQL query to execute to get the price bars records. The columns may have to be renamed to some standard names to be able to interpret the results. Example:
      param.info = "SELECT date, time, quote AS close FROM quotes_intraday_AEX";

 Recognized columns are:

Fields date and time (if present) will be interpreted as ISO formats (SQL default).

Timestamp placeholders
To optimize the execution of the data query, the param.info parameter is subject of expansion of the following placeholders:
   $< expanded with the begin date
   $> expanded with the end date
   $[ expanded with the begin time
   $] expanded with the end time
Mnemonic: think of $< and $> as opening and closing angle brackets defining a time span. $[ is a stylish $t (for time), and $] is a closing (matching) bracket for $[.

Example (MySQL):
param.info = "SELECT * FROM stock_data WHERE '$<' <= date AND '$>' >= date ORDER BY date";
param.info = "SELECT * FROM stock_data WHERE date BETWEEN '$<' AND '$>' ORDER BY date";

Example (ODBC):
param.info = "SELECT * FROM stock_data WHERE {d '$<'} <= date AND {d '$>'} >= date ORDER BY date";
param.info = "SELECT * FROM stock_data WHERE date BETWEEN {d '$<'} AND {t '$>'} ORDER BY date";

IMPORTANT NOTES
The order of the data is important! FIDAL cannot process unordered  historical data. Data should be ordered by date, time (or just date if time is not available). All rows should be unique on (date, time) key (or just on date if time is not available).

The portable way of specifying dates, times, and timestamps in ODBC is by using escape sequences:
  Date: {d 'yyyy-mm-ss'} e.g. {d '$<'}
  Time: {t 'hh:mm:ss'} e.g. {t '$['}
  Timestamp: {ts 'yyyy-mm-dd hh:mm:ss'} e.g. {ts '$< $['}

4.0 Historical Data

Once the data source are added, the historical data can be retrieved like usual with FD_HistoryAlloc. Each call will cause a database access, there is no local caching of historical data.

5.0 Command Line FD_SQL

The command line utility "fd_sql" allows to quickly prototype and tests your queries. Type "fd_sql" with no parameter for a description.

There is no database included in the package, so you will need to build your own. Here is an example of database. This is not a design recommendation, just one way of doing for demonstration purpose:

The following commands shows how to access the database category/symbol index and the data. The content is displayed in italic bold:

P:\FIDAL\c\bin>fd_sql -c odbc:SQLDemo "Select category from TimeSeries"
US.NASDAQ.STOCK
US.NYSE.STOCK
ZZ.OTHER.OTHER

P:\FIDAL\c\bin>fd_sql -s odbc:SQLDemo "Select category from TimeSeries" US.NASD
AQ.STOCK "Select symbol from TimeSeries where category='$c'"
GOOG
MSFT

P:\FIDAL\c\bin>fd_sql -dd odbc:SQLDemo "Select category from TimeSeries" US.NAS
DAQ.STOCK "Select symbol from TimeSeries where category='$c'" MSFT "Select Date,
Close from DailyData where TimeSeriesId=(Select TimeSeriesId from TimeSeries whe
re category='$c' and symbol='$s')"
2004-11-23,26.53
2004-11-24,26.64
2004-11-26,26.60

Here is the corresponding code to add access to that database:

FD_AddDataSourceParam param;
memset( &param, 0 , sizeof(FD_AddDataSourceParam) );
param.id = FD_SQL;
param.location = "odbc:SQLDemo";
param.category = "select category from TimeSeries";
param.symbol = "select symbol from timeSeries where category='$c'";
param.info = "select Date,Close from DailyData where TimeSeriesId=(select TimeSeriesId from TimeSeries where category='$c' and symbol='$s')";
retCode = FD_AddDataSource( udb, &param );
if( retCode == FD_SUCCESS )
   printf( "SQL data source successfully added" );

Here is the code to extract daily data for MSFT:

FD_History *history;
FD_HistoryAllocParam haParam;
memset( &haParam, 0, sizeof(FD_HistoryAllocParam) );
param.category = "US.NASDAQ.STOCK";
param.symbol = "MSFT";
param.period = FD_DAILY;
FD_HistoryAlloc( udb, &haParam, &history );
 

6.0 FD_AddDataSource Parameters Details

Here are a quick overview of how each FD_AddDataSource parameters are used for an ASCII data source:

'param.id'
Must be FD_SQL

'param.location'
URL describing the access to the SQL database

'param.info'
SQL Query to extract the price bar data.

'param.category'
Fix category string or SQL Query to build the list of category.

'param.symbol'
Fix symbol string or SQL Query to build the list of symbols for a given category.

'param.country', 'param.exchange', 'param.type'
Unused. Must be NULL.

'param.username', 'param.password'
Used to log in to the server. May be NULL if not needed. In that case, username defaults to user login name, password defaults to an empty password.

'param.period'
Must be set to the period value that is used by this source e.g. FD_DAILY for sources delivering daily data.

'param.flags'
FD_REPLACE_ZERO_PRICE_BAR or NULL.