Well I just listened to FoxShow Episode 63, and there is already a commercial tool underway that is going to do exactly that.

(If you want, go ahead and listen to that podcast…I’ll wait. You back? Pretty cool, huh?)

Uwe Habermann, who to my mind sounds like the bad guy in every James Bond movie, is going to be showing off this tool and doing a lot of other Silverlight stuff at the Southwest Fox Conference.  Here’s the point I really want to make: Even if your workplace has decided that FoxPro is passé and all new development must be done in .NET, you owe it to yourself (or they owe it to you) to get to Southwest Fox. There is no other conference where you’re going to learn so much about .NET from a FoxPro developer’s unique point-of-view.

We slowly built up to the main theme by reintroducing a theme from a previous meeting, Freshbooks. We discussed some of the features of Freshbooks that I use the most in my business, and some of the features that it currently lacks [sometimes it's the notes you don't play]. We started looking for instruments that would get at those empty places, first discussing the Freshbooks API, and the SOAPish interface to get at the data. Because of the way the request needs to be made and authenticated in a single step and the bountiful documentation that used it, we looked at Curl, rather than wwIPStuff. We started a proof of concept by successfully using the command-line CURL interface to retrieve my client list from Freshbooks, and then looked at ways to get that functionality from within Visual FoxPro. Turns out there’s a LibCurl.dll that is callable from a class library written by Carlos Alloatti that does the trick for us, with the help of the VFP2C32 library!

One of the side themes we played with along the way were the pros and cons of using GUIDs as primary keys for tables (great for offline data that’s brought back online, but a little slower and you don’t get subsequent records in order the way you do with autoincrementing integers), and the algorithm used to create them (formerly a hash of your MAC and the datetime, now a pseudo-random thingy). There are some amusing posts about the possibilities of duplicate GUIDs existing. The VFP2C32 library mentioned above has a CreateGUID() function that is easily called from VFP.

Another theme was XML and the different ways VFP can interact with an XML string, including brute force such as STRTRAN(), FOPEN()/FWRITE(), and of course the old standby of using the MSXML DOM object.

We riffed on Great Plains Accounting for a while and its API, and compared with with Sage Pro f/ka/a AccPac v/k/a SBT. Then we discussed getting started with programming using XNA Studio or the Visual Studio Express versions. And for an encore I soloed on my current flirtation with Silverlight.

We wrapped it up by having gyros on the rooftop of the Parthenon. It was a beautiful night, and another great MadFox meeting.

1. We started by talking about Christof’s new project, FoxMock. Before we could really talk about FoxMock however, we had to review unit testing (via FoxUnit) and mock objects in general. Once we got that all figured out, we looked at FoxMock, and how we could create mocks in our FoxUnit tests to get them to pass even if we didn’t have everything finished in the object. We spent quite a bit of time discussing the scenarios in which mocks are useful, and what the advantages and disadvantages of other approaches are. We felt it would be especially useful in a team environment where another developer is responsible for parts of the class you want to test.

2. We next discussed a design pattern that would allow an application’s business logic to be user-definable and stored in a table that could then be executed ad-hoc. We looked at variations of EVAL() and the _VFP.() methods before realizing that simply writing the code out to a temporary .prg file and executing that .prg would be a simple way to accomplish what we wanted. I’d like to see C# do that!

3. After the meeting we had a lovely Mexican dinner at an Irish place. Mmmm….Guiness and burritos.

Next month’s meeting on July 20th back at Salty Dog World HQ. See ya then!

Eric

One day a Panda arrived to protect the kingdom. His task was to keep viruses from getting through the gates, like a dreamcatcher for malware. It turned out the previous virus-catcher was stealing memory and talked too much, so he was replaced with this Panda. The Panda promised to work quickly and in the “cloud,” a new way of keeping memory usage low and virus definitions up-to-date.

After the arrival of the Panda however, the Fox started acting weird. Even though he was running as administrator, he could no longer create these EXEs. The message “File cannot be read.” kept coming up. He didn’t know what to do! He searched the land for a solution and the sage man at Foxite suggested putting the Panda in a cage while creating the EXEs. And this worked!

But he didn’t want to put the Panda in a cage every time he needed to create an EXE, since he might forget to let him back out again to protect the kingdom against malfeasance. So he sent a message to the Panda’s owner asking if they had a better idea. And lo, it came to pass almost immediately that the Panda learned not to interfere with the Fox’s work. Was this some magic that happened in the so-called cloud? The Fox may never know, but he’s happy to have this resolved so he may go about his business.

But when it came time to check how quickly I could read back the data, I was befuddled by MySql’s insistence on returning my integer fields back as char(20). eg. If I try this

