Roadmap item vfp embedded SQL via Advantage

[mainhatten]

Hi Robert,

From Roadmap:
Implementation of FoxPro embedded SQL based on Advantage Database Server
that way a lot of functionality already in Advantage can be reused, which makes LOTS of sense, esp when I look at the enhancements already finished there.

Guessing that you need to hook ACE via ALS license installed in the system that raises for me the question if FoxPro embedded SQL can be called if running (in the future under .Net5 or .Net6) on ARM (iOS new Macs, iPads and iPhone) and/or Xamarin Android ? Those are explicit compile targets under Xamarin, but Advantage only installs on Linux and Windoze on intel86/AMD64 CPU architecture last time I checked (when Thread "A simple program - many errors!" was fresh topic and your call to SAP was imminent.

If you could recompile sources of ACE to run under the other Xamarin backed CPU/OS combinations or SAP guarantees that as pending release enhancement soon, that would be great and probably best way to save implementing already existing functionality.
Running on mobile without ORM on just a thin local cursor layer selected from backend data via cursoradapter to munge locally is the thing I cannot do in vfp - x# "just" as a newer optionally type safe desktop system is still interesting, but has to compete with VFPA and VFP C++ Compiler running as 64bit process if wanted or compiling to C++ and with 32/64 bit options running the already optimized vfp local data engine on dbf cursors with known benefits and limitations.

Offering vfp embedded SQL engine on ARM based mobile, network or home automation device is close to "no contest" or "winning by default" at least for me, as that (including embedded/smaller stuff like RasPi) are growing much stronger than traditional PC. Due to most ARM to still offer less CPU power (Apple M1 currently only exception) benefit of local caching to dbf will give more benefit, the weaker the local CPU is.

Could you shed some light if/how you plan to offer FoxPro embedded SQL on ARM/iOS/Android and possibly WASM as compile targets ? Xamarin offering relatively easy path for Dotnet IL to non-intel at least for me the biggest lure.

Any further news (besides option to buy Advatage directly) on the SAP phone call on plans to support more than intel CPU ? Our phone conference was till 17:45H, so no chance for me to chat on the topic...

thx in advance
thomas

[robert]

Thomas,
Our plans to map VFP embedded SQL to ADS will use the existing ACE layer on Windows.
I am afraid that this will NOT work on other platforms.
And I do not expect that SAP will come with an ADS layer for other platforms.
For our ADS dealership we do not deal with SAP directly but with one of their distributors.
SAP does not seem to be interested to communicate with a small company like ours.

Robert

[mainhatten]

Our plans to map VFP embedded SQL to ADS will use the existing ACE layer on Windows.
I am afraid that this will NOT work on other platforms.

Bummer! But from reading manuals I was afraid of that... In theory, most of my envisioned needs could be solved via a tweaked implementation of cursoradapter over Ado.Net standard objects, after Query redirecting the datareader to fill a table instead of .DataTable, but without embedded SQL to query local tables another layer needs to be queried. vfp ODBC is hidebound and intel only, Advantage would support newer features like binary indices and more, cData supports only FPW data types, DEVART seems to be intel only..
.
Do you have any knowledge of a native or ODBC driver to vfp-compatible dbf layer which could be recompiled for ARM or tweaked where necessary ?

Going for SQLite as local data layer on ARM might be a possible alternative in theory, but lacks some data types and optimization options IIRC.

And I do not expect that SAP will come with an ADS layer for other platforms.
For our ADS dealership we do not deal with SAP directly but with one of their distributors.
SAP does not seem to be interested to communicate with a small company like ours.

Perhaps I should go to a shareholder meeting, prepare and submit question why such a simple recompile is not offered, esp. as MySQL and Postgres can be installed on RasPi

When your dev team was brainstorming on embedded SQL for vfp, was Advantage only realistic option or were there serious contenders on how to implement from scratch or (much preferred) existing source code ?

About 35 years ago I had built a simple SQL-Select implementation on a now forgotten dBase 3 file handler, SQL parser ported from a free source version in Pascal - worked,but was nowhere near the perf of even first FoxPro (Dos) Selects, even if it sported an index-based optimizer, so I fully realize the benefits of tested and already optimized sources

X# on Android/Xamarin as compile target probably a topic better handled on the product forum - it was mentioned (demoed?) a few times years ago (I searched for that topic when learning about X#), but seems less relevant/pressing target in 2021 as it slipped from roadmap ?

regards
thomas

[robert]

Thomas,
- We have not forgotten about Android/Xamarin and also IOS/Xamarin: to support these we will add support for .Net Core / .Net 5 / .Net 6. That is our best bet.
- Yes we have looked at other possibilities for embedded SQL. ADS is the fastest way to bring this to our product, but indeed it has the limitation of being Intel only. Ideally we would write our own SQL parser and bind that parser to our RDD system. However the reality is that our team is too small to do that at this moment. And we do not want to delay the embedded SQL support, because we want to get ready for FoxPro asap.
- The problem with SAP and compiling ADS for other platforms is that SAP is not really interested in ADS. I get the impression that ADS has a position within SAP like VO had inside CA. SAP as a company makes its money in a completely different market.

Robert

[mainhatten]

Robert

- We have not forgotten about Android/Xamarin and also IOS/Xamarin: to support these we will add support for .Net Core / .Net 5 / .Net 6. That is our best bet.

I should have asked about the level of interest in current FOX members, as they (rightfully) set the priorities of development effort - in the open fora the level of interest in mobile/ARM app development is not really overwhelming

- Yes we have looked at other possibilities for embedded SQL. ADS is the fastest way to bring this to our product, but indeed it has the limitation of being Intel only.

If I take an eagle eye view on my usage of vfp and SQL, there are 2 very different use cases:
for application development SQL is used to create a well defined local subset of data to display and perhaps modify/persist. This creates the Cursor/View/Buffered Table layer which allows "playing" inside the biz object rules to get to a state able and deemed worthy enough to persist - which then is done via easy call to top biz layer object cascading down to the governed Cursoradapters or Cursors also firing their Table-Update. The "CUD" evoked by those Table-updates is simple and easy to map into xBase Append/Replace, Replace, Delete commands.

The Selects are - most of the time - relatively simple Selects of single tables with filters via where or "Joins" used as filter methods. Those could be translated either into xBase JOIN commands or explicitly rolled out into scan loops - thereby allowing the table-buffering analogue already built into ADO.Net to be employed without replacing dbf with SQLite database layer on mobile. Between 80 to 95% (if you include lookup mappings into data store housing entries for drop-down lists for instance) should be handled, while a few tables either need server SQL pre processing, local post processing or other special effort, mostly for display or recalculating along biz rules.

Second use case is work station data munging and data mining - those will stay desktop bound (and could to large degree also be handled via a C/S SQL install), so depending on Advantage, staying in vfp/vfpA or moving to Postgres are all valid options to this data miner, as long as keyboard, mouse and big screens are in the deal.

Ideally we would write our own SQL parser and bind that parser to our RDD system. However the reality is that our team is too small to do that at this moment. And we do not want to delay the embedded SQL support, because we want to get ready for FoxPro asap

I think ANY reuse will be beneficial, which is the reason Advantage ease makes "only Intel" palatable. Writing SQL parser from scratch might add too much technical burden as SQL evolves as well - I thought more on the lines of adapting current Open Sources of SQLite to RDD, as their sources have been transpiled and ported - there even exists a version running on Javascript Arrays as well (AlaSQL) or reusing other tools/offerings (did not research what is found in OS or offered commercially: second might be too costly...).

- The problem with SAP and compiling ADS for other platforms is that SAP is not really interested in ADS. I get the impression that ADS has a position within SAP like VO had inside CA. SAP as a company makes its money in a completely different market.

Here the idea could be an offer to SAP to totally outsource further maintenance work (as SAP is not really interested in ADS, offer to reduce running cost) - I don't think Advantage is much of a profit center there, but could give X# developers secondary, relatively stable source of revenue easy to predict. X# could market ARM side, perhaps not stressing single code base toooo much, as result of porting effort. As long as there is a buy-in clause if SAP decides to stop Advantage altogether, it could be profitable to both sides. Such a buy in option instead of dead-ending Advantage might be palatable to SAP if it guarantees part of later revenue after buy in, similar to a share of the revenues earned on the ARM side resulting from a port.

Separating ARM licensing from current revenue model via other biz entity is probably only way of coexisting, as things like home automation cannot be saddled with a "cost per unit sold" for each home automation center if SQLite/MySQL/Postgres can be installed for free on those devices. IMO selling point there is ONLY reduced developer cost offsetting a developer license..

my 0.02€
thomas