I have been writing FoxPro Code for almost 30 years and have written one application using C# and WebForms. I have a legacy VFP application with several hundred forms that I would like to convert to run from the cloud and replace the VFP tables with SQL. What would be the pros and cons of using Web Connection to do this? I am struggling to learn Angular and to be honest it looks like a complicated pain in the ? for a large project.

Hello! Have you seen FoxInCloud? I have an application made in production and I am very satisfied. It is also based on West Wind, takes advantage of codes and forms created for the desktop in the web. If you have any questions, I can help you.

Bob,

It depends on what you actually want to do. Angular is just one way to build a Web application - there are number of others that take a different approach.

You can build

• Purely server side applications (ie. MVC style)
• Use a different framework (Vue Js, React etc. although similar to Angular)

The former lets you stick more with FoxPro, but produces Web applications as 'pages' that refresh, so they don't feel quite as interactive as JavaScript drive SPA applications of the latter type.

Either way - if you've never built a Web application before there's a ton of stuff that one needs to work with and understand from HTML to CSS on the design side, to scripting and JavaScript and a pure client side disconnected client.

If you're starting for the first time using something like Angular, Vue or React is going to be painful because there are so many concepts to get going with. But once you do know the HTML and disconnected features then building rich applications usiong these client side technologies actually ends up being more similar to a classic FoxPro application compared to a purely server driven application.

As Arcadio points out there's also FoxInCloud which lets you use FoxPro forms which is also an option but it too comes with its own set of learning you have to do to understand what works and what doesn't...

The best thing I can recommend is to look at walk through's and see if you can build something simple first. Before diving head first into a big project it'll be helpful to build something small just to get a feel for the technology(ies).

+++ Rick ---

Hi Rick,

Some further details on top of what was written so far about FoxInCloud in this thread…

its own set of learning you have to do to understand what works and what doesn't...

FoxInCloud comes with 2 free tools that greatly help in this matter:

1. By scanning the whole application code, FoxInCloud Adaptation Assistant tells exactly what is supported or not in this specific application and, if any unsupported features, where they occur: in which program, class, procedure, etc.
   Here is mainly what FoxInCloud does not currently support:

• Formset: displaying several forms at a time in a portable device would be awkward
• ToolBar: could easily be supported however the menu (.mnx and plain commands) support seems so far sufficient
• define window and @say…: FoxInCloud only supports OOP forms, either .scx or .vcx
• some activeX controls: currently FoxInCloud supports:
  • PDF viewers: see http://foxincloud.com/tutotest/bs/report.tuto
  • Shell.Explorer.2 (Browser control): see http://foxincloud.com/tutotest/bs/ie.tuto
  • MSComctlLib.TreeCtrl.2 (Treeview) and linked MSComctlLib.ImageListCtrl.2: see http://foxincloud.com/tutotest/bs/treeView.tuto
  • MSComctl2.MonthView.2 and MSComctl2.DTPicker.2 (date time picker) could easily be implemented at first user demand using a web-based date-time picker like illustrated in http://foxincloud.com/tutotest/bs/Event.tuto
• .drag* events, however .OLEdrag* events are supported; see http://foxincloud.com/tutotest/bs/dragDrop.tuto
• some marginal events like .dropDown() (could be supported in Bootstrap mode) and .scrolled(); all other controls and events are supported either on the server or on the client as demoed here

2. In case of a doubt, the FoxInCloud Live Tutorial (FLT) comes to the rescue where developer can see the running code and the original VFP form (very similar to the West-Wind Web Connect sample page). The FLT is available on-line and on localhost after installing the FoxInCloud Application Server.

Here are the main FoxInCloud concepts that a developer needs to understand:

1. modal form callback: like for any Web application, code involving a user decision must be split into before and after asking the user; compared to a traditional VFP app., the code processing the user's answer must be moved into a separate method called call-back. Each FoxInCloud base class comes with 10 call-back methods named .wFormCallBack*(). Using these methods makes it easier to locate such code in development.
   As FoxInCloud only supports call-back code in methods of forms or form members, if user question is raised in an independent procedure, a reference to the form or form member must be passed to the procedure and response code moved to a method, or even the whole procedure (as FoxInCloud inserts a layer of classes under your application, code can be moved to one of them).
2. application startup: as FoxInCloud supports hot restart (restarting the server seamlessly for the users), all application-wide resources must be made available at startup and not during the course of running forms: public vars, SQLconnect(), SET…, set database, set proc, set path, etc.
3. form.Init() and *.destroy() behave slightly differently: if these methods are not used quite the way they are intended, moving code to other methods may be necessary.
4. reporting into PDF: maybe not a big deal as many VFP apps already implement some PDF reporting, however directing the output file to the browser requires a slight adaptation

The other adaptations can be learnt and understood just by doing the first one, no concept to be understood, just routinely adapting the code by replacing and/or moving and/or adding VFP instructions.

replace the VFP tables with SQL

Unless Bob is facing the 2 GB limitation, using VFP tables and xBase commands in a FoxInCloud Web Application is… a benefit in terms of response time, and eliminates the risk for data corruption because of technical (power outage) or malicious (data hacking) reasons: the VFP tables are completely hidden behind the Web App Server and only the maintenance personal can access it, just like a SQL database.

FoxInCloud comes with a bullet-proof 2-way VFP data synchronisation class that allows sharing data updates between a Web Application and a LAN Application.

The original reason I was leaning toward SQL was the ease of hanging multiple Basic Azure Sql databases off a single server and my experience so far with one app using SQL is I haven't had any problem with index corruption like we occasionally run into with VFP. It would appear that with either Web Connection or FoxinCloud, I could start with existing VFP tables and migrate to SQL if needed. I will take a short while to look at both to see which way will be best to go. Thank you both for your replies.

I couldn't recommend using FoxPro tables for any new development. There are just too many issues to deal with:

• Memo Bloat
• Lock issues in high volume apps
• Index corruption
• Exclusive access required for packing (and the need to for packing in the first place)

For this reason I think any new application development and especially Web development should look towards using some sort of SQL backend. It doesn't have to be SQL Server either although that's probably the path of least resistance with FoxPro and SQL Express is free (up to 10gb per Db).

That said, Web Connection (or FoxInCloud I suppose) doesn't really care whether you're using Fox or SQL data since those are specific to the application rather than the frameworks.

If you do want to start with FoxPro data, using the wwBussinessObject classes provides a simple business object layer that can provide some abstraction for your data access so that it can run both FoxPro and SQL data or allow you to more easily switch in the future - mainly for CRUD operations.

+++ Rick ---

Our customers work around the VFP tables limitations with a routine called at server startup: if it can gain exclusive access to the database and/or tables, it runs PACK MEMO (removes memo bloat) and REINDEX (prevents index corruption and makes Rushmore optimisation faster).

They usually schedule a full Windows Server restart once a week (Sunday around 3:00 AM in most cases); As the WWWC dll loads COM servers in sequence, the first server can always gain exclusive access to the tables and perform the above maintenance routine.
Using wc.wc?_maintain~MaintMode also does the trick.

WRT locking issues, as with FoxInCloud the app and tables usually lie on the same machine, an easy workaround it to decrease Sys(3051) to the minimum (100 ms) and increase SET REPROCESS to a high value (such as 100, maximum being 32,767) … of course this requires a conservative use of LOCK() and RLOCK() by the app., both points that FAA identifies and warns about. Best remains to rely on automatic locking by VFP, as explained in chapter Locking Data of VFP help file.

BTW I noticed that wwServer uses SET REPROCESS TO 2 (THIS.ADDOBJECT("oReprocess","wwEnv","REPROCESS",2)), and this is a little too low even for adding records to the log and session tables. awServer, derived from wwServer increases this value to 10.
A FoxInCloud application being hosted in a separate object of class Session, it runs its own independent SET REPROCESS (scoped to the current datasession).

