FoxPro Ramblings

ASP.Net Development Crash Course

Michial Thompson — Sat, 03 Feb 2007 10:14:38 GMT

I should probably be writing articles titled an ASP.Net Crash Course for Developers right now, the past 10 days or so I have been involved in dealing with an ASP.Net Application written by someone else, as well as two ASP.Net application which I am developing as well.

The application written by someone else is the product of another failed outsourcing project, I was asked to initially do a code review of this mess, as well as now I am having to re-write a large majority of it. When are companies going to realize that just because they call themselves programmers, and are cheaper than the people sitting down the hall doesn't mean that they even understand the concept of doing business in the United States? (This is a real pet peeve of mine, and I'll save this rant for another post.)

Anyway, so now the re-write of the outsource mess is project number one, and Project Number two is a Web Interface into my Business's Software that enables all the same functionality as the FoxPro App.

Now here's the kicker, My experience to date in ASP.Net is to use it for developing Web Services to move functionality requiring major processor horsepower from the desktop application to the Server. And my experience for Web Development in General is limited to HTML 3.0 and ASP using VB-Script. So for the past 10 days I have been struggling with Visual Studio 2005's UI for developing Web Forms, and struggling to create a general enough Application Framework that going forward I won't be forced to re-write.

Now my programming past includes c and c++, and I've been doing Web Services in c# for about a year now, so I'm no real stranger to the languages, and HTML 3.0 is more than sufficient to get good looking Web Forms. So what's the big deal here? Well I've never been a big fan of buzz words, and with .Net Microsoft changed all the names for things, so doing searches for information is where the real challenge is.

BUT there is light at the end of this tunnel for me, I finally got over the Sand Dune yesterday, and now I've gotten all the major aspects of developing these applications figured out. The last two major obstacles for me were the Page.Master files, and User Controls.

The last application framework that I developed was in 1999-2000, and it was FoxPro based. It's designed around using SQL Server and FoxPro. That project's original goal was to separate the Presentation Layer from the Business Rules, and everything from the Data Layer. When the applications were small, it worked very well, but when the applications started to grow in complexity, they began mixing the three layers together to tightly that it's no longer possible to replace one layer without affecting all three.

In the .Net arena I don't plan to make that mistake again, this time I know most of the major pitfalls, and I chronicled the history of my FoxPro framework well enough that I think I know the major problems that required me to bring the layers more tightly together, so this time I think I can do a better job at separating them.

Microsoft Vista...

Michial Thompson — Mon, 29 Jan 2007 21:04:43 GMT

Well tomorrow is the day I have been waiting for, Microsoft Vista is due on the shelves tomorrow. I'm planning to go buy my copy at lunch so that I can get home with it and thing about installing.

I've still not gotten a direct answer about the features that I need, so I still don't know what version I will be getting yet. I've been trying to find out if the Home Premium has a local IIS and the Remote Desktop client so that I can still do my web development and access the servers, if it does then that's the version I am buying.

If not then I will be buying the Ultimate Version, these features are absolute requirements, even more important to me than the ability to simply run.... I'll keep a running blog on here about what I think of Vista.

Accessing VFP Tables Across Network w/ .Net

Michial Thompson — Thu, 11 Jan 2007 17:09:18 GMT

I run into an interesting issue this week that I needed to resolve, and I have to admit it was fun and interesting. We were developing a Web Service for a Third Party interface to FoxPro free tables. Initially we were going to do this by developing a VFP Web Service, but we found out that the Soap Toolkit is being dropped by Microsoft, and that to make it work on the server we would need to enable something that we didn't want to enable to make it work.

Anyway, as I started writing the c# Web Service everything was going well, I was able to access the data that was local, and the Web Service was working perfect. Then when I got ready to deeply it to the Development Test platform, things broke. The reason they broke was that the VFP Tables were stored on a different server than where the Web Service was running, and ADO could not access the tables on the mapped drives from IIS.

Then as I got further into this, I was finally able to get my Web Service to Impersonate a user other than the IIS user, and that got me connected to the server, but then things failed when I tried doing a select on the data. So back to doing research, and I finally found a website where someone had posted that he had paid Microsoft $200 to get the answer and he was waiting for a response back. Someone made the comment that $200 is a lot of money to spend only to find out you have to add "BackgroundFetch=No" to the connect string for the tables. I got a laugh out of that comment, but amazing it solved my problem right off!!!

