Tod means Fox

A few days ago I decided to refactor a 6-year old data integration program I wrote in VFP7 (or was it VFP6?) that moves data from a legacy tax system to an operational data warehouse for reporting (SQL Server). The program was self-contained (could be run as a compiled exe from the command line or through the IDE). To make a long story short, I decided to up the code to VFP9 and add some TRY/CATCH statements and additional parameter checking.

The code in this program makes heavy use of TEXTMERGE, utilizing the “\” and “\\” commands.

I wrote a few test cases to exercise my new error handling. What I discovered was that some of my logging stopped working after I added a few TRY…CATCH blocks. Hugh? Here’s a sample:

SET TEXTMERGE ON
SET TEXTMERGE TO test ADDITIVE NOSHOW 
\hello
TRY
    USE xxx
CATCH TO oErr
    \Error: <<oErr.Message>>
ENDTRY
\world
SET TEXTMERGE TO
MODIFY FILE test.txt

If you run the above, you only see “hello” printed in the file test.txt:

hello

I went back to my original code without any TRY/CATCH blocks and to my surprise, got the same result. Only ‘hello’ printed. But WAIT… I’m running in VFP9 SP1.

When I run the original code in VFP7:

ON ERROR \Error: <<MESSAGE()>>
SET TEXTMERGE ON
SET TEXTMERGE TO test ADDITIVE NOSHOW 
\hello
USE xxx
\world
SET TEXTMERGE TO
ON ERROR
MODIFY FILE test.txt

I get the expected output:

hello
Error: 'xxx.dbf' does not exist.
world

It appears as if program errors are killing my TEXTMERGE in VFP9 SP1. I’ll have to try this out on my laptop with VFP9 SP2 to see if it magically got fixed.

To get around the problem in VFP9, you can put the line “SET TEXTMERGE TO test ADDITIVE NOSHOW” in the CATCH block above just before the “\” command. If an error occurs, you remind TEXTMERGE that it needs to keep working. Perhaps it figures it needs some time off.

Recommendations

Unless someone has some insight or additional thoughts, I would suggest searching through your source code for cases where you are using TEXTMERGE with TRY/CATCH/FINALLY blocks. If so, insert the SET TEXTMERGE TO … line in your CATCH block. Alternately, you could use STRTOFILE() to append logging information to a file. This is the final approach I took in this program.

STRTOFILE("hello"+CHR(13),"test.txt",1)
TRY
    USE xxx.dbf
CATCH TO oErr
    STRTOFILE("Error: " + oErr.Message+CHR(13),"test.txt",1)
ENDTRY
STRTOFILE("world"+CHR(13),"test.txt",1)
MODIFY FILE test.txt

I hope this helps someone, or maybe someone who knows more about this particular issue can help me!

For some time now I have been using MSBuild exclusively for deploying data warehouse applications. Because the deployment process is often time-consuming and can touch many components and elements of a data warehouse, using MSBuild is a smart choice.

Consider an integration project that contains a dozen SSIS (dts) packages, some reports (rdl files), a few C# assemblies, an ASP.NET interface, schema changes, new database functions, new user roles, expanded constraints, and updated stored procedures. Now expand this deployment across development, quality, and production environments. Introduce deltas (incremental updates of varying components) and you can begin to understand why some companies pay two or more people just to deploy applications.

But not here. MSBuild (and other similar build/deploy projects like Apache ANT or GNU MAKE) is an excellent solution to the problem. With some planning up front, and a little… ehem… trial and error, you can simplify the entire deployment process so much it will make your head spin.