iHandle=SQLSTRINGCONNECT("Driver={MySQL ODBC 5.1 Driver};Server=xxxxx;Port=3306;Database=arc;User=xxxx;Password=xxxx;Option=3;",.t.)
SQLEXEC("SELECT 12345 AS id FROM arc_data", "crsData")

The id column will be a 20-digit character field.
If I try to force it with
SQLEXEC("SELECT CAST(12345 AS unsigned) AS id FROM arc_data", "crsData") or
SQLEXEC("SELECT CONVERT(12345, unsigned) AS id FROM arc_data", "crsData")

The result is still a 20-digit character field. There are other fields that come back this way too but this is the jist of my problem. [I'm using MySql Server 5.0.45, and I've tried this with ODBC drivers 3.51 and 5.1, both with the same result.]

On a whim, I tried using a DSN rather than a connection string. It didn’t work either, but at least I had access to all of the options. The one that first looked interesting was the one that said “Treat BIGINT columns like INT columns.” Hmmm…not exactly what I’m looking for but let’s try it.

It works perfectly now.

So how do we make that work with a connectionstring, my preferred method since I can keep those in a table and not have to distribute it to each machine? I suspect it has something to do with the OPTION= clause in the string, but I’ve never found that to be well documented. However this time when I googled around a bit I found this page, which perfectly laid out how the options in the DSN map to the OPTION= clause. Yay!

A quick change of code to add 16384 to my OPTIONS= mask…
iHandle=SQLSTRINGCONNECT("Driver={MySQL ODBC 5.1 Driver};Server=xxxxx;Port=3306;Database=arc;User=xxxx;Password=xxxx;Option=16387;",.t.)
SQLEXEC("SELECT 12345 AS id FROM arc_data", "crsData")

…and problem solved.

Here are the MySQL Options, copied here from the CodePowered blog page:

MySQL ODBC Driver 5.1 Connection String Options
In a MySQL connection string for ODBC, a parameter called OPTION can be specified to change the behavior of the server. It works as a bitmask, so to use it just take whatever flag you need from the following list and sum up the corresponding numbers, then put that as the value to the OPTION parameter.

2 = Return matched rows instead of affected rows
8 = Allow big result sets
16 = Don’t prompt when connecting
32 = Enable dynamic cursors
64 = Ignore schema in column specifications
128 = Disable driver-provided cursor support
256 = Don’t use setlocale()
512 = Pad CHAR to full length with space
1024 = Include table name in SQLDescribeCol()
2048 = Use compression
4096 = Ignore space after function names
8192 = Force use of named pipes
16384 = Treat BIGINT columns as INT columns
32768 = Disable catalog support
65536 = Read options from my.cnf
131072 = Enable safe options (see documentation)
262144 = Disable transaction support
524288 = Log queries to myodbc.sql
1048576 = Don’t cache results of forward-only cursors
2097152 = Force use of forward-only cursors
4194304 = Enable automatic reconnect
8388608 = Enable SQL_AUTO_IS_NULL
67108864 = Allow multiple statements
134217728 = Limit column size to signed 32-bit range
268435456 = Always handle binary function results as character data

For example if you want to enable ‘Return matched rows instead of affected rows’ and ‘Use compression’, the value to pass as OPTION is 2 + 2048 which is 2050:

Driver={MySQL ODBC 5.1 Driver};Option=2050;Server=localhost;Database=myDataBase;User=myUsername;Password=myPassword;

Test 1: Straight Performance Pulling Data

The first test I did was to take the SQL code from getTimeDetail, a method of one of my business objects that’s run probably hundreds of time each night in a variety of queries to retrieve a campaign’s detailed time tracking.

You can see that based on this raw data, VFP is 10-40% faster. But of course that’s not the only factor…

Test 2: Network Impact Pulling Data

One of the benefits of a client server apps are the reduced traffic going over the network. Indeed VFP creates so much NetBIOS traffic that it’s unusable over a Wide-Area Network (WAN). Since one of our goals is to reduce network traffic, I next ran a test where I used WireShark to watch the conversation between my network card and the server’s network card.

VFP SQL Direct

Advantage Database Server

Difference

Clearly Advantage has the, er, advantage here. There’s about 90% fewer packets coming across the network here, and quite a few less bytes as well (VFP has to pull unwanted data to get at the stuff you actually want). What’s curious however, is how much smaller the packet sizes are in ADS, causing essentially lower throughput in this test.
Our network admin thought smaller packet sizes may actually be an advantage during the batch, as the switches will be able to move smaller packets more efficiently. The tech support guys at Advantage said this will be better when multiple users are hitting the server, as well. Which leads us to the next test…

Test 3: Multi-User Speed Testing

In this test, I ran the test bed three times concurrently on 22 campaigns. This should test how well the server performs when multiple clients are querying the data simultaneously. This graph shows the aggregated average times to run each campaign.