So in a nutshell, the Web Service is working, and everyone is happy with what I finally accomplished!!!

No on to the details for making this work:

Impersonating a user from an ASP.NET c# program:
Here is a link to Microsoft's website where I found the code: support.microsoft.com/kb/306158 This turned out to work almost flawless for me. I copied their code into my ASMX file, and changed the user name, domain and password and bang I was instantly impersonating the user I wanted.

Connect String I used to connect to the tables:
string cConnectionString1 = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=\\\\UNCPath\\DiretoryPath\\;BackgroundFetch=No;Exclusive=No";

Comments:
If you don't know how to use this code you may want to spend a little time doing some reading, for now I don't have a lot of time to write an article on programming, but I will eventually get around to doing this.

c# and FoxPro Tables ODBC

Michial Thompson — Mon, 08 Jan 2007 22:11:09 GMT

Well I run into an interesting problem today using c# to create a Web Service that accesses FoxPro Tables. After getting everything in place, and getting the ASPX file to compile I started getting an ODBC error. Turns out that when you install FoxPro it doesn't necessarily install the actual FoxPro ODBC Drivers, so you get an error when trying to call the open() method.

I will say this, the more I work with .net the more I am liking being forced back to the Case Sensitive and Structured programming arena. I didn't realize just how lazy my development had gotten. Its amazing how much I curse C# for forcing me to type my variables the same every time.....

One more thing, for a Web Server that allows someone to search data, it would be nice to put checks in to see if it even found anything... .Net is picky about trying to access datasets that don't have anything in them....

Ever feel like a total idiot programming?

Michial Thompson — Mon, 08 Jan 2007 21:14:35 GMT

The past few days I have not really been able to post much because I have been going through a FoxPro App screen by screen removing an Image Object, and 3 ShapeObjects... The app only has 934 Form Objects, and you will probably never guess why I was doing all this work....

First off let me sat this application took me close to 6 years to develop to the point that it's currently at, I initially started off developing it with the plain old Grey on Grey w/ Black text look, but after getting tired of a friend badgering me about how it looked like every other FoxPro App I went in and added a background, and several shapes to create a consistent but colorful look. Well the app has been live for the past 2 years, and people have made some comments about the colors, so this past week I got the hair to make a change to the colors.

So I decided, instead of me controlling the colors I would give the users control by implementing Skins, since I developed the framework as an object oriented structure, it should be pretty easy, just create a table, add some fields for the various values such as font, colors etc..

I then went onto the images, well this turned into a nightmare. I found out that I had 4 different ways that I had put the images on the forms. NOT one of the four was to put these images on the base classes. I have probably 300 forms that had them as objects on the main form, then I had probably 3 other parent forms that had these images on them. To make a long story short, I had to chase down where these images were.

So anyway, as I got into this project I ended up opening each form, and then deleting or at least trying to delete each image and move to the next.

Adding the new image was easy, I used the form property Picture which is what I should have used in the first place. My idiot self created an 800x600 jpg that I then put on every form, instead of creating a 1x600 image that would be tiled across the screen.... needless to say when I got through all this I had reduced the EXE size by nearly 750k or more, and the screens actually performed a hair faster.

So mark one more up on the bonehead tally.... BUT I will say this way also makes developing the screens a little easier too....

New site ...

Michial Thompson — Fri, 05 Jan 2007 12:15:07 GMT

I know it's not the best thing to move your blog to a new site like this, but Google's Blogger could not do all the things that I wanted it to do so I started looking for a different one. I found this blog software, and so far it seems to do everything I want it to do.

Anyway, I have been sitting on this domain for about a year or so now, wanting to actually do something with it. When I got it I had planned to use it more as a joke, and was going to just mess around making things up, but I never got around to doing anything with it.

When I found this software, I got the idea that I could put this domain to use, and have a little fun at the same time. So here we are, I've spent the past several days working on getting this setup, and moving my old blog, and next comes a little development and I should be up and running.

