Wednesday, December 17, 2008

Microsoft Security Update

Microsoft recently released a security update that lists Visual FoxPro 8 and 9 amongst the affected applications. In case you're wondering exactly what this is about, here are the details. This update provides patched versions of the following ActiveX controls that ship with VFP:

  • MSWinSck.ocx (Microsoft WinSock Control 6.0)
  • MSMask.ocx (Microsoft Masked Edit Control 6.0)
  • MSFlxGrd.ocx (Microsoft FlexGrid Control 6.0)
  • MSHFlxGd.ocx (Microsoft Hierarchical FlexGrid Control 6.0)
  • MSChrt20.ocx (Microsoft Chart Control 6.0)
  • ComCt232.ocx (Microsoft Animation Control 5.0, Microsoft UpDown Control 5.0)
  • MSComCt2.ocx (Microsoft Animation Control 6.0, Microsoft Date and Time Picker 6.0, Microsoft Flat Scrollbar Control 6.0, Microsoft MonthView Control 6.0, Microsoft UpDown Control 6.0)

In addition to updating your own system, remember to distribute the updated versions of any OCXs your applications use to your clients.

Tuesday, December 09, 2008

ATC(), Only Better

Because our flagship product, Stonefield Query, is a reporting tool, we do a LOT of parsing here, especially expression parsing. One parsing task we frequently do is look for the existence of a field in an expression. It's not as simple as it sounds. For example, suppose you have a SQL statement like this stored in a variable named lcSelect:

select Employees.FirstName,Employees.LastName,Employees.CountryOfBirth from ...

and you want to know if the Country field is involved in the query. Unfortunately, ATC('Country', lcSelect) will return a false positive because "Country" is contained within "CountryOfBirth".

One of Stonefield's genius developers, Trevor Mansuy, wrote a replacement for ATC() called ATCWord that searches for words rather than substrings. He used the VBScript regular expression parser to do the hard work. So, using the example above, ATCWord('Country', lcSelect) returns 0.

There are a lot of comments in ATCWord that explains how it works. Thanks, Trevor.

Update: Mike Potjer tweaked the code so it returns the correct position when the string you're searching for comes after a substring match, such as looking for "Country" in "Employees.CountryOfBirth,Employees.Country". Thanks, Mike.

* Function: ATCWord
* Purpose: Performs an ATC() on whole words, not substrings
* Author: Trevor Mansuy, with a tweak by Mike Potjer
* Last revision: 12/09/2008
* Parameters: tcSearch - the string to search for
* tcString - the string to search for a match in
* tnOccurrence - which occurrence to search for
* Returns: the index of the first character of the match
* Environment in: VBScript.RegExp can be instantiated
* Environment out: none

lparameters tcSearch, ;
tcString, ;
local lnOccurrence, ;
loRegExp, ;
lcLeftBoundary, ;
lcRightBoundary, ;
lcSearch, ;
loMatches, ;
lnReturn, ;
loMatch, ;

* Ensure the necessary parameters were passed.

assert vartype(tcSearch) = 'C' and not empty(tcSearch) ;
message 'ATCWord: invalid search string passed'
assert vartype(tcString) = 'C' ;
message 'ATCWord: invalid string passed'

* Bug out if the string is empty.

if empty(tcString)
return 0
endif empty(tcString)

* Set an occurrence if one wasn't passed.

if not vartype(tnOccurrence) = 'N'
lnOccurrence = 1
lnOccurrence = tnOccurrence
endif not vartype(tnOccurrence) = 'N'

* Create a VBScript.RegExp object.

loRegExp = createobject('VBScript.RegExp')
loRegExp.IgnoreCase = .T.
loRegExp.Global = .T.

