ODBC Data & AFL Plugin - results are different from same SQL table

I am seeing an apparent discrepancy between the way that the ODBC data and AFL plugin are operating.

I am using AmiBroker 6.30 with the 1.7 ODBC plugins.

I am accessing a SQL Server table with the following fields.

[Symbol],[Date],[Open],[High],[Low],[Last],[Volume],[OpenInt],[BuyVolume],[SellVolume]

I have no problem accessing the basic price data via the following SQL line.

SELECT [Symbol],[Date],[Open],[High],[Low],[Last],[Volume] FROM [tbl_TEMP] ORDER BY DATE DESC

But when I try and access one of the fields using one of the AFL array functions, it is only bringing in a small segment of the data available. This can be seen in the bottom pane in this image.

Capture_A

The same result occurs whether I use:

odbcGetArray( "tbl_TEMP", "@ES#", "BuyVolume" );

or

odbcGetArraySQL( "SELECT [BuyVolume],[DATE] FROM [tbl_TEMP] WHERE SYMBOL='@ES#' ORDER BY [DATE] ASC" );

I thought that it might be related to this thread here (ODBC issue -- Single value filling the arrays ) but because the data is all coming from the same table (and thus all have the same timestamps/same source) I thought that it could a different problem?

Does anyone have any suggestions as to what I might be overlooking or what I might try?

thank you,

Can anyone advise if the ODBCA.dll plugin is functional with 6.30? It would help me to troubleshoot if I knew if others were having success with it or if I should look for another solution.

I'm following your thread with great interest, hoping someone will chime in.

If you are specifically pointing at 6.30, can you first test with 6.20 and confirm that the discrepancy actually exists ?
What I mean is, you haven't mentioned anything about previous versions and whether you have experience with them earlier, whether the same setup worked etc

6.20 and 6.30 (and many earlier versions) work exactly the same with regards to plugins. ODBC/ODBCA plugins works fine. They were written 12+ years ago and they are working for everybody for 12 years with EVERY version of AmiBroker, 12 years back and forth, without changing any single character in original source code.
You just need to make sure to have PROPER data in the database you are accessing. Especially the DATE stamps must be correct and ALIGNED between symbols.
As documented in http://www.amibroker.com/odbc.html there is strictly defined format that is supported. Follow the instructions. Things that are not documented are not supported.

It must be something that I am doing and knowing that is helpful. thank you.

My data, while all the fields are coming from the same table - both for the O,H,L,C and the BuyVolume and SellVolume definitively has data holes - meaning that there are missing rows for where nothing was traded - and I think that is what you mean about that the data needs to be aligned? I'll pad the data from my database and report back with the results ...

Here is my original data:

originalData

I tried padding the source data table with a row for every second, but ended up with some very strange results. AmiBroker fills in the nulls in the O,H,L,C fields with numbers like -20000 or so.

My padding attempt:

FromSQL

Result in Ami:

FillsInNulls

One thing that I am not understanding is that if the timestamps and number of rows of the data that I am requesting through the odbcGetArraySQL() function match exactly with the data in the AmiBroker database, would any of the data holes matter? Would not the data requested through the odbcGetArraySQL() function line up perfectly with the timestamps from the data coming from the ODBC AmiBroker database?

Show your database settings

DatabaseSettings

This is what the data types are:

tableTypes

SQL NULL is totally different animal than AFL Null. Don't place NULL records in your SQL database. Missing data should be just that - missing. NO records for missing data.

2 Likes

Could you go back to your original data , without NULLs, and post again your first chart using 1 minute period ?

For sure - here is the first chart, with the original data, but zoomed in to the start so that you can see what is happening.

1min

Results of some more testing. I tried remapping the [BuyVolume] to Open and the [SellVolume] to Close in the ODBC interface. I then plotted out both fields in the top pane. The [BuyVolume]/[SellVolume] data is there, and is available from the ODBC plugin.

I just can not get odbcGetArraySQL() or odbcGetArray() to retrieve the same information in the bottom pane.

CanGetFieldsRemap