By the way, if your interested in having your blog hosted here, email me at michialt@gmail.com, and I'll set it up for free. If you happen to be a good web developer let me know, I could use some help getting an intro page set up for the site.

Brief timeout to discuss the Playstation 3

Michial Thompson — Thu, 28 Dec 2006 01:00:00 GMT

I have had a Playstation 3 since shortly after they were released, but have not been able to take advantage of the Hi-Definition because the component cable had been on back order. Well finally I received a component cable last night, and got to see the system working in full 1080p resolution.

All I have to say is WOW. The picture from the Blu-Ray movies are amazing. I watched a couple different movies last night and was shocked at the color contrast, and the sharpness of the picture. Even greater than from the Cable broadcasts that I watch in Hi-Def.

One of the amazing things about the PS3 that I have noticed so far though is how well thought out it seems to be. I'm sure many gamers would disagree with me, and I by far am no gamer. In fact I rarely play games. I purchased the PS3 as a cheap Blu-Ray player that supported Digital Audio, the rest was just gravy.

The PS3 comes with an Internet Browser, something I figured would be a piece of crap that was intended to work mostly with their web server/pages just so you could buy things. I've been playing with it a little the past few days in standard resolution and the text is a little fuzzy, and there isn't much real-estate for development, but it was able to handle every web page I threw at it from my servers, and even did well on google, gmail, and a number of other sites I hit.

I did notice a few java errors, and one thing it could not seem to process is the Windows Security login that pops up when a IIS does not have rights to a directory. But overall I would say it did a good job.

Now last night I got to play in Hi-Def 1080p, and the text was a little better but still not perfect, but that may be because of the projector I am using. It does a good job for my laptop too, but the text on the laptop is still a little fuzzy.

Overall I would say that the web browser that they used for the PS3 is acceptable, especially for a game system attached to your TV. My web development is kept strictly to Web Applications though, and I like to minimize the amount of java I use so keep that in mind when reading that statement.

Other aspects of the PS3 that are impressive is that you are able to install Linux on it, and have a fully functional computer right there on your TV, you can use a USB keyboard, and I assume mouse too. I wonder if a Blue tooth keyboard/mouse would pair up with it. My next keyboard is going to be blue tooth so we'll find out in a few months.

I bought the 60gig version so I got the wi-fi and Ethernet networking, and the memory card slots and I as for the wi-fi, it wasn't too bad to get setup, took me longer to type the WEP code than anything else. I use a 26byte wep code, and it took me probably 10 minutes to use the cellphone like keypad and game controller to type the code. I will say this though, with a little network game play and having to use that controller to type I've gotten better, probably take me 3-5 minutes to type the 26 chr code now. :)

As far as the game play and game graphics, all I can say is that I was impressed with the old EGA video graphics, so I'll leave that to the serious gamers to review. The PS3 gets a thumbs up from me though.

What to use Visual Foxpro for (and not to use it for).

Michial Thompson — Wed, 27 Dec 2006 01:00:00 GMT

Earlier I said that there were a a number of packages available for using Visual Foxpro with a Web Server, and I told you that I felt that they were all outdated and not necessary. Well today we will talk about what you should consider using Visual Foxpro for, and what not to use it for in a web page.

Here is where you will learn why. Most of the packages on the market basically replace HTML with some extension that then calls a Foxpro prg to create the HTML that is sent to the client. This is about the most inefficient use of resources that I have ever seen on a web server.

Microsoft has done some great things with IIS, and every new release seems to improve on the previous. It's a shame that both the good and the bad get improved upon, but that's for another blog.

IIS supports scripting through ASP (Active Server Pages). ASP supports a number of languages such as VBScript, C# etc. With these scripting languages you are able to embed them directly into your HTML, and the server will process the code in line as it sends the HTML to the client to be viewed.

What makes this better than the Foxpro method is that current each of the programs for Foxpro that are on the market that I have worked with have required a "Foxpro Control Panel" to be running. Basically this is a Foxpro program that runs either as a service, or as a desktop program that sits and waits for a web hit, when it received the hit from the isapi dll, it then processes a PRG that creates HTML and sends it back to the isapi which then sends it to the client.