MSBuild not only builds .NET projects (like C#.NET and VB.NET), but it can also run any number of additional “tasks”, such as:

1. Create directories and log files
2. Do a VSS Get by Label
3. Compile a FoxPro project (see Rick Borup’s article in Advisor: “Automate Microsoft Visual FoxPro“)
4. Compare directory contents
5. Upload SSIS packages
6. Run test scripts
7. Execute stored procedures
8. Etc…

I think you get the idea. There is not a single task MSBuild cannot wrap (at least nothing that I’ve come across). And with its ability to handle dependencies, talk with source control, and run homegrown assemblies, the limit is only limited to your needs and imagination.

I am starting to sound like a commercial, I know.

But the point is simple: The deployment of a large or ‘heavy’ project needs special attention, planning, and resource allocation. Using MSBuild (or ANT, or MAKE, etc.) can alleviate the burden on developers and DBAs and provide consistent and predictable build and deployment scenarios.

I will share some additional insights and lessons learned on using MSBuild over the next few weeks, especially details on how to deploy multi-pronged projects for different environments. I will also try to share some tips on how to handle delta deployment scenarios. In the meantime, if you are new to MSBuild, take a look at the MSBuild Team Blog and the MSBuild Wiki.

As a side note, I have never used MSBuild for deploying VFP applications in the past. This is all about to change…

The SQLEXEC command in FoxPro allows you to sends SQL statements to a database where they can be executed, returning one or more result sets. You might make a call like this in your application:

SQLEXEC(lnConnHand,'SELECT * FROM Customer')

Of course, using SQLEXEC in this way isn’t a great architectural choice, although serviceable. It is difficult to manage code in this way and difficult to support varying back end databases. I would never code customer applications in this way. Instead, I would load an external SQL file and execute that instead.

SQLEXEC(lnConnHand,FILETOSTR("c:\sqlserver2005\script1.sql"))

This approach not only decouples the proprietary SQL code from my application, but it also allows me to test and review the scripts in the native application (and likely get syntax coloring and intellisense). For example, script1.sql might contain TSQL code that I can review (and test) in SSMS. Even better, if I have a lone client running SQL Server 2000, I can use a different script if a new command (like INTERSECT, for example) is not available. This approach will prove even more valuable as some clients upgrade to SQL Server 2008. I will be able to take advantage of some of the new, faster features of SQL Server 2008 while still supporting older versions.

Using Metadata

To take this a step further, I have built metadata to manage these scripts. Consider an application that supports 4 different back-end databases: Visual FoxPro, SQL Server 2000, SQL Server 2005, and MySQL. Create the following table and store all SQL for each back-end database in the appropriate memo field:

CREATE TABLE sql_meta FREE (;
  query_id character(15),;
  sql_vfp m,;
  sql_ss2005 m,;
  sql_ss2000 m,;
  sql_mysql m)

Include this table in your project. In your beforebuild event in your project hook, import or update the sql scripts from your development folders into this table when you build your application.

Now, when you want to execute a script, your code would look a little like this:

*-- create the query/sql management object and execute the code
oMQ = CREATEOBJECT( "manage_query" ) 
SQLEXEC(lnConnHand, oMQ.get_sql('SQL_ss2005','CUSTOMER'))
 
*-- ok, so not very bullet-proof but you get the idea:
DEFINE CLASS manage_query AS Custom
 
  PROCEDURE get_sql
    LPARAMETERS tcBackEnd, tcQueryId	
    SELECT sql_meta 
    LOCATE FOR query_id = tcQueryId 
    RETURN sql_meta.&tcBackEnd
  ENDPROC 
 
ENDDEFINE

I’m a big fan of writing readable code. In fact, it is a priority in my development tasks. I’m also a big fan of making testing easier. This approach allows me to view and test each SQL script outside of my business and data access objects. It also makes for more readable and manageable code.

I’ve left out some examples on how you could use parameters or SQLPREPARE using this approach. I’ll be sure to make this a topic in a future entry.

Just curious, but does anyone know who will be blogging from Southwest Fox 2007?

If it might be YOU (or someone near and dear to you), then please let me know! This looks to be a good Fox-filled weekend. I’m envious and wish I could have been there.

As most of you know, VFP9 SP2 came out a couple of days ago. This morning I had the chance to install the update and take a closer look at all the items that it addressed. Among them are several reporting and language fixes.

At first glance, everything seems hunky dory. The install went great and a few of my most critical applications recompiled without any problem.

Perhaps the greatest thing fixed (at least it seems fixed) for me was the annoying data session window refresh behavior I have been experiencing. For some reason, when the data session window is docked with the command window, the open aliases were not showing up in the aliases list at first. They would pop in after a few seconds, or after clicking on one of the buttons in the session window. I work with the command and data sessions windows docked, so I’m not sure if this annoying behavior is happening under different circumstances. I’m no longer experiencing this.

You can get SP2 at Microsoft.com. It is worthy to note that you do not need SP1 installed to install SP2. However, you MUST uninstall the CTP or Beta versions of SP2 before you install SP2. Milind Lele also reports that XSource.zip is not included with SP2 but will be available as a separate download soon.

Now, I am eagerly awaiting the Sedna add-ons…

Here’s a real fast way to find the keys (natural or surrogate) that are missing from Set A or Set B. This query is similar to finding the symmetric difference between two tables, except that it only compares keys:

SELECT DISTINCT EVL(tableA.pkey, tableB.pkey) AS not_in ;
  FROM tableA ;
    FULL OUTER JOIN tableB ON tableA.pkey  = tableB.pkey ;
  WHERE tableA.pkey IS NULL OR tableB.pkey IS NULL

I used EVL in order to show a unique set of values that are missing from either TableA or TableB. To see exactly which keys are missing from each table, simply leave the EVL out and display both columns.

I tested this on two tables (A & B) with more than a million records using an integer surrogate key. The query took 8.6 seconds. A similar approach works in TSQL. Just use COALESCE instead of EVL and you’re off to the races!