As the Advantage tech support guy suggested, Advantage does perform better under a multi-user load. The 10-40% edge that VFP had under a single load disappeared, and it’s likely that this spread will increase under larger loads. Conclusion: Advantage scales better than FoxPro. And of course Advantage is usable over wide-area networks, allowing you to distribute your clients anywhere.

Considerations

Advantage has a very nice feature that allows you use existing VFP tables without modification, but that doesn’t mean you can just install it and, poof, everything is now client/server.  If you don’t use structural indexes in your VFP tables, you’ll need to manually link the indexes to the databases using stored procedures in the database.  Depending on how much forethought you put into your data access classes, switching your VFP front-end to use a non-VFP database can be fairly easy or a major undertaking.
Bear in mind these tests were done using a directory of VFP tables, not using Advantage’s native data schema. Performance may vary.

Last night Jay Wendt and Chris Franz from Sybase took a detour after their presentation at Chicago’s FoxPro User/Developer’s Group to come up to Madison to give a presentation to the Madison FoxPro Users Group.  However MadFox wasn’t meeting last night, so we booked ‘em for the more general Madison PC Users Group meeting.  Chris and Jay toned down the technical details of the Advantage Database Server and gave a very interesting rundown of the features of their product.  I’m not going to recount them all here, you can certainly read that for yourself, but here are a couple of the highlights that make this product interesting for me:

• Can use VFP tables as the data store, without breaking existing apps (I can’t drive this one home enough)
• Very low cost of ownership (~ $100/ seat, with no recurring fees)
• Participates in MS clustering, giving you a high availability solution at a low cost
• There’s a .NET DataReader class that allows you to traverse the data in an xBase type manner familiar to VFP developers

Big thanks to Jay and Chris for coming up to Madison, and thanks for the USB drives with the installation on it.

9. The number of attendees declined from last year, but it didn’t feel sparse at all and in fact I felt like it gave me the opportunity to interact with more people. During the final wrapup I was looking around the room and recognizing almost everyone by name.  The social aspect was great.

8. I was recognized by an attendee who last saw me in 1995 at the DevCon in San Diego. That was pretty cool. There was also quite a bit of “Oh, I’ve read your posts and it’s nice to put a face to the name.”

7.  This sticks out in my head: I was chatting with someone between sessions when I heard Craig Boyd walk up to another fellow and say “Hey, my name’s Craig Boyd and I don’t think we’ve met. Where ya from?”

6. I attended over 20 sessions, and it still didn’t feel like enough. I had to miss some good ones, and feel like I could have gone 2 more days.

5. There’s is so much to know.  Every session, even the ones where I felt like there couldn’t be any more to say about that topic, had a ton of good new information. I don’t understand how you could call yourself a FoxPro developer and not come to conferences, user or groups, or at least subscribe to FoxRockX.

4. Some people are certifiably genius, and we’re lucky to have them working with us.

3. VFPX continues to expand, and will keep FoxPro alive for many years to come. Need a snazzier interface? Better reporting? Check here first.

2. If Tamar, Rick, and Doug ever give up programming (god forbid), they’ve got a future in event planning. The whole conference seemed, from this attendee’s perspective, like a well-oiled machine.The facilities were top-notch, and the food was fabulous.  (I’m really gonna miss the food.  I think I’m going through bacon withdrawal today). The wifi was quick and flawless this year, and there were plenty of power outlets for laptops. There were also great vendors, giveaways, and a silent auction to raise money for the Ceil Silver Ambassador Fund.

1. Southwest Fox 2010 is announced. Support your product, join the community, get involved.

Prior to VFP 9, enumerating through a collection object rendered COM objects which were…problematic.  In VFP9, Microsoft apparently introduced a FOXOBJECT keyword which will not make it a COM object, but rather a native VFP object.  This speeds things up tremendously, improves memory and stability, and reduces overhead.  I wasn’t aware of this, but a very nice tip to pick up.

(Tamar mentions that if you subscribe to FoxRockX, which you all should do, you would have known about this months ago.)

So it was very refreshing when Tamar didn’t pull out the tired old Northwind database and start wrapping the orders table in a business object. Instead she created a Soduku game in VFP from the ground up, starting with the cell object and building up to the entire game object, all before building a U/I.   When she was done building the “engine” of the Soduku game, she mentioned she now could go any number of ways for wrapping a U/I around it, including the possibility for building a text-based version of the game.  (She may have been joking, but I think there are possibilities here for blind people who want to play Soduku!)

She then showed how her graphical U/I (sorry, Stevie Wonder, you’ll have to wait) interacts with those business objects.  Of specific note was the way she used BINDEVENT in the game object to keep elements of the game well encapsulated, rather than relying on Valid()  methods.

Usually Tamar’s sessions cover ways to make your life as a VFP developer more productive, such as VFP UI enhancements.  This session was different, and really exceeded my expectations.