There are several problems, first each control panel can only run a single hit at a time, yes you can have several control panels running, but each one can process only one hit at a time. The next problem is that Foxpro is not optimized or coded to take advantages of multi-processor machines. So now you have to do some fancy configuration to tell each control panel what processor you want it to run on, and it's stuck on that processor.

Next, when there are no hits, the server's memory resources are still being consumed, as well as processor cycles are being consumed while these processes wait for the next hit.

And last off, if you happen to cause a fatal error in one of your Foxpro programs, its possible to shut down the entire panel and shut down you web site. Granted most of them have very good recovery methods, but its not a sure thing.

With all that said, they do have one absolutely positive feature about them. They all take the incoming string from a user's post from a web form and parse it into an easy to work with variable set. This is one thing that I have to say is the single most useful thing about them. The other thing that's good is that they make it possible to link Foxpro functions to various buttons and links on the web site, but this is actually less impressive.

What I am going to be showing you through this series of blog entries is how to combine HTML, and ASP scripting with Foxpro DLL's to accomplish all the same things that the other packages accomplish, and still preserve the resources of your server so that you are able to handle a much larger traffic load.

If you are getting skittish about merging multiple languages into your development process, don't worry. The scripting languages are minimal, and easy to use. I reserve the scripting to strictly formatting the HTML, and some basic data calls. Visual Foxpro will still handle any of the complex work, and all the business rules. And we may introduce some javascript into the mix just to make things interesting, but for the most part I will be avoiding it as well.

So how do you determine what goes into Foxpro, and what goes in to HTML/Scripting? This is easy, if it relates to formatting the page it goes into HTML, if it relates to the business rules or the data it goes into Foxpro. Well not entirely, but we will discuss those as we approach them.

First off, all through these blogs I will assume that you are an experienced programmer, and have a good understanding of Visual Foxpro, and it's tools. I won't be discussing how to create a table, I will just tell you to create one. I won't be discussing how to format a SQL Select command, I will tell you what fields it needs, and what conditions to put in the where clause.