Thierry,

The fact that you have to do all of this is kind of making my point 😄. And in a load balanced environment (or even in local multi-instance setups?) this isn't going to work.

Not saying that it's not possible to make FoxPro tables work for this scenario. You can, but it's just a royal pain in the ass especially if you have large databases as these Packing and Reindexing can take a lot of time all while the site is down. This is something that you want to avoid in any online environment.

Take for example this message board. It has a large database with very large memo files. The reindex/pack operation takes about 2 minutes to run - that's downtime that I can't work around in any reasonable way (other than archiving data separately) - while that's happening the site is down. To do this I have to push the app into single server mode, do the reindex and flip it back. I can automate that process, but still it's a pain in the ass. Now if I use a SQL backend I never have to worry about memo bloat (which is the main reason for packing/reindexing in this case). So yeah it works, but it's far from ideal. And this is a low priority example where the down time doesn't matter, but many applications definitely care about downtime and two minutes is a long ass time to be unavailable.

Additionally I've had to clear out data because the tables were approaching 2 gig. Before even getting there perf started falling off drastically. So I dumped data - I guess I could have archived and used workarounds to split my data, but this is just bullshit work that in this day and age we shouldn't have to deal with. There are many (free) database solutions that work just fine with FoxPro, that handle all this automatically plus properly storing Unicode data in the database.

Do you really think most people running FoxPro tables think of these issues when they throw together an application at first? Nope - that comes later usually after the apps been in production for a while and then you go - oh shit, we're not supposed to be down for a even a minute when we provide a SAAS solution that is supposed to have a specified level of uptime. We're hitting 2 gig what do we now? You end up having to make major redesigns to make it work.

+++ Rick ---

Allow me to give my opinion too. I think that, in addition to the app downtime, another thing that influences any project, is time that will be spent in an app to adapt to work on a sql database. Obviously, a sql database is better than dbf tables, however, what is feasible or not depends on the time and budget available to adapt. Sometimes a weekly stop is more "cheaper" than a complete overhaul to work on a sql database. This depends on the business the application is intended for. Stopping at some point will always be stopped. Like forced windows updates, for example. Anyway, I think that there is no single recipe.

I'm not suggesting to switch if you have an existing application that works. The cost of conversion probably outweighs the cost of dealing with the complications of having to deal with FoxPro data in a server environment. And if the application is old and running on FoxPro data in the first place it's also very likely that the data demands and traffic aren't going to be very high in the first place.

Sure - if you take an existing application then that's a big consideration and making that change is likely not trivial depending on the size. But for a new application I think FoxPro data shouldn't be considered for anything but local desktop applications. Anything in an active shared environment IMHO should be using some sort of SQL engine to avoid the issues I mentioned and if you're dealing with a greenfield application I think going with a SQL data is a no-brainer.

+++ Rick ---

OK, to make it clearer for the OP, I propose to put it this way:

• if you plan to adapt to the Web with FoxInCloud an existing VFP application using VFP tables, unless you approach the 2 GB limitation (and you already have the issue on your desktop application) and/or you can't afford 5 minutes additional downtime each week (planned system restart each Sunday @ 3:00 AM, and you probably have a similar routine on your desktop deployment), you can keep your VFP tables and xBase code for startup, and later consider moving to a SQL database…
  Obviously moving to the Web per se raises no additional constraint on the data storage compared to the desktop: all the issues that may arise on a Web deployment already exist on current desktop deployment.
• if you plan to stay with a VFP desktop application, given the SMB issue and other instabilities and vulnerabilities inherent to sharing files over a network, you will probably consider moving to a SQL database.

About downtime, I don't know any business application that needs to be up and running 24/24 7/7… usually ERP users tend to take a rest over the weekend and sleep at least one night in the week: Saturday or Sunday according to the culture.