* This is here for future proofing. Right now, the function expects
* whatever we search for to begin and end with non-word characters,
* but just in case, save the boundary characters in variables so we
* can change them easily. Right now, we are using word-boundaries
* (the space between a word and a non-word character. I originally
* wanted to use non-word characters (\W) for this, but the match
* consumes the character, so side-by-side matches won't be found. The
* drawback of \b is if we do an ATC() for a string with a non-word
* character at the beginning or end, there is no guarantee that there
* is a word boundary on the other side of that character.

lcLeftBoundary = '\b'
lcRightBoundary = '\b'

* Certain RegExp special characters prevent matches, so escape them.

lcSearch = AddRegExEscape(tcSearch)

* In the pattern below, the three sets of parentheses represent three
* match groups. The first match group, (\W|^), means we first match
* either a non-word character or the beginning of a string. Similarly,
* (\W|$) means at the end we match either a non-word character or the
* end of the string. This means we make a match in every situation
* except where the string in lcSearch is a substring of one of the
* strings in tcString.

loRegExp.Pattern = '(' + lcLeftBoundary + '|^)(' + lcSearch + ')(' + ;
lcRightBoundary + '|$)'

* Test the string. If a match collection is returned, check how many
* matches were made. If the number of matches is less than the
* occurrence passed, return 0, otherwise, do the ATC.

loMatches = loRegExp.Execute(tcString)
if loMatches.Count < lnOccurrence
lnReturn = 0

* Retrieve the specified occurrence from the Item collection. If the
* match is the one we want, return its start location in the string.

loMatch = loMatches.Item(lnOccurrence - 1)
lcMatch = loMatch.Value
lnReturn = iif(upper(tcSearch) = upper(lcMatch), ;
loMatch.FirstIndex + 1, 0)
endif loMatches.Count < lnOccurrence
return lnReturn

* Function: AddRegExEscape
* Purpose: Escape certain characters in regular expressions
* Author: Trevor Mansuy
* Last revision: 09/03/2008
* Parameters: tcString - the string to escape
* Returns: the string with certain characters escaped
* Environment in: none
* Environment out: none

function AddRegExEscape
lparameters tcString
local lcString
lcString = strtran(tcString, '\', '\\')
lcString = strtran(lcString, '?', '\?')
lcString = strtran(lcString, '*', '\*')
lcString = strtran(lcString, '+', '\+')
lcString = strtran(lcString, '.', '\.')
lcString = strtran(lcString, '|', '\|')
lcString = strtran(lcString, '{', '\{')
lcString = strtran(lcString, '}', '\}')
lcString = strtran(lcString, '[', '\[')
lcString = strtran(lcString, ']', '\]')
lcString = strtran(lcString, '(', '\(')
lcString = strtran(lcString, ')', '\)')
lcString = strtran(lcString, '$', '\$')
return lcString

Friday, December 05, 2008

Updated Blog Page

I added a couple of new gadgets on my blog page today:

  • Links allowing you to subscribe to posts and comments using a variety of means.
  • Members, using the new Google Friend Connect gadget. Feel free to join my site.
  • Ratings, which allows you to rate blog posts.

Tuesday, December 02, 2008

Dynamically Moving Controls

I have a class that provides a file selection control. It consists of a label, a textbox, and a command button. The user can either type the name of the file or click the button to bring up a file dialog.


Obviously, a more descriptive label than "File" would be useful, but it would be painful to manually change the caption and then adjust the textbox so the two don't overlap. To make this dynamic, the class has a cLabelCaption property you can set. That property has an Assign method that calls a custom AdjustControls method. AdjustControls set the caption of the label to the value in cLabelCaption and then changes the Left and Width properties of the textbox to make room for the caption. The Init method also calls AdjustControls so entering a value for cLabelCaption in the Properties window adjusts the controls as well.

The problem is that this didn't always work. Sometimes the label and textbox were sized properly and sometimes the label overlapped the textbox. In tracking this down, I discovered that the problem occurred when cLabelCaption was changed before the form the control is on was visible. Changes to the size of controls isn't applied until the form actually appears. So, my calculations which relied on the Width of the label, which I assumed changed automatically when Caption is changed because AutoSize is .T., didn't work.

The solution is to manually determine the width of the label if the form isn't visible yet. You could use TXTWIDTH(), but as I blogged about a couple of years ago, the GDI+ GdipMeasureString function is more accurate. My SFGDIMeasureString class is a wrapper for that function.

Here's the code I now use in AdjustControls. Note the handling of txtFile.Anchor; any time you manually change the position of a control that has anchoring turned on, you need to do this or anchoring won't work properly for that control.

local loSFGDI

with This
if not empty(.cLabelCaption) and ;
not .lblFile.Caption == .cLabelCaption
.lblFile.Caption = .cLabelCaption
    if not Thisform.Visible
      loSFGDI = newobject('SFGDIMeasureString', ;
      .lblFile.Width = loSFGDI.GetWidth(.lblFile.Caption, ;
        .lblFile.FontName, .lblFile.FontSize, ;
        iif(.lblFile.FontBold, 'B', ''))
endif not Thisform.Visible
   .txtFile.Anchor = 0
  .txtFile.Left   = .lblFile.Width + 5
   .txtFile.Width  = .Width - .txtFile.Left - .cmdGetFile.Width
   .txtFile.Anchor = 10
endif not empty(.cLabelCaption) ...

Determining the First Date in a Week

Here's a simple algorithm to determine the first date in the week a certain date falls in. This is handy for reporting on aggregate values by week, displayed as "Week of SomeDate". The gotcha is that you need to know what day the user thinks of as the first day of a week. Some people consider Sunday to be the first day, others Monday, and so on. The DOW() function can accept a value indicating what day is the first one, from 0 for Sunday to 7 for Saturday. You'll need to ask the user which day they want a week to start from.

In this expression, ldDate is the date from which to determine the first date in the week (for example, a transaction date) and lnFirstDay is the day number for the first day in a week.

ldDate - dow(ldDate, lnFirstDay) + 1

Here's an example that shows the total freight charged by week, with Sunday being the first day of the week.

select OrderDate - dow(OrderDate, 0) + 1 as Week, sum(Freight) from Orders group by 1