When it comes to the ASP Scripting, I will for the most part be using real code, but frequently I rely on suedo code to get my point across with examples, I'll try to differentiate the two. There are a number of sites out there that go into detail to teach you these languages, and I don't want to waste keystrokes talking about the details of the language. (Yes I know I waste a lot of keystrokes on other things, but it's my rambling blog so I can type what I want :)) )

Every book you read uses different types of examples for their programming project, we will be no different here. I've been trying to figure out what would be a good project that has REAL business practices, but that won't take a lot of development. I'm also trying to figure out what features I need in one of my projects that would fit into this as well. I'll get back to this because I need to do some thinking about it.

SQL Server vs Visual Foxpro

Let's take a vew minutes and talk about Visual Foxpro vs SQL Server. Age old debate right? Well perhaps I can put the debate to rest for you, or at least give you something to debate untill I give up and say "whatever" like a teenager too.

Now that Microsoft is releasing a free version of SQL Sever (MSDE, or now SQL Server Express), there is no reason not to at least consider using SQL Server. And keep this in mind as well, Microsoft has optimized IIS to work very well with SQL Server. Basically what they are doing is saying, "here's this awesome tool, and we are not going to give you an excuse not to use it."

I'm a 25+ year veteran Foxpro Developer and even I have been converted. SQL Server is all the great things from Visual Foxpro and none of the bad. They have optimized the code and the indexing so that it's both efficient and fast. But for me they have made two major things possible, the first is to be able to backup the system reliably with users still working. The second is that they have almost completely eliminated data and index corruption on reliable hardware. These two features alone should have you re-considering your thoughts of SQL Server.

Now, for performance; every Foxpro developer I know is convinced Foxpro can outperform SQL Server hands down, and in about 10 minutes I can convince them they are absolutely wrong in all but the most basic of applications.

For those of you convinced that Foxpro can outperform SQL Server, set up this environment:

SQL Server running on a web server accessed by a DSL connection
Now house your data on your local network

Create 3 tables in both places, one for names, one for address history, and one for order history.

Put 10k names, with at least 5 Addresses, and 15 orders each, so you address history will have 50k records, and you order history will have 150k records. Put all the applicable indexes on the Foxpro side. Now run the following select on both servers:

select names, address, orderhistory
where orderhistory links to address
and address to customer
and orderdate between 1/1/2006 and 2/1/2006
order by customername and orderdate

Assuming that you have populated the tables with the same data, and it's relatively random, you should receive a fair amount of data back.

What you will see is that the data you receive back over the DSL, and the data you get across the lan will take close to the same amount of time to access. Here is the reason why, Visual Foxpro uses indexes VERY VERY well, but it still has to load those indexes across the LAN which takes time. If you have any number of indexes the compound index files can get to be quite large, and often the index is actually more data than the actual query will return.

Now once VFP finds the records it needs to load, it STILL needs to pull that data across the LAN from the tables as well. Now in contrast, with SQL Server you simply send the server a question "I want all the records that meat this criteria from these tables, and ordered in this order?" and it replies OK, here are ONLY those records you asked for. So when you look at the amount of traffic on the wire you find its substantially less. When you look at workstation resources you also find substantially less processor time too getting more work from you workstation.

Now here's where this makes a huge difference, Visual Foxpro does a good job at caching indexes for the next time you access the data, BUT remember what I said about the Web being Stateless? Guess what, after that web page was built from that data, the table was closed, so the next request will need to do it all over again.

With SQL Server, that's not the case, SQL server cached as much of that data as it could, and now the next person that requests that data will have the data in memory making their query actually run slightly faster.

The other day I mentioned that Thor contained 8 gig of memory, of that 8 gig I have told SQL Server to use up to a maximum of 6 gig for it's self leaving 2 gig for IIS and the operating system. My customer's data all combined is just of 50gig. SQL Server runs just over 2.5 gig on average of memory used. What this says is that during the course of time, the users keep using the same 2.5 gig worth of data, and rarely hit older data.

Well this is true, my customers key in maybe 2-3 records per user per day, and over the course of a week, they will access those same records a dozen times each while accessing historical data maybe 2 or 3 times a week. As the records age they are accessed less and less. SQL Server realizes that and keeps only the newest information in memory for each customer.

Now what has Microsoft done with IIS that impacts this?

In IIS Microsoft has what is called Connection Pooling. What this basically does is simple, once a user has connected to SQL Server and gotten their data and closed the connection, IIS keeps that connection open for a period of time waiting for the next user that tries to connect using the same connect string which gets them to the same database/server.

If someone tries to connect before the pool is flushed of that connection, it re-uses the same connection from earlier making the connection instantaneous rather than having to go through the connect handshake again.

This process kicks ass when it comes to a web site that takes a large volume of hits per day such as Thor does. Just for your reference, Thor takes almost 35 thousand hits per hour, about 70% of those hits access the database server for at least part of the data. That's 840 thousand hits in a 24 hour period, or 588 thousand data queries per 24 hours.

Just so you know, I was working on a project written using one of the Foxpro Interface applications, and that company had 4 servers, and less than 10 gigs of data, and they could barely process 100k hits per day, and I was amazed until I started seeing where their bottle necks were.

Stay tuned, next I might finally get into what our project will be, and how to go about designing and laying out the system.

Our project unveiled!!!

Michial Thompson — Wed, 27 Dec 2006 01:00:00 GMT

I've decided what our project should be. BUT, let me give a little background first (how else could I make this post longer than it needs to be).

One of the things that I used to enjoy as a teenager was to play Role Playing games such as Dungeons and Dragons. Eventually I got heavily into the computer games like Zork. Then as the graphics capabilities of computers improved it seemed like the computer games left the old Text Adventures behind.

Back in the BBS days there was also a short fad where someone would start a story by writing a paragraph or two, and then leave it open for others to participate by doing the same thing. I also used to enjoy participating in those as well, but the frustration was that inevitably someone would start killing off the story because they got bored.

Anyway, I've always had this dream of developing a computer based Text Adventure Game that would be infinite in size and scope. I have a number of years dreaming of doing this, but have never taken the time to actually develop it.

Well with the Internet, the concept of my text adventure game might be able to be developed. And if I combine the Text Adventure with the community story project. I might actually see my game come to fruition.

So how does this even remotely apply to Business Applications? It's Simple, in an Adventure Game you will have a series of links that will lead to various content, and that content will link to other content etc.

You also have a series of calculations what will need to be performed with every click/link, and these calculations will determine the the outcome or the output that the user sees.

As well you will have the Security features, Data storage updating and retrieving, and you will have a set of Business Rules that apply as well.

Visual Foxpro Web Sites

Michial Thompson — Tue, 26 Dec 2006 01:00:00 GMT

There are a few products out there that are used for interfacing Visual Foxpro and Web pages together to give your views dynamic content. Just a couple of these products are Web Connect, and FoxWeb. It's my opinion that both products are outdated and no longer necessary.

While products such as these do make some aspects of dynamic web content easier, they are in no way efficient, and they more often than not are a waste of your Web Server's resources. Today's blog is going to explain how to use Visual Foxpro on a Web Server, and minimize the amount of resources that are wasted.

For this blog, I will be referencing my production server Thor. Thor is a Dual Zeon 2.4ghz server with 8 gig of ram, and 8 300 gig hard drives which are mirrored in pairs giving me 1.2 gig of overall storage. Thor is housed in a data center where I have burstable bandwidth up to a full 100mbps.

First off lets talk about the four basic resources that you are working with on your web server, and how important each one is to the overall product.

Bandwidth (burstable to 100mbps):
Your bandwidth is the amount of data you can move across your network at any given time. Its often measures in Megabits per second or mbps. In cases of smaller networks, it could also be measured in Kilobits er Second or kbps.

Most commercial Internet connections will be synchronous (same speed both directions.) There are way too many options to discuss here. I the case of Thor I chose to Co-Locate at a local Data Center, and I have an allotted bandwidth, and if I should go over that amount I am billed for that addition bandwidth. What this means is that my bandwidth is calculated for an entire month, and the averaged out over that time frame, and if it exceeds the allotted amount I am billed extra.

My servers also have access to "Burstable Bandwidth" of 100mbps. What this means is that if my server what hit by an unusually large number of hits, it would not max out until reaching 100mbps.

On the average server, this is typically your bottleneck, and in most cases if you have excess bandwidth, and you are suffering poor performance, you are having problems somewhere else in the system.

Memory (8 gigs)
You cannot go wrong with more memory, the more memory your system has the better overall it will operate, just remember to make sure you are running an operating system, and software that can use all the memory you give it. For instance Windows 2003 Server Standard Edition can only access the first 4 Gig of memory. Anything over 4 gig is a waste. I personally wouldn't recommend Standard Edition for any Web Server, and ESPECIALLY not one that's going to be running Visual Foxpro for dynamic content.

Processor (Dual Zeon 2.4ghz)
This is where the real magic is performed on your server. You would think that the faster the processors the faster the server is, but this isn't the case. The only time that your processor utilization should climb on a server is during backups, or in the event that you are running a complex mathematical process.

For a typical website that serves data driven dynamic content, I would rarely expect your processor to reach more than 15-20% utilization, and even then only for short bursts during the peak hours.

In most cases a single processor with middle of the road speed would do fine. I personally recommend dual processors for servers that are going to run anything other that IIS and VBScript. So adding Visual Foxpro to the mix, you simply add a processor too.

Hard Drive I/O.
Visual Foxpro has enjoyed the reputation for having the fastest data access of any desktop Database system for years. The reason for this is because it uses a combination of memory and Hard drive IO to get that reputation. Visual Foxpro loses its speed and reputation when it comes to accessing it's data across a network, and becomes impossible across a wide area network.

If your data is going to be housed on your server's local drives expect to see your hard drives getting a workout. Make sure you are running the highest quality hard drives you can get. If your data is not going to reside on the local drives of the Web Server I would highly recommend against using Foxpro's Local Tables.

Don't Skimp on your hard drives, go with the FASTEST, most reliables drives you can get, and replace them frequently to keep them reliable.

On Thor, I replace two hard drives ever three months giving me a total of one year's use on the oldest drive in the system. With hot swappable mirrored sets, it's easy to do this, you just remove on drive in the pair, plug in the new one and tell the system to rebuild the mirror and your done.

I then move the drives I pulled out of the system to my backup server, and from that server to the off site server and eventually if they survive long enough they end up on a shelf in my office as temporary spares.

Trust me the expense of doing this pays off in spades with system reliability. You would be surprised to hear that I have had only one drive failure in the first year of operation, one in 12-18 months and three failures in the 18-24 month range. And these are high quality server drives from a reputable manufacturer.

How a web page is accessed.
If you are new to accessing data with a web server, you are in for a surprise. Web Servers open and close data tables every time a page is served to the user. They do not remember what the user typed, or what was even sent to the user.

This means that you will be taking a whole different approach to laying out your applications. Traditionaly Foxpro programmers have opened the tables at the beggining of the application and then closed them when the user quit the application. Or a variant on this of opening the tables when the user needed access to them, then closing them at the end of the need for the tables.

Rarely if ever did we open a table, get the data from the table, and then immediately close the table before we allowed the user to do their work. Forcing the user to endure the process time for opening a table, getting the data, closing it, only to open the same data, save the user changes, and then close it again... You wouldn't consider that at all on a desktop application.

Well guess what, that's exactly what you will be doing with web content. To make matters worse, once you send the web page to the user, you will forget you ever sent that page/data to the user. Then when they return the replacement data your program will start off all fresh.

This is because a Web Server operates in a mode called "Stateless." What this means is that every transaction to the server is a complete transaction, and does not rely on anything else. Sounds fun right? Well actually it is, not too much really changes between the way you currently work and the way you will need to be working with data.

There are only two real areas that you will need to change about how you access your data. The first is that you will no longer have the record in the table current when the user saves, so you will need to work out some form of pointer to the record. For Foxpro Tables you can usually get away with using recno(). The other area is record locking. This is the area that becomes a real pain when working with data via the web.

Record Pointers can be as simple as the record number, or they can be a series of fields that make a record unique, or they can be a single field.

I don't like using record numbers because they have a tendency to change over time, especially if you do any data maintenance such as packing tables to remove deleted records. They work because they only have to be accurate during the scope for which the user is editing the records.

Using a combination of fields or any field that a user keys in or changes is dangerous. If a user happens to change one of these fields then the link back to the original data is lost.

I prefer to use a "Primary Key" field, what this is is a numeric field that is auto incremented, and kept unique. I never renumber these fields or reset counter for zapped tables so I end up with a number that is 100% unique for the life of the data. Works perfect for linking to records. And coincidentally it also works beautiful for foreign keys in other tables too...

Record Locking in a Stateless Environment:
Most books I have read have basically said that there is no such thing as record locking in a stateless environment, and they are partially correct. You could not ever use Foxpro's Record locking, the reason is that by the time the user has posted their changes back to the server the previous session would have already closed the tables which releases the locks.

Record locking is a complex thing when it comes to stateless because you have no way of even knowing if the user is going to post changes back or not, so basically you have a few choices, none of which are elegant.

1) Last Save Wins
What this means is that the last person to save a record gets to keep their changes, so in the case of two people editing the same record the last one wins.

2) First Save Wins
This is similar to Last Save, but instead it stores a timestamp with the save, and if the timestamp from your source record is earlier than the one in the table your saves are not saved. This one is better in my opinion because it gives you an option to pragmatically move on to a conflict resolution option where you display the data saved while you were editing, and the data you changed and give the user the chance to make the decision about what is saved.

3) Create your own record locking flags
This is the option I actually use in most cases. The way that I implemented this is to create a table "Record Locks" and in this table, I have TableName, RecordID, SessionID, DateTimeStamp. When a user clicks to edit a record I insert a record(s) into this table for the data they chose to edit. Then I delete that record when they save.

With this option, you need to offer a way for locks to automatically be released, or your in for a lot of calls asking you to release such and such record because its in use by someone. So first you need to determine a period of time that a lock can remain active. Then simply remove the locks after that period of time.

I make several choices to make this work, first off when a user logs off the system correctly I automatically remove all their locks. Second when their session expires (we'll talk about this later) I delete the locks, and last off, I allow a lock to remain in affect for 1 hour, after one hour it's automatically removed.

Stick around, tomorrow we will get into exactly what to use Foxpro for and what not to use it for when developing web pages.

Merry Christmas to everyone, and hope you stop back tomorrow.