@Rick

I share your experience about performance decreasing when the table size increases; I've seen it not only for VFP tables, also for PostgreSQL.
WRT to VFP tables, I've seen it more correlated to the number of records rather than the size in GB; eg. a simple SEEK will be slower over 100k records. Reason why we auto-archive large tables at server startup, eg. see the sample code appended.

@Arcadio

I share the opinion that any tech move must be seen through a benefit/cost/risk analysis, and priorities set accordingly; software companies no longer have unlimited resource to stick to each of the latest tech toy, especially in the Web world where techs raise and die so fast. You need to carefully evaluate each step, taking into consideration the weigh of 20 years and thousands LOC of 'legacy' software that still pay your bills.

procedure awCAScheckArchive && Archive awSales!awCAScheck at server startup
lparameters result as String && @ résultat

local success as Boolean;
, loAsserts as abSet of abDev.prg;
, loAppHost as awAppHost of awAppHost.prg;
, loSelect as abSelect of abData.prg;
, nSeconds as Number;
, lcAlias as String;
, lcAlias_ as String;
, ltArchive as Datetime;

success = .T.
store '' to lcAlias, lcAlias_

if .T.;
 and (!lDevMode() or .F.); && .T. executes in dev mode
 and wlAppHost(@m.loAppHost);
 and m.loAppHost.oConfigApp.lawCAScheckArchiveAtStartup

	private pltModKeep, plSync
	store .T. to pltModKeep, plSync

	m.loAppHost.ServerFormUpdate([Archiving awCAScheck ...])
	nSeconds = Seconds()
	loAsserts = Iif(lDevMode(), abSet('ASSERTS', 'ON'), .null.)

	loSelect = abSelect()
	ltArchive = Dtot(Gomonth(Date(), -1))

	success = .T.;
	 and lExclusiveForce(@m.lcAlias, 'awSales!awBPserver');
	 and Lower(m.lcAlias) == Lower('awBPserver');
	 and lExclusiveForce(@m.lcAlias, 'awSales!awCAScheck');
	 and lExclusiveForce(@m.lcAlias_, 'awSales!awCAScheck_') && and MakeTransactable(m.lcAlias) and MakeTransactable(m.lcAlias_) && inutile puisque ce sont des tables de base de données
	if m.success
		
		select awCAScheck;
			from (m.lcAlias);
			where tCheck < m.ltArchive and nCAS > 0;
			into cursor awCAScheck_C
		use
		if _Tally > 2000 && minimum # of records to be archived
			
			begin transaction

			try
				select (m.lcAlias_)
				append from (Dbf(m.lcAlias)) for tCheck < m.ltArchive and nCAS > 0 and not Indexseek(awCAScheck, .F., m.lcAlias_, 'awCAScheck')
				select (m.lcAlias)
				delete for tCheck < m.ltArchive
				result = Textmerge([<<_tally>> records archived in <<cSeconds(m.nSeconds)>>])
			catch to result
				success = .F.
				result = cException(m.result)
			endtry

			if m.success
				end transaction
				select (m.lcAlias)
				pack && pack in (m.lcAlias) provoque une erreur
				select (m.lcAlias_)
				pack memo
			else
				rollback
			endif

		else
			result = Textmerge([too few records to archive (<<_Tally>>)])
		endif

		select awBPserver
		pack memo && in awBPserver : erreur de compilation

	else
		result = [could not open tables or make them transactable]
	endif

	use in Select(m.lcAlias)
	use in Select(m.lcAlias_)
	use in Select('awBPserver')
	store '' to lcAlias, lcAlias_

	m.loAppHost.ServerFormUpdate(m.result + CR)

	if lExclusiveForce(@m.lcAlias, 'awSales!awContact')
		select (m.lcAlias)
		reindex
	endif
	use in Select(m.lcAlias)

endif

assert m.success message cAssertMsg(m.result)
return m.success
endproc